Monday, September 12, 2016

Golden gate 12c Trouble shooting and GGSCI Commands

Goldengate 12c Troubleshooting Using LogDump Utility

Oracle GoldenGate Software includes the Logdump utility for viewing data directly from the trail files. Without the Logdump, it is not possible to read the content of the Oracle GoldenGate trail files, as the trail files are in a binary format. With Logdump, we can open up the trail file, read it’s content, navigate through the file, view transactions at different RBA’s (relative byte address – file position), help identify the type of commands (DML or DDL) issued on the source, including delete, insert, update, alter and create statements.

Logdump Commands

Open Logdump

Navigate to the directory where the Oracle GoldenGate Software is installed and execute the Logdump.

[GoldenGate]$ $GG_HOME/logdump

Open a Trail File

To open a trail file and read it’s content, specify the trail file at the logdump prompt. Trail files are usually found in the GoldenGate dirdat directory.

ls -lrt $GG_HOME/dirata

-rw-rw-rw- 1 oracle oinstall 78325 Dec 7 10:38 EE000001
-rw-rw-rw- 1 oracle oinstall 78325 Dec 7 10:42 EE000002
-rw-rw-rw- 1 oracle oinstall 78325 Dec 7 10:55 EE000003

You can also determine the current trail file directory/name by running the “INFO process_name” command at the ggsci prompt.

Open and view the details of local trail file.

Logdump> OPEN ./dirdat/EE000001
Change the file name and location as required.

Set Output Format

Enable the following options so that you are able to view the results in a readable format in your Logdump sessionL

Set trail file header detail on
The FILEHEADER contains the header details of the currently opened trail file.

Logdump> FILEHEADER DETAIL

Record Header
Logdump> GHDR ON
Set Column Details on
It displays the list of columns, their ID, length, Hex values etc.

Logdump> DETAIL ON
User token details
User token is the user defined information stored in trail, associated with the table mapping statements. The CSN (SCN in Oracle Database) associated with the transaction is available in this section.

Logdump> USERTOKEN DETAIL
Set length of the record to be displayed
In this case it is 128 characters.

Logdump> RECLEN 128

Viewing the Records

To view particular records in the trail files, navigate as below in the local trail file.
First record in the trail file
Here “0” is the beginning of the trial file
Logdump> POS 0
Move to a specific record, at a particular RBA
The “xxxx” is the RBA number.
Logdump> POS xxxx
Next record in the opened trail file
Logdump> N
Or
Logdump> NEXT
Moving forward or reverse in the trail file
Logdump> POS FORWARD
or
Logdump> POS REVERSE
Skip certain number of records
Here ‘x’ is the number of records you want to skip.

Logdump> SKIP x
Last record in the trail file
Logdump> POS last

Filter Commands

We can use filter commands to view the specific operations or data records, a record at a specific RBA, the record length, record type, etc. using the commands below.
To start filtration, use the “filter” keyword, followed by include or exclude. These options allow the data to be removed or shown, based on the filter criteria. Then apply other conditions like file name, rectype, iotype etc. Here rectype is record type and iotype is input output type.
There are number of operation we can filter using the Logdump. To view the list of operation types and the number assigned to them, run below command.
Show the Record Types
Logdump> SHOW RECTYPE
Enable or disable filtration

Logdump> FILTER [ ENABLE | DISABLE ]

Filter Records by Table Name

Logdump> FILTER INCLUDE FILENAME CC_APP.IMAGE_DETAIL

Filter Records By Operation Type
Operation types are Insert, Update, and Delete.

Logdump> FILTER INCLUDE IOTYPE INSERT
Filter Records using the operation number
You can specify the IOTYPE by using the equivalent operation number.

Logdump 374> FILTER INCLUDE IOTYPE 160
Logdump 374> N
n
Sample Output:

2013/02/18 00:36:05.000.000 DDLOP Len 1169 RBA 3049
Name:
After Image: Partition 0 G s
2c43 353d 2733 3135 3435 272c 2c42 373d 2733 3135 | ,C5='31545',,B7='315
3735 272c 2c42 323d 2727 2c2c 4233 3d27 5331 272c | 75',,B2='',,B3='S1',
2c42 343d 2754 4553 545f 3132 272c 2c43 3132 3d27 | ,B4='TEST',,C12='
272c 2c43 3133 3d27 272c 2c42 353d 2754 4142 4c45 | ',,C13='',,B5='TABLE
272c 2c42 363d 2743 5245 4154 4527 2c2c 4238 3d27 | ',,B6='CREATE',,B8='
4747 5553 4552 2e47 4753 5f44 444c 5f48 4953 5427 | GGUSER.GGS_DDL_HIST'
2c2c 4239 3d27 5331 272c 2c43 373d 2731 312e 322e | ,,B9='S1',,C7='11.2.
Filtering suppressed 2 records

Note: Here 160 represent DDL operation and in the detail we can see the DDL type like below is “CREATE” and suppressed means number of records skipped to reach next filter value.
View currently applied filters

Logdump> FILTER SHOW

Sample output:
Data filters are ENABLED

Include Match ANY
Rectypes : DDLOP

Exclude Match ANY

Filter on multiple conditions
We can filter the data of trail file using the multiple conditions together.

For that we can string multiple FILTER commands together, separating each one with a semicolon, as shown in the below example:

Logdump>FILTER INCLUDE FILENAME [SCHEMA].[TABLE]; FILTER RECTYPE 5; FILTER INCLUDE IOTYPE INSERT
The above example will display only “5”,” insert” statement records from the specified table.

Note: [SCHEMA] & [TABLE] is the name of the schema and table, and should be in upper case.
Clear the filter in the session
Logdump> FILTER CLEAR

Other Useful Commands

Count of the records in trail file
Logdump> COUNT
Sample Output:
Logtrail /u01/GoldenGate/dirdat/ST000010 has 5 records
Total Data Bytes 2161
Avg Bytes/Record 432
Insert 2
RestartOK 1
DDL 1
Others 1
After Images 4
Average of 4 Transactions
Bytes/Trans ..... 600
Records/Trans ... 1
Files/Trans ..... 1
It will display the count of DDL, DML, DCL (Commit or Rollback) operations, etc.
Display count details
Logdump> COUNT DETAIL
Sample Output of Addition Data:
Partition 0
Total Data Bytes 1194
Avg Bytes/Record 597
RestartOK 1
DDL 1
After Images 2
*FileHeader* Partition 0
Total Data Bytes 931
Avg Bytes/Record 931
Others 1
Search for large transaction
Logdump>TRANSHIST 200
Logdump>TRANSRECLIMIT 50
Logdump>FILTER INCLUDE FILENAME CC_APP.IMAGE_DETAIL
Logdump>COUNT
Previously used commands in the current Logdump session
Logdump> HISTORY
Scan for next good header of record
Logdump> SFH
or
Logdump> SCANFORHEADER
The above command will show the next good header of the record in the trail file.
Sample Output:
2013/02/18 00:36:52.797.000 FileHeader Len 931 RBA 0
Name: *FileHeader*
3000 01c5 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...
0002 3200 0004 2000 0000 3300 0008 02f1 fc23 c46f | ..2... ...3......#.o
2448 3400 0047 0045 7572 693a 6368 642d 706b 6175 | $H4..G.Euri:LOCAL
7368 616c 323a 5345 4153 4941 3a52 4f4f 543a 5345 | MACHINE
4153 4941 434f 4e53 554c 5449 4e47 3a43 4f4d 3a64 | :d
7269 7665 2d44 3a67 6f6c 6465 6e67 6174 6536 0000 | rive-D:GoldenGate6..
1500 1364 3a5c 7465 7374 5c6d 315c 6574 3030 3030 | ... /u01/GoldenGate/dirdat/ST0000
Scan for end of the transaction
Logdump> SCANFORENDOFTRANSACTION
or
Logdump> SFET
Some of the other SCAN options are:
SCANFORRBA
SCANFORTIME
SCANFORTYPE
Open the next trail file
Logdump> NEXTTRAIL
Sample Output:
Logtrail /u01/GoldenGate/dirdat/ST000010 closed
Current Logtrail is /u01/GoldenGate/dirdat/ST000011
For example if we had the trail file ST000010 opened, the NEXTTRAIL command will open the next trailfile, ST000011.

Exiting the Logdump Utility

Logdump> EXIT

Save A Part Of A GoldenGate Trail To A New Trail

We can save the records of trail file to a new trail file

Save all contents of the trail file

Logdump> SAVE [file]
Save the subset of data
Set the filter condition for the table we want data.

Logdump> FILTER EXCLUDE FILENAME [SCHEMA].[TABLE]
Save a subset of records
Logdump> SAVE [file] [n] RECORDS
Note: Here [file] is the name of the new file and [SCHEMA] & [TABLE] is the name of the schema and table, and should be in upper case.

GoldenGate: GGSCI Commands I

GGSCI commands executed at the ggsci line utility, are used to setup, manage, monitor and troubleshoot the different components of the Goldengate Replication configuration. You are able to look at the current status, lag, database versions, previous commands run, add/delete processes, etc.

Some of the more interesting Goldengate commands are below.

GGSCICommand -> history
GGSCI (proddb01) 44> history

GGSCI Command History

35: info PMP02 detail
36: info all
37: info PMP02 detail
38: info all
39: info all
40: history
41: view report EXT02
42: history
43: view report EXT02 detail
44: history

GGSCI (proddb01) 45> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING PMP02 00:00:00 00:00:01
EXTRACT RUNNING EXT02 00:00:00 00:00:01
REPLICAT RUNNING RHA02 00:00:00 00:00:03

GGSCICommand -> !

To rerun the previous command use “!”

GGSCI (proddb01) 46> !
info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING PMP02 00:00:00 00:00:06
EXTRACT RUNNING EXT02 00:00:00 00:00:06
REPLICAT RUNNING RHA02 00:00:00 00:00:08

To run a specific command from the history use “!” with the command line number.

GGSCI (proddb01) 47> !42
history

GGSCI Command History

38: info all
39: info all
40: history
41: view report EXT02
42: history
43: view report EXT02 detail
44: history
45: info all
46: info all
47: history

GGSCICommand -> versions

VERSION: You can view this to view the version of the OS, host info and the database version.

GGSCI (proddb02) 3> versions
Operating System:
SunOS
Version Generic_147440-01, Release 5.10
Node: proddb02
Machine: sun4u

Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

GGSCICommand -> view report

REPORTS: View the reports for specific processes.
GGSCI (proddb01) 49> view report EXT02

Opened new report file at 2013-03-12 00:01:00.

***********************************************************************
** Run Time Messages **
***********************************************************************

2013-03-12 02:43:12 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p21775_extr: start=SeqNo: 17632, RBA: 21008, SCN: 0.1148
5118 (11485118), Timestamp: 2013-03-12 02:41:35.000000, Thread: 1, end=SeqNo: 17632, RBA: 21504, SCN: 0.11485118 (11485118), Timestamp: 2013-03-1
2 02:41:35.000000, Thread: 1.

2013-03-12 06:43:20 INFO OGG-01738 BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p21775_extr: start=SeqNo: 17646, RBA: 18448, SCN: 0.1149
0824 (11490824), Timestamp: 2013-03-12 06:42:08.000000, Thread: 1, end=SeqNo: 17646, RBA: 18944, SCN: 0.11490824 (11490824), Timestamp: 2013-03-1
2 06:42:08.000000, Thread: 1.

GGSCICommand -> show all

Use the “show” command to look at the configuration info on the different processes.

GGSCI (proddb01) 50> show

Parameter settings:

SET SUBDIRS ON
SET DEBUG OFF

Current directory: /u01/app/ha/ggs

Using subdirectories for all process files

Editor: vi

Reports (.rpt) /u01/app/db01/ggs/dirrpt
Parameters (.prm) /u01/app/db01/ggs/dirprm
Stdout (.out) /u01/app/db01/ggs/dirout
Replicat Checkpoints (.cpr) /u01/app/db01/ggs/dirchk
Extract Checkpoints (.cpe) /u01/app/db01/ggs/dirchk
Process Status (.pcs) /u01/app/db01/ggs/dirpcs
SQL Scripts (.sql) /u01/app/db01/ggs/dirsql
Database Definitions (.def) /u01/app/db01/ggs/dirdef

GGSCI (proddb01) 52> show all

Parameter settings:

SET SUBDIRS ON
SET DEBUG OFF

Current directory: /u01/app/ha/ggs

Using subdirectories for all process files

Editor: vi

GoldenGate: GGSCI Commands II

GGSCI Commands : Manager

GGSCI (xgoldengate01) 1> status manager
Manager is running (IP port xgoldengate 7833).

GGSCI (xgoldengate01) 2> status extract XINT1
EXTRACT XINT1: RUNNING

GGSCI (xgoldengate01) 3> status replicat RINT1
REPLICAT RINT1: RUNNING

GGSCI (xgoldengate01) 4> status ER *
REPLICAT RINT1: RUNNING
EXTRACT XINT1: RUNNING

GGSCI Commands: Info

• manager

• extract .., detail

• replicat ..,detail

• all

• ER

• exttrail *

• rmttrail *

GGSCI (xgoldengate01) 1> info manager

Manager is running (IP port xgoldengate01.7833).

GGSCI (xgoldengate01) 2> info extract XINT1, detail

EXTRACT XINT1 Last Started 2013-04-25 16:16 Status RUNNING
Checkpoint Lag 00:00:22 (updated 18:38:35 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2013-04-25 16:15:57
SCN 0.0 (0)

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB
./dirdat/xn 8 1056 100

Extract Source Begin End
/oracle/oradata/onlinelog/kf_7m2wbtv7_.log * Initialized * 2013-04-24 11:20
/oracle/oradata/onlinelog/kf_7m2wbtv7_.log 2013-04-24 11:19 2013-04-24 11:20
/oracle/oradata/onlinelog/kf_7m2w9bbb_.log 2013-04-24 10:48 2013-04-24 10:59
/oracle/oradata/onlinelog/kf_7m2wdkok_.log 2013-04-24 10:48 2013-04-24 10:48

Current directory /u01/app/ggs

Report file /u01/app/ggs/dirrpt/XINT1.rpt
Parameter file /u01/app/ggs/dirprm/XINT1.prm
Checkpoint file /u01/app/ggs/dirchk/XINT1.cpe
Process file /u01/app/ggs/dirpcs/XINT1.pce
Stdout file /u01/app/ggs/dirout/XINT1.out
Error log /u01/app/ggs/ggserr.log

GGSCI (xgoldengate01) 3> info replicat RINT1, detail

REPLICAT RINT1 Last Started 2013-04-25 15:48 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/ps000000
2013-04-25 15:48:39.000000 RBA 0

Extract Source Begin End

/u01/app/ggs/dirdat/ps000000 2013-04-25 15:48 2013-04-25 15:48
/u01/app/ggs/dirdat/ps000000 * Initialized * 2013-04-25 15:48

Current directory /u01/app/ggs

Report file /u01/app/ggs/dirrpt/RINT1.rpt
Parameter file /u01/app/ggs/dirprm/rINT1.prm
Checkpoint file /u01/app/ggs/dirchk/RINT1.cpr
Checkpoint table GGS_CHECKPOINT
Process file /u01/app/ggs/dirpcs/RINT1.pcr
Stdout file /u01/app/ggs/dirout/RINT1.out
Error log /u01/app/ggs/ggserr.log

GGSCI (xgoldengate01) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING XINT1 00:00:00 00:00:01
EXTRACT RUNNING PINT1 00:00:02 00:00:04
REPLICAT RUNNING RINT1 00:00:00 00:00:02

GGSCI (xgoldengate01) 5> info ER *

EXTRACT PINT1 Last Started 2013-04-25 14:26 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/xn000008
2013-04-25 16:16:19.555173 RBA 1056

EXTRACT XINT1 Initialized 2013-04-25 17:18 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2013-04-25 17:18:55
SCN 0.0 (0)

REPLICAT RINT1 Last Started 2013-04-25 15:48 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint File /u01/app/ggs/dirdat/ps000000
2013-04-25 15:48:39.000000 RBA 335

GGSCI (xgoldengate01) 6> info exttrail *

Extract Trail: /u01/app/ggs/dirdat/pi
Extract: PINT1
Seqno: 3
RBA: 1227
File Size: 100M

Extract Trail: ./dirdat/xn
Extract: XINT1
Seqno: 3
RBA: 167
File Size: 100M

GGSCI (xgoldengate01) 7> info rmttrail *

Extract Trail: /u01/app/ggs/dirdat/pi
Extract: PINT1
Seqno: 3
RBA: 1227
File Size: 100M

Extract Trail: ./dirdat/xn
Extract: XINT1
Seqno: 3
RBA: 167
File Size: 100M

Stats

• extract ..

• replicat

• ER

GGSCI (xgoldengate01) 1> stats XINT1

Sending STATS request to EXTRACT XINT1 ...

Start of Statistics at 2013-04-26 11:16:54.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***
Operations 955.00
Mapped operations 12.00
Unmapped operations 932.00
Other operations 11.00
Excluded operations 943.00
......

GGSCI (xgoldengate01) 2> stats replicat RINT1

Sending STATS request to REPLICAT RINT1 ...
......
*** Latest statistics since 2013-04-09 14:55:12 ***
Total inserts 80.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 80.00
......

GGSCI (xgoldengate01) 3> stats ER *

......

Output to /u01/app/ggs/dirdat/p1:

Extracting from GGS_APP.COM_AUDIT_TRAIL to GGS_APP.COM_AUDIT_TRAIL:

*** Total statistics since 2013-02-28 08:30:48 ***
Total inserts 35028.00
Total updates 0.00
Total deletes 64139.00
Total discards 0.00
Total operations 99167.00
........

GoldenGate: OGG Commands III

Cataloged here are some very important, common commands that are used via the GGSCI interface during the GoldenGate configuration and for troubleshooting.

INFO
INFO MANAGER	Provides details of the Manager process
INFO MGR	Also provides details of the Manager process
STATUS MANAGER	This command also display the info of manager
REFRESH
REFRESH MANAGER	Reloads from the Manager Parameter file
REFRESH MGR	Reloads from the Manager Parameter file
SEND
SEND MANAGER CHILDSTATUS	Displays status of processes, started by Manager.
SEND MANAGER CHILDSTATUS DEBUG	Return the ports numbers allocated by the Manager
SEND MANAGER GETPORTINFO	Displays the list of currently allocated ports by Manager process
SEND MANAGER GETPORTINFO DETAIL	Provides info on ports and process assigned to them.
SEND MANAGER GETPURGEOLDEXTRACTS	Retrieves trail purge retention info.
START
START MANAGER	Starts the Manager Process
START MGR	Starts the Manager Process
STOP
STOP MGR	Stops the Manager Process
STOP MANAGER !	Stops Manager without asking for user confirmation.
STOP MGR !	Stops Manager without asking for user confirmation.
ADD Command	Creates an Extract group.
ADD EXTRACT E_SRC, Tranlog, Begin Now	Used to specify transaction logs as data source for extract.
ADD EXTRACT E_SRC, Begin Now, Passive	Specifies the extract to be run in passive mode.
ADD EXTRACT E_SRC, Extseqno 000008 Extrba 287458, Begin Now	Specifies the extract process starting position
ADD EXTRACT E_SRC, SOURCEISTABLE	Extracts data from data tables for initial loading.

ALTER Command
ALTER EXTRACT e_src, BEGIN NOW	Instructs extract to start processing
ALTER EXTRACT e_src, BEGIN 2013-04-16	Instructs extract to start processing from specific date
ALTER EXTRACT e_src, ETROLLOVER	Extract rolls over to next trail file
ALTER EXTRACT e_src, EXTSEQNO 01, EXTRBA 2887	Alters extract to start from the specific locaton in the trail
ALTER EXTRACT e_src, THREAD 4, BEGIN 2012-03-09	Alters extract thread & start date for RAC
ALTER EXTRACT e_src, LSN 1234:123:1	Altering extract for the SQL Server

CLEANUP Command	It is used to clear the run history for the specific extract group
CLEANUP EXTRACT e_src	It purges all history of records except last
CLEANUP EXTRACT e_src, SAVE 10	It saves last 10 records and deletes all other

DELETE Command	It is used to delete the extract process, its checkpoints detail and unregister the extract group.

DELETE EXTRACT e_src	deletes the extract process
DELETE EXTRACT e*	deletes all extract process whose name starts with e
DELETE EXTRACT e* !	deletes all extract process whose name starts with e without prompting

INFO Command	It is used to display the info of the extract like its status, lag, checkpoint, run history, trail info etc
INFO EXTRACT e_src, SHOWCH	Display checkpoint info of extract
INFO EXTRACT e_src, DETAIL	Display trail info, run history
INFO EXTRACT e_src, TASKS	Display extract tasks

KILL Command	It is used to kill the extract that can't be stopped with STOP Command""
KILL EXTRACT e_src

LAG Command	It is used to find the lag time between Extract and data source more precisely than the "INFO Command"
LAG EXTRACT e_src	To find lag for extract "e_src"
LAG EXTRACT *	To find lag for all of the extract processes

REGISTER Command	It is used to register the extract process, so that it can retain the archive logs required for its recovery.
REGISTER EXTRACT e_src LOGRETENTION	To register extract "e_src"

SEND Command	It is used to communicate with the running extract like sending requests for report creation, stats, to force extract to rollover to next trail etc
SEND EXTRACT finance, ROLLOVER	To increment the extract to next file in trail
SEND EXTRACT finance, STOP	To stop the extract process
SEND EXTRACT finance, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20	For the Oracle RAC, specify the time after which the OGG scan and delete the orphan transactions
SEND EXTRACT finance, SKIPTRANS 5.17.27634 THREAD 2	For skipping the transaction in the Oracle RAC environment
SEND EXTRACT e_src, SHOWTRANS	Display the info about the open transactions like checkpoint, extract group name, SCN, Redo log and RB, status etc
SEND EXTRACT e_src, SHOWTRANS COUNT 2	Display the info for two transactions only

START Command	It is used to start the Extract process
START EXTRACT e_src

STATS Command	It is used to display the stats for the extract process including the DDL and DML operations.
STATS e_src
STATS EXTRACT e_src	stats will be displayed for the extract E_SRC""
STATS EXTRACT e_src REPORTRATE SEC	display the stats for the fetch opertions per sec
STATS EXTRACT e_src, TOTAL, DAILY	The total stats is shown since the start of the day
STATS EXTRACT e_src, TOTAL, HOURLY, REPORTRATE MIN, RESET, REPORTFETCH	By using comma between the keywords multiple options can be used for the stats command

STATUS Command	It is used to check whether extract process is currently running or not.
STATUS EXTRACT e_src	To check the status for extract "E_SRC"
STATUS EXTRACT e*	To check the status for all extracts starting with "e"

STOP Command	It is used to stop the running extract process
STOP EXTRACT e_src	To stop the extract "e_src"
STOP EXTRACT e*	To stop all of the running extract process whose name start with "e"
STOP EXTRACT *	To stop all of the running extract processes

UNREGISTER Command	it is used to unregister the extract group by removing its registration from oracle DB.
UNREGISTER EXTRACT e_src LOGRETENTION	To unregister the extract e_src""

ADD Command	It is used to create the replicat process by creating checkpoints
ADD REPLICAT INITLOAD, SPECIALRUN	It will create a special run replicat as task.
ADD REPLICAT r_trg, EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TT	Create the replicat with the trail
ADD REPLICAT r_trg, EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TT, CHECKPOINTTABLE OGG_USER.OGG_CHECKPOINT	Create the replicat with the trail and the checkpoint info like the DB table used to save checkpoint info.
ADD REPLICAT r_trg, EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TT, NODBCHECKPOINT	Create the replicat with the trail and specifying that this replicat did'nt write info to DB table

ALTER Command	It is used to change the properties of the existing replicat process
ALTER REPLICAT r_trg, BEGIN NOW	Alter replicat to start processing from now
ALTER REPLICAT r_trg, BEGIN 2013-05-11	Alter replicat to start processing from specific date
ALTER REPLICAT r_trg, BEGIN 2013-01-07 08:00:01	Alter replicat to start processing from specific date and time
ALTER REPLICAT r_trg, EXTSEQNO 00007	Alter replicat to start from the specific trail file
ALTER REPLICAT r_trg, EXTRBA 65477	Alter replicat to start from the specific location in the trail

CLEANUP Command	It is used to clear the run history for the specific replicat process
CLEANUP REPLICAT r_trg	It purges all history of records except last
CLEANUP REPLICAT r_trg, SAVE 10	It saves last 10 records and deletes all other
DELETE Command	It is used to delete the replicat process, by removing the checkpoints and freeing trail file for purging by manager. But the process must be stopped and user must be logged in using DBlogin command
DELETE REPLICAT r_trg	deletes the replicat process
DELETE EXTRACT r*	deletes all replicat process whose name starts with r
DELETE EXTRACT r* !	deletes all replicat process whose name starts with r without prompting

INFO Command	It is used to display the replicat info like processing history, status, appox lag, trail info, checkpoint info and environment of replicat.
INFO REPLICAT *, TASKS	It is used to display replicat tasks only.
INFO REPLICAT r_trg	It is used to display the information of replicat.
INFO REPLICAT r_trg, DETAIL	It display the detailed information of replicat.
INFO REPLICAT r_trg, SHOWCH	It displays the checkpoint table information, from checkpoint file and checkpoint table.

KILL Command	It is used to kill the replicat that can't be stopped with "STOP Command", but checkpoint info remain unchanged and transactions are rolled back in DB
KILL REPLICAT r_trg

LAG Command	It is used to find the lag time between Replicat and trail more precisely than the "INFO Command"
LAG REPLICAT r_trg	To find lag for replicat "r_trg"
LAG REPLICAT *	To find lag for all of the replicat processes

SEND Command	It is used to communicate with the running replicat process
SEND REPLICAT r_trg, HANDLECOLLISIONS	Enable the handlecollisions option of OGG used for error handling
SEND REPLICAT r_trg, REPORT HANDLECOLLISIONS r_*	Generate statical report to replicat report file
SEND REPLICAT r_trg, GETLAG	Get the lag info in seconds

START Command	To start the replicat process
START REPLICAT r_trg
START REPLICAT r_trg, ATCSN 6454388	To start the replicat process from the oracle-specific CSN number including the CSN no transaction
START REPLICAT r_trg, AFTERCSN 6454389	To start the replicat process from the oracle-specific CSN number, but ignoring that CSN no transaction

STATS Command	It is used to display the stats for the replicat process.
STATS r_trg
STATS REPLICAT r_trg	stats will be displayed for the REPLICAT r_trg""
STATS REPLICAT r_trg REPORTDETAIL SEC	display the stats for the opertaion that were not replicated due to errors
STATS REPLICAT r_trg, TOTAL, DAILY	The total stats is shown since the start of the day
STATS REPLICAT r_trg, TOTAL, HOURLY, REPORTRATE MIN, RESET, NOREPORTDETAIL	By using comma between the keywords multiple options can be used for the stats command

STATUS Command	It is used to find whether replicat is running or not
STATUS REPLICAT r_trg	To check the status for replicat "r_trg"
STATUS REPLICAT r*	To check the status for all replicat starting with "r"

STOP Command	It is used to stop the running replicat process
STOP REPLICAT r_trg	To stop the replicat "r_trg"
STOP REPLICAT r*	To stop all of the running replicat process whose name start with "r"
STOP REPLICAT *	To stop all of the running replicat processes
ER Commands	They are used to control the multiple extract and replicat processes
INFO ER *	To info all of the process
KILL ER *	To kill all of the process
LAG ER *	To get lag info of all the process
SEND ER *	To use send command on all of the process
START ER *	To start all of the process
STATS ER *	To check the stats all of the process
STATUS ER *	To find status of all the process
STOP ER *	To stop all of the process

ADD EXTTRAIL	It is used to create the local trail file for extract process on local system
ADD EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/SE, EXTRACT e_src, MEGABYTES 100	Create EXTTRAIL with the Prefix"SE", and the size of 100 mb
ADD EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/SE000009	To create the EXTTRAIL with specific sequence number

ADD RMTTRAIL	It is used to create the remote trail files for the extract or pump processes on remote systems
ADD RMTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TE, EXTRACT p_src, MEGABYTES 100	Create RMTTRAIL with the Prefix"TE", and the size of 100 mb
ADD RMTTRAIL /ORACLE/GOLDENGATE/DIRDAT/SE000009	To create the RMTTRAIL with specific sequence number

ALTER EXTTRAIL	It is used to change the options of the existing EXTTRAIL file for extract process on local system
ALTER EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/SE, EXTRACT e_src, MEGABYTES 50

ALTER RMTTRAIL	It is used to change the options of the existing RMTTRAIL file of extract or pump processes on remote systems
ALTER RMTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TE, EXTRACT p_src, MEGABYTES 50

DELETE EXTTRAIL	It is used to delete the exttrail assigned to the extract on local system by deleting its references from checkpoint file
DELETE EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/SE

DELETE RMTTRAIL	It is used to delete the exttrail for the extract or pump on remote system by deleting its references from checkpoint file
DELETE RMTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TE

INFO EXTTRAIL	It is used to display the info of local trail like name, associated extract, rba and file size etc
INFO EXTTRAIL /ORACLE/GOLDENGATE/DIRDAT/SE	Display info for specific exttrails
INFO EXTTRAIL *	Display info for all exttrails

INFO RMTTRAIL	It is used to display the info of remote trail like name, associated extract, rba and file size etc
INFO RMTTRAIL /ORACLE/GOLDENGATE/DIRDAT/TE	Display info for specific rmttrails
INFO RMTTRAIL *	Display info for all rmttrails

Oracle Database Upgrade Interview Questions?

1)For database projects, what are the biggest challenge?

Downtime for upgrade is a biggest challenge.

2)What kind of upgrade project you have done so far?

I have work for almost all type of upgrade: RAC and non-RAC . OLTP and DW .Small database and large database.

3)What were the size of database?

From 1 TB -15 TB

4)How long it will take to upgrade 2 nodes RAC database of 10 TB size?

Basic upgrade is independent of volume of database but depend mostly on number of components.

5)Can you please explain some of the issue you faced for DB upgrade?

Performance issue after upgrade is a major concern.

Error related to timezone

6)What are the different method for upgrading DB?

Manually or using DBUA.

7)Which one you prefer and why?

I generally prefer manual .Reason: to have more control over the steps of upgrade.

8)One major difference between upgrading DB manual vs DBUA?

Manual process : we need to set cluster_database=FALSE but when upgrading via DBUA cluster_database=TRUE

9)One parameter which once set, cant be revert back but need to recover database from last backup.

COMPATIBLE

10)In which months oracle release CPU patches?
JAN, APR, JUL, OCT

11) In which mode you will start your database for upgrading from 10x to 11x?

Startup upgrade

12)Why not in startup migrate?

startup migrate was used to upgrade a database till 9i.But from 10G we are using startup upgrade to upgrade database.

13)Can we apply PSU on database without applying PSU on grid in 11.2.0.4?

No, unfortunately, you need to apply the PSU patches to both the grid-home and the rdbms-home as sometimes these patches contain fixes for a bug in both homes.

Tuesday, September 6, 2016

RMAN Incremental Backups

RMAN Incremental Backups (Level 0 & Level 1)

The goal of an incremental backup is to backup only those data blocks that have changed
since a previous backup.

RMAN incremental backups backup only datafile blocks that have changed since
previous backup. We can use RMAN to create incremental backups of datafiles, tablespaces,
or the whole database.

The primary reasons for making incremental backups, part of our strategy are:

For use in a strategy based on incrementally updated backups, where these incremental

backups are used to periodically roll forward an image copy of the database

To reduce the amount of time needed for daily backups
To save network bandwidth when backing up over a network
To get adequate backup performance when the aggregate tape bandwidth available for

tape write I/Os is much less than the aggregate disk bandwidth for disk read I/Os

To be able to recover changes to objects created with the NOLOGGING option. For example,

direct load inserts do not create redo log entries and their changes cannot be reproduced with

media recovery. They change data blocks and so are captured by incremental backups.

To reduce backup sizes for NOARCHIVELOG databases. Instead of making a whole

database backup every time, we can make incremental backups.

As with full backups, if you are in ARCHIVELOG mode, you can make incremental backups
if the database is open; if the database is in NOARCHIVELOG mode, then you can only make
incremental backups after a consistent shutdown.

One effective strategy is to make incremental backups to disk, and then backup the resulting
backup sets to a media manager with BACKUP AS BACKUPSET. The incremental backups are
generally smaller than full backups, which limits the space required to store them until they
are moved to tape. Then, when the incremental backups on disk are backed up to tape, it is
more likely that tape streaming can be sustained because all blocks of the incremental backup
are copied to tape. There is no possibility of delay due to time required for RMAN to locate
changed blocks in the datafiles.

RMAN backups can be classified in these ways:

Full or incremental
Open or closed
Consistent or inconsistent

Note that these backup classifications apply only to datafile backups. Backups of other files,
such as archivelogs and control files, always include the complete file and are never inconsistent.

Backup Type	Definition
Full	A backup of a datafile that includes every allocated block in the file being backed up. A full backup of a datafile can be an image copy, in which case every data block is backed up. It can also be stored in a backup set, in which case datafile blocks not in use may be skipped. A full backup cannot be part of an incremental backup strategy; that is, it cannot be the parent for a subsequent incremental backup.
Incremental	An incremental backup is either a level 0 backup, which includes every block in the file except blockscompressed out because they have never been used, or a level 1 backup, which includes only those blocksthat have been changed since the parent backup was taken. A level 0 incremental backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.
Open	A backup of online, read/write datafiles when the database is open.
Closed	A backup of any part of the target database when it is mounted but not open. Closed backups can be consistent or inconsistent.
Consistent	A backup taken when the database is mounted (but not open) after a normal shutdown. The checkpointSCNs in the datafile headers match the header information in the control file. None of the datafiles has changes beyond its checkpoint. Consistent backups can be restored without recovery. Note: If you restore a consistent backup and open the database in read/write mode without recovery, transactions after the backup are lost. You still need to perform an OPEN RESETLOGS.
Inconsistent	A backup of any part of the target database when it is open or when a crash occurred or SHUTDOWN ABORT was run prior to mounting. An inconsistent backup requires recovery to become consistent.

During media recovery, RMAN examines the restored files to determine whether it can recover them with an incremental backup. If it has a choice, then RMAN always chooses incremental backups over archived logs, as applying changes at a block level is faster than reapplying individual changes.

RMAN does not need to restore a base incremental backup of a datafile in order to apply incremental backups to the datafile during recovery. For example, we can restore non-incremental image copies of the datafiles in the database, and RMAN can recover them with incremental backups.

Incremental Backup Algorithm

Each data block in a datafile contains a system change number (SCN), which is the SCN at which the most recent change was made to the block. During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN of the parent incremental backup. If the SCN in the input data block is greater than or equal to the checkpoint SCN of the parent, then RMAN copies the block.

Note that if you enable the block change tracking feature, RMAN can refer to the change tracking file to identify changed blocks in datafiles without scanning the full contents of the datafile. Once enabled, block change tracking does not alter how we take or use incremental backups, other than offering increased performance.

Level 0 and Level 1 Incremental Backups

Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

A level 1 incremental backup can be either of the following types:

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Incremental backups are differential by default.

Note:

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

The size of the backup file depends solely upon the number of blocks modified and the incremental backup level.

The following commands performs a level 0 backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

Differential Incremental Backups

In a differential level 1 backup, RMAN backs up all blocks that have changed since the most recent cumulative or differential incremental backup, whether at level 1 or level 0. RMAN determines which level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, RMAN copies all blocks changed since the level 0 backup.

The following command performs a level 1 differential incremental backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN. If compatibility <10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup, to be consistent with the behavior in previous releases.

Source: Oracle Website

In the example shown in above Figure, the following occurs:

Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

Monday - Saturday

On each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

The cycle is repeated for the next week.

Cumulative Incremental Backups

In a cumulative level 1 backup, RMAN backs up all the blocks used since the most recent level 0 incremental backup. Cumulative incremental backups reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups require more space and time than differential backups, because they duplicate the work done by previous backups at the same level.

The following command performs a cumulative level 1 incremental backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;

Source: Oracle Website

In the example shown in Figure, the following occurs:

Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

Monday - Saturday

A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.

The cycle is repeated for the next week.

Basic Incremental Backup Strategy

Choose a backup scheme according to an acceptable MTTR (mean time to recover). For example, you can implement a three-level backup scheme so that a full or level 0 backup is taken monthly, a cumulative level 1 is taken weekly, and a differential level 1 is taken daily. In this scheme, you never have to apply more than a day's worth of redo for complete recovery.

When deciding how often to take full or level 0 backups, a good rule of thumb is to take a new level 0 whenever 50% or more of the data has changed. If the rate of change to your database is predictable, then you can observe the size of your incremental backups to determine when a new level 0 is appropriate.

The following query displays the number of blocks written to a backup set for each datafile with at least 50% of its blocks backed up:

SQL> SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5 ORDER BY COMPLETION_TIME;

Compare the number of blocks in differential or cumulative backups to a base level 0 backup. For example, if you only create level 1 cumulative backups, then when the most recent level 1 backup is about half of the size of the base level 0 backup, take a new level 0.

Making Incremental Backups: BACKUP INCREMENTAL

After starting RMAN, run the BACKUP INCREMENTAL command at the RMAN prompt. This example makes a level 0 incremental backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

This example makes a differential level 1 backup of the SYSTEM tablespace and datafile tools01.dbf. It will only backup those data blocks changed since the most recent level 1 or level 0 backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 TABLESPACE SYSTEM DATAFILE 'ora_home/oradata/trgt/tools01.dbf';

This example makes a cumulative level 1 backup of the tablespace users, backing up all blocks changed since the most recent level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE TABLESPACE users;

Incrementally Updated Backups: Rolling Forward Image Copy Backups

Oracle's Incrementally Updated Backups feature avoid the overhead of taking full image copy backups of datafiles, while providing the same recovery advantages as image copy backups.

At the beginning of a backup strategy, RMAN creates an image copy backup of the datafile. Then, at regular intervals, such as daily, level 1 incremental backups are taken, and applied to the image copy backup, rolling it forward to the point in time when the level 1 incremental was created.

During restore and recovery of the database, RMAN can restore from this incrementally updated copy and then apply changes from the redo log, with the same results as restoring the database from a full backup taken at the SCN of the most recently applied incremental level 1 backup.

A backup strategy based on incrementally updated backups can help minimize time required for media recovery of your database. For example, if you run scripts to implement this strategy daily, then at recovery time, you never have more than one day of redo to apply.

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5 ORDER BY COMPLETION_TIME;