 To Bottom |  |
In this Document
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]Information in this document applies to any platform. ** checked for relevance '7-Jul-2015' ** Goal** checked for relevance '7-Jul-2015' **This document offers step by step procedure to create physical standby from ASM primary. Database Name :- chicago Primary db_unique_name :- chicago standby db_unique_name :- Boston Primary Hostname :- raca.idc.oracle.com standby Hostname :- core1.idc.oracle.com Solution1. Enable Force Logging,SQL> ALTER DATABASE FORCE LOGGING; Database altered. 2. On the primary node, create a staging directory. For example:
[oracle@raca chicago]$ mkdir –p /home/oracle/stage
3. Create the same exact path on the standby host:
[oracle@core1 ~]$ mkdir -p /home/oracle/stage
4. On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:
create pfile='/home/oracle/stage/boston.ora' from spfile;
5. On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:
RMAN> run{
2> backup device type disk format '/home/oracle/stage/%U' database; 3> backup device type disk format '/home/oracle/stage/%U' current controlfile for standby; 4> } Starting backup at 24-FEB-09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=30 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=+DATA/chicago/datafile/system.271.679675991 input datafile fno=00003 name=+DATA/chicago/datafile/sysaux.273.679676023 input datafile fno=00002 name=+DATA/chicago/datafile/undo1.272.679676015 channel ORA_DISK_1: starting piece 1 at 24-FEB-09 channel ORA_DISK_1: finished piece 1 at 24-FEB-09 piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 24-FEB-09 channel ORA_DISK_1: finished piece 1 at 24-FEB-09 piece handle=/home/oracle/stage/02k8651v_1_1 tag=TAG20090224T154323 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-FEB-09 Starting backup at 24-FEB-09 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including standby control file in backupset channel ORA_DISK_1: starting piece 1 at 24-FEB-09 channel ORA_DISK_1: finished piece 1 at 24-FEB-09 piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-FEB-09 RMAN> 6. Copy the contents of the staging directory from primary to standby's staging location. For example
[oracle@raca stage]$ scp /home/oracle/stage/* oracle@core1:/home/oracle/stage/
oracle@core1's password: 01k8650r_1_1 100% 236MB 10.7MB/s 00:22 02k8651v_1_1 100% 6016KB 5.9MB/s 00:00 03k86525_1_1 100% 5984KB 5.8MB/s 00:01 boston.ora 100% 458 0.5KB/s 00:00 [oracle@raca stage]$ 7. Prepare the initialization file for standby instance
[oracle@core1 ~]$ mkdir -p /u01/app/oracle/product/10.2/oradata
Set the below parameters in init.ora of standby,[oracle@core1 ~]$ cd /u01/app/oracle/product/10.2/oradata [oracle@core1 oradata]$ mkdir redo data ctrl bdump udump srl arc1 [oracle@core1 oradata]$
*.db_name='chicago'
*.db_create_file_dest='/u01/app/oracle/product/10.2/oradata/boston/data/' *.db_create_online_log_dest_1='/u01/app/oracle/product/10.2/oradata/boston/redo/' *.db_file_name_convert='+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/','/u01/app/oracle/product/10.2/oradata/boston/data/' *.log_file_name_convert='+DATA/chicago/onlinelog/','/u01/app/oracle/product/10.2/oradata/boston/redo/' *.db_unique_name=boston
NOTE: For RAC primary to non RAC standby , include below line in the standby init.ora,
cluster_database=false 8. Create password file for standby database
[oracle@core1 boston]$ export ORACLE_SID=boston
[oracle@core1 dbs]$orapwd file=orapwboston password=oracle [oracle@core1 dbs]$ ls -ltr *boston* -rw-r----- 1 oracle oinstall 1536 Mar 3 15:55 orapwboston 9. Compose a tnsnames or connect string at standby server chicago = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =raca.idc.oracle.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = chicago) ) ) boston = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =core1.idc.oracle.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = boston) ) )
Note:-
10. Create standby databasechicago will connect to primary while boston connects to standby itself
[oracle@core1 boston]$ sqlplus / as sysdba
11 Add standby redo logs to standby databaseSQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 3 16:55:19 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/u01/app/oracle/product/10.2/oradata/boston/initboston.ora'; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 327155712 bytes Fixed Size 1218844 bytes Variable Size 150996708 bytes Database Buffers 104857600 bytes Redo Buffers 70082560 bytes SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@core1 boston]$ /u01/app/oracle/product/10.2/bin/rman target sys/oracle@chicago auxiliary / Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 3 16:56:09 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: CHICAGO (DBID=1289394690) connected to auxiliary database: CHICAGO (not mounted) RMAN> duplicate target database for standby; Starting Duplicate Db at 03-MAR-09 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=36 devtype=DISK contents of Memory Script: { restore clone standby controlfile; sql clone 'alter database mount standby database'; } executing Memory Script Starting restore at 03-MAR-09 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/03k86525_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output filename=/u01/app/oracle/product/10.2/oradata/boston/ctrl/ctrl01.ctl Finished restore at 03-MAR-09 sql statement: alter database mount standby database released channel: ORA_AUX_DISK_1 contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991"; set newname for datafile 2 to "/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015"; set newname for datafile 3 to "/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023"; restore check readonly clone database ; } executing Memory Script executing command: SET NEWNAME renamed temporary file 1 to /u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043 in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 03-MAR-09 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=36 devtype=DISK channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991 restoring datafile 00002 to /u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015 restoring datafile 00003 to /u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023 channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/01k8650r_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 03-MAR-09 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy recid=4 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991 datafile 2 switched to datafile copy input datafile copy recid=5 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015 datafile 3 switched to datafile copy input datafile copy recid=6 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023 Finished Duplicate Db at 03-MAR-09 RMAN>
SQL> alter database add standby logfile group 3 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl3a.log' size 150m;
12. Establish the communication to primary from standbyDatabase altered. SQL> alter database add standby logfile group 4 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl4a.log' size 150m; Database altered. SQL> alter database add standby logfile group 5 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl5a.log' size 150m; Database altered. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL>
SQL> select name,database_role from v$database;
NAME DATABASE_ROLE --------- ---------------- CHICAGO PHYSICAL STANDBY SQL> alter system set standby_archive_dest='/u01/app/oracle/product/10.2/oradata/boston/arc1/'; System altered. SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/10.2/oradata/boston/arc1/ valid_for=(all_logfiles,all_roles) db_unique_name=boston'; SQL> alter system set log_archive_config='dg_config=(chicago,boston)'; System altered. SQL> alter system set log_archive_dest_2='service=chicago lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=chicago'; System altered. SQL> alter system set fal_client=boston; System altered. SQL> alter system set fal_server=chicago; System altered. SQL> alter system set standby_file_management=auto; System altered. 13. Compose tnsnames or connect strings at Primary server chicago = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =raca.idc.oracle.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = chicago) ) ) boston = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =core1.idc.oracle.com )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = boston) ) )
Note:-
Boston will connect to standby while chicago connects to primary itself From 11g we can use RMAN>DUPLICATE FROM ..ACTIVE DATABASE, DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '', '' SET DB_FILE_NAME_CONVERT '', '' SET LOG_FILE_NAME_CONVERT '', '' SET SGA_MAX_SIZE 200M SET SGA_TARGET 125M; For STEP 10 detailed Step on DUPLICATE from ACTIVE DATABSE refer, Note 1075908.1Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE 14. Establish the communication to standby from primary
SQL> alter system set log_archive_config='dg_config=(chicago,boston)';
System altered. SQL> alter system set log_archive_dest_2='service=boston lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=boston'; System altered. SQL> alter system set fal_client=chicago; System altered. SQL> alter system set fal_server=boston; System altered. SQL> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- CHICAGO PRIMARY SQL> 15. Set role transition specific parameters for current primary
SQL> alter system set db_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/' scope=spfile;
16. Configure a Standby Redo Log,System altered. SQL> alter system set log_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/redo/','+DATA/chicago/onlinelog/' scope=spfile; System altered. SQL> alter system set standby_file_management=auto; System altered.
SQL> alter database add standby logfile group 3 size 150m;
Database altered. SQL>alter database add standby logfile group 4 size 150m Database altered. SQL>alter database add standby logfile group 5 size 150m Database altered. |
No hay comentarios:
Publicar un comentario