Step by step Physical standby database creation using RMAN DUPLICATE in oracle 11g.
*******************************************************************************************************
Oracle binaries installed with one stand alone database created.
IP: 192.168.56.2
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=prod
on Physical standby, I have installed only oracle binaries.
IP: 192.168.56.3
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=stdby
Required Parameters:
********************
DB_NAME - Must be same on primary & on all standby
DB_UNIQUE_NAME - Must be UNIQUE on all database respectively
LOG_ARCHIVE_CONFIG - This parameter includes DB_UNIQUE_NAME which are the part of DATAGUARD configuration
LOG_ARCHIVE_DEST_n - Define Local & Remote archive log file location
LOG_ARCHIVE_DEST_STATE_n - Define state of archiving (ENABLE or DEFER)
REMOTE_LOGIN_PASSWORDFILE - Must be in EXCLUSIVE mode
FAL_SERVER - Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT - Required when directory structure is different FOR datafiles
LOG_FILE_NAME_CONVERT - Required when directory structure is different FOR logfiles
STANDBY_FILE_MANAGEMENT - Keep AUTO to create file automatically on standby
STEPS:
******
on PRIMARY database:
1) ENABLE ARCHIVELOG MODE
SELECT LOG_MODE FROM V$DATABASE;
IF ARCHIVELOG MODE is DISABLE, follow below steps to enable it.
SHUT IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2) ENABLE FORCE LOGGING MODE
SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
3) VERIFY DB_NAME & DB_UNIQUE_NAME OF Primary Database
SHOW PARAMETER DB_NAME;
SHOW PARAMETER DB_UNIQUE_NAME;
4) CONFIGURE LOG_ARCHIVE_CONFIG PARAMETER
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stdby)';
5) SET ARCHIVELOG DESTINATION FOR STANDBY
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ngprd VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ngprd';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
6) SET REMOTE LOGIN PASSWORD TO EXCLUSIVE
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
7) SET FAL_SERVER PARAMETER
ALTER SYSTEM SET FAL_SERVER=stdby;
8) ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
9) CONFIGURE REQUIRED NETWORK SERVICES using "NETMGR"
10) CREATE STANDBY CONTROLFILE & PFILE
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndcnt.ctl';
CREATE PFILE='/tmp/initstdby.ora' FROM SPFILE;
11) MODIFY initstdby.ora file
i) change DB_UNIQUE_NAME
ii) change FAL_SERVER
iii) change log_archive_dest_n
iv) change location of controlfile
To achieve switchover & switchback (DR) activity, modify same parameters as in production into standby pfile.
12) CREATE APPROPRIATE DIRECTORY STRUCTURE AND MOVE BACKUP, PASSWORD FILE, CONTROLFILE & PFILE
NOTE:- If you fail to move password file to standby destination, you will face below error, while transporting logs to standy site.
ORA-01031: insufficient privileges
In order to rectify this error, move password file to $ORACLE_HOME/dbs location and rename with ora$ORACLE_SID.
ON PHYSICAL SERVER:
*******************
1) CREATE NETWORK SERVICES & UPDATE /etc/oratab FILE
2) START LISTENER on BOTH database
3) Startup database in nomount with updated standby pfile.
SQL> startup nomount pfile='/u01/app/oracle/standbyuct/1120/dbhome/dbs/initstdby.ora';
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2213136 bytes
Variable Size 788531952 bytes
Database Buffers 536870912 bytes
Redo Buffers 8560640 bytes
SQL>
ON PRIMARY SERVER:
******************
13) Connect to RMAN with target and auxiliary server
[oracle@prod ~]$ rman target sys/dg123@prod auxiliary sys/dg123@stdby
Recovery Manager: Release 11.2.0.1.0 - standbyuction on Sun Jun 18 00:55:50 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3169964256)
connected to auxiliary database: prod (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 18-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/standbyuct/1120/dbhome/dbs/orapwprod' auxiliary format
'/u01/app/oracle/standbyuct/1120/dbhome/dbs/orapwstdby' ;
}
executing Memory Script
Starting backup at 18-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
Finished backup at 18-JUN-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/prod/datafiles/prod/stndcnt.ctl';
}
executing Memory Script
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/standbyuct/1120/dbhome/dbs/snapcf_prod.f tag=TAG20170618T005703 RECID=3 STAMP=946947426
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-JUN-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/prod/datafiles/prod/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/prod/datafiles/prod/system01.dbf";
set newname for datafile 2 to
"/u01/prod/datafiles/prod/sysaux01.dbf";
set newname for datafile 3 to
"/u01/prod/datafiles/prod/undotbs01.dbf";
set newname for datafile 4 to
"/u01/prod/datafiles/prod/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/prod/datafiles/prod/system01.dbf" datafile
2 auxiliary format
"/u01/prod/datafiles/prod/sysaux01.dbf" datafile
3 auxiliary format
"/u01/prod/datafiles/prod/undotbs01.dbf" datafile
4 auxiliary format
"/u01/prod/datafiles/prod/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/prod/datafiles/prod/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/prod/datafiles/prod/system01.dbf
output file name=/u01/prod/datafiles/prod/system01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/prod/datafiles/prod/sysaux01.dbf
output file name=/u01/prod/datafiles/prod/sysaux01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/prod/datafiles/prod/undotbs01.dbf
output file name=/u01/prod/datafiles/prod/undotbs01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/prod/datafiles/prod/users01.dbf
output file name=/u01/prod/datafiles/prod/users01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JUN-17
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=946947666 file name=/u01/prod/datafiles/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=946947666 file name=/u01/prod/datafiles/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=946947666 file name=/u01/prod/datafiles/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=946947666 file name=/u01/prod/datafiles/prod/users01.dbf
Finished Duplicate Db at 18-JUN-17
RMAN>
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@standby ~]$
ON STANDBY SERVER
******************
4) Check the database status and start MRP process
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE
-----------------------
MANAGED
IDLE
IDLE
IDLE
IDLE
IDLE
32 rows selected.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2213136 bytes
Variable Size 788531952 bytes
Database Buffers 536870912 bytes
Redo Buffers 8560640 bytes
Database mounted.
Database opened.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
*******************************************************************************************************
Oracle binaries installed with one stand alone database created.
IP: 192.168.56.2
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=prod
on Physical standby, I have installed only oracle binaries.
IP: 192.168.56.3
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=stdby
Required Parameters:
********************
DB_NAME - Must be same on primary & on all standby
DB_UNIQUE_NAME - Must be UNIQUE on all database respectively
LOG_ARCHIVE_CONFIG - This parameter includes DB_UNIQUE_NAME which are the part of DATAGUARD configuration
LOG_ARCHIVE_DEST_n - Define Local & Remote archive log file location
LOG_ARCHIVE_DEST_STATE_n - Define state of archiving (ENABLE or DEFER)
REMOTE_LOGIN_PASSWORDFILE - Must be in EXCLUSIVE mode
FAL_SERVER - Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT - Required when directory structure is different FOR datafiles
LOG_FILE_NAME_CONVERT - Required when directory structure is different FOR logfiles
STANDBY_FILE_MANAGEMENT - Keep AUTO to create file automatically on standby
STEPS:
******
on PRIMARY database:
1) ENABLE ARCHIVELOG MODE
SELECT LOG_MODE FROM V$DATABASE;
IF ARCHIVELOG MODE is DISABLE, follow below steps to enable it.
SHUT IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2) ENABLE FORCE LOGGING MODE
SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
3) VERIFY DB_NAME & DB_UNIQUE_NAME OF Primary Database
SHOW PARAMETER DB_NAME;
SHOW PARAMETER DB_UNIQUE_NAME;
4) CONFIGURE LOG_ARCHIVE_CONFIG PARAMETER
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stdby)';
5) SET ARCHIVELOG DESTINATION FOR STANDBY
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ngprd VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ngprd';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
6) SET REMOTE LOGIN PASSWORD TO EXCLUSIVE
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
7) SET FAL_SERVER PARAMETER
ALTER SYSTEM SET FAL_SERVER=stdby;
8) ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
9) CONFIGURE REQUIRED NETWORK SERVICES using "NETMGR"
10) CREATE STANDBY CONTROLFILE & PFILE
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndcnt.ctl';
CREATE PFILE='/tmp/initstdby.ora' FROM SPFILE;
11) MODIFY initstdby.ora file
i) change DB_UNIQUE_NAME
ii) change FAL_SERVER
iii) change log_archive_dest_n
iv) change location of controlfile
To achieve switchover & switchback (DR) activity, modify same parameters as in production into standby pfile.
12) CREATE APPROPRIATE DIRECTORY STRUCTURE AND MOVE BACKUP, PASSWORD FILE, CONTROLFILE & PFILE
NOTE:- If you fail to move password file to standby destination, you will face below error, while transporting logs to standy site.
ORA-01031: insufficient privileges
In order to rectify this error, move password file to $ORACLE_HOME/dbs location and rename with ora$ORACLE_SID.
ON PHYSICAL SERVER:
*******************
1) CREATE NETWORK SERVICES & UPDATE /etc/oratab FILE
2) START LISTENER on BOTH database
3) Startup database in nomount with updated standby pfile.
SQL> startup nomount pfile='/u01/app/oracle/standbyuct/1120/dbhome/dbs/initstdby.ora';
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2213136 bytes
Variable Size 788531952 bytes
Database Buffers 536870912 bytes
Redo Buffers 8560640 bytes
SQL>
ON PRIMARY SERVER:
******************
13) Connect to RMAN with target and auxiliary server
[oracle@prod ~]$ rman target sys/dg123@prod auxiliary sys/dg123@stdby
Recovery Manager: Release 11.2.0.1.0 - standbyuction on Sun Jun 18 00:55:50 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3169964256)
connected to auxiliary database: prod (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 18-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/standbyuct/1120/dbhome/dbs/orapwprod' auxiliary format
'/u01/app/oracle/standbyuct/1120/dbhome/dbs/orapwstdby' ;
}
executing Memory Script
Starting backup at 18-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
Finished backup at 18-JUN-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/prod/datafiles/prod/stndcnt.ctl';
}
executing Memory Script
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/standbyuct/1120/dbhome/dbs/snapcf_prod.f tag=TAG20170618T005703 RECID=3 STAMP=946947426
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-JUN-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/prod/datafiles/prod/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/prod/datafiles/prod/system01.dbf";
set newname for datafile 2 to
"/u01/prod/datafiles/prod/sysaux01.dbf";
set newname for datafile 3 to
"/u01/prod/datafiles/prod/undotbs01.dbf";
set newname for datafile 4 to
"/u01/prod/datafiles/prod/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/prod/datafiles/prod/system01.dbf" datafile
2 auxiliary format
"/u01/prod/datafiles/prod/sysaux01.dbf" datafile
3 auxiliary format
"/u01/prod/datafiles/prod/undotbs01.dbf" datafile
4 auxiliary format
"/u01/prod/datafiles/prod/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/prod/datafiles/prod/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/prod/datafiles/prod/system01.dbf
output file name=/u01/prod/datafiles/prod/system01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/prod/datafiles/prod/sysaux01.dbf
output file name=/u01/prod/datafiles/prod/sysaux01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/prod/datafiles/prod/undotbs01.dbf
output file name=/u01/prod/datafiles/prod/undotbs01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/prod/datafiles/prod/users01.dbf
output file name=/u01/prod/datafiles/prod/users01.dbf tag=TAG20170618T005716
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JUN-17
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=946947666 file name=/u01/prod/datafiles/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=946947666 file name=/u01/prod/datafiles/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=946947666 file name=/u01/prod/datafiles/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=946947666 file name=/u01/prod/datafiles/prod/users01.dbf
Finished Duplicate Db at 18-JUN-17
RMAN>
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@standby ~]$
ON STANDBY SERVER
******************
4) Check the database status and start MRP process
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE
-----------------------
MANAGED
IDLE
IDLE
IDLE
IDLE
IDLE
32 rows selected.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size 2213136 bytes
Variable Size 788531952 bytes
Database Buffers 536870912 bytes
Redo Buffers 8560640 bytes
Database mounted.
Database opened.
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
No comments:
Post a Comment