Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted April 29, 2013

From RAC to Standby RAC in No Time

By Alex Polishchuk

Oracle RAC Physical Standby DataGuard Creation

 

The following article describes how to create an Oracle RAC 11gR2 physical standby database with Active DataGuard real-time apply enabled on Linux. This procedure can easily be modified for any other supported platform. Creating a standby database as RAC directly from primary RAC, rather than standalone, saves time and prevents potential issues, while converting from standalone to RAC. In addition, the following approach ensures the proper placement of database files at the standby ASM disk groups.

 

Both systems are using ASM for storage. The storage layout is identical between the primary and the standby databases. System, data, redo log, and fast recovery area are located on separate ASM disks for performance and recoverability reasons.

 

The source and target databases are 2-node Oracle RAC 11gR2. Maintaining standby database as RAC with Active DataGuard and a real-time apply feature allows using it for reading, backup, and testing by converting it over to a snapshot standby database and back after the completion.

 

The following are the names and their meaning used in this article.

 

Name

Description

MY_DB_DOMAIN

Database domain name

MYDB

Database name

STNDBY

Unique database name

stndbyhost1, stndbyhost2

Standby hosts

STNDBY1, STNDBY2

Standby instances

PRIMARY1, PRIMARY2

Primary instances

primaryhost1, primaryhost2

Primary hosts

<hostname>-vip

Host’s virtual IP

primary-grid-scan-name

Primary RAC Grid SCAN

standby-grid-scan-name

Standby RAC Grid SCAN

ASM_diskN

ASM disk name N

Oracle Grid should already be installed and running on all of the standby hosts. Oracle database software should also be installed on all of the standby hosts. All the prerequisites should have been completed for both environments for the Oracle physical standby DataGuard configuration.

The following are the actual steps needed for this process.

1. Prepare the primary database

a. Ensure that the database is in archivelog mode

SQL > SELECT LOG_MODE FROM V$DATABASE;
		LOG_MODE
		------------
		ARCHIVELOG

b. Enable force logging, so that all database changes are logged

SQL > ALTER DATABASE FORCE LOGGING;

c. Create standby redo logs

SQL > ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 '<name>' SIZE <size> GROUP 6 '<name>' SIZE <size> GROUP 7 '<name>' SIZE <size>;
SQL > ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 '<name>' SIZE <size> GROUP 9 '<name>' SIZE <size> GROUP 10 '<name>' SIZE <size>;
 

Where <name> and <size> specify the location and size of log files

Note the following when creating standby redo logs:

  • The quantity is determined by (maximum # of logfile groups +1) * maximum # of threads.
  • Must be the same size as online redo logs.
  • Should not be multiplexed.

2. Create $ORACLE_HOME/dbs/initSTNDBY1.ora on stndbyhost1 to start an auxiliary database instance.

*.db_domain='MY_DB_DOMAIN'
*.db_name='MYDB'
*.db_unique_name='STNDBY'

Note: delete spfileSTNDBY1.ora if one exists.

3. Create adump directory on stndbyhost1 and stndbyhost2.

CMD > mkdir -p $ORACLE_BASE/admin/STNDBY/adump

4. Create and start a temporary listener with static service information on stndbyhost1 in database home.

Edit $ORACLE_HOME/network/admin/listener.ora as follows:

SID_LIST_LISTENER_DBCLONE =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STNDBY.MY_DB_DOMAIN)
      (ORACLE_HOME = $ORACLE_HOME)
      (SID_NAME = STNDBY1)
    )
  )
    
  LISTENER_DBCLONE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL=TCP)(HOST = stndbyhost1)(PORT = 1521))
      )
    )
  )

Start the listener.

CMD > lsnrctl start LISTENER_DBCLONE

5. Add standby database to $ORACLE_HOME/network/admin/tnsnames.ora on primaryhost1.

This entry points to the listener in the previous step.

STNDBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stndbyhost1-vip)(PORT = 1521))
    (CONNECT_DATA = (UR=A)
      (SERVER = DEDICATED)
      (SERVICE_NAME = STNDBY.MY_DB_DOMAIN)
    )
  )

Note: (UR=A) is needed to connect to a BLOCKED unmounted database instance.

6. Modify and reload the standby grid listeners with static service information for the standby database.

Edit $GRID_HOME/network/admin/listener.ora on both standby hosts and add the following configuration.

stndbyhost1

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL=IPC)(KEY=LISTENER))
      (ADDRESS = (PROTOCOL = TCP)(HOST = stndbyhost1-vip)(PORT = 1521))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STNDBY.MY_DB_DOMAIN)
      (ORACLE_HOME = $ORACLE_HOME)
      (SID_NAME = STNDBY1)
    )
  )

stndbyhost2

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = stndbyhost2-vip)(PORT = 1521))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STNDBY.MY_DB_DOMAIN)
      (ORACLE_HOME = $ORACLE_HOME)
      (SID_NAME = STNDBY2)
    )
  )

7. Reload the grid listeners on stndbyhost1 and stndbyhost2.

CMD > lsnrctl reload

8. Copy the password file from primaryhost1 to stndbyhost1 and stndbyhost2.

CMD > scp $ORACLE_HOME/dbs/orapwMYDB1 stndbyhost1:$ORACLE_HOME/dbs/orapwSTNDBY1
    CMD > scp $ORACLE_HOME/dbs/orapwMYDB1 stndbyhost2:$ORACLE_HOME/dbs/orapwSTNDBY2

9. Add instance name to /etc/oratab.

Add STNDBY1 instance name to /etc/oratab on stndbyhost1 and STNDBY2 on stndbyhost2 and set the environment. Note that command line starts with dot “.” to make the environment values permanent.

CMD >. oraenv

10. Start the standby database in nomount state on stndbyhost1.

CMD > sqlplus / as sysdba
SQL > startup nomount
SQL > exit

11. Duplicate the database from primaryhost1.

Note: standby database name STNDBY1 matches to the one in step 4.

Parameter notes:

  • db_create_file_dest and db_recovery_file_dest must be reset in order for all the files to be placed at the same location on destination as on the source.
  • cluster_database must be initially disabled, because duplication works with one node only.
  • remote_listener is required to register the database with SCAN listener.

Allocate multiple primary channels, as the primary work is performed by target channels.

CMD > rman target=sys@PRIMARY1 auxiliary=sys@STNDBY1
     
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 DATABASE FOR STANDBY FROM ACTIVE DATABASE
NOFILENAMECHECK
SPFILE
  PARAMETER_VALUE_CONVERT 'MYDB','STNDBY'
  SET db_unique_name='STNDBY'
  SET db_file_name_convert='/MYDB/','/STNDBY/'
  SET log_file_name_convert='/MYDB/','/STNDBY/'
  SET fal_server='(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = MYDB.MY_DB_DOMAIN)))'
  SET standby_file_management='AUTO'
  SET log_archive_config='dg_config=(STNDBY,MYDB)'
  SET log_archive_dest_1='LOCATION=use_db_recovery_file_dest', 'valid_for=(ALL_ROLES,ALL_LOGFILES)'
  SET log_archive_dest_2='service="(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = MYDB.MY_DB_DOMAIN)))"',
'LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="MYDB" net_timeout=30',
'valid_for=(all_logfiles,primary_role)'
  SET log_archive_dest_state_2='enable'
  SET remote_listener='standby-grid-scan-name:1521'
  SET cluster_database='false'
  RESET db_create_file_dest
  RESET db_recovery_file_dest
;
}
exit

12. Set standby database parameters.

The following parameters were reset before the duplication and should be set now.

Run the following on the stndbyhost1, while replacing “+ARCH” and “+APP” with your own values:

SQL > ALTER SYSTEM SET db_recovery_file_dest='+ARCH';
SQL > ALTER SYSTEM SET db_create_file_dest='+APP';
SQL > ALTER SYSTEM SET cluster_database=true SCOPE=spfile;
SQL > ALTER SYSTEM RESET db_file_name_convert;
SQL > ALTER SYSTEM RESET log_file_name_convert;

13. Create pfile from spfile on stndbyhost1.

CMD > cd $ORACLE_HOME/dbs
SQL > CREATE pfile='p.ora' FROM spfile;

Edit pfile as follows:

  • Remove parameter local_listener, if one exists.
  • Replace all parameter prefixes MYDB[N] with STNDBY[N], while keeping parameter values.

14. Create spfile on ASM from pfile created in the previous step.

SQL > CREATE spfile='+SYS/STNDBY/spfileSTNDBY.ora' FROM pfile='p.ora';

15. Prepare the standby database on stndbyhost1.

a. Shutdown the standby database and listener LISTENER_DBCLONE on stndbyhost1.

b. Rename or delete $ORACLE_HOME/network/admin/listener.ora, as it was used for duplication only.

16. Create initialization file on the stndbyhost1.

a. Remove spfile from $ORACLE_HOME/dbs.

b. Replace the contents of $ORACLE_HOME/dbs/initSTNDBY1.ora as follows:

SPFILE='+SYS/STNDBY/spfileSTNDBY.ora'

17. Prepare database on primaryhost1 for DataGuard.

SQL > ALTER SYSTEM SET log_archive_config='dg_config=(MYDB,STNDBY)';
SQL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=+ARCH valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=MYDB';
SQL > ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION = (LOAD_BALANCE = ON)
		(ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = STNDBY.MY_DB_DOMAIN)))"', 
		'LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="STNDBY" net_timeout=30', 
		'valid_for=(all_logfiles,primary_role)';
SQL > ALTER SYSTEM SET log_archive_dest_state_2='enable';
SQL > ALTER SYSTEM SET dg_broker_start=true;

18. Register RAC on the standby.

Run the following commands on stndbyhost1 or stndbyhost2 to register standby RAC with Oracle Grid, while replacing ASM disk groups “ASM_disk” with the actual ones

CMD > srvctl add database -d STNDBY -o $ORACLE_HOME -c RAC -a "ASM_disk1,ASM_disk2,...,
	ASM_diskN" -p "+SYS/STNDBY/spfileSTNDBY.ora" -m "MY_DB_DOMAIN" -r physical_standby -n MYDB
CMD > srvctl add instance -d STNDBY -n stndbyhost1 -i STNDBY1
CMD > srvctl add instance -d STNDBY -n stndbyhost2 -i STNDBY2

19. Startup the standby RAC from any standby node.

CMD > srvctl start database -d STNDBY

20. Enable flashback and start the Managed Recovery Process (MRP) on stndbyhost1.

SQL > ALTER DATABASE FLASHBACK ON;
	SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If licensed for Active Dataguard (ADG), then open the Standby Database in READ ONLY mode:

SQL > ALTER DATABASE OPEN READ ONLY;

21. Verify the standby database.

On primaryhost1:

SQL > ALTER SYSTEM ARCHIVE LOG CURRENT;

On stndbyhost1 and primaryhost1:

The sequence number should be the same on both databases.

SQL > SELECT sequence#, first_time, next_time, applied
    FROM v$archived_log ORDER BY sequence#;

22. Create DataGuard configuration on the primaryhost1.

CMD > dgmgrl /
 
CREATE CONFIGURATION 'MYDB.MY_DB_DOMAIN' AS PRIMARY DATABASE IS 'MYDB' CONNECT IDENTIFIER IS '(DESCRIPTION = (LOAD_BALANCE = ON)
	(ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = MYDB.MY_DB_DOMAIN)))';
 
ADD DATABASE 'STNDBY' AS CONNECT IDENTIFIER IS '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= standby-grid-scan-name) (PORT=1521))
	(CONNECT_DATA=(SERVICE_NAME=STNDBY.MY_DB_DOMAIN)(SERVER=DEDICATED)))';
 
ENABLE CONFIGURATION;
 
EDIT DATABASE 'STNDBY' SET PROPERTY 'Enterprise Manager Name' = 'STNDBY.MY_DB_DOMAIN';
 
EDIT DATABASE 'MYDB' SET PROPERTY 'Enterprise Manager Name' = 'MYDB.MY_DB_DOMAIN';

23. Add the new standby database to Oracle Enterprise Manager (OEM).

Now, you see how easy it was!

Alex Polishchuk is the founder and president of Advanced Computer Consulting (www.advcomputerconsulting.com). Alex has over twenty years of professional experience designing, developing, and implementing database applications in various industries and companies ranging from small to Fortune 50 corporations. Alex’s primary area of expertise is in database performance optimization and tuning. He can be contacted at alex@advcomputerconsulting.com.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date