Creating a Physical Standby Database by Using SQL and RMAN Commands.
On Both Nodes check.
# cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.100 11gr2.rhel5.com 11gr2
192.168.1.200 11gr2sb.rhel5.com 11gr2sb
Ping each nodes.
Create Directories
On standby
mkdir /u01/app/oracle/admin/stdy
mkdir -p /u01/app/oracle/admin/stdy/adump
mkdir -p /u01/app/oracle/admin/stdy/bdump
mkdir -p /u01/app/oracle/admin/stdy/cdump
mkdir -p /u01/app/oracle/admin/stdy/udump
mkdir -p /u01/app/oracle/admin/stdy/dpdump
mkdir -p /u01/app/oracle/admin/stdy/pfile
mkdir -p /u01/app/oracle/oradata/stdy
chown -R oracle:oinstall /u01/app/oracle/admin/stdy/
chown -R oracle:oinstall /u01/app/oracle/oradata/stdy
$ . oraenv
+ASM
$ asmcmd
ASMCMD> ls
DATA/
FRA/
ASMCMD> cd data
ASMCMD> ls
ASM/
ASMCMD> pwd
+data
ASMCMD> mkdir stdy
On Standby
$ crsctl start resource -all
On Primary
See the DB & ASM & Listener are up
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE 11gr2
ora.FRA.dg ora....up.type ONLINE ONLINE 11gr2
ora....ER.lsnr ora....er.type ONLINE ONLINE 11gr2
ora.asm ora.asm.type ONLINE ONLINE 11gr2
ora.cssd ora.cssd.type ONLINE ONLINE 11gr2
ora.diskmon ora....on.type ONLINE ONLINE 11gr2
ora.orcl.db ora....se.type ONLINE ONLINE 11gr2
On Primary
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1216M
memory_target big integer 1216M
shared_memory_address integer 0
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select force_logging from v$database;
FOR
---
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
Now create standby redo files
SQL> SELECT group#, member, status FROM V$LOGfile;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> SELECT GROUP#, BYTES/1024, STATUS FROM V$STANDBY_LOG;
GROUP# BYTES/1024 STATUS
---------- ---------- ----------
4 51200 UNASSIGNED
5 51200 UNASSIGNED
6 51200 UNASSIGNED
Setting Enviornment & Listener & Tnsnames.ora files
On Standby
Edit .bash_profile
Change the ORACLE_SID=stdy
$ env |grep ORA
ORACLE_SID=stdy
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
Configure listener.ora & tnsnames.ora on both servers to hold entries for both databases
On Primary
$ . oraenv
orcl
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora
STDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = stdy)(UR = A)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.rhel5.com)
)
)
$ tnsping stdy
$ . oraenv
+ASM
$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
Notice the value (UR=A) in the CONNECT_DATA of the tns entry
This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
On the Standby
Edit listener.ora or use netmgr by setting (+ASM environment) & Add static service (stdy) to the listener
$ cd /u01/app/oracle/product/11.2.0/grid/network/admin
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdy.rhel5.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = stdy)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
$ lsnrctl status
Bounce the listener
$ lsnrctl reload
$ lsnrctl status
Add service (orcl) on the standby
export ORACLE_SID=stdy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ env|grep ORA
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
$ vi tnsnames.ora
STDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = stdy)(UR = A)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.rhel5.com)
)
)
Tnsping from both servers
$ . oraenv
orcl
On Standby
$ tnsping orcl
On Primary
$ tnsping stdy
Copy the password file from Primary to Standby, sys password must be identical
On Primary
$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ scp orapworcl oracle@11gr2sb:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstdy
Configuring primary database initialization parameter’s
SQL> show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdy)';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDY VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=STDY;
System altered.
SQL> ALTER SYSTEM SET FAL_CLIENT=ORCL;
System altered.
Check All the Parameters
SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW FROM V$ARCHIVE_DEST;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ONLINE_LOGFILE PRIMARY_ROLE YES
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
orcl
stdy
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=STDY VALID_FOR=(PRIMAR
Y_ROLE,ONLINE_LOGFILES) DB_UNI
QUE_NAME=STDY
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(orcl,stdy)
SQL> show parameter fal_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string ORCL
fal_server string STDY
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
On Standby
In Oracle Home/dbs directory, create a file initstdy.ora containing add below lines. This will serve as the initialization file for the standby instance.
export ORACLE_SID=stdy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ vi initstdy.ora
db_name=orcl
Start the instance stdy in NOMOUNT state
sqlplus / as sysdba
SQL> startup nomount
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------
local_listener string
On Primary
SQL> alter system set local_listener=
'(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2.rhel5.com)(PORT=1521))' scope=memory;
On Standby
SQL> alter system set local_listener=
'(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2sb.rhel5.com)(PORT=1521))' scope=memory;
Active DataGuard
Now that all initial preparation is completed, it's time to call the all-powerful RMAN script that creates the standby database. On orcl, start RMAN and run the following script. You will find it easier to save this in a file and run the script from the RMAN prompt. In case of RMAN connection error restart auxiliary instance and listener.
On Primary
$ rman
RMAN> connect target sys/oracle@orcl
connected to target database: ORCL (DBID=1223873454)
RMAN> connect auxiliary sys/oracle@stdy
connected to auxiliary database: ORCL (not mounted)
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck
spfile
parameter_value_convert 'orcl','stdy'
set 'db_unique_name'='stdy'
set control_files='+DATA/stdy/control.ctl'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FRA'
set DB_RECOVERY_FILE_DEST_SIZE='10G'
set log_archive_max_processes='5'
set fal_client='stdy'
set fal_server='orcl'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(orcl,stdy)'
set log_archive_dest_2='service=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl';
}
This script creates the standby database, places the appropriate parameters in the spfile for the standby instance, creates the diagnostic destination for the standby database, and restarts the standby.
NOFILENAMECHECK:
Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.
The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if you want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK
PARAMETER_VALUE_CONVERT:
Replaces the first string with the second string in all matching initialization parameter values. Note that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are exceptions to this rule and are not affected.
You can use PARAMETER_VALUE_CONVERT to set a collection of initialization parameter values and avoid explicitly setting them all. For example, if the source database uses disk group +ALPHA while the standby database will use +BETA, then you could modify all parameters that refer to these disk groups by specifying SPFILE PARAMETER_VALUE_CONVERT (‘+ALHPA’,'+BETA’).
Add entry into Standby
$ vi /etc/oratab
+ASM:/u01/app/oracle/product/11.2.0/grid:N
stdy:/u01/app/oracle/product/11.2.0/db_1:N
Data Guard & Oracle Restart in 11gR2
$ srvctl add database -d stdy -o /u01/app/oracle/product/11.2.0/db_1 -r PHYSICAL_STANDBY -s MOUNT
$ srvctl config database -d stdy
Database unique name: stdy
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: MOUNT
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups:
Services:
Test the DataGuard Configuration
On the Primary database, connect as HR and add a row to the HR.REGIONS table
SQL> connect hr/hr@orcl
Connected.
SQL> insert into HR.REGIONS values (30,'OpenWorld');
1 row created.
SQL> commit;
Commit complete.
ON Standby
SQL> select * from hr.regions;
Test2
On Primary
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
On Standby
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 30
On Primary
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
On Standby
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 0
Current log sequence 31
SQL> SELECT SEQUENCE#, FIRST_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
1 03-MAR-12 NO
29 03-MAR-12 YES
30 03-MAR-12 YES
31 03-MAR-12 YES
32 03-MAR-12 YES
33 03-MAR-12 YES
34 03-MAR-12 YES
New Columns in v$database
On Primary
SQL> select controlfile_type from v$database;
CONTROL
-------
CURRENT
On Standby
SQL> select controlfile_type from v$database;
CONTROL
-------
STANDBY
SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> select archivelog_compression from v$database;
ARCHIVEL
--------
DISABLED
SQL> select db_unique_name, PRIMARY_DB_UNIQUE_NAME from v$database;
DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME
------------------------------ ------------------------------
stdy orcl
SQL> select dataguard_broker from v$database;
DATAGUAR
--------
DISABLED
SQL> select CONTROLFILE_CONVERTED from v$database;
CON
---
NO
SQL> select remote_archive from v$database;
REMOTE_A
--------
ENABLED
Enable Recovery on the Standby
SQL> select recovery_mode from v$archive_dest_status;
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select recovery_mode from v$archive_dest_status;
Cancel Recovery
SQL> alter database recover managed standby database cancel;
On Both Nodes check.
# cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.100 11gr2.rhel5.com 11gr2
192.168.1.200 11gr2sb.rhel5.com 11gr2sb
Ping each nodes.
Create Directories
On standby
mkdir /u01/app/oracle/admin/stdy
mkdir -p /u01/app/oracle/admin/stdy/adump
mkdir -p /u01/app/oracle/admin/stdy/bdump
mkdir -p /u01/app/oracle/admin/stdy/cdump
mkdir -p /u01/app/oracle/admin/stdy/udump
mkdir -p /u01/app/oracle/admin/stdy/dpdump
mkdir -p /u01/app/oracle/admin/stdy/pfile
mkdir -p /u01/app/oracle/oradata/stdy
chown -R oracle:oinstall /u01/app/oracle/admin/stdy/
chown -R oracle:oinstall /u01/app/oracle/oradata/stdy
$ . oraenv
+ASM
$ asmcmd
ASMCMD> ls
DATA/
FRA/
ASMCMD> cd data
ASMCMD> ls
ASM/
ASMCMD> pwd
+data
ASMCMD> mkdir stdy
On Standby
$ crsctl start resource -all
On Primary
See the DB & ASM & Listener are up
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE 11gr2
ora.FRA.dg ora....up.type ONLINE ONLINE 11gr2
ora....ER.lsnr ora....er.type ONLINE ONLINE 11gr2
ora.asm ora.asm.type ONLINE ONLINE 11gr2
ora.cssd ora.cssd.type ONLINE ONLINE 11gr2
ora.diskmon ora....on.type ONLINE ONLINE 11gr2
ora.orcl.db ora....se.type ONLINE ONLINE 11gr2
On Primary
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1216M
memory_target big integer 1216M
shared_memory_address integer 0
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select force_logging from v$database;
FOR
---
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
Now create standby redo files
SQL> SELECT group#, member, status FROM V$LOGfile;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> SELECT GROUP#, BYTES/1024, STATUS FROM V$STANDBY_LOG;
GROUP# BYTES/1024 STATUS
---------- ---------- ----------
4 51200 UNASSIGNED
5 51200 UNASSIGNED
6 51200 UNASSIGNED
Setting Enviornment & Listener & Tnsnames.ora files
On Standby
Edit .bash_profile
Change the ORACLE_SID=stdy
$ env |grep ORA
ORACLE_SID=stdy
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
Configure listener.ora & tnsnames.ora on both servers to hold entries for both databases
On Primary
$ . oraenv
orcl
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora
STDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = stdy)(UR = A)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.rhel5.com)
)
)
$ tnsping stdy
$ . oraenv
+ASM
$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
Notice the value (UR=A) in the CONNECT_DATA of the tns entry
This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
On the Standby
Edit listener.ora or use netmgr by setting (+ASM environment) & Add static service (stdy) to the listener
$ cd /u01/app/oracle/product/11.2.0/grid/network/admin
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdy.rhel5.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = stdy)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
$ lsnrctl status
Bounce the listener
$ lsnrctl reload
$ lsnrctl status
Add service (orcl) on the standby
export ORACLE_SID=stdy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ env|grep ORA
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
$ vi tnsnames.ora
STDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = stdy)(UR = A)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.rhel5.com)
)
)
Tnsping from both servers
$ . oraenv
orcl
On Standby
$ tnsping orcl
On Primary
$ tnsping stdy
Copy the password file from Primary to Standby, sys password must be identical
On Primary
$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ scp orapworcl oracle@11gr2sb:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstdy
Configuring primary database initialization parameter’s
SQL> show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdy)';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDY VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=STDY;
System altered.
SQL> ALTER SYSTEM SET FAL_CLIENT=ORCL;
System altered.
Check All the Parameters
SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW FROM V$ARCHIVE_DEST;
DEST_ID VALID_TYPE VALID_ROLE VALID_NOW
---------- --------------- ------------ ----------------
1 ALL_LOGFILES ALL_ROLES YES
2 ONLINE_LOGFILE PRIMARY_ROLE YES
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
orcl
stdy
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=STDY VALID_FOR=(PRIMAR
Y_ROLE,ONLINE_LOGFILES) DB_UNI
QUE_NAME=STDY
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(orcl,stdy)
SQL> show parameter fal_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string ORCL
fal_server string STDY
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
On Standby
In Oracle Home/dbs directory, create a file initstdy.ora containing add below lines. This will serve as the initialization file for the standby instance.
export ORACLE_SID=stdy
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ vi initstdy.ora
db_name=orcl
Start the instance stdy in NOMOUNT state
sqlplus / as sysdba
SQL> startup nomount
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------
local_listener string
On Primary
SQL> alter system set local_listener=
'(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2.rhel5.com)(PORT=1521))' scope=memory;
On Standby
SQL> alter system set local_listener=
'(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2sb.rhel5.com)(PORT=1521))' scope=memory;
Active DataGuard
Now that all initial preparation is completed, it's time to call the all-powerful RMAN script that creates the standby database. On orcl, start RMAN and run the following script. You will find it easier to save this in a file and run the script from the RMAN prompt. In case of RMAN connection error restart auxiliary instance and listener.
On Primary
$ rman
RMAN> connect target sys/oracle@orcl
connected to target database: ORCL (DBID=1223873454)
RMAN> connect auxiliary sys/oracle@stdy
connected to auxiliary database: ORCL (not mounted)
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck
spfile
parameter_value_convert 'orcl','stdy'
set 'db_unique_name'='stdy'
set control_files='+DATA/stdy/control.ctl'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FRA'
set DB_RECOVERY_FILE_DEST_SIZE='10G'
set log_archive_max_processes='5'
set fal_client='stdy'
set fal_server='orcl'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(orcl,stdy)'
set log_archive_dest_2='service=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl';
}
This script creates the standby database, places the appropriate parameters in the spfile for the standby instance, creates the diagnostic destination for the standby database, and restarts the standby.
NOFILENAMECHECK:
Prevents RMAN from checking whether datafiles of the source database share the same names as the standby database files that are in use.
The NOFILENAMECHECK option is required when the standby and primary datafiles and online redo logs have identical filenames. Thus, if you want the duplicate database filenames to be the same as the source database filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK
PARAMETER_VALUE_CONVERT:
Replaces the first string with the second string in all matching initialization parameter values. Note that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are exceptions to this rule and are not affected.
You can use PARAMETER_VALUE_CONVERT to set a collection of initialization parameter values and avoid explicitly setting them all. For example, if the source database uses disk group +ALPHA while the standby database will use +BETA, then you could modify all parameters that refer to these disk groups by specifying SPFILE PARAMETER_VALUE_CONVERT (‘+ALHPA’,'+BETA’).
Add entry into Standby
$ vi /etc/oratab
+ASM:/u01/app/oracle/product/11.2.0/grid:N
stdy:/u01/app/oracle/product/11.2.0/db_1:N
Data Guard & Oracle Restart in 11gR2
$ srvctl add database -d stdy -o /u01/app/oracle/product/11.2.0/db_1 -r PHYSICAL_STANDBY -s MOUNT
$ srvctl config database -d stdy
Database unique name: stdy
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: MOUNT
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups:
Services:
Test the DataGuard Configuration
On the Primary database, connect as HR and add a row to the HR.REGIONS table
SQL> connect hr/hr@orcl
Connected.
SQL> insert into HR.REGIONS values (30,'OpenWorld');
1 row created.
SQL> commit;
Commit complete.
ON Standby
SQL> select * from hr.regions;
Test2
On Primary
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
On Standby
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 30
On Primary
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
On Standby
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 0
Current log sequence 31
SQL> SELECT SEQUENCE#, FIRST_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
1 03-MAR-12 NO
29 03-MAR-12 YES
30 03-MAR-12 YES
31 03-MAR-12 YES
32 03-MAR-12 YES
33 03-MAR-12 YES
34 03-MAR-12 YES
New Columns in v$database
On Primary
SQL> select controlfile_type from v$database;
CONTROL
-------
CURRENT
On Standby
SQL> select controlfile_type from v$database;
CONTROL
-------
STANDBY
SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> select archivelog_compression from v$database;
ARCHIVEL
--------
DISABLED
SQL> select db_unique_name, PRIMARY_DB_UNIQUE_NAME from v$database;
DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME
------------------------------ ------------------------------
stdy orcl
SQL> select dataguard_broker from v$database;
DATAGUAR
--------
DISABLED
SQL> select CONTROLFILE_CONVERTED from v$database;
CON
---
NO
SQL> select remote_archive from v$database;
REMOTE_A
--------
ENABLED
Enable Recovery on the Standby
SQL> select recovery_mode from v$archive_dest_status;
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select recovery_mode from v$archive_dest_status;
Cancel Recovery
SQL> alter database recover managed standby database cancel;
No comments:
Post a Comment