Creating physical standby using RMAN duplicate
Creating physical standby using RMAN duplicate without shutting down the primary
Note : In RMAN Standby naming, the primary db (which you are duplicating) is the target and the standby (which you are creating) is the auxiliary
Push the backups to auxilary location or use shared storage which is visible to both primary and standby.
Push the backups to auxilary location or use shared storage which is visible to both primary and standby.
Database Name :- orcl
Primary db_unique_name :- orcl
standby db_unique_name :- stdby
1. Ensure Primary is in archiving mode.
SQL> archive log list;
2. Create password file if not present on primary.
3. Ensure or Enable Force Logging on primary.
SQL> alter database force logging;
4. Configure a Standby Redo Log on primary.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log4a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log5a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log6a.log' size 50m;
5. Modify the primary initialization parameter for dataguard on primary.
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdby)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/oracle/product/10.2.0/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
Note : Create Archive folder
SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';
SQL>alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set FAL_SERVER=stdby;
SQL> alter system set FAL_CLIENT=orcl;
SQL> alter system set DB_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/stdby/','/home/oracle/oracle/product/10.2.0/oradata/orcl/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/stdby/','/home/oracle/oracle/product/10.2.0/oradata/orcl/' scope=spfile;
6. Run the backup job at the primary by connecting to target and catalog DB(if any)
$rman target / catalog RMAN/RMAN@catdb [ Note : if no catalog database then use $rman target / ]
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
backup archivelog all format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
backup current controlfile for standby format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
}
7. Create parameter file on primary copy it to standby and make the necessary changes,
SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initstdby.ora' from spfile;
Make the necessary changes on the initstdby.ora file on standby.
db_name=orcl
db_unique_name=stdby
log_archive_config='DG_CONFIG=(orcl,stdby)'
log_archive_dest_1='LOCATION=/home/oracle/oracle/product/10.2.0/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
log_archive_dest_state_1=ENABLE
db_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/orcl/','/home/oracle/oracle/product/10.2.0/oradata/stdby/'
log_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/orcl/','/home/oracle/oracle/product/10.2.0/oradata/stdby/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
standby_archive_dest='/home/oracle/oracle/product/10.2.0/oradata/stdby/arch/'
standby_file_management=AUTO
fal_client=stdby
fal_server=orcl
-- change control_files parameter to specify new location
8. Create Password file on standby.
create the same password as that of primary. Alternatively we can copy the password file from primary on standby.
For example from primary copry the password file,
$cp orapworcl orapwstdby
9. Create necessary directories and start the auxiliary instance on standby
$mkdir udump bdump cdump adump
$export ORACLE_SID=stdby
$sqlplus "/as sysdba"
SQL> startup nomount pfile=<newly created stdby pfile with changes>;
SQL>exit;
10. Create net services on both primary and standby,
In listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_2)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = stdby.oracle.com)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_2)
(SID_NAME = stdby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
)
In tnsnames.ora
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby.oracle.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
11. Use RMAN duplicate for standby on standby,
on primary,
sql> alter system archive log current;
on standby,
Connect to target(i.e. prim via connect string) and catalog database(if any) and this auxiliary database by,
$rman target /@orcl catalog RMAN/RMAN@catdb auxiliary / [ Note : if no catalog database then $rman target sys/oracle@orcl auxiliary / ]
[oracle@sys109 stdby]$ rman target sys/oracle@orcl auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 16 17:33:09 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1230876740)
connected to auxiliary database: STDBY (not mounted)
RMAN> run {
--set newname for datafile 1 to '/oradata/datafiles/../filename.dbf
-- .......
-- allocate auxiliary channel ch1 type disk;
-- allocate auxiliary channel ch2 type disk;
duplicate target database for standby dorecover;
}
--set newname for datafile 1 to '/oradata/datafiles/../filename.dbf
-- .......
-- allocate auxiliary channel ch1 type disk;
-- allocate auxiliary channel ch2 type disk;
duplicate target database for standby dorecover;
}
Starting Duplicate Db at 16-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 510230;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
....
....
....
media recovery complete, elapsed time: 00:00:05
Finished recover at 16-NOV-09
Finished Duplicate Db at 16-NOV-09
RMAN>exit
12 .Start the MRP(managed recovery process) on standby,
SQL> select name,db_unique_name,database_role from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ---------------- --------------
ORCL stdby PHYSICAL STANDBY
SQL> alter database recover managed standby database disconnect;
Database altered.
13. Enable the log_archive_dest_2 on primary, which is to send the logs to standby server.
SQL>alter system set log_archive_dest_state_2=enable;
14 . Check the standby whether it is in SYNC with primary,
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
select sequence#,applied from v$archived_log order by sequence#;
