Few days back we got below error on few instance of GG.
2013-06-07 01:25:06 WARNING OGG-01930 Oracle GoldenGate Delivery for Oracle, dummy1.prm: Datastore error in 'dirbdb': BDB0113 Thread/process 17080/1125251392 failed: BDB1507 Thread died in Berkeley DB library.
Later found that one of the Teammate is trying to configure Monitoring using OEM plugin for OGG.
If your version of GoldenGate from Version 11.2.0.0.0 to 11.2.1.0.4 then You must upgrade till OGG 11.2.1.0.5_02 and re-create datastore.
you may found more reference document on.
GoldenGate Monitor: BDB0060 BDB1581 BDB1582 BDB1507 BDB0118 BDB2027 BDB0113 BDB0087 Errors In Datastore [ID 1495998.1]
Friday, June 7, 2013
Wednesday, May 8, 2013
Goldengate Implementation Automated.........
Coming soon....
Rac enabled , supported on 10.2.0.4 to 11.2.0.3 .
script is based on 2 method.
Menu-based user interactive and semi-automatic.
Rac enabled , supported on 10.2.0.4 to 11.2.0.3 .
script is based on 2 method.
Menu-based user interactive and semi-automatic.
Friday, April 26, 2013
Goldengate ArchiveLog delete issue when Extract is registered.
When you register extract on source database using Log retention. It marks scn from that point.
2013-02-07 21:53:51 INFO OGG-01749 Oracle GoldenGate Command Interpreter for Oracle: Successfully registered EXTRACT XCOMTE to start managing log retention at SCN 10855583972257.
After it registered , when you try to delete Archivelog using RMAN you get below errors.
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file
This mostly occurs on 10.2.0.4 to 11.2.0.2, it also happens due to Orphan Logminer sessions.
You may refer below links.
Why is RMAN Not Purging Archive Logs After Backup Since OGG was Configured? [ID 1351352.1]
It is also recommend to apply Patch on database.
Important: To support RMAN log retention on Oracle RAC, you must download and install the database patch that is provided in BUGFIX 11879974, before you add the Extract groups.
2013-02-07 21:53:51 INFO OGG-01749 Oracle GoldenGate Command Interpreter for Oracle: Successfully registered EXTRACT XCOMTE to start managing log retention at SCN 10855583972257.
After it registered , when you try to delete Archivelog using RMAN you get below errors.
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file
This mostly occurs on 10.2.0.4 to 11.2.0.2, it also happens due to Orphan Logminer sessions.
You may refer below links.
Why is RMAN Not Purging Archive Logs After Backup Since OGG was Configured? [ID 1351352.1]
It is also recommend to apply Patch on database.
Important: To support RMAN log retention on Oracle RAC, you must download and install the database patch that is provided in BUGFIX 11879974, before you add the Extract groups.
Wednesday, April 24, 2013
Incorrect Parallel degree calculated when Auto DOP used in 11.2.0.3 Exadata.
Links to this post
Labels:
AUTO DOP,
Exadata,
Exadata parallelism
This may be bug in 11.2.0.3 on exadata, AUTO DOP calculate Incorrect Parallel degree, when parallel hints are used with Auto DOP.
SQL> explain plan for
SELECT /*+PARALLEL(E)*/ SITE_ID, ITEM_NUMBER, SERIAL_NUMBER, PORT_NUMBER, EQUIPMENT_ADDRESSABLE, TRIM(STATUS_DATE)
STATUS_DATE, QUALITY_ASSURANCE_CODE, TRIM(QUALITY_ASSURANCE_DATE) QUALITY_ASSURANCE_DATE, EQUIPMENT_ADDRESS,
EQUIPMENT_OVERRIDE_ACTIVE, INITIALIZE_REQUIRED, PARENTAL_CODE, TEMP_ENABLED, TRIM(TRANSMISSION_DATE) TRANSMISSION_DATE,
DNS_NAME, IP_ADDRESS, FQDN, LOCAL_STATUS, TRIM( LOCAL_STATUS_DATE) LOCAL_STATUS_DATE, SERVER_ID, SERVER_STATUS,
TRIM(SERVER_STATUS_DATE) SERVER_STATUS_DATE, EQUIP_DTL_STATUS, PORT_CATEGORY_CODE, HEADEND, ACCOUNT_NUMBER,
SUB_ACCOUNT_ID, VIDEO_RATING_CODE, PORT_TYPE, SERVICE_CATEGORY_CODE, SERVICE_OCCURRENCE, CREATED_USER_ID, TRIM(DATE_CREATED)
DATE_CREATED, LAST_CHANGE_USER_ID, TRIM(LAST_CHANGE_DATE) LAST_CHANGE_DATE, CABLE_CARD_ID, JOURNAL_DATE
FROM CLE_DUMMY_DETAIL E
WHERE JOURNAL_DATE >= (SELECT LAST_RUN_DATE FROM ETL_SITE_RUN_DATE@ODS.WORLD
WHERE TABLE_NAME = 'xyz' AND SITE_NAME = 'CLE' )
AND JOURNAL_DATE <= (SELECT HEART_BEAT_DATE FROM ETL_SITE_RUN_DATE@ODS.WORLD
WHERE TABLE_NAME = 'xyz' AND SITE_NAME = 'CLE' )
;
Explained.
SQL> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 350686579
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1158 | 203K| 18 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1158 | 203K| 16 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1158 | 203K| 16 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| CLE_CDO1CPP | 1158 | 203K| 16 (0)| 00:00:01 | Q1,00 | PCWP | |
| 5 | REMOTE | ETL_SITE_RUN_DATE | 1 | 47 | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | REMOTE | ETL_SITE_RUN_DATE | 1 | 47 | 1 (0)| 00:00:01 | PODS_~ | R->S | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("JOURNAL_DATE">= (SELECT "LAST_RUN_DATE" FROM "ETL_SITE_RUN_DATE" WHERE "SITE_NAME"='CLE' AND
"TABLE_NAME"='xyz') AND "JOURNAL_DATE"<= (SELECT "HEART_BEAT_DATE" FROM "ETL_SITE_RUN_DATE"
WHERE "SITE_NAME"='CLE' AND "TABLE_NAME"='xyz'))
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "SITE_NAME","TABLE_NAME","HEART_BEAT_DATE" FROM "ETL_SITE_RUN_DATE" "ETL_SITE_RUN_DATE" WHERE
"SITE_NAME"='CLE' AND "TABLE_NAME"='xyz' (accessing 'PODS_ETL_CONTROL.WORLD' )
Note
-----
- Degree of Parallelism is 32767 because of hint
31 rows selected.
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ -----------
parallel_adaptive_multi_user FALSE
parallel_automatic_tuning FALSE
parallel_degree_limit 8
parallel_degree_policy AUTO
parallel_execution_message_size 16384
parallel_force_local FALSE
parallel_io_cap_enabled FALSE
parallel_max_servers 196
parallel_min_percent 0
parallel_min_servers 2
parallel_min_time_threshold 60
parallel_server TRUE
parallel_server_instances 5
parallel_servers_target 90
parallel_threads_per_cpu 2
recovery_parallelism 0
FileName
----------------
Tested with without DBLINK.
-- Testing this on 11.2.0.3
SQL> select /*+ Parallel(E) */ * from foo e; Execution Plan ---------------------------------------------------------- Plan hash value: 3135133324 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18M| 1708M| 9922 (1)| 00:00:01| | | | | 1 | PX COORDINATOR | | | | || | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 18M| 1708M| 9922 (1)| 00:00:01| Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 18M| 1708M| 9922 (1)| 00:00:01| Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| FOO | 18M| 1708M| 9922 (1)| 00:00:01| Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Note ----- - Degree of Parallelism is 32767 because of hint SQL> connect / as sysdba Connected. SQL> select count(*) from foo; COUNT(*) ---------- 18472960 parallel_adaptive_multi_user TRUE parallel_automatic_tuning FALSE parallel_degree_limit 2 parallel_degree_policy AUTO parallel_execution_message_size 16384 parallel_force_local FALSE parallel_instance_group parallel_io_cap_enabled FALSE parallel_max_servers 135 parallel_min_percent 0 parallel_min_servers 0 parallel_min_time_threshold 1 parallel_server FALSE parallel_server_instances 1 parallel_servers_target 64 parallel_threads_per_cpu 2 recovery_parallelism 0
comparison on 11.2.0.2 and 11.2.0.3
----test case drop table foo; alter session set parallel_degree_policy = auto; alter session set parallel_degree_limit = 2; alter session set parallel_min_time_threshold = 1; create table foo as select * from all_objects where rownum < 16; alter table foo parallel 6; set autotrace traceonly explain select /*+ Parallel(e) */ * from foo e; -------- FOO has a default DOP of 6 11.2.0.3 -- select /*+ Parallel(a) */ * from foo a; --- Degree of Parallelism is 32767 because of hint 11.2.0.2 -- select /*+ Parallel(a) */ * from foo a; -- automatic DOP: Computed Degree of Parallelism is 1 11.2.0.3 select /*+ Parallel */ * from foo; -- - automatic DOP: Computed Degree of Parallelism is 2 11.2.0.2 select /*+ Parallel */ * from foo; -- - automatic DOP: Computed Degree of Parallelism is 2 11.2.0.3 select /*+ Parallel(20) */ * from foo; -- - Degree of Parallelism is 20 because of hint 11.2.0.2 select /*+ Parallel(20) */ * from foo; -- - Degree of Parallelism is 20 because of hint
Friday, April 5, 2013
Goldengate Mapping and Transformation for ETL
There are situation when you want to take advantage of GG. it can map and transform data in shape which you want on Target.
we had same one situation,when Source table have 90+ rows and 5 million rows gets Replicated/day and Target table is not partitioned.
so we did partition Target table not just partitioned but we sub-partition it.
Table will be first Partitioned based on date column by New 11g Interval partition and
then subpartitioned by site id which is again subset of account number, Account number column is Varchar2 with some 20 digits Number.so site id will be parsed from first 3 digits from Account Number.
In nutshell we are decomposing account Number on Target.
In addition to this ,we added three extra column.
R_TGT_CREATED_DT = Whenever record inserted first time on target it will have target current timestamp.
R_TGT_UPDATED_DT = Whenever record inserted & updated on target it will have target timestamp.
R_ACTION_CD = type of DML will be captured in this column, (insert/update/delete)
Again addition to this, we want to preserve rows which will be deleted in separate partitioned archived table.
so we had again archive table called ${TABLE_NAME}_DEL , Sorry using variable of Unix.
Note: As source and Target DDL is not identical , You must use defgen utility to generate source DDL. First time You must load data using Initial Load,so rows will fall into right partitions and Extra mapping columns will also populated as same time.
Note: When you want use INSERTDELETES on Targetside, You must use NOCOMPRESSDELETES on Source.
DDL of Source table.
DDL of Target table.
---ARCHIVED DELETE TABLE TO PRESERVE DELETED ROWS.
---Goldengate Mapping On source
Please keep in mind that you put NOCOMPRESSDELETES in Source parameter file.
---Goldengate Mapping On Target
Please keep in mind that you need to put ALLOWDUPTARGETMAP in Target Mapping file.
we had same one situation,when Source table have 90+ rows and 5 million rows gets Replicated/day and Target table is not partitioned.
so we did partition Target table not just partitioned but we sub-partition it.
Table will be first Partitioned based on date column by New 11g Interval partition and
then subpartitioned by site id which is again subset of account number, Account number column is Varchar2 with some 20 digits Number.so site id will be parsed from first 3 digits from Account Number.
In nutshell we are decomposing account Number on Target.
In addition to this ,we added three extra column.
R_TGT_CREATED_DT = Whenever record inserted first time on target it will have target current timestamp.
R_TGT_UPDATED_DT = Whenever record inserted & updated on target it will have target timestamp.
R_ACTION_CD = type of DML will be captured in this column, (insert/update/delete)
Again addition to this, we want to preserve rows which will be deleted in separate partitioned archived table.
so we had again archive table called ${TABLE_NAME}_DEL , Sorry using variable of Unix.
Note: As source and Target DDL is not identical , You must use defgen utility to generate source DDL. First time You must load data using Initial Load,so rows will fall into right partitions and Extra mapping columns will also populated as same time.
Note: When you want use INSERTDELETES on Targetside, You must use NOCOMPRESSDELETES on Source.
DDL of Source table.
CREATE TABLE DUMMYEVENT ( ACC_NUM VARCHAR2(20 BYTE) NOT NULL, ORIGINAL_ACC_NUM VARCHAR2(20 BYTE), RULE_NUMBER NUMBER(1), -----few columns Trim. PREVIOUS_EVENT_REF VARCHAR2(16 BYTE), PREVIOUS_EVENT_SEQ NUMBER(9), PRODUCT_SEQ NUMBER(9), TWIN_EVENT_BOO VARCHAR2(1 BYTE) )
DDL of Target table.
CREATE TABLE DUMMYEVENT
( SITE_ID NUMBER(3),
ACC_NUM VARCHAR2(20 BYTE) NOT NULL,
ORIGINAL_ACC_NUM VARCHAR2(20 BYTE),
RULE_NUMBER NUMBER(1),
-----few columns Trim.
PREVIOUS_EVENT_REF VARCHAR2(16 BYTE),
PREVIOUS_EVENT_SEQ NUMBER(9),
PRODUCT_SEQ NUMBER(9),
TWIN_EVENT_BOO VARCHAR2(1 BYTE)
R_TGT_CREATED_DT DATE,
R_TGT_UPDATED_DT DATE,
R_ACTION_CD VARCHAR2(100 BYTE)
)
TABLESPACE DUMMY_ADMIN
PARTITION BY RANGE (ACC_NUM)
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
subpartition by list (SITE_ID)
(PARTITION DUMMYEVENT1 VALUES LESS THAN (TO_DATE('20-02-2013', 'DD-MM-YYYY'))
(
SUBPARTITION CO_1 VALUES (1),
SUBPARTITION CO_126 VALUES (126),
SUBPARTITION CO_131 VALUES (131),
SUBPARTITION CO_132 VALUES (132),
SUBPARTITION CO_135 VALUES (135),
SUBPARTITION CO_182 VALUES (182),
SUBPARTITION CO_186 VALUES (186),
SUBPARTITION CO_214 VALUES (214),
SUBPARTITION CO_215 VALUES (215),
SUBPARTITION CO_216 VALUES (216),
SUBPARTITION CO_238 VALUES (238),
SUBPARTITION CO_239 VALUES (239),
SUBPARTITION CO_333 VALUES (333),
SUBPARTITION CO_334 VALUES (334),
SUBPARTITION CO_342 VALUES (342),
SUBPARTITION CO_436 VALUES (436),
SUBPARTITION CO_476 VALUES (476),
SUBPARTITION CO_477 VALUES (477),
SUBPARTITION CO_541 VALUES (541),
SUBPARTITION CO_580 VALUES (580),
SUBPARTITION CO_609 VALUES (609),
SUBPARTITION CO_UK VALUES (DEFAULT)
)
);
---ARCHIVED DELETE TABLE TO PRESERVE DELETED ROWS.
CREATE TABLE IDENTITY.DUMMYEVENT_DEL
( SITE_ID NUMBER(3),
ACC_NUM VARCHAR2(20 BYTE) NOT NULL,
ORIGINAL_ACC_NUM VARCHAR2(20 BYTE),
RULE_NUMBER NUMBER(1),
-----few columns Trim.
PREVIOUS_EVENT_REF VARCHAR2(16 BYTE),
PREVIOUS_EVENT_SEQ NUMBER(9),
PRODUCT_SEQ NUMBER(9),
TWIN_EVENT_BOO VARCHAR2(1 BYTE)
R_TGT_CREATED_DT DATE,
R_TGT_UPDATED_DT DATE,
R_ACTION_CD VARCHAR2(100 BYTE)
)
TABLESPACE IDM_DATA
PARTITION BY RANGE (R_TGT_UPDATED_DT)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION costed1 VALUES LESS THAN (TO_DATE('20-02-2013', 'DD-MM-YYYY')) TABLESPACE IDM_DATA
);
---Goldengate Mapping On source
Please keep in mind that you put NOCOMPRESSDELETES in Source parameter file.
TABLE ICBS_ADMIN.DUMMYEVENT, KEYCOLS(ACC_NUM);
---Goldengate Mapping On Target
Please keep in mind that you need to put ALLOWDUPTARGETMAP in Target Mapping file.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--THIS IS FOR INSERT AND DELETE RECORD ONLY.
ALLOWDUPTARGETMAP
GETINSERTS
GETDELETES
IGNOREUPDATES
MAP ICBS_ADMIN.DUMMYEVENT, TARGET ICBS_ADMIN.DUMMYEVENT,
KEYCOLS(ACC_NUM),
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
R_TGT_CREATED_DT= @DATENOW (),
R_TGT_UPDATED_DT= @DATENOW (),
R_ACTION_CD= @GETENV ("GGHEADER","OPTYPE")
);
--THIS IS FOR UPDATE RECORD ONLY.
GETUPDATES
IGNOREINSERTS
IGNOREDELETES
MAP ICBS_ADMIN.DUMMYEVENT, TARGET ICBS_ADMIN.DUMMYEVENT,
KEYCOLS(ACC_NUM),
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
R_TGT_UPDATED_DT= @DATENOW (),
R_ACTION_CD= @GETENV ("GGHEADER","OPTYPE")
);
---------------------------------------------------------------------------------
--To preserve deletes.
---------------------------------------------------------------------------------
GETDELETES
IGNOREINSERTS
IGNOREUPDATES
INSERTDELETES
MAP ICBS_ADMIN.DUMMYEVENT, TARGET ICBS_ADMIN.DUMMYEVENT_DEL,
KEYCOLS(ACC_NUM),
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
SITE_ID= @STREXT (ACC_NUM, 1, 3),
R_TGT_CREATED_DT= @DATENOW (),
R_TGT_UPDATED_DT= @DATENOW (),
R_ACTION_CD= @GETENV ("GGHEADER","OPTYPE")
);
Saturday, February 2, 2013
Remote sqlplus Over SSH
Example is to show how to run sqlplus over ssh.
/usr/bin/ssh -qn catdummyxyz 'ORACLE_HOME=/u00/app/oracle/product/10.2.0/DB;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s sys/dummy@ASM as sysdba << EndOfFile select dummy from dual; select instance_number ,INSTANCE_NAME from v\$instance; exit; EndOfFile'You can also export ORACLE_SID if you want.
/usr/bin/ssh -qn catdummxyz 'ORACLE_SID=+ASM2;export ORACLE_SID;ORACLE_HOME=/u00/app/oracle/product/10.2.0/DB;export ORACLE_HOME;$ORACLE_HOME/bin/sqlplus -s sys/dummy@ASM as sysdba << EndOfFile select dummy from dual; select instance_number ,INSTANCE_NAME from v\$instance; exit; EndOfFile'
Thursday, January 3, 2013
Restore Database RAC from multiple Instances
This is restore & recovery of RAC database running on 3 nodes.
While restore & recover database we allocate channel to all Instances for Load balance.
Oracle Database version is 11.2.0.3.
Steps to perform restore & recovery.
Restore spfile
Restore controlfile
Bring all the instances in mount state (do not put cluster_database=false)
Allocate channel using multiple instances
Restore database
Recover database
Open the database in resetlogs in one node
Open the database on other nodes
Now copy spfile to all nodes & rename according to it.
------Restore controlfile
Mount database on All nodes & make sure cluster_database parameter is set to TRUE
----------Restore database.
---------recover database.
---------Open database in resetlogs on first instance & rest with normal.
---------output of restore
------output of recover database.
While restore & recover database we allocate channel to all Instances for Load balance.
Oracle Database version is 11.2.0.3.
Steps to perform restore & recovery.
Restore spfile
Restore controlfile
Bring all the instances in mount state (do not put cluster_database=false)
Allocate channel using multiple instances
Restore database
Recover database
Open the database in resetlogs in one node
Open the database on other nodes
---------RESTORE SPFILE
TEST271 | DUMMYGG1 | /export/home/oracle/scripts/rman
> rman target / catalog rman/RMAN@catalogdb
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 3 12:06:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> set dbid=90275615
executing command: SET DBID
database name is "DUMMYGG" and DBID is 90275615
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7639172 Incr 0 80.00K DISK 00:00:01 02-JAN-13
BP Key: 7639190 Status: AVAILABLE Compressed: NO Tag: TAG20130102T121926
Piece Name: /ORAGG/backup/DUMMYGG15_12nudf6q_1_1
SPFILE Included: Modification time: 02-JAN-13
SPFILE db_unique_name: DUMMYGG
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7640203 Incr 0 80.00K DISK 00:00:01 02-JAN-13
BP Key: 7640224 Status: AVAILABLE Compressed: NO Tag: TAG20130102T124658
Piece Name: /ORAGG/backup/DUMMYGG05_2gnudgqg_1_1
SPFILE Included: Modification time: 02-JAN-13
SPFILE db_unique_name: DUMMYGG
RMAN> STARTUP FORCE NOMOUNT;
RMAN> restore spfile from '/ORAGG/backup/DUMMYGG05_2gnudgqg_1_1';
Starting restore at 03-JAN-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /ORAGG/backup/DUMMYGG05_2gnudgqg_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 03-JAN-13
RMAN> exit
Now copy spfile to all nodes & rename according to it.
------Restore controlfile
> rman target / catalog rman/RMAN@catalogdb Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 3 12:14:22 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: DUMMYGG (not mounted) connected to recovery catalog database RMAN> set dbid=90275615; executing command: SET DBID database name is "DUMMYGG" and DBID is 90275615 RMAN> restore controlfile; Starting restore at 03-JAN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 instance=DUMMYGG1 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: copied control file copy input file name=/u00/app/oracle/diag/control.ctl output file name=+TESTGG/DUMMYGG/controlfile/current.958.803736875 Finished restore at 03-JAN-13----------Mount all Instance.
Mount database on All nodes & make sure cluster_database parameter is set to TRUE
----------Restore database.
rman target / catalog rman/RMAN@catalogdb
run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
sql 'alter system set "_backup_file_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_file_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG01_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c02 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG02_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c03 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG03_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c04 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG04_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c05 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG05_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c06 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG06_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c07 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG07_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c08 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG08_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c09 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG09_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c10 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG10_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c11 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG11_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c12 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG12_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c13 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG13_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c14 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG14_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c15 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG15_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c16 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG16_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c17 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG17_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c18 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG18_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
restore database;
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
release channel c06;
release channel c07;
release channel c08;
release channel c09;
release channel c10;
release channel c11;
release channel c12;
release channel c13;
release channel c14;
release channel c15;
release channel c16;
release channel c17;
release channel c18;
}
---------recover database.
RMAN> run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
sql 'alter system set "_backup_file_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_file_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/ORAGG/backup/' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c07 DEVICE TYPE DISK FORMAT '/ORAGG/backup/' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c13 DEVICE TYPE DISK FORMAT '/ORAGG/backup/' CONNECT 'sys/SYSTEM@DUMMYGG3';
set until time "to_date('2013-01-02:13:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
recover database;
release channel c01;
release channel c07;
release channel c13;
}
---------Open database in resetlogs on first instance & rest with normal.
TEST271 | DUMMYGG1 | /export/home/oracle/scripts/rman/log sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 12:46:30 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options 12:47:01 @DUMMYGG1 SQL>alter database open resetlogs; Database altered. Elapsed: 00:00:14.39 TEST272 | DUMMYGG2 | /export/home/oracle sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 12:47:43 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options 12:47:43 @DUMMYGG2 SQL>alter database open; Database altered. Elapsed: 00:00:03.92 12:47:50 @DUMMYGG2 SQL>
---------output of restore
sql statement: alter system set "_backup_disk_bufcnt"=64 scope=memory sql statement: alter system set "_backup_disk_bufsz"=1048576 scope=memory sql statement: alter system set "_backup_file_bufcnt"=64 scope=memory sql statement: alter system set "_backup_file_bufsz"=1048576 scope=memory allocated channel: c01 channel c01: SID=222 instance=DUMMYGG1 device type=DISK allocated channel: c02 channel c02: SID=5 instance=DUMMYGG1 device type=DISK allocated channel: c03 channel c03: SID=68 instance=DUMMYGG1 device type=DISK allocated channel: c04 channel c04: SID=99 instance=DUMMYGG1 device type=DISK allocated channel: c05 channel c05: SID=129 instance=DUMMYGG1 device type=DISK allocated channel: c06 channel c06: SID=162 instance=DUMMYGG1 device type=DISK allocated channel: c07 channel c07: SID=67 instance=DUMMYGG2 device type=DISK allocated channel: c08 channel c08: SID=161 instance=DUMMYGG2 device type=DISK allocated channel: c09 channel c09: SID=191 instance=DUMMYGG2 device type=DISK allocated channel: c10 channel c10: SID=222 instance=DUMMYGG2 device type=DISK allocated channel: c11 channel c11: SID=5 instance=DUMMYGG2 device type=DISK allocated channel: c12 channel c12: SID=38 instance=DUMMYGG2 device type=DISK allocated channel: c13 channel c13: SID=134 instance=DUMMYGG3 device type=DISK allocated channel: c14 channel c14: SID=72 instance=DUMMYGG3 device type=DISK allocated channel: c15 channel c15: SID=133 instance=DUMMYGG3 device type=DISK allocated channel: c16 channel c16: SID=194 instance=DUMMYGG3 device type=DISK allocated channel: c17 channel c17: SID=10 instance=DUMMYGG3 device type=DISK allocated channel: c18 channel c18: SID=74 instance=DUMMYGG3 device type=DISK Starting restore at 03-JAN-13 channel c01: starting datafile backup set restore channel c01: specifying datafile(s) to restore from backup set channel c01: restoring datafile 00003 to +TESTGG/DUMMYGG/datafile/undotbs1.258.792518685 channel c01: reading from backup piece /ORAGG/backup/DUMMYGG05_22nudgq6_1_1 channel c02: starting datafile backup set restore channel c02: specifying datafile(s) to restore from backup set channel c02: restoring datafile 00005 to +TESTGG/DUMMYGG/datafile/undotbs2.264.792518777 channel c02: reading from backup piece /ORAGG/backup/DUMMYGG06_23nudgq6_1_1 channel c03: starting datafile backup set restore channel c03: specifying datafile(s) to restore from backup set channel c03: restoring datafile 00006 to +TESTGG/DUMMYGG/datafile/undotbs3.265.792518777 channel c03: reading from backup piece /ORAGG/backup/DUMMYGG07_24nudgq6_1_1 channel c04: starting datafile backup set restore channel c04: specifying datafile(s) to restore from backup set channel c04: restoring datafile 00013 to +TESTGG/DUMMYGG/datafile/c2oindx_tbs.974.799865727 channel c04: reading from backup piece /ORAGG/backup/DUMMYGG08_25nudgq8_1_1 channel c05: starting datafile backup set restore channel c05: specifying datafile(s) to restore from backup set channel c05: restoring datafile 00014 to +TESTGG/DUMMYGG/datafile/c2odata_tbs.939.799865745 channel c05: reading from backup piece /ORAGG/backup/DUMMYGG09_26nudgqa_1_1 channel c06: starting datafile backup set restore channel c06: specifying datafile(s) to restore from backup set channel c06: restoring datafile 00009 to +TESTGG/DUMMYGG/datafile/idm_data.344.793627825 channel c06: reading from backup piece /ORAGG/backup/DUMMYGG10_27nudgqa_1_1 channel c07: starting datafile backup set restore channel c07: specifying datafile(s) to restore from backup set channel c07: restoring datafile 00015 to +TESTGG/DUMMYGG/datafile/c2odata_tbs_reorg1.948.799865763 channel c07: reading from backup piece /ORAGG/backup/DUMMYGG14_2bnudgqc_1_1 channel c08: starting datafile backup set restore channel c08: specifying datafile(s) to restore from backup set channel c08: restoring datafile 00016 to +TESTGG/DUMMYGG/datafile/mvlog_data.735.799865763 channel c08: reading from backup piece /ORAGG/backup/DUMMYGG15_2cnudgqc_1_1 channel c09: starting datafile backup set restore channel c09: specifying datafile(s) to restore from backup set channel c09: restoring datafile 00017 to +TESTGG/DUMMYGG/datafile/c2odata_tbs_reorg0.305.799865765 channel c09: reading from backup piece /ORAGG/backup/DUMMYGG16_2dnudgqe_1_1 channel c10: starting datafile backup set restore channel c10: specifying datafile(s) to restore from backup set channel c10: restoring datafile 00004 to +TESTGG/DUMMYGG/datafile/users.259.792518685 channel c10: reading from backup piece /ORAGG/backup/DUMMYGG18_2fnudgqg_1_1 channel c11: starting datafile backup set restore channel c11: specifying datafile(s) to restore from backup set channel c11: restoring datafile 00007 to +TESTGG/DUMMYGG/datafile/gg_data.312.793368947 channel c11: reading from backup piece /ORAGG/backup/DUMMYGG11_28nudgqb_1_1 channel c12: starting datafile backup set restore channel c12: specifying datafile(s) to restore from backup set channel c12: restoring datafile 00011 to +TESTGG/DUMMYGG/datafile/soa_data.328.795 channel c12: reading from backup piece /ORAGG/backup/DUMMYGG13_2anudgqc_1_1 channel c13: starting datafile backup set restore channel c13: specifying datafile(s) to restore from backup set channel c13: restoring datafile 00001 to +TESTGG/DUMMYGG/datafile/system.256.79251 channel c13: reading from backup piece /ORAGG/backup/DUMMYGG12_29nudgqb_1_1 channel c14: starting datafile backup set restore channel c14: specifying datafile(s) to restore from backup set channel c14: restoring datafile 00012 to +TESTGG/DUMMYGG/datafile/taps_data.617.79 channel c14: reading from backup piece /ORAGG/backup/DUMMYGG03_20nudgq5_1_1 channel c15: starting datafile backup set restore channel c15: specifying datafile(s) to restore from backup set channel c15: restoring datafile 00002 to +TESTGG/DUMMYGG/datafile/sysaux.257.79251 channel c15: reading from backup piece /ORAGG/backup/DUMMYGG04_21nudgq6_1_1 channel c16: starting datafile backup set restore channel c16: specifying datafile(s) to restore from backup set channel c16: restoring datafile 00008 to +TESTGG/DUMMYGG/datafile/identity_data.34 channel c16: reading from backup piece /ORAGG/backup/DUMMYGG01_1unudgq5_1_1 channel c17: starting datafile backup set restore channel c17: specifying datafile(s) to restore from backup set channel c17: restoring datafile 00010 to +TESTGG/DUMMYGG/datafile/taps_data.329.79 channel c17: reading from backup piece /ORAGG/backup/DUMMYGG02_1vnudgq5_1_1 channel c07: piece handle=/ORAGG/backup/DUMMYGG14_2bnudgqc_1_1 tag=TAG20130102T124658 channel c07: restored backup piece 1 channel c07: restore complete, elapsed time: 00:00:05 channel c08: piece handle=/ORAGG/backup/DUMMYGG15_2cnudgqc_1_1 tag=TAG20130102T124658 channel c08: restored backup piece 1 channel c08: restore complete, elapsed time: 00:00:05 channel c09: piece handle=/ORAGG/backup/DUMMYGG16_2dnudgqe_1_1 tag=TAG20130102T124658 channel c09: restored backup piece 1 channel c09: restore complete, elapsed time: 00:00:05 channel c10: piece handle=/ORAGG/backup/DUMMYGG18_2fnudgqg_1_1 tag=TAG20130102T124658 channel c10: restored backup piece 1 channel c10: restore complete, elapsed time: 00:00:17 channel c11: piece handle=/ORAGG/backup/DUMMYGG11_28nudgqb_1_1 tag=TAG20130102T124658 channel c11: restored backup piece 1 channel c11: restore complete, elapsed time: 00:00:17 channel c12: piece handle=/ORAGG/backup/DUMMYGG13_2anudgqc_1_1 tag=TAG20130102T124658 channel c12: restored backup piece 1 channel c12: restore complete, elapsed time: 00:00:17 channel c13: piece handle=/ORAGG/backup/DUMMYGG12_29nudgqb_1_1 tag=TAG20130102T124658 channel c13: restored backup piece 1 channel c13: restore complete, elapsed time: 00:00:37 channel c06: piece handle=/ORAGG/backup/DUMMYGG10_27nudgqa_1_1 tag=TAG20130102T124658 channel c06: restored backup piece 1 channel c06: restore complete, elapsed time: 00:00:57 channel c01: piece handle=/ORAGG/backup/DUMMYGG05_22nudgq6_1_1 tag=TAG20130102T124658 channel c01: restored backup piece 1 channel c01: restore complete, elapsed time: 00:01:58 channel c02: piece handle=/ORAGG/backup/DUMMYGG06_23nudgq6_1_1 tag=TAG20130102T124658 channel c02: restored backup piece 1 channel c02: restore complete, elapsed time: 00:02:48 channel c03: piece handle=/ORAGG/backup/DUMMYGG07_24nudgq6_1_1 tag=TAG20130102T124658 channel c03: restored backup piece 1 channel c03: restore complete, elapsed time: 00:02:48 channel c04: piece handle=/ORAGG/backup/DUMMYGG08_25nudgq8_1_1 tag=TAG20130102T124658 channel c04: restored backup piece 1 channel c04: restore complete, elapsed time: 00:02:48 channel c05: piece handle=/ORAGG/backup/DUMMYGG09_26nudgqa_1_1 tag=TAG20130102T124658 channel c05: restored backup piece 1 channel c05: restore complete, elapsed time: 00:02:48 channel c15: piece handle=/ORAGG/backup/DUMMYGG04_21nudgq6_1_1 tag=TAG20130102T124658 channel c15: restored backup piece 1 channel c15: restore complete, elapsed time: 00:04:37 channel c14: piece handle=/ORAGG/backup/DUMMYGG03_20nudgq5_1_1 tag=TAG20130102T124658 channel c14: restored backup piece 1 channel c14: restore complete, elapsed time: 00:05:07 channel c17: piece handle=/ORAGG/backup/DUMMYGG02_1vnudgq5_1_1 tag=TAG20130102T124658 channel c17: restored backup piece 1 channel c17: restore complete, elapsed time: 00:10:38 channel c16: piece handle=/ORAGG/backup/DUMMYGG01_1unudgq5_1_1 tag=TAG20130102T124658 channel c16: restored backup piece 1 channel c16: restore complete, elapsed time: 00:10:48 Finished restore at 03-JAN-13 released channel: c01 released channel: c02 released channel: c03 released channel: c04 released channel: c05 released channel: c06 released channel: c07 released channel: c08 released channel: c09 released channel: c10 released channel: c11 released channel: c12 released channel: c13 released channel: c14 released channel: c15 released channel: c16 released channel: c17 released channel: c18 RMAN> quit Recovery Manager complete.
------output of recover database.
sql statement: alter system set "_backup_disk_bufcnt"=64 scope=memory sql statement: alter system set "_backup_disk_bufsz"=1048576 scope=memory sql statement: alter system set "_backup_file_bufcnt"=64 scope=memory sql statement: alter system set "_backup_file_bufsz"=1048576 scope=memory allocated channel: c01 channel c01: SID=5 instance=DUMMYGG1 device type=DISK allocated channel: c07 channel c07: SID=67 instance=DUMMYGG2 device type=DISK allocated channel: c13 channel c13: SID=133 instance=DUMMYGG3 device type=DISK executing command: SET until clause Starting recover at 03-JAN-13 starting media recovery archived log for thread 1 with sequence 1697 is already on disk as file +TESTGG/DUMMYGG/archivelog/2013_01_02/thread_1_seq_1697.776.803667609 archived log for thread 2 with sequence 1140 is already on disk as file +TESTGG/DUMMYGG/archivelog/2013_01_02/thread_2_seq_1140.1090.803668029 archived log for thread 3 with sequence 1195 is already on disk as file +TESTGG/DUMMYGG/archivelog/2013_01_02/thread_3_seq_1195.485.803669415 archived log file name=+TESTGG/DUMMYGG/archivelog/2013_01_02/thread_1_seq_1697.776.803667609 thread=1 sequence=1697 archived log file name=+TESTGG/DUMMYGG/archivelog/2013_01_02/thread_2_seq_1140.1090.803668029 thread=2 sequence=1140 archived log file name=+TESTGG/DUMMYGG/archivelog/2013_01_02/thread_3_seq_1195.485.803669415 thread=3 sequence=1195 media recovery complete, elapsed time: 00:00:01 Finished recover at 03-JAN-13 released channel: c01 released channel: c07 released channel: c13 RMAN>
Subscribe to:
Posts (Atom)