Brief: Data Guard is an Oracle feature that primarily provides database redundancy. This is done by having a standby (physical copy) database, preferably in another location and on separate disk. This standby database is maintained by applying the changes from the primary database to it. Standby databases can be maintained with either Redo (Physical standby) or SQL (Logical standby).
Environment:- Oracle 11gR2 (11.2.0.3) on Windows 2008R2
- The Primary database is called dubai and resides on a server called OR-11
- The physical standby will be called riyadh and reside on a server called OR-12
- The Primary database is archivelog mode (required)
Preparing the Primary for Data Guard
There are some preparation on Primary database to make it ready for Data Guard. Database must be in archivelog mode.
First, for a Physical Standby to be an exact copy it must receive redo for the changes made to the primary. With Oracle a database user can instruct the database to not log redo (e.g. the NOLOGGING clause). For a physical standby database this is a big problem, so you must make sure that redo is logged regardless of what a user tells the database to do. To do this you turn on Forced Logging.
First, for a Physical Standby to be an exact copy it must receive redo for the changes made to the primary. With Oracle a database user can instruct the database to not log redo (e.g. the NOLOGGING clause). For a physical standby database this is a big problem, so you must make sure that redo is logged regardless of what a user tells the database to do. To do this you turn on Forced Logging.
1)
SQL> archive log listDatabase log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2)
SQL> select name,database_role from v$database2 /
NAME DATABASE_ROLE
--------- ----------------
DUBAI PRIMARY
SQL>
3)
SQL> select force_logging from v$database
2 /
FOR
---
NO
SQL> alter database force logging;
Database altered.
4)
Make sure that when we add or drop datafiles on our primary database, that those files are also added or dropped on the standby.
SQL> alter system set standby_file_management = 'AUTO';System altered.
5)
Make sure the primary database has Standby Log Files. Standby Log Files are
used by a standby database to store the redo it receives from the primary database. Standby log files should be created the same size as the online logfiles. Preferably you should have as many, or more, standby logfile groups as online logfile groups.
--add logfile groups for standby
SQL> alter database add standby logfile group 11 ('D:\app\Inam\oradata\dubai\sbl01.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 12 ('D:\app\Inam\oradata\dubai\sbl02.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 13 ('D:\app\Inam\oradata\dubai\sbl03.log') size 50m;
Database altered.
6)
You will need to use Oracle password file authentication. For this you must create a password file and have REMOTE_LOGIN_PASSWORDFILE set to
EXCLUSIVE or SHARED. It is likely this is already done so check first, but if not:
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile; (and bounce)
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
OS> orapwd password=
orapwd password=oracle1
7)
Make sure your primary database has the db_unique_name parameter set for consistency.
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string dubai
8) Use Flashback Database
Flashback Database allows you to flashback your database to a previous point in time. This is very useful when a failover occurs because you can use it to flashback your old primary database and then activate it as a Standby. If you don’t have flashback correctly configured across your Data Guard configuration then you have to rebuild the Standby which means
copying all those datafiles again. In addition to this use, Flashback Database can you save you from having to restore/recover your database in some situations.
To setup Flashback Database you need to configure the Fast Recovery Area first. To do this set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters if not set already.
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\app\Inam\fast_recovery_area
db_recovery_file_dest_size big integer 8000M
-- make flashback on
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
9) Server SQL*NET Configuration
Ensure the databases in configuration will be able to talk to each other. This is required before creating the database if we want to use the RMAN “duplicate from active database” feature. To do this configure both the listener and TNSNames.
--listener configuration listener.ora
--On Primary "dubai" DB Server
# listener.ora Network Configuration File: D:\app\Inam\product\11.2.0.3\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Inam\product\11.2.0.3\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Inam\product\11.2.0.3\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = dubai)
(ORACLE_HOME = D:\app\Inam\product\11.2.0.3\dbhome_1)
(SID_NAME = dubai)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OR-11)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\app\Inam
DIRECT_HANDOFF_TTC_LISTENER=OFF
-- on Riyadh DB Server
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OR-12)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = riyadh)
(ORACLE_HOME =C:\app\Inam\product\11.2.0\dbhome_1)
)
)
--- TNSNAMES.ORA on both Primary "dubai" and Standby "riyadh"
dubai =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = or-11.domain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dubai)
)
)
riyadh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = or-12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = riyadh)
)
)
10) Test connectivity
--on Primary "dubai" server ping riyadh
D:\app\Inam\product\11.2.0.3\dbhome_1\BIN>tnsping riyadh
TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 07-MAY-2013 10:46:07
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
D:\app\Inam\product\11.2.0.3\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = inam-pc)(PORT = 1521)) (CONNECT_DATA
= (SERVER = DEDICATED) (SERVICE_NAME = riyadh)))
OK (20 msec)
--on riyadh server ping dubai
C:\Windows\system32>tnsping dubai
TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 07-MAY-2013 10:44:33
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
C:\app\Inam\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = or-11.domain)(PORT = 1521)) (CONNEC
T_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dubai)))OK (20 msec)
11) Configuring Redo Transport
Ensure that Redo is archived and transported appropriately. We need to configure this on the existing primary and then modify the standby after it is created.
SQL> alter system set log_archive_dest_1 = 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=dubai';
System altered.
This statement says to use the DB_RECOVERY_FILE_DEST as the location to archive to, that it should be used to archive all logfiles in any (all) roles, and that this is for the database dubai. The manual says to use online_logfiles; however, this will result in the Standby being unable to archive the standby logfiles because they are not online logfiles. If you use all_logfiles both the primary and standby will be able to archive logfiles whether they are online or standby. You’ll want this if you’re backing up on the standby and want to have
archived logfiles backed up there too. You could configure this to archive only on the primary or only on the standby if you’d like.
12) Configure the transport of the redo to the Standby database.
SQL> alter system set log_archive_dest_2 = 'service=riyadh async valid_for=(online_logfile,primary_role) db_unique_name=riyadh';
System altered.
This statement says that if it is the primary database, it should transport online logfiles using the service name "riyadh" and that the target is the database "riyadh".
13) Change parameter FAL_SERVER. This specifies where the database should look for missing archive logs if there is a gap in the logs. It is used when the database is in the standby role and has a gap in the redo it has received. This type of situation occurs when redo transport is interrupted, for example when you do maintenance on the standby server. During that maintenance no logs would have been transported and a gap would exist. Setting this parameter allows the standby to find the missing redo and have it transported.
SQL> alter system set fal_server = 'riyadh';
System altered.
14) Let the database know what other databases are in the Data Guard configuration.
SQL> alter system set log_archive_config = 'dg_config=(dubai,riyadh)';
System altered.
Preparing the Standby environment
We are now ready to setup the standby environment for creation of the Standby database.
1) Create a password file and server parameter file (spfile) for the new standby database. The password file can be copied directly over, and only needs it name changed.
copy password file
D:\app\Inam\product\11.2.0.3\dbhome_1\database\PWDdubai.ORA (from dubai server)
to
D:\app\Inam\product\11.2.0.3\dbhome_1\database\PWDriyadh (renamed on riyadh server)
2) Create pfile for standby database
Just create INITriyadh.ORA on standby server with parameters (D:\app\Inam\product\11.2.0.3\dbhome_1\database). I just set one required parameter.
INITriyadh.ora
db_name='riyadh'
3) Now, on your standby server create the directory structures and modify files to support the new database.
ORACLE_BASE/admin/ORACLE_SID
ORACLE_BASE/admin/ORACLE_SID/adump (or whatever your audit_file_dest is)
Datafile directories
Controlfile directories
Logfile directories
DB Recovery File Destination (Fast Recovery Area)
4) Create the standby instance (riyadh), startup your standby instance for creation of the database.
--create instance
C:\Windows\system32>oradim -new -sid riyadh
Instance created.
-- startup the instance
C:\Windows\system32>set oracle_sid=riyadh
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 7 12:13:57 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup nomount pfile=C:\app\Inam\product\11.2.0\dbhome_1\database\INITriyadh.ORA
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2254624 bytes
Variable Size 335546592 bytes
Database Buffers 838860800 bytes
Redo Buffers 9191424 bytes
SQL>
Creating the Standby Database and Starting Redo Apply
There are many different ways you can create the standby database. With 11g I use RMAN duplication to do the creation because it is easy. You perform this activity on Primary "dubai" database.
1)Connect with RMAN
C:\Users\inam>rman target sys/oracle1@dubai auxiliary sys/oracle1@riyadh
Run the below script
run {
allocate channel C1 type disk;
allocate auxiliary channel STDBY type disk;
duplicate target database for standby from active database spfile
SET SGA_TARGET="1024M"
SET SGA_MAX_SIZE="1024M"
set db_unique_name='riyadh'
set diagnostic_dest='D:\app\Inam'
set audit_file_dest='D:\app\Inam\admin\riyadh\adump'
set db_recovery_file_dest='D:\app\Inam\fast_recovery_area'
set db_recovery_file_dest_size='8388608000'
set db_file_name_convert='D:\app\Inam\oradata\dubai','D:\app\Inam\oradata\riyadh'
set log_file_name_convert='D:\app\Inam\oradata\dubai','D:\app\Inam\oradata\riyadh'
set control_files='D:\app\Inam\oradata\riyadh\control01.ctl','D:\app\Inam\fast_recovery_area\riyadh\control02.ctl'
set log_archive_max_processes='5'
set fal_client='riyadh'
set fal_server='dubai'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(dubai,riyadh)'
set log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=riyadh'
set log_archive_dest_2='service=dubai ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dubai'
;
}
RMAN> exit
Recovery Manager complete.
C:\Users\inam>
2) Check the status of standby "dubai"
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DUBAI MOUNTED PHYSICAL STANDBY
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE# BLOCK# DELAY_MINS
--------- ---------- ------------ ---------- ---------- ---------- ----------
ARCH 3276 CONNECTED 0 0 0 0
ARCH 12864 CONNECTED 0 0 0 0
ARCH 6948 CONNECTED 0 0 0 0
ARCH 12108 CONNECTED 0 0 0 0
3) Start Redo Apply on Standby "dubai"
SQL> alter database recover managed standby database disconnect from session;
Database altered.
This starts the recovery process using the standby logfiles that the primary is writing the redo to. It also tells the standby to return you to the SQL command line once the command is complete.
If you want to stop the recovery you do:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;4) Check the status of standby "dubai" again
SQL> select PROCESS,PID,STATUS,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby;
PROCESS PID STATUS THREAD# SEQUENCE# BLOCK# DELAY_MINS
--------- ---------- ------------ ---------- ---------- ---------- ----------
ARCH 7708 CONNECTED 0 0 0 0
ARCH 7048 CONNECTED 0 0 0 0
ARCH 7652 CLOSING 1 87 1 0
ARCH 7852 CLOSING 1 86 1 0
ARCH 1480 CONNECTED 0 0 0 0
RFS 6780 IDLE 0 0 0 0
RFS 7668 IDLE 0 0 0 0
RFS 1756 IDLE 0 0 0 0
RFS 5860 IDLE 1 88 18 0
MRP0 4788 WAIT_FOR_LOG 1 88 0 0
10 rows selected.
Verifying/Testing
1) Make sure our destinations are all valid on both the primary and the standby:
select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2;
On standby
| DEST_ID | STATUS | DESTINATION | ERROR |
|---|---|---|---|
1
|
VALID | USE_DB_RECOVERY_FILE_DEST | |
2
|
VALID | dubai |
| DEST_ID | STATUS | DESTINATION | ERROR |
|---|---|---|---|
1
|
VALID | USE_DB_RECOVERY_FILE_DEST | |
2
|
VALID | riyadh |
The destinations should show as VALID.
2) see if the redo is actually being applied, From the primary run:
select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from
V$ARCHIVED_LOG where name = 'riyadh' order by FIRST_TIME;
| SEQUENCE# | FIRST_TIME | NEXT_TIME | APPLIED | ARCHIVED |
|---|---|---|---|---|
84
|
5/19/2013 09:43:05 AM | 5/19/2013 10:05:21 AM | YES | YES |
85
|
5/19/2013 10:05:21 AM | 5/19/2013 10:05:57 AM | YES | YES |
86
|
5/19/2013 10:05:57 AM | 5/19/2013 10:10:43 AM | YES | YES |
87
|
5/19/2013 10:10:43 AM | 5/19/2013 10:22:14 AM | YES | YES |
88
|
5/19/2013 10:22:14 AM | 5/19/2013 10:31:42 AM | YES | YES |
3) If you notice that logs aren’t applying, it is possible you might have a gap in your redo, in which case the standby cannot apply. If you have the FAL_SERVER parameter set correctly, this shouldn’t be a problem. You can check to see if there are any gaps in the redo, by running the following query on the primary:
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID =2
| STATUS | GAP_STATUS |
|---|---|
| VALID | NO GAP |
It should return VALID and NO GAP if everything is OK. If you want to play around with this and see how the FAL_SERVER works you can shutdown your standby, switch out several logs, wait a bit, bring your standby back up, and switch another log out. You should soon see a GAP appear. If your FAL_SERVER parameter is correct on the standby and points to the service name of the primary, then the logs should be brought over and applied.
4) The V$DATAGUARD_STATUS view is very useful for looking for errors or just seeing what has happened. You can query it on the primary and standby to see status for that database.
select * from V$DATAGUARD_STATUS order by TIMESTAMP;
| FACILITY | SEVERITY | DEST_ID | MESSAGE_NUM | ERROR_CODE | CALLOUT | TIMESTAMP | MESSAGE |
|---|---|---|---|---|---|---|---|
| Log Transport Services | Informational |
0
|
2
|
0
|
NO | 5/19/2013 10:05:16 AM | ARC1: Archival started |
| Log Transport Services | Informational |
0
|
1
|
0
|
NO | 5/19/2013 10:05:16 AM | ARC0: Archival started |
| Log Transport Services | Informational |
0
|
4
|
0
|
NO | 5/19/2013 10:05:17 AM | ARC3: Archival started |
| Log Transport Services | Informational |
0
|
5
|
0
|
NO | 5/19/2013 10:05:17 AM | ARC3: Becoming the 'no FAL' ARCH |
| Log Transport Services | Informational |
0
|
3
|
0
|
NO | 5/19/2013 10:05:17 AM | ARC2: Archival started |
5) Sometimes you want to really know the data is there. A more reassuring way to verify is
to actually check the standby and verify that the new data is there. You can do this by changing the standby’s role to readonly. First you’ll need to stop managed recovery and then open databaase:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
You can now run your query to see that the changes have come across. When you’re done, do not forget to take your database back to MOUNT and restart recovery.
6) If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
Database altered.
7) Protection Mode
There are three protection modes for the primary database:
Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum performance mode.
SELECT protection_mode FROM v$database;
| PROTECTION_MODE | |
|---|---|
| MAXIMUM PERFORMANCE |
The mode can be switched using the following commands. Note the alterations in the redo transport attributes.
-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=riyadh';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=riyadh';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=riyadh';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;
Note: you can switch to maximum availability and maximum performance any time without bouncing the instance. But once you change to maximum protection, and you want to change to maximum availability or maximum performance back, you will have to do the below
- Shutdown the instance
- Startup instance with mount
- Change to maximum availability or maximum performance
- Open the database
You will get the below error if you don't follow the above sequence.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQ
UE_NAME=riyadh';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NA
ME=riyadh'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16159: Cannot change protected standby destination attributes
8) Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements. Disconnect all sessions before doing this activity other wise you may get the error ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected.
-- Convert primary "dubai" database to standby
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2254624 bytes
Variable Size 436209888 bytes
Database Buffers 738197504 bytes
Redo Buffers 9191424 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Database altered.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2254624 bytes
Variable Size 436209888 bytes
Database Buffers 738197504 bytes
Redo Buffers 9191424 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
On the original standby "riyadh" database issue the following commands.
-- Convert standby database to primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2262048 bytes
Variable Size 281021408 bytes
Database Buffers 780140544 bytes
Redo Buffers 5513216 bytes
Database mounted.
Database opened.
SQL>
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2262048 bytes
Variable Size 281021408 bytes
Database Buffers 780140544 bytes
Redo Buffers 5513216 bytes
Database mounted.
Database opened.
SQL>
After testing switch the primary database back to the original server by doing another switchover. This is known as a switchback.
9) Failover
select name,open_mode,database_role,db_unique_name from v$database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
select name,open_mode,database_role,db_unique_name from v$database;
SQL> ALTER DATABASE OPEN;
Database altered.
select name,open_mode,database_role,db_unique_name from v$database;
If the primary database is not available the standby database can be activated as a primary database using the following statements. For this post purpose I just did the shutdown abort on primary "dubai" database.
select name,open_mode,database_role,db_unique_name from v$database;
| NAME | OPEN_MODE | DATABASE_ROLE | DB_UNIQUE_NAME |
|---|---|---|---|
| DUBAI | READ ONLY | PHYSICAL STANDBY | riyadh |
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
select name,open_mode,database_role,db_unique_name from v$database;
| NAME | OPEN_MODE | DATABASE_ROLE | DB_UNIQUE_NAME |
|---|---|---|---|
| DUBAI | MOUNTED | PRIMARY | riyadh |
Database altered.
select name,open_mode,database_role,db_unique_name from v$database;
| NAME | OPEN_MODE | DATABASE_ROLE | DB_UNIQUE_NAME |
|---|---|---|---|
| DUBAI | READ WRITE | PRIMARY | riyadh |
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily, If not, the whole setup process must be followed, but this time using the original primary server as the standby.
One of the best documents ...which really help me to configure standby ...thank you so much ....all the best ...
ReplyDelete