Oracle 10g Step By Step Data Guard Configuration

INTRODUCTION:
Oracle dataguard ensures high availability data protection and disaster
recovery for enterprise data. The dataguard configuration will always have 2
databases running transactionally consistent. If the production database becomes
unavailable because of planned or unplanned outage. Dataguard can be switched to
any standby database in the configuration to production role, minimizing down
time.
Standby databases are created by the backup of the primary database.
Once configured dataguard will maintain the standby database automatically by
transmitting redo of the primary database and applying the transmitted data to the
standby database(Redo apply for physical standby, SQL apply for logical standby).
TYPES OF STANDBY DATABASES:
Standby databases can be of two types.
1. Physical standby database.
Provides a physically identical copy of the primary database, with on disk
database structures that are identical to the primary database on a block-for-block
basis. The database schema, including indexes, are the same.
2. Logical standby database.
Contains the same logical information as the production database, although the
physical organization and structure of the data can be different.


BENEFITS OF PHYSICAL STANDBY DATABASE:
1.Redo apply:
Standby database is made consistent by applying redo data from archivelog files.
Recovery applies the changes from redo block to the data block.
2.Read only mode:
Physical standby database can be opened in read only mode for querying, and it
is available for taking the backups. Thus we can minimize the load on the
production database. Redo data will not be applied if the standby database is
opened in read only mode, but redo data will be still put on the standby side.
3. Read write mode:
Physical standby database can be opened in read write mode for temporary
purpose (using flashback feature).
4.Disaster recovery and high availability
It gives best disaster recovery and high availability solution by easily managed
switch over and failover capabilities between the primary and standby databases.
5. Data protection
Using a physical standby database, Data Guard can ensure no data loss, even in the
face of unforeseen disasters. A physical standby database supports all datatypes,
and all DDL and DML operations that the primary database can support.
BENEFITS OF LOGICAL STANDBY DATABASE:
1.Efficient use of standby hardware resources
A logical standby database can be used for other business purposes in addition to
disaster recovery requirements. It can host additional database schemas beyond
the ones that are protected in a Data Guard configuration, and users can
perform normal DDL or DML operations on those schemas any time.


2.Reduction in primary database workload
A logical standby database can remain open at the same time its tables are
updated from the primary database, and those tables are simultaneously available
for read access. This makes a logical standby database an excellent choice to do
queries, summations, and reporting activities, thereby off-loading the primary
database from those tasks and saving valuable CPU and I/O cycles.
DATA GUARD SERVICES:
1.Redo Transport Services
Control the automated transfer of redo data from the production database to
one
or more archival destinations.
2.Log Apply Services
Apply redo data on the standby database to maintain transactional
synchronization with the primary database. Redo data can be applied either from
archived redo log files, or, if real-time apply is enabled, directly from the standby
redo log files as they are being filled, without requiring the redo data to be
archived first at the standby database.
3. Role Transitions
Change the role of a database from a standby database to a primary database, or
vice versa using either a switchover or a failover operation.
CREATING PHYSICAL STANDBY DATABASE:
1. Enable forced logging for primary database.
SQL>alter database force logging;
2. Configure standby redologfiles
Standby redolog files are required for Maximum availability and maximum
protection modes. Ensure logfile sizes are identical on both primary and standby
side. Create groups adding logfiles to the groups.


SQL> alter database add standby logfile '/home/oracle10g/orcl/redo3.log' size 50m;
SQL> alter database add standby logfile '/home/oracle10g/orcl/redo4.log' size 50m;
SQL> alter database add standby logfile '/home/oracle10g/orcl/redo5.log' size 50m;
SQL> alter database add standby logfile '/home/oracle10g/orcl/redo6.log' size 50m;
3. primary database parameters
*.background_dump_dest='/home/oracle10g/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle10g/orcl/control/control01.ctl'
*.db_block_size=8192
log_archive_config='DG_CONFIG=(orcl,stand)'
log_archive_dest_1='LOCATION=/home/oracle10g/orcl/archloc/ VALID_FOR
=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcl'
log_archive_dest_2='SERVICE=con_stand
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stand'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='enable'
db_unique_name='orcl'
FAL_CLIENT='orcl' #Connect descriptor name which connects to primary
FAL_SERVER='stand' #Connect descriptor name which connects to standby
standby_file_management='auto'
remote_login_passwordfile='EXCLUSIVE'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.job_queue_processes=10


*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle10g/orcl/udump'
log_file_name_convert='/home/oracle10g/stand/','/home/oracle10g/orcl/'
db_file_name_convert='/home/oracle10g/stand/','/home/oracle10g/orcl/'
4. standby database parameters
*.background_dump_dest='/home/oracle10g/stand/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle10g/stand/control/constd.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/home/oracle10g/orcl','/home/oracle10g/stand'
*.db_name='orcl'
*.db_unique_name='stand'
*.fal_client='stand' #Connect descriptor name which connects to standby
*.fal_server='orcl' #Connect descriptor name which connects to primary
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(stand,orcl)'
*.log_archive_dest_1='LOCATION=/home/oracle10g/stand/archloc/ VALID_FOR=
(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stand'
*.log_archive_dest_2='SERVICE=con_orcl LGWR ASYNC VALID_FOR=
(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'


*.log_archive_max_processes=2
*.log_file_name_convert='/home/oracle10g/orcl','/home/oracle10g/stand'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.standby_archive_dest='/home/oracle10g/stand/arch/'
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle10g/stand/udump'
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
5. Backup the primary database completely:
NOTE: Backup can be taken either by shutting down the database or by RMAN.
6. Start the primary database in mount or open phase to take control file backup.
SQL> alter database create standby controlfile as '/home/oracle10g/stdctl.ctl';
7. Copy standby controlfile, datafiles and redologfile to standby side.
Create appropriate directories where datafile and redolog file has to be kept in
the standby side. Update the parameters DB_FILE_NAME_CONVERT,
LOG_FILE_NAME_CONVERT in the parameter files of both primary and standby
databases as shown above.


8. Shutdown primary database
9. Configure listener on both primary and standby side and suitable connect
descriptors which can connect to each other.
10.Create password file for both primary standby database.
Note: Both standby and primary database should have same password.
11. Start the standby database in nomount phase and mount it.
SQL> alter database mount standby database;
12. Start primary database.
SQL> select error from v$archive_dest;
NOTE: Above query Should not give any errors for any destination which
transmits redo data from the primary database. If any error occurs check the
listener status on the standby side and corresponding connect descriptor on the
primary side.
13.Verifying the standby database.
SQL> select SEQUENCE#,ARCHIVED,APPLIED,FIRST_TIME,NEXT_TIME from
v$archived_log;
SEQUENCE# ARC APP FIRST_TIM NEXT_TIME
-------------------- ------- ------ ---------------- -----------------
19 YES YES 17-FEB-08 18-FEB-08
20 YES YES 18-FEB-08 18-FEB-08
21 YES YES 18-FEB-08 18-FEB-08
22 YES YES 18-FEB-08 18-FEB-08
23 YES YES 18-FEB-08 18-FEB-08
SQL> alter system switch logfile; (Primary database)


SQL> select SEQUENCE#,ARCHIVED,APPLIED,FIRST_TIME,NEXT_TIME from
v$archived_log; (Standby database)
SEQUENCE# ARC APP FIRST_TIM NEXT_TIME
---------- --- --- --------- ---------
19 YES YES 17-FEB-08 18-FEB-08
20 YES YES 18-FEB-08 18-FEB-08
21 YES YES 18-FEB-08 18-FEB-08
22 YES YES 18-FEB-08 18-FEB-08
23 YES YES 18-FEB-08 18-FEB-08
24 YES YES 18-FEB-08 18-FEB-08 (New Archive logfile applied)
Note: The Query has to be checked for the subsequent log switches that occur on
the primary side. Primary database will be one log switch ahead of the standby
database. Also ensure that MRP process is running on standby side by giving
ps -ef grep ora_ command.
DATAGUARD PROTECTION MODES:
Following table shows the archive parameter attributes to be specified for keeping
the database in different protection modes properly.
Maximum
Protection
Maximum
Availability
Maximum
Performance
Redo archival
process
LGWR LGWR LGWR or ARCH
Network
transmission
mode
SYNC SYNC SYNC or ASYNC when using LGWR process.
SYNC if using ARCH process
Disk write
option
AFFIRM AFFIRM AFFIRM or NOAFFIRM
Standby redo
log required?
Yes Yes No, but it is recommended


UPGRADING FROM PERFORMENCE TO AVAILIBILITY:
1. Verify the parameters of the primary database.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=con_stand VALID_FOR=(O
NLINE_LOGFILES,ALL_ROLES) DB_U
NIQUE_NAME=stand
2. If not set properly, set the archive parameters with the proper log transport
services as required for the protection modes.
SQL> alter system set log_archive_dest_2='SERVICE=con_stand LGWR SYNC
AFFIRM VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stand';
3. Shutdown and startup the database in mount phase. And issue the following
command for keeping database in maximum availability mode.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
4. Open the database
SQL> ALTER DATABASE OPEN;
5.Verify
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
--------------------------------- --------------------------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION


DOWNGRADING FROM AVAILABILITY TO PERFORMANCE:
1. Set the archive parameter suitably.
SQL> alter system set log_archive_dest_2='SERVICE=con_stand ARCH SYNC
AFFIRM VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stand';
2. Issue the following command to put the database back in Performance Mode.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PERFORMANCE;
3. Verify
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
--------------------------------- ---------------------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
NOTE: For downgrading form Availability to performance or protection to
Availability there is no need to restart the instance.
VERIFY THE LOGSHIPPING:
NOTE: Following Queries provides you the information regarding the STATUS for
the Online logfiles, Maximum LSN, and the Archive log file with the latest LSN
which is Transmitted.
SQL> select thread#,sequence#,archived,status from v$log;
THREAD# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 35 NO CURRENT
1 33 YES INACTIVE
1 34 YES INACTIVE


SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
------------------------------ ----------
34 1
SQL> select destination,status,archived_thread#,archived_seq# from
v$archive_dest_status where status <> 'DEFERED' and status <> 'INACTIVE';
DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ#
--------------------------------------------- ------------ --------------------------------- --------------------------
/home/oracle10g/orcl/archloc VALID 1 34
con_stand VALID 1 34
DATABASE ROLE TRANSITIONS:
1. Check the database's current role in the configuration.
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
2. To choose the suitable standby database to which we can failover or switchover
with the least possible apply time. We have following query on the standby
database.
SQL> select NAME,VALUE,UNIT from v$dataguard_stats;
NAME VALUE UNIT
------------------ ---------------- ------------------------------
apply finish time day(2) to second(1) interval
apply lag +00 02:19:59 day(2) to second(0) interval
estimated startup 10 second
time
standby has been o N
pen
transport lag +00 02:17:32 day(2) to second(0) interval


3. Verify current switchover status on primary database.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
4. Issue switchover command on primary database first.
SQL> alter database commit to switchover to physical standby;
5. Shutdown and Start the standby database in mount phase.
6. Verify the proper switchover of Primary to Standby by following query.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
7. Further carry out switchover operation on standby side.
SQL> alter database commit to switchover to primary;
8. Verify the proper switchover of Standby to Primary.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
9. Shutdown and startup the new primary database normally.
NOTE: OR alter database open if the standby database is not open in read only
mode even for the first time.
10. Start Managed recovery process on new standby side.
SQL> alter database recover managed standby database disconnect;


11. Verify the MRP process.
SQL> SELECT PROCESS,STATUS FROM V$MANAGED_STANDBY;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 WAIT_FOR_LOG
RFS IDLE
FAILOVER:
1. Issue the following query to check for any archive gaps. If exists, Archive gaps
has to be resolved first.
NOTE: Resolving Archive gaps is demonstrated in further steps.
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;
2. To check the archive log file with the highest LSN issue following query Until
which archive gap has to be resolved.
SQL> select unique thread# as thread, max(sequence#) over (partition by
thread#) as last from v$archived_log;
THREAD LAST
---------------- ----------
1 45
NOTE: If archive gap exists copy corresponding archive log file to the standby
database and register it to the standby side. Repeate the step until the gap is
resolved.
SQL> alter database register standby logfile 'path and name';
3. To Failover issue the following commands on standby.
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
NOTE: Shutdown and startup if the database was opened in read only any time
after configuration.


HOW TO OPEN STANDBY DATABASE IN READ ONLY MODE?
1.When it is shutdown just STARTUP will open standby database in read only mode.
2.If the standby database is mounted then:
a. SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select OPEN_MODE from v$database;
OPEN_MODE
--------------------
READ ONLY
NOTE: Further... If we start MRP process while the standby database is opened, it
will be automatically put into Mount phase as shown below.
SQL> alter database recover managed standby database disconnect;
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> SELECT PROCESS,STATUS FROM V$MANAGED_STANDBY;
PROCESS STATUS
-------------- ------------
ARCH CLOSING
ARCH CLOSING
MRP0 WAIT_FOR_LOG
RFS IDLE
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> SELECT PROCESS,STATUS FROM V$MANAGED_STANDBY;
PROCESS STATUS
-------------- ------------
ARCH CLOSING
ARCH CLOSING
RFS IDLE


NOTE: The above query shows MRP process stopped as it has been cancelled.
MANAGEMENT:
Tablespace Management.
If STANDBY_FILE_MANAGEMENT is set to auto.
SQL> create tablespace tbs1
datafile '/home/oracle10g/orcl/tbs1.dbf' size 20m;
SQL> alter system archive log current;
In standby side the datafile will be automatically created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle10g/stand/system01.dbf
/home/oracle10g/stand/undotbs01.dbf
/home/oracle10g/stand/sysaux01.dbf
/home/oracle10g/stand/users01.dbf
/home/oracle10g/stand/example01.dbf
/home/oracle10g/stand/tbs1.dbf
If the Tablespace is dropped
SQL> drop tablespace tbs1;
SQL> alter system archive log current;
In standby side, the datafile will be automatically dropped.


SQL> select name from v$datafile;
NAME
----------------------------------------------------------------
/home/oracle10g/stand/system01.dbf
/home/oracle10g/stand/undotbs01.dbf
/home/oracle10g/stand/sysaux01.dbf
/home/oracle10g/stand/users01.dbf
/home/oracle10g/stand/example01.dbf
If STANDBY_FILE_MANAGEMENT is not set to auto.
Renaming datafile:
If a datafile is renamed in the primary side, The same datafile has to be rename
manually in the standby side.
A. SQL> alter tablespace example offline;
B. $ mv example01.dbf example.dbf
C. alter database rename file '/home/oracle10g/orcl/example01.dbf' to
'/home/oracle10g/orcl/example.dbf';
SQL> alter system archive log current;
On standby side:
D. SQL> alter system set standby_file_management=manual;
E. SQL> alter database rename file '/home/oracle10g/stand/example01.dbf'
to '/home/oracle10g/stand/example.dbf';
F. SQL> alter database recover managed standby database disconnect;
G. SQL> alter system set standby_file_management=auto;
H. SQL> select name from v$datafile;
NAME
----------------------------------------------------------
/home/oracle10g/stand/system01.dbf
/home/oracle10g/stand/undotbs01.dbf


/home/oracle10g/stand/sysaux01.dbf
/home/oracle10g/stand/users01.dbf
/home/oracle10g/stand/example.dbf
MONITORING PROCESS:
Following queries are helpful in getting the information regarding the processes
and the Archvelog files.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- ---------------- ------------------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 63 WAIT_FOR_LOG
RFS N/A 0 IDLE
SQL> select archived_thread#,archived_seq#,applied_thread#,applied_seq# from
v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1 64 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
1 63 1 62
SQL> select name,creator,sequence#,applied,completion_time from
v$archived_log;
NAME CREATOR SEQUENCE# APP COMPLETION
---------- --------------- ------------------- ---------------------------
/home/oracle10g ARCH 65 YES 28-MAR-08
/stand/archloc/
1_65_646949374.dbf


SQL> SELECT FIRST_TIME,FIRST_CHANGE#,NEXT_CHANGE#,SEQUENCE#
FROM V$LOG_HISTORY;
17-FEB-08 446075 474198 1
17-FEB-08 474198 476839 2
17-FEB-08 476839 476841 3
------------ --------- ---------- -
------------ --------- --------- -
------------ --------- ---------- -
27-MAR-08 554446 561139 63
28-MAR-08 561139 561141 64
28-MAR-08 561141 561853 65
SQL> select thread#,max(sequence#) as "LAST_APPLIED" from v$log_history
group by thread#;
THREAD# LAST_APPLIED
---------- ------------
1 65
OPENING STANDBY DATABASE IN READ AND WRITE MODE:
NOTE: Set DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST to
some directory name in physical size.
NOTE: Here we use Activate and Convert command.
1. Stop the MRP Process on standby.
SQL> alter database recover managed standby database cancel;
2. Create a Guarantee Restore point on standby database until which you can
flashback.
SQL> create restore point rest1 guarantee flashback database;
3. Switch the logfile on Primary side to archive the latest changes to standby side.
SQL> alter system switch logfile;
4.Defer the Standby destination of the primary database.
SQL> alter system set log_archive_dest_state_2=defer;


5. Activate the standby database.
SQL> alter database activate standby database;
6. Startup in mount phase and put the standby database in Max performance.
SQL> alter database set standby database to maximize performance;
7. Open the standby database in Read Write mode.
SQL> alter database open;
8. Verify the open mode.
SQL> select OPEN_MODE from v$database;
OPEN_MODE
--------------------
READ WRITE
NOTE: Standby database can be opened in this mode only for test purpose. After
competing the test operations it has to be again flashed back to the restore point.
9. To Flashback the standby database to before the point where it was opened in
Read Write mode, Shutdown and start the standby database in mount phase.
SQL> flashback database to restore point rest1;
10.Convert the standby database back in to physical standby.
SQL> alter database convert to physical standby;
11. Startup the standby database in mount phase and start the MRP process.
SQL> alter database recover managed standby database disconnect;
12. Enable the status of the Archive destination in the primary database.
SQL> alter system set log_archive_dest_state_2=enable;


RESOLVING ARCHIVE GAP MANUALLY:
1. Following SQL statement gives the latest LSN and the LSN of the archive log file
which is archived to the standby.
SQL> select max(r.sequence#) last_seq_recd, max(l.sequence#) last_seq_sent from
v$archived_log r, v$log l where r.dest_id=2 and l.archived='YES';
LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
77 78
2.We can also know the name of the archive log files between those LSNs on the
primary side.
SQL> select name from v$archived_log where thread#=1 and dest_id=1 and
sequence# between 77 and 80;
NAME
--------------------------------------------------------------------------------
/home/oracle10g/orcl/archloc/1_77_646949374.dbf
/home/oracle10g/orcl/archloc/1_78_646949374.dbf
/home/oracle10g/orcl/archloc/1_79_646949374.dbf
SQL> select name from v$archived_log where thread#=1 and dest_id=1 and
sequence# > 76 and sequence# <> show parameter standby_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string /home/oracle10g/stand/arch


4. Copy the archive log files listed in the above query manually to the standby
archive destination and issue the following command to start automatic recovery.
SQL> alter database recover automatic standby database;
alter database recover automatic standby database
*
ERROR at line 1:
ORA-00279: change 580282 generated at 04/01/2008 14:17:02 needed for thread 1
ORA-00289: suggestion :
/home/oracle10g/stand/archloc/1_80_646949374.dbf
ORA-00280: change 580282 for thread 1 is in sequence #80
ORA-00278: log file '/home/oracle10g/stand/archloc/1_80_646949374.dbf' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle10g/stand/archloc/1_80_646949374.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
NOTE: The automatic recovery will always searches for the archive log file with the
latest LSN, Which may not be transported to the standby side. Recovery will be
automatically stopped with the above error.
5. Start the MRP process in the standby side.
SQL> alter database recover managed standby database disconnect;
FLASHBACK AFTER ROLE TRANSITION (FAILOVER):
1. Verify Flashback mode for the standby database, it should be ON.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES


2. Check for the archive gap. If it exists resolve it as in the above mentioned steps.
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;
no rows selected
3. Failover to standby by issuing following command.
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
---------------------------
PRIMARY
4. Find out the SCN at which the standby database has become primary.
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM
V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
601721
5. Shutdown and Startup the OLD PRIMARY DATABASE in mount phase.
6. Flashback the OLD PRIMARY SIZE to the above SCN.
SQL> flashback database to scn 601721;
7. Convert the OLD PRIMARY DATABASE to physical standby.
SQL> alter database convert to physical standby;
8. Shutdown and Start the NEW STANDBY DATABASE in mount phase.


9. ON PRIMARY DATABASE check for the archive destination errors and archive
dest status.
SQL> select dest_id,dest_name,status,protection_mode,destination,error, sql from
v$archive_dest_status;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=ENABLE;
SQL> select DEST_ID,ERROR,LOG_SEQUENCE from v$archive_dest;
NOTE: Above query shows the the error that may occur while archiving. There
should be no errors for proper Archiving.
10. ON NEW STANDBY database start MRP process.
SQL> alter database recover managed standby database disconnect;
NOTE: properly verify log archive parameters in new standby database i.e
ALL_LOGFILES,ALL_ROLES' values are set for VALID_FOR value and properly set
DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT.
CREATING LOGICAL STANDBY DATABASE.
NOTE: In 10G first Physical standby has to be created and later it can be converted
to Logical standby.
1. Stop the MRP Process on standby database.
SQL> alter database recover managed standby database cancel;
2. On primary database Build the logminer directory by executing the following
package on primary database.
SQL> execute dbms_logstdby.build;
3. Change the database name on Standby database by issuing the following
command.
SQL> alter database recover to logical standby LOGICAL;
alter database recover to logical standby logical
ERROR at line 1:
ORA-16254: change db_name to STAND in the client-side parameter file (pfile)


NOTE: You will encounter above ERROR if the standby database is using pfile. It
also prompts you to change the DB_NAME Parameter in the parameter file to new
name which you wish to give. If the standby database is using SPFILE then,
DB_NAME parameter will be automatically updated to the new name. Here in the
above example new database name is LOGICAL.
4. Shutdown the standby database edit DB_NAME parameter to new name if the
standby database is using pfile.
5. create a password file and set the following parameters on logical standby.
log_archive_dest_1='LOCATION=/home/oracle10g/logical/archloc/ VALID_FOR=
(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=logical'
log_archive_dest_2='SERVICE=con_orcl LGWR ASYNC VALID_FOR=
(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_3='LOCATION=/home/oracle10g/logical/arch VALID_FOR=
(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=logical'
6. Start the instance in mount phase.
7. Open the database with resetlogs.
SQL> alter database open resetlogs;
8. Start the LSP process.
SQL> alter database start logical standby apply immediate;