Always keep the Backup copy of the control file before any operation.
Identify the Primary Database Datafiles Primary
Make a Copy of the Primary Database Primary
Create a Control File for the Standby Database Primary
Prepare the Initialization Parameter File to be Copied to the Standby
Database Primary
Copy Files from the Primary System to the Standby System Primary
Set Initialization Parameters on a Physical Standby Database Standby
Create a Windows Service Standby
Configure Listeners for the Primary and Standby Databases Primary and Standby
Enable Dead Connection Detection on the Standby System Standby
Create Oracle Net Service Names Primary and Standby
Create a Server Parameter File for the Standby Database Standby
Start the Physical Standby Database Standby
Initiate Log Apply Services Standby
Enable Archiving to the Physical Standby Database Primary
1. Primary database should be in archive log mode:-
Connect as sysdba
SQL>Select log_mode from v$database; Or
Sql>Archive log list;
Sql>Alter database archivelog;
If automatic archival is not enabled then enable it using:-
Sql>Alter system Archive log start;
2. Enable Force Logging to Primary database:-
Sql>Alter database force logging; (It force the writing of redo records. FORCE LOGGING is to ensure complete media recovery while it degrade the performance.)
Antonym:
(Alter database noarchivelog;
Alter database no force logging;)
3. Set the local archive destination:-
Sql>Alter system set log_archive_dest_1=’LOCATION=d:\oradata\oraHome92\oradata\BST1 Mandatory’ scope=BOTH;
4. Identifying the Pr. Database datafile:-
Sql>Select name from V$DATAFILE;
5. Make a copy of the Pr.database file:-
Sql>Alter system switch logfile;
Sql>shutdown immediate;
Now copy all the datafile and place into a temp folder to transfer to Standby Site.
Sql>Startup;
6. Now make a standby control file:-
Sql>Alter database create standby controlfile as ‘d:\oradata\oraHome92\oradata\BST1\Stby_Cf.ctl’;
7. Prepare the Initialization Parameter file to be copied to the Standby Site:-
Sql>Create PFILE=’d:\oradata\oraHome92\dbs\InitStby.ora’ from SPFILE;
8. Transfer the Backup datafile,Control file and InitStby initialization file to the Standby site at same position.
9. Modify the Initialization file on Primary and Standby site both as follows:-
On Pr.Dbase:—
Log_ archive_dest_1=’LOCATION=d:\oradata\oraHome92\oradata\BST1\Arch _ive REOPEN=5 MAX_FAILURE=3’
Log_archive_dest_2=’SERVICE=BST1SBY LGWR ASYNC AFFIRM’(or BST1 whatever oracleNetServiceName is.)
Log_archive_dest_state_1=enable
Log_archive_dest_state_2=enable
On Standby Site:—
Log_ archive_dest_1=’LOCATION=d:\oradata\oraHome92\oradata\BST1\Archive’
Log_archive_dest_2=’SERVICE=BST1SBY LGWR ASYNC AFFIRM’(or BST1 whatever oracleNetServiceName is.)
Log_archive_dest_state_1=enable
Log_archive_dest_state_2=enable
Log_archive_start=true
Standby_file_management=AUTO
Remote_archive_enable=true
Remote_archive_dest=’D:\….\….\…\Archive’
Fal_server=Primary1(net service name on the standby machine for Pr.Dbase) optional
Fal_Client=BST1SBY(net service name on the Pr.Dbase for standby
dbase) optional
10. Create Oracle Net configuration on both the machine to connect each other.
Eg.
Tnsnames.ora File on Primary site will look like
# standby db identification on Pr. Site
BST1SBY.RAMTECH.COM = Net Service Name Standby host
(DESCRIPTION = of Stby db.
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAMTECH-144)(PORT = 1521))
)
(CONNECT_DATA =
(SID = KGIS)
)
)
Tnsnames.ora File on Standby site will look like
# Primary db identification on Standby Site
PRIMARY1.RAMTECH.COM = Net Service Name pr.Host
(DESCRIPTION = of Pr.Db.
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RAMTECH-201)(PORT = 1521))
)
(CONNECT_DATA =
(SID = KGIS)
)
)
BST1.RAMTECH.COM=->GLOBALNAME TO ACCESS FOR EX.CONNECT SYS/RAMTECH@BST1
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ramtech-117)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID= BST1SBY) —->> ORADIM…SID NAME
)
)
Listener.ora File on Standby site will look like
Add the standby database details in listener file on Standby machine.
No changes reqd in listener file on Pr. Machine.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ramtech-144)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oradata\oraHome92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = BST1)
(ORACLE_HOME = D:\oradata\oraHome92)
(SID_NAME = BST1SBY)
)
)
11. Create a window service on Standby machine:-
C:\>oradim –NEW –SID BST1SBY –STARTMODE MANUAL
–PFILE D:\oradata\oraHome92\dbs\file_name.ora
12. Stop and Start the listener:-
C:\>lsnrctl stop/reload/start/status
13. Enable the dead connection detection on the Standby site in SQLNET.ORA file in admin folder
SQLNET.Expire_time=2
SQLNET.AUTHENTICATION_SERVICES= (NTS)
14. Create a Password file on Standby site:-
C:\>orapwd file= D:\oradata\oraHome92\database\PWDBST1Sby.ora password=ramtech entries=10
15. Create a Server parameter file on Standby database:
Note:- Before creating SPFILE make sure that all the necessary Initialization parameter text file entries done.
Sql>connect sys/ramtech@BST1 as sysdba
Connected to an idle instance.
Note:–If there is End –Of-File Communication error comes,then first restart the service and after that connect to an idle instance and then create spfile from pfile.
Sql>create SPFILE from PFILE=’ D:\oradata\oraHome92\dbs\file_name.ora’
16. start the standby database:-
Sql>startup nomount;
Sql>alter database mount standby database;
Sql>alter database recover managed standby database disconnect from session;
17. On Primary site:-
Enable archiving as follows:-
Sql>Alter system set log_archive_dest_2=’SERVICE=BST1SBY LGWR ASYNC AFFIRM’;
Sql>Alter system set log_archive_dest_state_2=Enable;
18. Start Remote archiving on Pr.database to push log file manually:-
Sql>Alter system archive log current;
19. Check the update on Standby site:-
Sql>alter database recover managed standby database CANCEL;
Sql>alter database open read only;
Sql>select * from scott.emp;(Check update if any)
20. To bring up the database in recovery mode shutdown it first and then startup nomount……….like before till alter database recover …..statement.
For Maximum Protection:-
Log_archive_dest_3=’SERVICE=BST1SBY LGWR SYNC AFFIRM’
Start database in mount mode (not open)
Sql>Alter database set standby database to maximize protection;
(By default protection mode is Maximize performance)
Note:-ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};
You execute this statement on the primary database, which must be stopped and in the mount state.
Find Gap:
select thread#,low_sequence#,high_sequence# from V$archive_gap;
Status of individual archived log on Standby site :-
select thread#,Sequence#,Applied,Registrar from V$Archived_Log;
ARCHIVE_LAG_TARGET
Description
Limits the amount of data that can be lost and effectively increases the availability
of the standby database by forcing a log switch after the amount of time you specify
(in seconds) elapses. The standby database will not miss redo logs generated from a
time range longer than a value of the ARCHIVE_LAG_TARGET parameter.
Role
Applies to the primary database role
Examples
The following example sets the log switch interval to 30 minutes (a typical value):
ARCHIVE_LAG_TARGET = 1800
Role Management 7-11
7.2.1 Switchover Operations Involving a Physical Standby Database
This section describes how to perform a switchover operation that changes roles
between a primary database and a physical standby database. Always initiate the
switchover operation on the primary database and complete it on the physical
standby database. The following steps describe how to perform the switchover
operation.
On the current primary database
Step 1 Verify that it is possible to perform a switchover operation.
On the current primary database, query the SWITCHOVER_STATUS column of the
V$DATABASE fixed view on the primary database to verify that it is possible to
perform a switchover operation. For example:
Role Transitions Involving Physical Standby Databases
7-12 Oracle9i Data Guard Concepts and Administration
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
—————–
TO STANDBY
1 row selected
The TO STANDBY value in the SWITCHOVER_STATUS column indicates that it is
possible to switch the primary database to the standby role. If the TO STANDBY
value is not displayed, then verify that the Data Guard configuration is functioning
correctly (for example, verify that all LOG_ARCHIVE_DEST_n parameter values are
specified correctly).
Step 2 Initiate the switchover operation on the primary database.
To transition the current primary database to a physical standby database role, use
the following SQL statement on the primary database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
After this statement completes, the primary database is converted into a standby
database. The current control file is backed up to the current SQL session trace file
before the switchover operation. This makes it possible to reconstruct a current
control file, if necessary.
Step 3 Shut down and restart the former primary instance.
Shut down the former primary instance and restart it without mounting the
database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
Mount the database as a physical standby database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
At this point in the switchover process, both databases are configured as standby
databases (see Figure 7–3).
See Also: Chapter 14 for information about other valid values for
the SWITCHOVER_STATUS column of the V$DATABASE view
Role Transitions Involving Physical Standby Databases
Role Management 7-13
On the target physical standby database
Step 4 Verify the switchover status in the V$DATABASE view.
After you transition the primary database to the physical standby role and the
switchover notification is received by the standby databases in the configuration,
you should verify if the switchover notification was processed by the target standby
database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed
view on the target standby database.
For example:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
—————–
SWITCHOVER PENDING
1 row selected
The SWITCHOVER PENDING value of the SWITCHOVER_STATUS column indicates
the standby database is about to switch from the standby role to the primary role. If
the SWITCHOVER PENDING value is not displayed, then verify that the Data Guard
configuration is functioning correctly (for example, verify that all LOG_ARCHIVE_
DEST_n parameter values are specified correctly).
Step 5 Switch the physical standby database role to the primary role.
You can switch a physical standby database from the standby role to the primary
role when the standby database instance is either mounted in managed recovery
mode or open for read-only access. It must be mounted in one of these modes so
that the primary database switchover operation request can be coordinated.
The SQL ALTER DATABASE statement used to perform the switchover
automatically creates online redo logs if they do not already exist. This might
significantly increase the time required to complete the COMMIT operation.
Therefore, Oracle Corporation recommends that you always manually add online
redo logs to the target standby database when you create it. Use one of the
following methods to manually add the online redo logs if they do not already exist:
_ Copy the existing online redo logs from the initial primary database site to the
target standby database site and define the LOG_FILE_NAME_CONVERT
initialization parameter to correctly associate the standby site path names to the
new online redo logs (see Section 3.2.6).
See Also: Chapter 14 for information about other valid values for
the SWITCHOVER_STATUS column of the V$DATABASE view
Role Transitions Involving Physical Standby Databases
_ Drop any existing online redo logs at the target standby site and create new
ones using the ALTER DATABASE ADD STANDBY LOGFILE statement.
After you manually add the online redo logs, use the following SQL statement on
the physical standby database that you want to transition to the primary role:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Step 6 Shut down and restart the new primary database.
Shut down the target standby instance and restart it using the appropriate
initialization parameters for the primary role:
SQL> SHUTDOWN;
SQL> STARTUP;
The target physical standby database is now transitioned to the primary database
role.
On the new physical standby database
Step 7 Start managed recovery operations and log apply services.
Issue the following statement to begin managed recovery operations on the new
physical standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
On the new primary database
Step 8 Begin sending redo data to the standby databases.
Issue the following statement on the new primary database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Media recovery:
SQL>RECOVER MANAGED STANDBY DATABASE disconnect from session;
SQL> recover managed standby database cancel;
Both the above 2 cmds for complete media recovery.
Switchover
wanted to do a failover test. so i aborted my primary and activated my standby as primary using the following commands
recover managed standby database finish;
alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup
Important:
1. Initiate the failover operation on the target physical standby database
Since our Data Guard configuration was set up in maximum performance mode, it was not configured with any standby redo logs. Therefore, run the following SQL statement from the target physical standby database to initiate the failover operation. Please keep in mind that this statement will cause (minimal) data loss as the latest information from the down primary database is not available anymore.
SQL> alter database recover managed standby database finish skip standby logfile;
Database altered.
The above SQL statement performs incomplete
recovery until the last SCN included in the latest archived redo log available at the physical standby database. The next section describes the final step which is to make the target standby database the primary.
If your Data Guard configuration did contain standby redo logs (as required with protection modes like maximum availability and maximum protection AND you have not registered any partial archived redo log files, then you need to stop normal managed recovery and then initiate the failover operation using the following SQL statements:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database finish;
Database altered.
2. Convert the physical standby database to the primary role
After the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE … FINISH; SQL statement successfully completes, you will then need to transition the target physical standby database to the primary database role by issuing the following SQL statement:
SQL> alter database commit to switchover to primary;
The above SQL statement will only succeed if the correct “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE … FINISH;” statement was issued (in the previous section). If you forgot the “…SKIP STANDBY LOGFILE” clause although you have no standby redo log files, the “COMMIT TO SWITCHOVER” will fail with the error that more media recovery is required here.
If the “COMMIT TO SWITCHOVER” fails for any reason, you have to use the “ACTIVATE” SQL statement which forces the failover operation (and may cause data loss!):
SQL> alter database activate standby database;
Database altered.
After the “COMMIT TO SWITCHOVER” successfully completes, you can no longer use this database as a standby database. Also, subsequent redo logs from the original primary database cannot be applied.
Execute this statement if media recovery error comes:–
SQL> alter database activate standby database; and now shutdown as below…
3. Shutdown and restart the new primary database
To complete the failover operation, you need to shutdown the new primary database and restart it in read/write mode using the appropriate initialization parameter file (or SPFILE) for the primary role:
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 252777556 bytes
Fixed Size 451668 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
Tags: Database
