I have created the guide below to help all the ones who need to setup a Standby database using RMAN.
I’m using Oracle 11g Release 2 on two Linux CentOS 6. I’m also assuming that the database binaries are already installed in both servers and working properly.
For a convention I have called the primary db as ‘primary’ adn the standby db as ‘standby’ (nothing more obvious.. hehe).
Steps for the Pimary Database
1- Enable Force Logging
To make sure all data are sent over to the standby database, we need to force oracle to generate log for all changes to the database.
SQL> ALTER DATABASE FORCE LOGGING;
2 – Setup Standby Redo Log
The purpose of standby redo logs is high availability since the actual redo logs are copied to the standby redo logs (in the primary database) to assure that full recovery can be performed in case of db crash.
Make sure the stand by logs have the exactly size of the db redo logs. It’s also recommended that you create at least one more standby redo log then you have normal redo logs. For example if you have 3 redo log groups, create 4 standby redo log groups as follow.SQL
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/oradata/prim/redo/sbredo11.log' SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/oradata/prim/redo/sbredo12.log' SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/oradata/prim/redo/sbredo13.log' SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/oradata/prim/redo/sbredo14.log' SIZE 100M;
3 – Create the Password File for the Primary Database
You have to create the password file since the user sys will be used by oracle to access the standby db and also be accessed by the standby db. Make sure the password used for SYS on the primary database is the same used on the standby database.
on the OS prompt line do the following:
# orapwd file=orapwSID password=your_pwd entries=5
4 – Setup instance parameters for the primary database.
You can do that in your PFILE or SPFILE.
Below are the required parameters, i’m not showing the others.
*.DB_UNIQUE_NAME='primary'
*.DB_NAME='primary'
*.INSTANCE_NAME='primary'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='arch_%r_%t_%s.arc'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='primary'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='standby','primary' --Only use this parameter if you have different path in the standby server
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
Shutdown the database and start it up so the new parameters take effect.
5 – Archive Must be Enabled
If your database is not already running in archivelog, do the following
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
6 – Create a Copy of the PFILE
Create a copy of the primary database PFILE. It’ll be used on the standby db with some changes
7 – Backup Primary Database, Controlfile and Archivelogs
It’s necessary to create a full backup of the database with its archivelogs and also a backup of the controlfile for standby.
- Full database backup using RMAN
rman connect target /
run { allocate channel ch1 type Disk maxpiecesize = 1900M
FORMAT '/oradata/rmanbkp/hbk_%t_set%s_piece%p.rman';
backup full tag cold_db_f
filesperset 1
(database);
release channel ch1;
}
- Controlfile backup for Standby
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
- Archivelog
RMAN> BACKUP ARCHIVELOG ALL;
8 – Move Files to Standby Server
On the standby server create ALL directories using the same name convention and paths used in the primary server.
Move the PFILE, the standby controlfile, the archivelogs and the primary database backup to the same path in the standby server (do the file movement the best way it fits for you, FTP, SCP and so on).
Steps for Both Databases
9 – Setup Oracle Net
In order to have the redo logs being applied on the standby database, it’s necessary to have the network setup working properly. Let’s setup the listener for both database.
- Listener.ora on primary database
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = hostname_primary)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /usr/app/oracle/product/10.2.0)
(SID_NAME = primary)
)
)
- Listener.ora on standby database
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = hostname_secondary)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/app/oracle/product/10.2.0) (PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = secondary)
(ORACLE_HOME = /usr/app/oracle/product/10.2.0)
(SID_NAME = secondary)
)
- tnsnames.ora for both databases
primary=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname_primary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
secondary=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname_secondary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = secondary)
)
)
- Restart the listener in both servers
# lsnrctl stop;
# lsnrctl start;
Steps for Standby Database
10 – Setup instance parameters for the standby database.
Make a copy of you PFILE to $ORACLE_HOME/dbs and save as initsecondary.ora. Edit it with the parameters below:
*.DB_UNIQUE_NAME='standby'
*.DB_NAME='primary'
*.INSTANCE_NAME='standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='arch_%r_%t_%s.arc'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='standby'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='primary','standby'
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
11- Create password file for Standby database
Follow the same steps from bullet 3 to create the orapwstandby password file for the standby database.
12 – Startup nomount the standby database
Set your ORACLE_SID to standby and start it up in nomount mode using the new PFILE.
SQL> startup nomount;
13 – Recover database using RMAN
Since all the backup files are already in the same location within the standby database connect to RMAN, connect to both databases and perform the recovery as follows.
RMAN> connect target sys/pwd@primary;
RMAN> connect aixiliary sys/pwd@standby;
RMAN> duplicate target database for standby nofilenamecheck;
This command will automatically mount the database in standby mode.
Start the automatically redo apply
SQL> alter database recover managed standby database disconnect from session;
14 – Checking if Standby database is working properly
To check if the database is working correctly, the current log sequence must be the same in both databases.
- Primary database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/orcl/archive/
Oldest online log sequence 941
Next log sequence to archive 943
Current log sequence 943
- Standby database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/orcl/archive/
Oldest online log sequence 941
Next log sequence to archive 0
Current log sequence 943
Also check if logs are being applied
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APP
—————- —–
935 YES
936 YES
937 YES
938 YES
939 YES
940 YES
941 YES
942 YES
Like this:
Be the first to like this post.
