Database
DB_NAME
DB_UNIQUE_NAME
Oracle Net Service Name
Primary
ORCL
ORCL
ORCL
Physical standby
ORCL
ORCL_STBY
ORCL_STBY
Implementation
1) Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
NOARCHIVELOG
2) If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
3) Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
4) Set Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “ORCL” on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE
db_name string ORCL
SQL> show parameter db_unique_name
NAME TYPE VALUE
db_unique_name string ORCL
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value “ORCL_STBY”.
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ORCL,ORCL_STBY)’;
LOG_ARCHIVE_DEST_1=’LOCATION=/backup/archs VALID_FOR=(ALL_LOGFILES,ALL_ROLES)’
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=ORCL_STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY’;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCL_STBY;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
–ALTER SYSTEM SET DB_FILE_NAME_CONVERT=’ORCL_STBY’,’ORCL’ SCOPE=SPFILE;
–ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=’ORCL_STBY’,’ORCL’ SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
5) Create a Backup Copy of the Primary Database Datafiles Use can use any backup method . We prefer RMAN
RMAN>backup database plus archivelog;
6) Create Standby Controlfile and PFILE
Create a controlfile for the standby database by issuing the following command on the primary database.
STARTUP MOUNT;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/orcl_stby.ctl’;
ALTER DATABASE OPEN:
7) Create a parameter file for the standby database.
CREATE PFILE=’/tmp/initorcl_stby.ora’ FROM SPFILE;
8) Modifying Initialization Parameters for a Physical Standby Database
Amend the PFILE making the entries relevant for the standby database. I’m making a replica of the original server, so in my case I only had to amend the following parameters.
*.db_unique_name=’ORCL_STBY’
*.fal_server=’ORCL’
*.log_archive_dest_2=’SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
9) Create a window based service
Oradim ñnew -sid orcl_stby ñinpwsd pass
for unix export ORACLE_SID
10) CP password file from primary to standby an rename as per standby database name
11) Configure listener on standby
12) Add TNS entry on both primary and standby
13) stratstandby using pfile ‘/tmp/initorcl_stby.ora’
stratup nomount pfile =í/tmp/initorcl_stby.oraí;
14) Create a server parameter file for standby database
Create spfile from pfile =í/tmp/initorcl_stby.oraí;
Shu immediate;
15) Start physical standby database
Startup nomount
16) Restore database from primary database backup
RMAN> restore database;
17) Create Redo Logs
Create online redo logs for the standby. It’s a good idea to match the configuration of the primary server.
You should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/ORCL/standby_redo03.log’)
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/ORCL /standby_redo01.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/ORCL /standby_redo02.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/ORCL /standby_redo03.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/ORCL /standby_redo04.log’) SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
18) Startup redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECTS FROM SESSION;
19) Test archival operations to the physical standby database. Swich logfile on primary database;
ALTER SYSTEM SWITCH LOGFILE;20) Verify the new redo data was archived on the standby database.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
8 11-JUL-14 17:50:45 11-JUL-14 17:50:53
9 11-JUL-14 17:50:53 11-JUL-14 17:50:58
10 11-JUL-14 17:50:58 11-JUL-14 17:51:03