Summary

In the article we looked at the step by step setup of Data Guard solution where the primary and standby databases are RAC. The configuration was verified by performing switchover, failover and reinstating the former primary after the failover.

Annex

1.Primary database configuration

[oracle@raclinux1 ~]$ srvctl config database -d tst

Database unique name: tst

Database name: tst

Oracle home: /u01/app/oracle/product/11.2.0/db_10

Oracle user: oracle

Spfile: +DGDUP/spfiletst.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: tst

Database instances: tst1,tst2

Disk Groups: DGDUP

Mount point paths:

Services:

Type: RAC

Database is administrator managed

[oracle@raclinux1 ~]$

[oracle@raclinux1 ~]$ srvctl status database -d tst

Instance tst1 is running on node raclinux1

Instance tst2 is running on node raclinux2

[oracle@raclinux1 ~]$

[oracle@raclinux1 dbs]$ cat /tmp/itst.ora

tst.__db_cache_size=125829120

tst2.__db_cache_size=117440512

tst1.__db_cache_size=117440512

tst.__java_pool_size=4194304

tst2.__java_pool_size=4194304

tst1.__java_pool_size=4194304

tst.__large_pool_size=4194304

tst2.__large_pool_size=4194304

tst1.__large_pool_size=4194304

tst.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

tst1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

tst2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

tst.__pga_aggregate_target=209715200

tst2.__pga_aggregate_target=209715200

tst1.__pga_aggregate_target=209715200

tst.__sga_target=314572800

tst2.__sga_target=314572800

tst1.__sga_target=314572800

tst.__shared_io_pool_size=0

tst2.__shared_io_pool_size=0

tst1.__shared_io_pool_size=0

tst.__shared_pool_size=167772160

tst2.__shared_pool_size=180355072

tst1.__shared_pool_size=180355072

tst.__streams_pool_size=4194304

tst2.__streams_pool_size=0

tst1.__streams_pool_size=0

*.archive_lag_target=0

*.audit_file_dest=’/u01/app/oracle/admin/tst/adump’

*.audit_trail=’db’

*.cluster_database=TRUE

*.compatible=’11.2.0.0.0′

*.control_files=’+DGDUP/tst/controlfile/current.285.738230295′,’+DGDUP/tst/controlfile/current.288.738230295′#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest=’+DGDUP’

*.db_domain=”

*.db_name=’tst’

*.db_recovery_file_dest=’+DGDUP’

*.db_recovery_file_dest_size=4227858432

*.dg_broker_config_file1=’+DGDUP/tst/dr1tst.dat’

*.dg_broker_config_file2=’+DGDUP/tst/dr2tst.dat’

*.dg_broker_start=TRUE

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=tstXDB)’

*.fal_server=’tststby’

tst1.instance_number=1

tst2.instance_number=2

*.log_archive_config=’dg_config=(tst,tststby)’

*.log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST’,'valid_for=(ALL_LOGFILES, ALL_ROLES)’

*.log_archive_dest_2=’service=tststby SYNC valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=tststby’

*.log_archive_dest_state_2=’ENABLE’

tst2.log_archive_format=’%t_%s_%r.dbf’

tst1.log_archive_format=’%t_%s_%r.dbf’

*.log_archive_max_processes=4

*.log_archive_min_succeed_dest=1

tst2.log_archive_trace=0

tst1.log_archive_trace=0

*.memory_target=524288000

*.open_cursors=300

*.processes=150

*.remote_listener=’rac-scan:1521′

*.remote_login_passwordfile=’EXCLUSIVE’

*.standby_file_management=’MANUAL’

tst1.thread=1

tst2.thread=2

tst1.undo_tablespace=’UNDOTBS1′

tst2.undo_tablespace=’UNDOTBS2′

[oracle@raclinux1 dbs]$

2.Database online redo logfile and standby redo log files

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———

1 1 22 104857600 512 2 NO CURRENT 1166302 21-DEC-10 2.8147E+14 21-DEC-10

2 1 20 104857600 512 2 YES INACTIVE 1085090 20-DEC-10 1114616 20-DEC-10

3 1 21 104857600 512 2 YES INACTIVE 1114616 20-DEC-10 1114635 20-DEC-10

4 2 4 104857600 512 2 YES INACTIVE 1114683 20-DEC-10 1157139 21-DEC-10

5 2 5 104857600 512 2 NO CURRENT 1157139 21-DEC-10 2.8147E+14

6 2 3 104857600 512 2 YES INACTIVE 1085088 20-DEC-10 1114683 20-DEC-10

6 rows selected.

SQL>

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ————————————————– —

4 ONLINE +DGDUP/tst/onlinelog/group_4.290.738230689 NO

1 ONLINE +DGDUP/tst/onlinelog/group_1.297.738230611 NO

1 ONLINE +DGDUP/tst/onlinelog/group_1.298.738230625 YES

2 ONLINE +DGDUP/tst/onlinelog/group_2.299.738230635 NO

2 ONLINE +DGDUP/tst/onlinelog/group_2.300.738230643 YES

3 ONLINE +DGDUP/tst/onlinelog/group_3.301.738230655 NO

3 ONLINE +DGDUP/tst/onlinelog/group_3.302.738230667 YES

4 ONLINE +DGDUP/tst/onlinelog/group_4.289.738230711 YES

5 ONLINE +DGDUP/tst/onlinelog/group_5.292.738230723 NO

5 ONLINE +DGDUP/tst/onlinelog/group_5.291.738230739 YES

6 ONLINE +DGDUP/tst/onlinelog/group_6.293.738230763 NO

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ————————————————– —

6 ONLINE +DGDUP/tst/onlinelog/group_6.303.738230779 YES

12 rows selected.

SQL>

We are going to create 4 groups in each thread

alter database add standby logfile thread 1 group 7 size 104857600;

alter database add standby logfile thread 1 group 8 size 104857600;

alter database add standby logfile thread 1 group 9 size 104857600;

alter database add standby logfile thread 1 group 10 size 104857600;

alter database add standby logfile thread 2 group 11 size 104857600;

alter database add standby logfile thread 2 group 12 size 104857600;

alter database add standby logfile thread 2 group 13 size 104857600;

alter database add standby logfile thread 2 group 14 size 104857600;

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ————————————————– —

4 ONLINE +DGDUP/tst/onlinelog/group_4.290.738230689 NO

1 ONLINE +DGDUP/tst/onlinelog/group_1.297.738230611 NO

1 ONLINE +DGDUP/tst/onlinelog/group_1.298.738230625 YES

2 ONLINE +DGDUP/tst/onlinelog/group_2.299.738230635 NO

2 ONLINE +DGDUP/tst/onlinelog/group_2.300.738230643 YES

3 ONLINE +DGDUP/tst/onlinelog/group_3.301.738230655 NO

3 ONLINE +DGDUP/tst/onlinelog/group_3.302.738230667 YES

4 ONLINE +DGDUP/tst/onlinelog/group_4.289.738230711 YES

5 ONLINE +DGDUP/tst/onlinelog/group_5.292.738230723 NO

5 ONLINE +DGDUP/tst/onlinelog/group_5.291.738230739 YES

6 ONLINE +DGDUP/tst/onlinelog/group_6.293.738230763 NO

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ————————————————– —

6 ONLINE +DGDUP/tst/onlinelog/group_6.303.738230779 YES

7 STANDBY +DGDUP/tst/onlinelog/group_7.331.738335237 NO

7 STANDBY +DGDUP/tst/onlinelog/group_7.332.738335247 YES

8 STANDBY +DGDUP/tst/onlinelog/group_8.333.738335257 NO

8 STANDBY +DGDUP/tst/onlinelog/group_8.334.738335267 YES

9 STANDBY +DGDUP/tst/onlinelog/group_9.335.738335277 NO

9 STANDBY +DGDUP/tst/onlinelog/group_9.336.738335285 YES

10 STANDBY +DGDUP/tst/onlinelog/group_10.337.738335295 NO

10 STANDBY +DGDUP/tst/onlinelog/group_10.319.738335309 YES

11 STANDBY +DGDUP/tst/onlinelog/group_11.304.738335317 NO

11 STANDBY +DGDUP/tst/onlinelog/group_11.338.738335327 YES

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ————————————————– —

12 STANDBY +DGDUP/tst/onlinelog/group_12.339.738335339 NO

12 STANDBY +DGDUP/tst/onlinelog/group_12.340.738335349 YES

13 STANDBY +DGDUP/tst/onlinelog/group_13.341.738335359 NO

13 STANDBY +DGDUP/tst/onlinelog/group_13.342.738335367 YES

14 STANDBY +DGDUP/tst/onlinelog/group_14.343.738335379 NO

14 STANDBY +DGDUP/tst/onlinelog/group_14.344.738335385 YES

28 rows selected.

SQL>

SQL> select GROUP#, THREAD#, BYTES, STATUS from v$standby_log;

GROUP# THREAD# BYTES STATUS

———- ———- ———- ———-

7 1 104857600 UNASSIGNED

8 1 104857600 UNASSIGNED

9 1 104857600 UNASSIGNED

10 1 104857600 UNASSIGNED

11 2 104857600 UNASSIGNED

12 2 104857600 UNASSIGNED

13 2 104857600 UNASSIGNED

14 2 104857600 UNASSIGNED

8 rows selected.

SQL>

3.Changes to listener.ora and tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_10/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RACDB)

)

TST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = tst)

)

TSTSTBY

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID_NAME = tststby)

)

[oracle@raclinux1 admin]$

5. Create a static entry in the GI scan listener

[oracle@raclinux1 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/11.2.0.2/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = tst)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tst)

)

(SID_DESC =

(GLOBAL_DBNAME = tststby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tststby)

)

(SID_DESC =

(GLOBAL_DBNAME = tst_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tst)

)

(SID_DESC =

(GLOBAL_DBNAME = tststby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tststby)

)

SID_LIST_LISTENER_SCAN1 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = tst)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tst)

)

(SID_DESC =

(GLOBAL_DBNAME = tststby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tststby)

)

(SID_DESC =

(GLOBAL_DBNAME = tst_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tst)

)

(SID_DESC =

(GLOBAL_DBNAME = tststby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

(SID_NAME = tststby)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

)

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))

)

ADR_BASE_LISTENER_SCAN1 = /u01/app/oracle

[oracle@raclinux1 admin]$ cat listener.ora.old

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

[oracle@raclinux1 admin]$

4. pfile for the standby database creation

[oracle@raclinux1 dbs]$ cat inittststby1.ora

tststby.__db_cache_size=125829120

tststby2.__db_cache_size=117440512

tststby1.__db_cache_size=130023424

tststby.__java_pool_size=4194304

tststby2.__java_pool_size=4194304

tststby1.__java_pool_size=4194304

tststby.__large_pool_size=4194304

tststby2.__large_pool_size=4194304

tststby1.__large_pool_size=4194304

tststby.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

tststby1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

tststby.__pga_aggregate_target=209715200

tststby2.__pga_aggregate_target=209715200

tststby1.__pga_aggregate_target=209715200

tststby.__sga_target=314572800

tststby2.__sga_target=314572800

tststby1.__sga_target=314572800

tststby.__shared_io_pool_size=0

tststby2.__shared_io_pool_size=0

tststby1.__shared_io_pool_size=0

tststby.__shared_pool_size=167772160

tststby2.__shared_pool_size=180355072

tststby1.__shared_pool_size=167772160

tststby.__streams_pool_size=4194304

tststby2.__streams_pool_size=0

tststby1.__streams_pool_size=0

*.audit_file_dest=’/u01/app/oracle/admin/tststby/adump’

*.audit_trail=’db’

*.cluster_database=FALSE

*.compatible=’11.2.0.0.0′

*.db_block_size=8192

*.db_create_file_dest=’+DGDUP’

*.db_domain=”

*.db_name=’tststby’

*.db_recovery_file_dest=’+DGDUP’

*.db_recovery_file_dest_size=4227858432

*.diagnostic_dest=’/u01/app/oracle’

*.memory_target=524288000

*.open_cursors=300

*.processes=150

*.remote_listener=’rac-scan:1521′

*.remote_login_passwordfile=’EXCLUSIVE’

tststby1.instance_number=1

tststby2.instance_number=2

5.RMAN output from the standby creation

[oracle@raclinux1 dbs]$ sqlplus sys/sys1@tststby as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 21 17:09:57 2010

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittststby1.ora

ORACLE instance started.

Total System Global Area 521936896 bytes

Fixed Size 2228072 bytes

Variable Size 390070424 bytes

Database Buffers 125829120 bytes

Redo Buffers 3809280 bytes

SQL>

[oracle@raclinux1 dbs]$ rman target / auxiliary sys/sys1@tststby

Recovery Manager: Release 11.2.0.2.0 – Production on Tue Dec 21 17:42:12 2010

connected to target database: TST (DBID=1879374527)

connected to auxiliary database: TSTSTBY (not mounted)

RMAN> run {

allocate channel tst type disk;

allocate channel tst1 type disk;

allocate auxiliary channel tststby type disk;

duplicate target database for standby from active database DORECOVER

spfile

parameter_value_convert “tst”,”tststby”

set db_unique_name=”tststby”

set db_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”

set log_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”

set fal_client=”tststby”

set fal_server=”tst”

set standby_file_management=”AUTO”

set log_archive_config=”dg_config=(tst,tststby)”

set log_archive_dest_2=”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst”;}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>

using target database control file instead of recovery catalog

allocated channel: tst

channel tst: SID=58 device type=DISK

allocated channel: tst1

channel tst1: SID=61 device type=DISK

allocated channel: tststby

channel tststby: SID=25 device type=DISK

Starting Duplicate Db at 21-DEC-10

contents of Memory Script:

{

backup as copy reuse

targetfile ’/u01/app/oracle/product/11.2.0/db_10/dbs/orapwtst1′ auxiliary format

‘/u01/app/oracle/product/11.2.0/db_10/dbs/orapwtststby’ targetfile

‘+DGDUP/spfiletst.ora’ auxiliary format

‘/u01/app/oracle/product/11.2.0/db_10/dbs/spfiletststby.ora’ ;

sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0/db_10/dbs/spfiletststby.ora””;

}

executing Memory Script

Starting backup at 21-DEC-10

Finished backup at 21-DEC-10

sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0/db_10/dbs/spfiletststby.ora”

contents of Memory Script:

{

sql clone “alter system set audit_file_dest =

”/u01/app/oracle/admin/tststby/adump” comment=

”” scope=spfile”;

sql clone “alter system set control_files =

”+DGDUP/tststby/controlfile/current.285.738230295”, ”+DGDUP/tststby/controlfile/current.288.738230295” comment=

”” scope=spfile”;

sql clone “alter system set dispatchers =

”(PROTOCOL=TCP) (SERVICE=tststbyXDB)” comment=

”” scope=spfile”;

sql clone “alter system set db_unique_name =

”tststby” comment=

”” scope=spfile”;

sql clone “alter system set db_file_name_convert =

”+DGDUP/tst”, ”+DGDUP/tststby” comment=

”” scope=spfile”;

sql clone “alter system set log_file_name_convert =

”+DGDUP/tst”, ”+DGDUP/tststby” comment=

”” scope=spfile”;

sql clone “alter system set fal_client =

”tststby” comment=

”” scope=spfile”;

sql clone “alter system set fal_server =

”tst” comment=

”” scope=spfile”;

sql clone “alter system set standby_file_management =

”AUTO” comment=

”” scope=spfile”;

sql clone “alter system set log_archive_config =

”dg_config=(tst,tststby)” comment=

”” scope=spfile”;

sql clone “alter system set log_archive_dest_2 =

”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst” comment=

”” scope=spfile”;

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/tststby/adump” comment= ”” scope=spfile

sql statement: alter system set control_files = ”+DGDUP/tststby/controlfile/current.285.738230295”, ”+DGDUP/tststby/controlfile/current.288.738230295” comment= ”” scope=spfile

sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=tststbyXDB)” comment= ”” scope=spfile

sql statement: alter system set db_unique_name = ”tststby” comment= ”” scope=spfile

sql statement: alter system set db_file_name_convert = ”+DGDUP/tst”, ”+DGDUP/tststby” comment= ”” scope=spfile

sql statement: alter system set log_file_name_convert = ”+DGDUP/tst”, ”+DGDUP/tststby” comment= ”” scope=spfile

sql statement: alter system set fal_client = ”tststby” comment= ”” scope=spfile

sql statement: alter system set fal_server = ”tst” comment= ”” scope=spfile

sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile

sql statement: alter system set log_archive_config = ”dg_config=(tst,tststby)” comment= ”” scope=spfile

sql statement: alter system set log_archive_dest_2 = ”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 521936896 bytes

Fixed Size 2228072 bytes

Variable Size 327155864 bytes

Database Buffers 188743680 bytes

Redo Buffers 3809280 bytes

allocated channel: tststby

channel tststby: SID=30 device type=DISK

contents of Memory Script:

{

sql clone “alter system set control_files =

”+DGDUP/tststby/controlfile/current.366.738351783”, ”+DGDUP/tststby/controlfile/current.367.738351783” comment=

”Set by RMAN” scope=spfile”;

backup as copy current controlfile for standby auxiliary format ‘+DGDUP/tststby/controlfile/current.368.738351783′;

restore clone controlfile to ‘+DGDUP/tststby/controlfile/current.369.738351783′ from

‘+DGDUP/tststby/controlfile/current.368.738351783′;

sql clone “alter system set control_files =

”+DGDUP/tststby/controlfile/current.368.738351783”, ”+DGDUP/tststby/controlfile/current.369.738351783” comment=

”Set by RMAN” scope=spfile”;

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set control_files = ”+DGDUP/tststby/controlfile/current.366.738351783”, ”+DGDUP/tststby/controlfile/current.367.738351783” comment= ”Set by RMAN” scope=spfile

Starting backup at 21-DEC-10

channel tst: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_10/dbs/snapcf_tst1.f tag=TAG20101221T174303 RECID=34 STAMP=738351787

channel tst: datafile copy complete, elapsed time: 00:00:07

Finished backup at 21-DEC-10

Starting restore at 21-DEC-10

channel tststby: copied control file copy

Finished restore at 21-DEC-10

sql statement: alter system set control_files = ”+DGDUP/tststby/controlfile/current.368.738351783”, ”+DGDUP/tststby/controlfile/current.369.738351783” comment= ”Set by RMAN” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 521936896 bytes

Fixed Size 2228072 bytes

Variable Size 327155864 bytes

Database Buffers 188743680 bytes

Redo Buffers 3809280 bytes

allocated channel: tststby

channel tststby: SID=29 device type=DISK

contents of Memory Script:

{

sql clone ‘alter database mount standby database’;

}

executing Memory Script

sql statement: alter database mount standby database

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

set newname for tempfile 1 to

“+dgdup”;

switch clone tempfile all;

set newname for datafile 1 to

“+dgdup”;

set newname for datafile 2 to

“+dgdup”;

set newname for datafile 3 to

“+dgdup”;

set newname for datafile 4 to

“+dgdup”;

set newname for datafile 5 to

“+dgdup”;

backup as copy reuse

datafile 1 auxiliary format

“+dgdup” datafile

2 auxiliary format

“+dgdup” datafile

3 auxiliary format

“+dgdup” datafile

4 auxiliary format

“+dgdup” datafile

5 auxiliary format

“+dgdup” ;

sql ‘alter system archive log current’;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +dgdup in control file

executing command: SET NEWNAME

Starting backup at 21-DEC-10

channel tst: starting datafile copy

input datafile file number=00001 name=+DGDUP/tst/datafile/system.287.738229555

channel tst1: starting datafile copy

input datafile file number=00002 name=+DGDUP/tst/datafile/sysaux.282.738229779

output file name=+DGDUP/tststby/datafile/sysaux.371.738351819 tag=TAG20101221T174338

channel tst1: datafile copy complete, elapsed time: 00:02:25

channel tst1: starting datafile copy

input datafile file number=00003 name=+DGDUP/tst/datafile/undotbs1.281.738229967

output file name=+DGDUP/tststby/datafile/system.370.738351819 tag=TAG20101221T174338

channel tst: datafile copy complete, elapsed time: 00:02:55

channel tst: starting datafile copy

input datafile file number=00005 name=+DGDUP/tst/datafile/undotbs2.306.738231147

output file name=+DGDUP/tststby/datafile/undotbs1.372.738351989 tag=TAG20101221T174338

channel tst1: datafile copy complete, elapsed time: 00:00:24

channel tst1: starting datafile copy

input datafile file number=00004 name=+DGDUP/tst/datafile/users.280.738229991

output file name=+DGDUP/tststby/datafile/undotbs2.373.738352009 tag=TAG20101221T174338

channel tst: datafile copy complete, elapsed time: 00:00:23

output file name=+DGDUP/tststby/datafile/users.374.738352019 tag=TAG20101221T174338

channel tst1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 21-DEC-10

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like “+DGDUP/tst/archivelog/2010_12_21/thread_1_seq_32.375.738352033″ auxiliary format

“+DGDUP” ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 21-DEC-10

channel tst: starting archived log copy

input archived log thread=1 sequence=32 RECID=58 STAMP=738352034

output file name=+DGDUP/tststby/archivelog/2010_12_21/thread_1_seq_32.376.738352043 RECID=0 STAMP=0

channel tst: archived log copy complete, elapsed time: 00:00:01

Finished backup at 21-DEC-10

searching for all files in the recovery area

List of Files Unknown to the Database

=====================================

File Name: +dgdup/TSTSTBY/ARCHIVELOG/2010_12_21/thread_1_seq_32.376.738352043

File Name: +dgdup/TSTSTBY/DATAFILE/SYSTEM.370.738351819

File Name: +dgdup/TSTSTBY/DATAFILE/SYSAUX.371.738351819

File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS1.372.738351989

File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS2.373.738352009

File Name: +dgdup/TSTSTBY/DATAFILE/USERS.374.738352019

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.359.738350563

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.360.738350567

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.361.738350567

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.362.738350567

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.366.738351783

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.367.738351783

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: +dgdup/TSTSTBY/ARCHIVELOG/2010_12_21/thread_1_seq_32.376.738352043

File Name: +dgdup/TSTSTBY/DATAFILE/SYSTEM.370.738351819

File Name: +dgdup/TSTSTBY/DATAFILE/SYSAUX.371.738351819

File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS1.372.738351989

File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS2.373.738352009

File Name: +dgdup/TSTSTBY/DATAFILE/USERS.374.738352019

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.361.738350567

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.362.738350567

List of Files Which Where Not Cataloged

=======================================

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.359.738350563

RMAN-07517: Reason: The file header is corrupted

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.360.738350567

RMAN-07517: Reason: The file header is corrupted

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.366.738351783

RMAN-07517: Reason: The file header is corrupted

File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.367.738351783

RMAN-07517: Reason: The file header is corrupted

List of files in Recovery Area not managed by the database

==========================================================

File Name: +DGDUP/tststby/controlfile/current.368.738351783

RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter

number of files not managed by recovery area is 1, totaling 9.56MB

datafile 1 switched to datafile copy

input datafile copy RECID=41 STAMP=738352049 file name=+DGDUP/tststby/datafile/system.370.738351819

datafile 2 switched to datafile copy

input datafile copy RECID=42 STAMP=738352049 file name=+DGDUP/tststby/datafile/sysaux.371.738351819

datafile 3 switched to datafile copy

input datafile copy RECID=43 STAMP=738352051 file name=+DGDUP/tststby/datafile/undotbs1.372.738351989

datafile 4 switched to datafile copy

input datafile copy RECID=44 STAMP=738352051 file name=+DGDUP/tststby/datafile/users.374.738352019

datafile 5 switched to datafile copy

input datafile copy RECID=45 STAMP=738352052 file name=+DGDUP/tststby/datafile/undotbs2.373.738352009

contents of Memory Script:

{

set until scn 1303335;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 21-DEC-10

starting media recovery

archived log for thread 1 with sequence 32 is already on disk as file +DGDUP/tststby/archivelog/2010_12_21/thread_1_seq_32.376.738352043

archived log file name=+DGDUP/tststby/archivelog/2010_12_21/thread_1_seq_32.376.738352043 thread=1 sequence=32

media recovery complete, elapsed time: 00:00:02

Finished recover at 21-DEC-10

Finished Duplicate Db at 21-DEC-10

released channel: tst

released channel: tst1

released channel: tststby

RMAN>

6.. Create a pfile /tmp/inittststby2.ora from the spfiletststby.ora. Mount from the pfile; CReate a spfie for DG Broker to function properly; Make sure that both datavases are RACED after the stadby creation.

SQL> create spfile from memory;

File created.

SQL> !env | grep ORA

ORACLE_UNQNAME=RACDB

ORACLE_SID=tststby2

ORACLE_HOSTNAME=raclinux2.gj.com

ORACLE_BASE=/u01/app/oracle

ORACLE_TERM=xterm

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_10

SQL>

7. Test Standby database

SQL> select PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;

PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE

——————– ——————– ——————– —————-

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED PHYSICAL STANDBY

SQL>

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

SQL> select * from v$archive_gap;

no rows selected

SQL>

SQL> select PROCESS, CLIENT_PROCESS , GROUP#, THREAD#, SEQUENCE# from v$managed_standby;

PROCESS CLIENT_P GROUP# THREAD# SEQUENCE#

——— ——– —————————————- ———- ———-

ARCH ARCH N/A 0 0

MRP0 N/A N/A 1 35

SQL>

select sequence#, first_time, next_time, applied from v$archive_log order by sequence#;

SQL> select sequence#, applied from v$archived_log order by sequence#;

SEQUENCE# APPLIED

———- ———

12 YES

13 YES

14 YES

15 YES

16 YES

17 YES

18 YES

19 YES

20 IN-MEMORY

21 NO

22 NO

SEQUENCE# APPLIED

———- ———

23 NO

24 NO

25 NO

26 NO

27 NO

28 NO

29 NO

30 NO

31 NO

32 NO

32 YES

SEQUENCE# APPLIED

———- ———

33 YES

33 NO

34 NO

34 YES

35 YES

35 NO

36 YES

36 NO

37 NO

37 YES

38 YES

SEQUENCE# APPLIED

———- ———

38 NO

39 YES

40 YES

41 NO

42 NO

43 NO

44 NO

45 NO

46 NO

47 NO

48 NO

SEQUENCE# APPLIED

———- ———

49 NO

50 NO

51 NO

52 NO

53 NO

54 NO

55 NO

56 NO

57 NO

58 NO

59 NO

SEQUENCE# APPLIED

———- ———

60 NO

61 NO

57 rows selected.

SQL> select process, client_process, group#, thread#, sequence# from v$managed_standby;

PROCESS CLIENT_P GROUP# THREAD#

——— ——– —————————————- ———-

SEQUENCE#

———-

ARCH ARCH 11 2

38

ARCH ARCH 7 1

60

ARCH ARCH N/A 0

0

PROCESS CLIENT_P GROUP# THREAD#

——— ——– —————————————- ———-

SEQUENCE#

———-

ARCH ARCH 8 1

61

RFS ARCH N/A 0

0

RFS UNKNOWN N/A 0

0

PROCESS CLIENT_P GROUP# THREAD#

——— ——– —————————————- ———-

SEQUENCE#

———-

RFS UNKNOWN N/A 0

0

RFS ARCH N/A 0

0

RFS UNKNOWN N/A 0

0

PROCESS CLIENT_P GROUP# THREAD#

——— ——– —————————————- ———-

SEQUENCE#

———-

RFS UNKNOWN N/A 0

0

RFS LGWR 6 2

39

RFS LGWR 2 1

62

PROCESS CLIENT_P GROUP# THREAD#

——— ——– —————————————- ———-

SEQUENCE#

———-

MRP0 N/A N/A 1

62

13 rows selected.

SQL> select sequence#, applied from v$archived_log order by sequence#;

SEQUENCE# APPLIED

———- ———

12 YES

13 YES

14 YES

15 YES

16 YES

17 YES

18 YES

19 YES

20 YES

21 YES

22 YES

SEQUENCE# APPLIED

———- ———

23 YES

24 YES

25 YES

26 YES

27 YES

28 YES

29 YES

30 YES

31 YES

32 YES

SEQUENCE# APPLIED

———- ———

33 YES

34 YES

35 YES

36 YES

37 YES

38 YES

SEQUENCE# APPLIED

———- ———

38 YES

39 YES

40 YES

41 YES

42 YES

43 YES

44 YES

45 YES

46 YES

47 YES

48 YES

SEQUENCE# APPLIED

———- ———

49 YES

50 YES

51 YES

52 YES

53 YES

54 YES

55 YES

56 YES

57 YES

58 YES

59 YES

SEQUENCE# APPLIED

———- ———

60 YES

61 IN-MEMORY

57 rows selected.

ITPub博客

dataguard 11g

最新文章