Advertisements
Skip to content
March 10, 2012 / Shivananda Rao P

Creating Physical Standby Database on Oracle 11g

Some key points before proceeding with the physical standby setup.

 

Primary database should be in archivelog mode.
Forced Logging is on in Primary database (sql>alter database force logging;)
Initialization parameter “db_name” should be same on both primary and standby database.
Initialization parameter “db_unique_name” should be different on primary and standby databases.

 

Primary Database : PRIM

 

Standby Database : STND

 

1. Check if the primary database is using the password file or not. If not, then create one as below.

 

orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y

 

2. Add the following parameters in the initialization parameter file of the primary database.

 

initprim.ora

prim.__db_cache_size=46137344
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__oracle_base='/u01/home/oracle'#ORACLE_BASE set from environment
prim.__pga_aggregate_target=205520896
prim.__sga_target=239075328
prim.__shared_io_pool_size=0
prim.__shared_pool_size=167772160
prim.__streams_pool_size=8388608
*.audit_file_dest='/u01/home/oracle/admin/prim/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prim/control01.ctl','/u01/app/oracle/oradata/prim/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.diagnostic_dest='/u01/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.db_recovery_file_dest='/u01/app/fra'
*.db_recovery_file_dest_size=2070572032 #(I have it as nearly 2 GB)
*.memory_target=444596224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='prim'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
*.log_archive_dest_2='service=stnd valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stnd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable

 

3. Setup the connectivity (listener.ora and tnsnames.ora) for the primary and standby databases.

 

Primary:

listener.ora file

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
	(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
	)
)

SID_LIST_LISTENER =
(SID_LIST =
	(SID_DESC =
		(SID_NAME = prim)
		(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
	)
)

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

 

tnsnames.ora file

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIM =
(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVICE_NAME = prim)(UR=A)
	)
)

STND =
(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVICE_NAME = stnd)(UR=A)
	)
)

 

Standby:

 

listener.ora file

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
	(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
	)
)

SID_LIST_LISTENER =
(SID_LIST =
	(SID_DESC =
		(SID_NAME = stnd)
		(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
	)
)

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

 

tnsnames.ora file

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIM =
(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVICE_NAME = prim)(UR=A)
	)
)

STND =
(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVICE_NAME = stnd)(UR=A)
	)
)

 

4. Add the following parameters in the initialization parameter file of the standby database

 

initstnd.ora

stnd.__db_cache_size=230686720
stnd.__java_pool_size=4194304
stnd.__large_pool_size=4194304
stnd.__pga_aggregate_target=268435456
stnd.__sga_target=394264576
stnd.__shared_io_pool_size=0
stnd.__shared_pool_size=146800640
stnd.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stnd/adump'
# *.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/stnd/control01.ctl','/u02/app/oracle/oradata/stnd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.db_recovery_file_dest='/u01/app/fra'
*.db_recovery_file_dest_size=2070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stndXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=660602880
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stnd'
*.log_archive_dest_2='service=prim valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.db_unique_name='stnd'
*.db_file_name_convert='/u01/app/oracle/oradata/prim/','/u02/app/oracle/oradata/stnd/'
*.log_file_name_convert='/u01/app/oracle/oradata/prim/','/u02/app/oracle/oradata/stnd/'
*.standby_file_management=AUTO
*.FAL_SERVER='prim'
*.FAL_CLIENT='stnd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer

 

5. Now copy the password file of the primary database “orapwprim” located at $ORACLE_HOME/dbs to the standby server location $ORACLE_HOME/dbs and rename the file as “orapwstnd”.

Note that the format of the password file in Windows is as pwd<SID>.ora (Example: pwdPRIM.ora)

 

6. Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the Primary database is open and the standby database is in nomount stage (STARTED).

 

rman target sys/<password>@prim auxiliary sys/<password>@stnd

 

Now perform the duplicate operation to create the standby database.

 

rman>duplicate target database for standby from active database nofilenamecheck;

 

7. Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.

 

sqlplus sys/<password>@stnd as sysdba

 

Check the status of the standby database by making sure it is in mount stage.

 

sql>select status,instance_name,database_role from v$instance,v$database;

 

8. Now start the managed recovery process on the standby database.

sql>alter database recover managed standby database disconnect from session;

 

9. Now check if the managed recovery process (MRP) has been started on the standby database or not.

 

sql>select process,status,sequence# from v$managed_standby;

 

Example:

 

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         39

 

Here, the MRP has been started and is waiting for the log sequence# 39. If MRP is not started, then the above query would not show up the MRP0 under the process column.

 

10. On the primary database, perform a few log switches and check if the logs are applied to the standby database.

 

sqlplus sys/<password>@prim as sysdba

sql&gt;alter system switch logfile;

sql>select max(sequence#) from v$archived_log;

 

Example:

 

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
38

 

sqlplus sys/<password>@stnd as sysdba

sql>select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

Example:

 

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
---------- --------------
     1             38

 

Here, the maximum sequence# generated on the Primary database is 38 and the maximum sequence# applied on the standby database is also 38 which means that the standby database is in sync with the primary database.

Here you go !!!

 

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

 

 

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

 

Advertisements

33 Comments

Leave a Comment
  1. ramakantc / May 24 2012 9:02 am

    Wonderful article in simple words. Very easy to understand.
    Will I get informed whenever you post any new article?

  2. ramakantc / May 24 2012 9:10 am

    I have a question. I am trying to Create a Standby Database which will share the same storage as Primary Database ie ASM.
    Just wondering whether I need to do some changes in the parameter file of both Primary and standby parameter file .

    Appreciate for your advise.

  3. Shivananda Rao P / May 24 2012 9:57 am

    Hi Ramakant,
    Since you are having both the primary and standby on ASM, you’ll have to make sure the following parameters exist in your standby Pfile.

    db_create_file_dest=’+(disk group name on standby server where you want to place the standby datafiles)’
    control_files=’+(disk group name on standby server where you want to have the control files)’

    the value for db_create_file_dest and control_files can be same.

    If you intend to have the Flash Recovery Area set up, then you can make use of the below parameters.

    db_recovery_file_dest_size=(size)
    eg: db_recovery_file_dest_size=4000M

    db_recovery_file_dest=’+(disk group name on standby server where you want to have the FRA setup)’

  4. Shivananda Rao P / May 24 2012 10:00 am

    Thank you Ramakanth. You can follow the blog and you would get informed of any new posts that would be done here.

  5. ramakantc / May 24 2012 10:22 am

    Thank you so much for your advise.

    Have a nice day!

  6. site / Jun 7 2012 11:31 pm

    I was basically wanting to know if you ever thought of adjusting the design of your blog? It is well written; I really like what you have got to state. But maybe you could add a a bit more in the way of content so people can connect to it better. You have got a great deal of text for only having one or two photos. Maybe you can space it out better?

  7. san / Sep 21 2012 6:54 am

    NICE DOCUMENT THANKS. if possiable upload RAC DOcument THANKS IN advance

  8. Shivananda Rao P / Sep 22 2012 3:52 pm

    Hi San, would try to upload documents related to the issues with RAC soon.. Thanks

  9. hari / Jan 9 2013 11:21 am

    awesome artical.it is very helpful to me.

  10. Shivananda Rao P / Jan 9 2013 11:36 am

    Thank you !!

  11. Ravivarma / Jan 22 2013 7:55 am

    Hi Mr. Shivananda,
    Thank you very much for spending your time in preparing this Doc., I am new to oracle11g, I want to create standby mirroring database on a different box that is there in same LAN, Can you please explain the process.
    Thanks in advance..

  12. Krishna / Jan 31 2013 4:47 pm

    Really nice article. I have a question, what is the next step after the above process, do we need to configure the dataguard using Grid Control ‘Add Existing Standby Database’ option ?
    Basically, my requirement is to create a standby database (without using Grid since we have a copy script to copy database) and after that I need to configure the two databases for DR using Dataguard. Please advice.

  13. Shivananda Rao P / Jan 31 2013 6:28 pm

    Hello Krishna,

    With the above mentioned steps, your standby database is created successfully and would be ready to use. These steps mentioned in the above blog is for creating a physical standby database manually (without using Grid Control).

    Regards,
    Shivananda

  14. Krishna / Feb 11 2013 2:34 pm

    Thanks a lot for your advice Sir. !

  15. Krishna / Feb 26 2013 8:50 pm

    Hello Sir,

    I have successfully implemented the Physical Standby Database.

    I checked my V$log and also the sequence numbers, where both primary and secondary are in sync.

    But, for some reason, I am not able to open my standby database in Read only Mode, alert log just stucks with a message
    “Media Recovery Waiting for thread 1 sequence 175 (in transit)”

    Any idea on this ? I am using 11.2.0.3 version of DB, if my understanding is correct 11g should support Active DAtaguard which should allow Read only access along with Archive logs apply.

    Thanks,
    Krishna

  16. Dev Arora / Mar 8 2013 6:52 am

    Thanks Mr. Rao! it is very useful and easy to understand article. Please post article for RAC setup if possible.

  17. Shivananda Rao P / Mar 8 2013 11:25 am

    Hi Dev,

    I would soon be uploading doc on RAC related concepts…

    Regards,
    Shivananda

  18. yasir / Mar 15 2013 10:22 am

    where initprim.ora file, Rao means spfile

  19. ILP / Mar 20 2013 4:23 pm

    Hi Shivananda,
    What about a scenario where I do not have RMAN setup in my environment? How do I duplicate the standby database? what are the commands and steps please? I am new with this DG technology.
    Thanks very much for an awesome simple to follow set of intructions..
    ILP

  20. Shivananda Rao P / Mar 21 2013 11:15 am

    Hi,

    In that case, you can go with the hot backup method. Here is a link which shows how to create physical standby database using hot backup method.

    https://sites.google.com/site/catchdba/physical-standby-database-using-hotbackup

    Regards,
    Shivananda

  21. ILP / Mar 21 2013 12:18 pm

    Thanks so much for your quick response!

  22. Atul / Jun 18 2013 11:37 am

    It worked very nicely for me and clearing data guard conecpts Thanks a lot

  23. andrew / Jul 29 2013 7:51 pm

    select parameter,value from v$option where parameter=’Active Data Guard’;
    should return TRUE. active data guard need license.

  24. Jon / Aug 14 2013 2:44 pm

    Thanks for posting this blog. I really appreciate it.

    I’m trying to use this in 11g (11.2.0.2). I’ve completed the first 5 steps. In Step 6 above, it states to make sure the Standby database is started in nomount mode. When I do that and issue the subsequent rman statement “rman target sys/@prim auxiliary sys/@stnd”, it fails with the message :

    “RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections”

    This is because it’s in nomount state, right? This is obviously working for everyone else, what am I missing? Thanks.

  25. Shivananda Rao P / Aug 14 2013 3:31 pm

    Hi Jon,

    Make sure that your TNS entries for both primary and standby databases on both primary and standby servers have the entry as “UR=A” after the “SERVICE_NAME” entry as mentioned in step 3.

    Sample:

    PRIM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = prim)(UR=A)
    )
    )

    Regards,
    Shivananda

  26. Jon / Aug 14 2013 7:23 pm

    Perfect. Many thanks!

  27. Dawn Foltz / Oct 16 2013 9:06 pm

    Hi –
    This was excellent. Do you have the RAC document? Where can I find it?
    dawn.foltz@wyo.gov

  28. Rajesh / May 14 2014 8:23 am

    I’m trying to use this in 11g (11.2.0.2). I’ve completed the first 5 steps. In Step 6 above, it states to make sure the Standby database is started in nomount mode. When I do that and issue the subsequent rman statement “rman target sys/@prim auxiliary sys/@stnd”, it fails with the message :

    “RMAN-04006: error from auxiliary database: ORA-12154: TNS: could not resolve the connect identifier specified

  29. Rajesh / May 14 2014 10:31 am

    I’m trying to use this in 11g (11.2.0.2). I’ve completed the first 5 steps. In Step 6 above, it states to make sure the Standby database is started in nomount mode. When I do that and issue the subsequent rman statement “rman target sys/@prim auxiliary sys/@stnd”, it fails with the message :

    “RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

  30. Shivananda Rao P / May 14 2014 5:19 pm

    Hi Rajesh,

    Please check if you are able to connect to the standby database with SYS user. Also, make sure you have the password file created and have the “remote_login_passwordfile” parameter set to “EXCLUSIVE”.

    Regards,
    Shivananda

  31. htc one m8 mini / May 27 2014 9:37 am

    Merci pour votre expertise sur le sujet. En vous
    souhaitans plein dee succès.

  32. vivek / Jun 15 2014 10:19 am

    Simply awesome great..!!!

Trackbacks

  1. How to setup active dataguard in oracle 11g « shivanandarao

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: