CREATING A PHYSICAL STANDBY Single Instance WITH RMAN ACTIVE DUPLICATE IN 11.2.0.3
by Arif MalikOther DBAs have written about this topic, but I wanted it to be available on Pythian’s blog. When I searched for how this was done, other sites were either not very clear on the steps they did, assumed that you already knew what you are doing, or went through the steps too quickly.
If this is your first time building a standby, there is some terminology you need to know before going into any of the steps in creating your physical standby. It will help you to better understand your dataguard environment and what is being done, instead of simply copying a number of steps. These are just the definitions in Oracle’s documentation, but they will help you avoid the arduous search.
LOG_ARCHIVE_DEST_n .- It controls different aspects of how redo transport services transfer redo data from primary database destination to a standby.
This parameter has several attributes that are needed to setup your Dataguard environment, I will only mention the critical ones:
ASYNC .-This is the default, the redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit.
or
SYNC .-The redo data generated by a transaction must have been received by every enabled destination that has this attribute before that transaction can commit.
AFFIRM and NOAFFIRM .- Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.
DB_UNIQUE_NAME .- Specifies a unique name for the database at this destination. You must specify a name; there is no default value.
VALID_FOR .-Identifies when redo transport services can transmit redo data to destinations based on the following factors:
redo_log_type .-whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination
database_role .-whether the database is currently running in the primary or the standby role
FAL_SERVER .-Specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name.
FAL_CLIENT .-Specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client.
The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
LOG_ARCHIVE_CONFIG .- Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs.
This parameter has several attributes, the most important for this exercise is below:
DG_CONFIG .- Specifies a list of up to 30 unique database names (defined with the DB_UNIQUE_NAME initialization parameter) for all of the databases in the Data Guard configuration.
Now that we have the definitions out of the way (which you can find at Oracle 11.2. Documentation), we will continue with the setup of our Physical Standby.
For this exercise, I have the following :
Primary : testgg1 Server : dlabvm13
Standby : testgg2 Server : dlabvm14
The first thing that we need to do is find where the Redo Logs and Datafiles reside in the Primary and where will they reside in the Standby so that you can set your parameters LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT properly. Make sure that these directories have the necessary space to hold the Primary database. If you don’t have this space, then do not continue.
Next, assure that you are in archivelog mode, and that force logging is enabled in your primary.
If your database is not in archive log mode, do the following:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 364904448 bytes
Redo Buffers 3747840 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
Now that we are running in archive log mode and force logging is set for the primary, make sure that the Listener/ Tns entries are set correctly and that you can tnsping them both from the primary/standby.
For more details on how to set them up, go to Kamran’s blog entry ( Step 2 ).
The result in both the primary and standby servers should be similar to below:
oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ tnsping testgg1
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:34
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm13.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg1)))
OK (0 msec)
oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ tnsping testgg2
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-MAY-2013 09:44:40
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dlabvm14.dlab.pythian.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testgg2)))
OK (10 msec)
Then, create and replicate the password file from the primary $ORACLE_HOME/dbs, and rename it to the standby database name. The password file name must match the ORACLE_SID used at the standby site, not the DB_NAME.
oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle $ orapwd file=orapwtestgg1 password=test entries=5
oracle@dlabvm13.dlab.pythian.com [testgg1] /u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle $ scp orapwtestgg1 dlabvm14:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestgg2
orapwtestgg1 100% 2048 2.0KB/s 00:00
Now that the password file has been created, you can setup the init file for the Primary Database. As you can see at the end, it’s the parameters we explained at the beginning of this entry.
SQL> !cat dg.ora
*.audit_file_dest='/u01/app/oracle/oradata/dump/testgg1/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/testgg1/control/control01.ctl','/u01/app/oracle/oradata/testgg1/control/control02.ctl'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain=''
*.db_name='testgg1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=2G
*.db_unique_name='testgg1'
*.diagnostic_dest='/u01/app/oracle/oradata/dump/testgg1'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testgg1'
*.log_archive_dest_2='SERVICE=testgg2 ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testgg2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.open_cursors=300
*.pga_aggregate_target=128M
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=512M
testgg11.UNDO_TABLESPACE='UNDOTBS1'
FAL_SERVER=testgg2;
FAL_CLIENT=testgg1;
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/','/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/','/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(testgg1,testgg2)'
One of the coolest things about this method is that almost all of the work we will do will be in the Primary database server. The only thing you have to do in the Standby Server is create the locations of my diagnostic files/redo/datafiles/control files, verify the connectivity between the Primary and the Standby and just start the Standby Instance, which is our next step.
The next step is to set the ORACLE_SID, ORACLE_HOME, and ORACLE_BASE for the Standby Instance and open it with minimal options:
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_BASE=/u01/app/oracle
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ export ORACLE_SID=testgg2
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ cat dg.ora
DB_NAME=testgg1
DB_UNIQUE_NAME=testgg2
DB_BLOCK_SIZE=8192
oracle@dlabvm14.dlab.pythian.com [testgg2] /home/oracle/bin
oracle $ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 09:53:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/bin/dg.ora';
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now let’s get back to the Primary database server, where the next thing to do is add the standby logs. In this case, I created two with the same size as the Online Redo Logs that I have in my Primary.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg1 dlabvm13.dlab.pythian.com
SQL> select name from v$database;
NAME
---------
TESTGG1
SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;
GROUP# THREAD# MEMBERS BYTES
---------- ---------- ---------- ----------
1 1 1 104857600
2 1 1 104857600
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 104857600;
Database altered.
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES
---------- ----------
3 104857600
4 104857600
Once we have created the Standby Logs in my Primary Database, we will create the following RMAN run commands and the changes to the standby’s spfile in this RMAN block.
oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ cat duplicate.rmn
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'testgg1','testgg2'
set db_unique_name='testgg2'
set db_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/datafile/','/u01/app/oracle/oradata/testgg2/TESTGG2/datafile/'
set log_file_name_convert='/u01/app/oracle/oradata/testgg1/TESTGG1/onlinelog/','/u01/app/oracle/oradata/testgg2/TESTGG2/onlinelog/'
set control_files='/u01/app/oracle/oradata/testgg2/control/control01.ctl','/u01/app/oracle/oradata/testgg2/control/control02.ctl'
set log_archive_max_processes='10'
set fal_client='testgg2'
set fal_server='testgg1'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(testgg1,testgg2)'
set log_archive_dest_2='service=testgg1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=testgg1'
;
}
Now that the the RMAN command file has been created, you just need to run this command in the target database, which is the Primary Database , connecting to the Standby as the auxiliary.
oracle@dlabvm13.dlab.pythian.com [testgg1] /home/oracle/bin
oracle $ rman target sys/test@testgg1 auxiliary sys/test@testgg2
Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 16 08:42:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTGG1 (DBID=839852638)
connected to auxiliary database: TESTGG1 (not mounted)
RMAN> @duplicate.rmn
RMAN> exit
As you can see, that was as easy as pie. Now we can just start the recovery process in the Standby Database. In this case, I used Active Dataguard so that I could show you that it is actually working, but just be aware that this is a Licensable Option.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL> select protection_mode,protection_level,database_role,name from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg2 dlabvm14.dlab.pythian.com
SQL> select * from sender.test;
select * from sender.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
Now, I go back to the primary and create a table called test in the schema sender. As you can see above, this table doesn’t exist in the Standby database.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg1 dlabvm13.dlab.pythian.com
SQL> select protection_mode,protection_level,database_role,name from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TESTGG1
SQL> create table sender.test(id number);
Table created.
SQL> insert into sender.test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
I go back to the Standby, and we can see that we have the table that we just created.
SQL> select protection_mode,protection_level,database_role,name from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TESTGG1
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
testgg2 dlabvm14.dlab.pythian.com
SQL> select * from sender.test;
ID
----------
1
There are various ways to see if this is working or not. You can use it in the following query to see what was the last archive received/applied in the Standby Database.
SQL> SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES'
)
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );
LOGS TIME THREAD# SEQUENCE#
---------------- ------------------ ---------- ----------
Last Applied : 16-MAY-13:11:42:52 1 65
Last Received : 16-MAY-13:11:42:52 1 65
While in the Primary Database, you can check what is the current sequence and what is the pending sequence to be applied.
SQL> SELECT
(SELECT name FROM V$DATABASE
) name,
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
) Current_primary_seq,
(SELECT MAX (sequence#)
FROM v$archived_log
WHERE TRUNC(next_time) > SYSDATE - 1
AND dest_id = 2
) max_stby,
(SELECT NVL (
(SELECT MAX (sequence#) - MIN (sequence#)
FROM v$archived_log
WHERE TRUNC(next_time) > SYSDATE - 1
AND dest_id = 2
AND applied = 'NO'
), 0)
FROM DUAL
) "To be applied",
(
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
) -
(SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
)) "To be Shipped"
FROM DUAL;
NAME CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
TESTGG1 65 65 0 0
No comments:
Post a Comment