Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 15 April 2012

Manually Creating a Physical Standby Database in oracle 9i windows Env.

A standby database is a shadow copy of primary database on a remote as well as local server. It protects against data loss and availability of the primary database. It is synchronized with the primary database on continual basis and can be used for disaster recovery, backups, replication, high availability and reporting purpose.
A standby database is far better to a normal backup as it is instantly available in the event of a disaster or failure. To restore a backup you need more time, and also during the restore process the system is not available. With a standby database there is nothing to restore in the event of a failure as the standby database is always available, up to date and ready to take over. It is possible to switch applications over to the standby database in a matter of minutes to allow business continuity. The two different types of standby database: physical or logical standby.
Physical standby database provides the physical identical copy of the primary database on a block per block basis. The database schema including index is the same as primary database. It synchronize with primary through redo apply.
Logical standby database contains the same logical information where as physical organisation and structure of data can be different. It synchronize with primary through SQL apply, which transforms the data in the redo received from  primary database into SQL statements and then executing the SQL statements on the standby database.
The below example shows the manual configuration of physical standby database.
Technical Overview:
Database updates are captured in redologs and a copy is managed in the form of archive log. These archive logs are automatically transferred and applied to the standby server and ensuring the synchronization with primary server.
Configuration Requirements:
       Requires the same version and release of the Oracle database for the primary and all standby
       Each primary database and standby database must have its own control file.
       The primary database must run in ARCHIVELOG mode
       Requires the same hardware architecture on the primary and all standby sites.
       Does not require the same version and release of the operating system on the primary and all standby sites
       Database Version: Oracle 9.2.0.1.0
       Assumption:  The other Server having the same Configuration and Structure

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Step 1 - Prepare the Primary for Standby
Step 2 - Copy the necessary files to standby system
Step 3 - Configure the Standby Parameters
Step 4 - Configure OracleNet
Step 5 - Startup the Standby Site
Step 6 - Begin Shipping and Applying Redo

 

Setup the Primary Database
























Check Archiving and Force Logging






























Copy the Data files to the Standby


































Note: While the database is shutdown copy all dbf files from Primary Server to Standby server location.

Standby Control file and Init file
















Note: You cannot use a single control file for both the primary and standby databases.
Now Copy Control file and Pfile from the primary server to the standby server location.

Setup the Standby Init.ora Parameters
# Cannot change db_name
db_name=sadhan
# standby must be in current compatibility 
compatible=9.2.0.1.0
# Must change control file to pint to the standby controlfile
control_files= ‘D:\oracle\oradata\sadhan\standby\sadhan2.ctl’ 
# leave our local destination and send incoming redo to the same place
standby_archive_dest= ‘E:\oracle\standby’ 
log_archive_format= ‘ARC%S.%T’
log_archive_dest_1=(‘LOCATION=E:\oracle\standby’)
#Make sure local archiving is on and enable remote sending and recieving
log_archive_start=TRUE 
remote_archive_enable=TRUE
# Enable automatic datafile addition when datafiles are added to the primary
standby_file_management=AUTO
# May change instance name
instance_name=sadhan2
# Do not need lock name space really
lock_name_space=’’
# Add setup a service name for this standby
Service_names=’sadhan2’

Create a Windows Service for Standby

C:\oradim -NEW -SID sadhan2 -STARTMODE manual
Note: You must create same folder structure on standby server as on primary.

Configure Listeners for the Primary and Standby Databases

On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases. If you plan to manage the configuration using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register service information for each database using the SID for the database instance.
To restart the listeners (to pick up the new definitions), enter the following LSNRCTL utility commands on both the primary and standby systems:
C:\>lsnrctl stop
C:\>lsnrctl start
Enable Dead Connection Detection on the Standby System
Enable dead connection detection by setting the SQLNET.EXPIRE_TIME parameter to 2 in the SQLNET.ORA parameter file on the standby system. For example:
SQLNET.EXPIRE_TIME=2

Create Oracle Net Service Names

On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by log transport services.
Setup the Production Side TNS

Setup the Standby Side TNS



















Create a Server Parameter File for the Standby Database

SQL> CREATE SPFILE FROM PFILE=‘D:\oracle\ora92\dbs\initsadhan2.ora’;

Before Launch the Standby Database Create password file for it

D:\orapwd file=D:\oracle\ora92\database\orapwdsadhan2.ora password=oracle

Start the Physical Standby Database

On the standby database, issue the following SQL statements to start and mount the database in standby mode:
CMD>SQLPLUS “sys/oracle as sysdba”
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Initiate Log Apply Services

On the standby database, start log apply services as:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.

Start Sending Redo: Work on Primary database

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=‘SERVICE=sadhan2’ SCOPE=BOTH; 
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

Archiving of redo logs to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log becomes full. To force the current redo logs to be archived immediately as we know, use the SQL ALTER SYSTEM statement on the primary database. For example:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Verify the Primary is sending Redo

Once you create the physical standby database and set up log transport services, you may want verify that database modifications are being successfully shipped from the primary database to the standby database.
On the standby database, query the V$ARCHIVED_LOG view to identify existing archived redo logs. For example:
SQL> Select dest_id,sequence#,archived,applied from v$archived_log order by 1,2;
SQL> Select sequence#,first_time,next_time from v$archived_log order by sequence#;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Then again verify that new archived redo log was applied
 SQL> Select sequence#,first_time,next_time from v$archived_log order by sequence#;

Make Sure the Redo’s are being used on primary and standby such as:

SQL> Alter system archive log current;
SQL> select group#, sequence#, status from v$log;
    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         5 INACTIVE
         2         6 ACTIVE           --- Primary Site
         3         7 CURRENT


SQL> alter database recover managed standby database disconnect from session;
SQL> select group#, sequence#, status from v$standby_log;
GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         4         7 ACTIVE
         5         0 UNASSIGNED      --- Standby Site
         6         0 UNASSIGNED

9 comments:

  1. Hi getting a very different issues. All were fine, just few days ago, very old log sequence is getting searched and physical server is out of sync now, Tried all method of registering and all but nothing worked. This us Oracle 9i setup. Plz suggest.

    ReplyDelete
  2. Also you have not mentioned whether we need to copy the redo log file from primary to secondary or not. Like in some cases we can have 3 + 3 = 6 files, like 3 for online redolog file and 3 files for standby redo log file.

    ReplyDelete
    Replies
    1. Consider while following the document, you have already crated SRL(standby redo logs) ‎while preparing the primary site for standby creation (if not) then create as:‎
      SQL> ALTER DATABASE ADD STANDBY LOGFILE ‎‎('D:\oracle\oradata\sadhan\stby_redo01.log') SIZE 52428800;‎
      SQL> ALTER DATABASE ADD STANDBY LOGFILE ‎‎('D:\oracle\oradata\sadhan\stby_redo02.log') SIZE 52428800;‎
      SQL> ALTER DATABASE ADD STANDBY LOGFILE ‎‎('D:\oracle\oradata\sadhan\stby_redo03.log') SIZE 52428800;‎
      SQL> select GROUP#, BYTES FROM V$LOG;‎
      GROUP# BYTES
      ‎------- --------‎
      ‎ 1 52428800‎
      ‎ 2 52428800‎
      ‎ 3 52428800‎
      SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;‎
      GROUP# BYTES
      ‎------ ---------‎
      ‎ 4 52428800‎
      ‎ 5 52428800‎
      ‎ 6 52428800‎
      And then after, you have to copy Your full database Backup created, Standby Controlfile ‎Created, Pfile created for further modification for standby from Primary to standby site.‎

      Delete
    2. Thanks a lot for your response!!
      Yes on production server, we have three online redo log file and three standby redo log file. So was just curious whether I need to copy these 6 log files or not or I need to create these files on standby server of same config, as every time I am trying to restore the archive file, db engine is kept on looking for very old archive backup seq number.
      For full backup copy: are you saying to just copy the db files to standby server or you want me to take rman backup and then restore that there?
      I have been creating standby control file every time,
      For Pfile: do I need to copy this also and then update on standby?
      and do I also need to create the passwd file or existing one will be ok.
      IN my case: What I was doing:
      I was shutting down the standby then, copying all dbf files, creating control file for standby and then starting up the db and trying to bring database in sync mode which is not happening.
      Please suggest if anywhere I am missing something.
      Thanks for help!!

      Also:

      Delete
    3. Taking or copying full backup in case you are using RMAN but in your case (manual ‎configuarion).‎
      Copy all your datafiles with tempfile Primary to standby (create same directory structure ‎on standby site)‎
      Copy all your online log files primary to standby.‎
      Create standby controlfile on primary and copy it on to the standby site.‎
      Copy Pfile fom primary and copy to standby then further modify.‎
      Either you can create password on standby or Copy the primary pwdfile to standby and ‎rename it (any one).‎
      Configure tns and listener for both primary and standby site
      Then finally startup in mount standby db using modified pfile and create spfile from this ‎pfile and try to bounce back the database again
      SQL>startup nomount pfile=’D:\oracle\ora92\dbs\standby.txt’;
      SQL>create spfile from pfile=’D:\oracle\ora92\dbs\standby.txt’;‎
      SQL>shutdown immediate;
      SQL>startup mount;‎
      Now try to start redo apply
      SQL>alter database recover managed standby database disconnect from session;‎
      Hope it will help you.‎

      Delete
    4. Hi Shahid, please provide your comment!! It will help me for sure.

      Delete
  3. HI,

    CAN U PLEASE ELABORATE ON

    "Configure Listeners for the Primary and Standby Databases
    On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases. If you plan to manage the configuration using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register service information for each database using the SID for the database instance."

    ReplyDelete
    Replies
    1. Dear,

      If you already configure the listener on primary database server then you need to do only ‎necessary addition:‎
      For Example: on standby database server update Listener.ora
      ‎(SID_DESC =
      ‎(GLOBAL_DBNAME = SADSTBY)
      ‎(ORACLE_HOME = d:\oracle)
      ‎(SID_NAME = SADSTBY)
      ‎)‎
      ‎#Update tnsnames.ora on primary node:
      SADSTBY =
      ‎(DESCRIPTION =
      ‎(ADDRESS_LIST =
      ‎(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.222)(PORT = 1521))
      ‎)
      ‎(CONNECT_DATA =
      ‎(SERVICE_NAME = SADSTBY)
      ‎)
      ‎)‎

      Delete
  4. how can we create a manual standby database on oracle 10g Standard edition, windows 2008. Please mail me the procedure and scripts @ ateeqrahman786@gmail.com.

    Jazakallah khair

    ReplyDelete