Oracle 11g: How to create standby database using RMAN
Assuming you have:
Global Database name (Primary database): mydb
Service name: mydb
SID: mydb
Server name: myserver1
You want to have a physical database on the myserver2 server.
Global Database name (Standby database): mydb
Service name: mystbydb
SID: mystbydb
Server name: myserver2
Steps to do:
Step #1:
Create spfile from your primary database if you don't have one.
SQL> CREATE SPFILE FROM PFILE;
Then make/add following settings in the initmydb.ora file on
the PRIMARY Machine
db_unique_name='mydb'
FAL_Client='mydb'
FAL_Server='mystbydb'
Log_archive_config='DG_CONFIG=(primary,standby)'
Log_archive_dest_1='Location=/recovery
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=mydb'
Log_archive_dest_2='Service=mystbydb lgwr async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=mydb'
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names='mydb'
Standby_File_Management='AUTO'
dg_broker_start=true
Create the standby redo logs on the primary database.
Notice that although this is not required but this will enable the
changes occurring to be reflected on real time in the standby (Real Time
Apply - RTA). Also, since RMAN is going to be used, RMAN will create the
standby redo logs. Assuming that you already have 2 redo logs, then
create two more for your standby. Make sure that the size matches the
red log file size.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ("/u01/oradata/mydb/sb_redo01.log")
SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ("/u01/oradata/mydb/sb_redo02.log")
SIZE 500M;
Step #2:
Create a tnsnames.ora entry for your standby and primary on the primary
and standby servers (myserver1 and myserver2).
On the standby and primary servers:
mystbydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = mystbydb_dgmgrl)
)
)
mydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = mydb_dgmgrl))
)
)
Step #3:
Create an entry for mystbydb in listener.ora on myserver2 and another
entry for mydb on the myserver1. Then you will reload the listeners on
those two servers.
Examples:
On Primary:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
=
(GLOBAL_DBNAME = mydb_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11g/db_1)
(SID_NAME = mystbydb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
)
On Standby::
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
=
(GLOBAL_DBNAME = mystbydb_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11g/db_1)
(SID_NAME = mydb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
)
Reload the listener.
# lsnrctl reload
Step #4:
On myserver2 in $ORACLE_HOME/dbs, create one line pfile (initmystbydb.ora).
I will copy the primary and then I will change the SID and global
database name. Your database name is the same: db_name=mydb
Notice that Oracle will use this for the standby instance; the rest of
the parameters will be populated by RMAN.
On myserver2 go to $ORACLE_BASE/admin and do the following:
# cd $ORACLE_BASE/admin
# mkdir mystbydb
# cd mystbydb
# mkdir adump -- to hold the audit files for the standby instance.
Copy password file from myserver1 to myserver2 with the new name. You
can also create it.
Examples:
Assuming you are on myserver1:
# scp $ORACLE_HOME/dbs/orapwmydb.ora oracle@myserver2:$ORACLE_HOME/dbs/orapwmystbydb.ora
Or you can create one.
On the server2:
# cd
$ORACLE_HOME/dbs
# orapwd file=orapwmystbydb.ora
entries=5 password=xxx
On myserver2, create the instance mystbydb with the NOMOUNT option.
SQL> STARTUP NOMOUNT;
On either primary or standby:
Now, you are ready to create standby database using RMAN.
CONNECT TARGET SYS/pass@mydb
CONNECT AUXILIARY SYS/pass@mystbydb
# rman target sys/pass@mydb auxiliary
sys/pass@mystbydb
RMAN> duplicate target database for standby
from acitive database nofilenamecheck;
--OR--
RUN {
ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL S1 TYPE DISK;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
PARAMETER_VALUE_CONVERT 'mydb','mystbydb'
SET DB_UNIQUE_NAME='mystbydb'
SET DB_FILE_NAME_CONVERT='/mydb/','/mystbydb/'
SET LOG_FILE_NAME_CONVERT='/mydb/','/mystbydb/'
SET CONTROL_FILES='/ORADATA/mystbydb/CONTROL01.CTL'
SET FAL_CLIENT= mystbydb
SET FAL_SERVER= mydb
SET STANDBY_FILE_MANAGEMENT='AUTO'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(mydb,mystbydb)'
SET LOG_ARCHIVE_DEST_2='SERVICE= mydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME= mystbydb
SET LOG_ARCHIVE_DEST_STATE_2='ENABLE'
SET LOG_ARCHIVE_FORMAT='PRO11SB_%T_%S_%R.ARC'
;
SQL CHANNEL C1 "ALTER SYSTEM ARCHIVE LOG CURRENT";
SQL CHANNEL S1 "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING
CURRENT LOGFILE DISCONNECT";
}
Note that the duplicate target database command creates the standby
database from the primary by taking an image copy of the primary
database. Then, it internally issues a command (switch clone datafile
all;), which brings up the standby database as a clone. The set commands
in the script set the parameters for the SPFILE for the standby
instance. The database comes up as a standby database.
That's all.
Good Luck!
|