Oracle 9i Data Guard Configuration
Data Guard provides high availability, data protection and disaster for
the Enterprise Oracle Database. In Data Guard, the standby databases are
created which is the exact replica of the production database and these
are transactional sync copies of the production database. If for any
reason production database goes down, the standby can be switched over
as a production database. In the case of disaster, Data guard offers not
a single transaction lost.
In this testing, there are two Oracle
9i Databases. Two machines were used, one is Primary Database and the
second is Standby Database.
After configuring, the switch over
process applied on the Oracle 9i Data Guard configuration. Which turn
the old primary database into new standby database and old standby
database into new primary database.
After the switch over, the
fail over process also has been applied. Which is used in the case of
complete disaster of the primary database, this will make the standby
database into new primary database. And all the records are applied into
the new primary database.
Data Guard Setup Procedure:
1- Prepare the Primary Database
a) Run the following queries:
SQL> select name,database_role
2 from v$database;
NAME DATABASE_ROLE
--------- ----------------
PRIMARY PRIMARY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination G:\oraclepdb\oradata\arch_2
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
b) SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0
c) alter database to force logging mode.
SQL> alter database force logging;
Database altered.
d) Select name from v$datafile
NAME
------------------------------------------------------------
G:\ORACLEPDB\ORADATA\PRIMARY\SYSTEM01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\UNDOTBS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\CWMLITE01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\DRSYS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\EXAMPLE01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\INDX01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\ODM01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\TOOLS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\USERS01.DBF
G:\ORACLEPDB\ORADATA\PRIMARY\XDB01.DBF
e) shutdown immediate
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
f) Copy the datafiles to the Standy Mchine .
g) Startup the primary database
SQL> startup
ORACLE instance started.
Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
h) Create the standby controlfile
SQL> alter database create standby controlfile as 'G:\oraclepdb\oradata\standbyfiles\standbycontrolfile.ctl';
Database altered.
i) Create standby init file
SQL> create pfile='G:\oraclepdb\oradata\standbyfiles\standbyinitfile.ora' from spfile;
File created.
j) copy the standby controlfile and standby init file to the standby machine.
2- Prepare the Standby Database:
a) Setup the standby init parameters.
I. Change the controlfile parameter to reflect the standby controlfile.
control_files= ("F:\oraclesb\oradata\standbyfiles\STANDBYCONTROLFILE.CTL")
I. db_name=primary
II. standby_archive_dest="F:\oraclesb\oradata\arch"
III. remote_archive_enable=true
IV. #standby_file_management=auto
V. service_names="standby1"
3- Prepare the Primary Database TNS Names File:
a)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
STANDBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.199)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby1)
)
)
4- Prepare the Standby Database TNS Names file:
a)
STANDBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = epserver)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = standby1)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.71)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)
5- Launch The Standby Database:
a) Make a password file
Orapwd file=………….. password=…………….. entries=…………………..
b) startup the database in nomount phase
startup nomount pfile='D:\oracle9201\admin\standby1\pfile\init_standby1_110504.ora'
c) Mount the database in standby phase
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
6- Start Sending Redo from Primary Database
a) See the log_archive parameter
SQL> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
b) Set the log_archive_dest_3 to the standby database.
SQL> alter system set log_archive_dest_3='service=standby1 lgwr' scope=both;
System altered.
c) Enable the log_archive_dest_3
SQL> alter system set log_archive_dest_state_3=enable;
System altered.
d) Archive log the current archive file. It should be archived on the primary as well as on the standby database.
See the following location for the Primary Database Archive Destination
G:\oraclepdb\oradata\arch
See the following location for the Standby Database Archive Destination
F:\oraclesb\oradata\arch
e) Verify whether Primary Database is sending the redo files.
SQL> select dest_id,sequence#,archived,applied
2 from v$archived_log;
DEST_ID SEQUENCE# ARC APP
---------- ---------- --- ---
1 1 YES NO
2 1 YES NO
1 2 YES NO
2 2 YES NO
3 2 YES NO
3 3 YES NO
1 3 YES NO
2 3 YES NO
7- Add the standby Redo Log files into Standby Database:
a) SQL> select *
2 from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 STALE ONLINE
G:\ORACLEPDB\ORADATA\PRIMARY\REDO03.LOG
2 ONLINE
G:\ORACLEPDB\ORADATA\PRIMARY\REDO02.LOG
1 ONLINE
G:\ORACLEPDB\ORADATA\PRIMARY\REDO01.LOG
SQL> alter database add standby redolog file 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
alter database add standby redolog file 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
SQL> lter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
SP2-0734: unknown command beginning "lter datab..." - rest of line ignored.
SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG'
- file cannot be created
ORA-27040: skgfrcre: create error, unable to create file
OSD-04001: invalid logical block size (OS 512)
SQL> alter database add standby logfile 'F:\oraclesb\oradata\standby\sREDO03.LOG' size 100m;
Database altered.
SQL> alter database add standby logfile 'F:\oraclesb\oradata\standby\sREDO02.LOG' size 100m;
Database altered.
SQL> alter database add standby logfile 'F:\oraclesb\oradata\standby\sREDO01.LOG' size 100m;
Database altered.
b) SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
8- To make sure that Standby Redo log files are being used
(on Primary Database):
a) SQL> select *
2 from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 5 104857600 1 YES INACTIVE 225814 11-MAY-04
2 1 6 104857600 1 YES INACTIVE 225889 11-MAY-04
3 1 7 104857600 1 NO CURRENT 225897 11-MAY-04
SQL> alter system archive log current;
System altered.
SQL> select *
2 from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 8 104857600 1 NO CURRENT 227491 11-MAY-04
2 1 6 104857600 1 YES INACTIVE 225889 11-MAY-04
3 1 7 104857600 1 YES ACTIVE 225897 11-MAY-04
9- On the Standby Database:
a) SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select group#,sequence#,status
2 from v$standby_log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------
4 8 ACTIVE
5 0 UNASSIGNED
6 0 UNASSIGNED
Primary Database
SQL> alter system archive log current;
System altered.
SQL> select *
2 from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 8 104857600 1 YES ACTIVE 227491 11-MAY-04
2 1 9 104857600 1 NO CURRENT 229368 11-MAY-04
3 1 7 104857600 1 YES ACTIVE 225897 11-MAY-04
Standby Database
SQL> select group#,sequence#,status
2 from v$standby_log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------
4 9 ACTIVE
5 0 UNASSIGNED
6 0 UNASSIGNED
Object Creation in Primary Database
a)
Create some objects and insert some data into it. So after switch over
we could check whether the object and data have been applied into Data
Guard (Standby Database)
SQL> create table scott.test( id number, subject varchar(20));
Table created.
SQL> insert into scott.test values (&n,'&subject_name')
2 .
SQL>
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> insert into scott.test values (&n,'&subject_name');
Enter value for n: 1
Enter value for subject_name: oracle
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (1,'oracle')
1 row created.
SQL> /
Enter value for n: 2
Enter value for subject_name: vb
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (2,'vb')
1 row created.
SQL> 3
SP2-0226: Invalid line number
SQL> /
Enter value for n: 3
Enter value for subject_name: principles of management
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (3,'principles of management')
insert into scott.test values (3,'principles of management')
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL> /
Enter value for n: 34
Enter value for subject_name: c++
old 1: insert into scott.test values (&n,'&subject_name')
new 1: insert into scott.test values (34,'c++')
1 row created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
*********************************************************
Changing Roles
Switch Over Process:
1- Prepare the Primary Database Parameters:
a) Show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string %ORACLE_HOME%\RDBMS
standby_file_management string MANUAL
b) Show parameter log_archive_dest
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string service=standby1 lgwr
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string ENABLE
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0
c) set standby_archive_dest into primary database
SQL> alter system set standby_archive_dest='G:\oraclepdb\oradata\arch' scope=spfile;
System altered.
d) set the standby_file_management to auto
SQL> alter system set standby_file_management=auto scope=spfile;
System altered.
*********************************************************
2- Prepare the Standby Database Parameters:
a) show parameter log_archive
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=F:\oraclesb\oradata\a
rch
log_archive_dest_10 string
log_archive_dest_2 string location=F:\oraclesb\oradata\a
rch_2
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0
b) set the log_archive_dest_3 to the primary database service
SQL> alter system set log_archive_dest_3='service=primary lgwr' scope=spfile;
System altered.
c) set log_archive_dest_stat_3 to DEFER
SQL> alter system set log_archive_dest_state_3=defer scope=spfile;
System altered.
*********************************************************
3- Prepare to Switchover to Primary:
a) Select the switchover_status from the v$database
SQL> select switchover_status
2 from v$database;
SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE *it must be TO STANDBY
b) Select the username,status,type,process,sid,serial# from v$session
1 Select username,status,type,process,sid,serial# from v$session
2* where type='USER'
SQL> /
USERNAME STATUS TYPE PROCESS SID SERIAL#
------------------------------ -------- ---------- ------------ ---------- ----------
ACTIVE USER 1660 7 1
ACTIVE USER 1664 8 1
SYS ACTIVE USER 1376:2348 11 13
*********************************************************
4- Switchover Primary to Standby:
a) SQL> alter database commit to switchover to physical standby;
Database altered.
b) alert log report from the Primary database
Wed May 12 12:29:01 2004
ARCH: Evaluating archive log 1 thread 1 sequence 20
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: Beginning to archive log 1 thread 1 sequence 20
Creating archive destination LOG_ARCHIVE_DEST_3: 'standby1'
Wed May 12 12:29:01 2004
ARC0: Evaluating archive log 1 thread 1 sequence 20
ARC0: Unable to archive log 1 thread 1 sequence 20
Log actively being archived by another process
Wed May 12 12:29:02 2004
Creating archive destination LOG_ARCHIVE_DEST_2: 'G:\ORACLEPDB\ORADATA\ARCH_2\ARCH201.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'G:\ORACLEPDB\ORADATA\ARCH\ARCH201.ARCH'
ARCH: Completed archiving log 1 thread 1 sequence 20
Wed May 12 12:29:03 2004
SMON: disabling tx recovery
Wed May 12 12:29:03 2004
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Active process 2452 user 'SYSTEM' program 'ORACLE.EXE'
Active process 552 user 'SYSTEM' program 'ORACLE.EXE'
CLOSE: waiting for server sessions to complete.
CLOSE: all sessions shutdown successfully.
Wed May 12 12:29:06 2004
SMON: disabling cache recovery
Wed May 12 12:29:06 2004
Shutting down archive processes
Archiving is disabled
Wed May 12 12:29:06 2004
ARCH shutting down
ARC0: Archival stopped
Wed May 12 12:29:06 2004
ARCH shutting down
ARC1: Archival stopped
Wed May 12 12:29:06 2004
Thread 1 closed at log sequence 21
Successful close of redo thread 1.
Wed May 12 12:29:06 2004
ARCH: noswitch archival of thread 1, sequence 21
ARCH: End-Of-Redo archival of thread 1 sequence 21
ARCH: Evaluating archive log 2 thread 1 sequence 21
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARCH: Beginning to archive log 2 thread 1 sequence 21
Creating archive destination LOG_ARCHIVE_DEST_3: 'standby1'
Creating archive destination LOG_ARCHIVE_DEST_2: 'G:\ORACLEPDB\ORADATA\ARCH_2\ARCH211.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'G:\ORACLEPDB\ORADATA\ARCH\ARCH211.ARCH'
Wed May 12 12:29:06 2004
idle dispatcher 'D000' terminated, pid = (11, 1)
Wed May 12 12:29:07 2004
ARCH: Completed archiving log 2 thread 1 sequence 21
ARCH: archiving is disabled due to current logfile archival
Clearing standby activation ID 1363333035 (0x5142cfab)
The primary database controlfile was created using the
'MAXLOGFILES 50' clause.
There is space for up to 47 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
Archivelog for thread 1 sequence 21 required for standby recovery
MRP0 started with pid=10
MRP0: Background Managed Standby Recovery process started
Starting datafile 1 recovery in thread 1 sequence 21
Datafile 1: 'G:\ORACLEPDB\ORADATA\PRIMARY\SYSTEM01.DBF'
Starting datafile 2 recovery in thread 1 sequence 21
Datafile 2: 'G:\ORACLEPDB\ORADATA\PRIMARY\UNDOTBS01.DBF'
Starting datafile 3 recovery in thread 1 sequence 21
Datafile 3: 'G:\ORACLEPDB\ORADATA\PRIMARY\CWMLITE01.DBF'
Starting datafile 4 recovery in thread 1 sequence 21
Datafile 4: 'G:\ORACLEPDB\ORADATA\PRIMARY\DRSYS01.DBF'
Starting datafile 5 recovery in thread 1 sequence 21
Datafile 5: 'G:\ORACLEPDB\ORADATA\PRIMARY\EXAMPLE01.DBF'
Starting datafile 6 recovery in thread 1 sequence 21
Datafile 6: 'G:\ORACLEPDB\ORADATA\PRIMARY\INDX01.DBF'
Starting datafile 7 recovery in thread 1 sequence 21
Datafile 7: 'G:\ORACLEPDB\ORADATA\PRIMARY\ODM01.DBF'
Starting datafile 8 recovery in thread 1 sequence 21
Datafile 8: 'G:\ORACLEPDB\ORADATA\PRIMARY\TOOLS01.DBF'
Starting datafile 9 recovery in thread 1 sequence 21
Datafile 9: 'G:\ORACLEPDB\ORADATA\PRIMARY\USERS01.DBF'
Starting datafile 10 recovery in thread 1 sequence 21
Datafile 10: 'G:\ORACLEPDB\ORADATA\PRIMARY\XDB01.DBF'
Media Recovery Log G:\ORACLEPDB\ORADATA\ARCH_2\ARCH211.ARCH
Identified end-of-REDO for thread 1 sequence 21
Identified end-of-REDO for thread 1 sequence 21
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 309567
MRP0: Media Recovery Complete: End-Of-REDO
Resetting standby activation ID 1363333035 (0x5142cfab)
MRP0: Background Media Recovery process shutdown
Wed May 12 12:29:13 2004
Switchover: Complete - Database shutdown required
Completed: alter database commit to switchover to physical st
1- execute one statement on Primary database after moving it to Physical Standby DB
SQL> select *
2 from v$log;
from v$log
*
ERROR at line 2:
ORA-01507: database not mounted
c) shutdown the database
*********************************************************
5- Switchover the Standby to Primary:
a. select the switchover_status from v$database
SQL> select switchover_status
2 from v$database;
SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE
b. switchover to primary
SQL> alter database commit to switchover to primary;
Database altered.
c) Alert log entries after switching over to primary.
Sun Nov 28 12:57:09 2004
alter database commit to switchover to primary
Sun Nov 28 12:57:09 2004
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
RESETLOGS after incomplete recovery UNTIL CHANGE 309567
Resetting resetlogs activation ID 0 (0x0)
Online log 2 of thread 1 was previously cleared
Sun Nov 28 12:57:24 2004
Online log 6 of thread 0 was previously cleared
Switchover: Complete - Database shutdown required
Completed: alter database commit to switchover to primary
d) Shutdown the database.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
*********************************************************
6- Start the new Standby Database i.e. (old primary database)
a) Startup nomount
SQL> startup nomount
ORACLE instance started.
Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
b) Alert Log entries
ed May 12 12:55:41 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.2.1.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 109051904
large_pool_size = 25165824
java_pool_size = 25165824
control_files
= G:\oraclepdb\oradata\primary\CONTROL01.CTL,
G:\oraclepdb\oradata\primary\CONTROL02.CTL,
G:\oraclepdb\oradata\primary\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 25165824
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = location=G:\oraclepdb\oradata\arch
log_archive_dest_2 = location=G:\oraclepdb\oradata\arch_2
log_archive_dest_3 = service=standby1 lgwr
log_archive_dest_state_3 = ENABLE
log_archive_min_succeed_dest= 2
standby_archive_dest = G:\oraclepdb\oradata\arch
log_archive_format = arch%s%t.arch
db_file_multiblock_read_count= 16
standby_file_management = AUTO
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = primary
dispatchers = (PROTOCOL=TCP) (SERVICE=primaryXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = D:\oracle\admin\primary\bdump
user_dump_dest = D:\oracle\admin\primary\udump
core_dump_dest = D:\oracle\admin\primary\cdump
sort_area_size = 524288
db_name = primary
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed May 12 12:55:44 2004
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC1 started with pid=13
ARC1: Archival started
Wed May 12 12:55:45 2004
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed May 12 12:55:45 2004
ARC0: Thread not mounted
Wed May 12 12:55:45 2004
ARC1: Thread not mounted
c) Show parameter log_archive
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\oraclepdb\oradata\
arch
log_archive_dest_10 string
log_archive_dest_2 string location=G:\oraclepdb\oradata\
arch_2
log_archive_dest_3 string service=standby1 lgwr
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string ENABLE
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0
d) Set the parameter log_archive_dest_stat_3 to defer
SQL> alter system set log_archive_dest_state_3=defer scope=both;
System altered.
e) mount the database in standby mount phase
SQL> alter database mount standby database;
Database altered.
f)
SQL> alter database recover managed standby database disconnect from session;
Database altered.
g) Add the standby redo log files
SQL> startup nomount
ORACLE instance started.
Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO03.LOG' size 100m;
Database altered.
SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO02.LOG' size 100m;
Database altered.
SQL> alter database add standby logfile 'G:\ORACLEPDB\ORADATA\PRIMARY\sREDO01.LOG' size 100m;
Database altered.
*********************************************************
6- Start the new Primary Database i.e. (old standby database)
a) startup nomunt
SQL> startup nomount
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
b) show parameter log_archive
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=F:\oraclesb\oradata\a
rch
log_archive_dest_10 string
log_archive_dest_2 string location=F:\oraclesb\oradata\a
rch_2
log_archive_dest_3 string service=primary lgwr
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string DEFER
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string arch%s%t.arch
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 2
log_archive_start boolean TRUE
log_archive_trace integer 0
c) set the log_archive_dest_state_3 to enable
SQL> alter system set log_archive_dest_state_3=enable scope=both;
System altered.
d) mount and open the database
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
e) Alert Log entries
alter database mount
Sun Nov 28 13:24:00 2004
Successful mount of redo thread 1, with mount id 1380889259.
Sun Nov 28 13:24:00 2004
Database mounted in Exclusive Mode.
Completed: alter database mount
Sun Nov 28 13:24:05 2004
alter database open
Sun Nov 28 13:24:06 2004
LGWR: Primary database is in CLUSTER CONSISTENT mode
Assigning activation ID 1380889259 (0x524eb2ab)
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
Creating archive destination LOG_ARCHIVE_DEST_3: 'primary'
LNS0 started with pid=15
Sun Nov 28 13:24:10 2004
LGWR: Beginning to archive log 1 thread 1 sequence 23
Thread 1 advanced to log sequence 23
Thread 1 opened at log sequence 23
Current log# 1 seq# 23 mem# 0: F:\ORACLESB\ORADATA\STANDBY1\REDO01.LOG
Successful open of redo thread 1.
Sun Nov 28 13:24:10 2004
SMON: enabling cache recovery
Sun Nov 28 13:24:10 2004
ARC0: Evaluating archive log 2 thread 1 sequence 22
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3
ARC0: Beginning to archive log 2 thread 1 sequence 22
Creating archive destination LOG_ARCHIVE_DEST_3: 'primary'
Creating archive destination LOG_ARCHIVE_DEST_2: 'F:\ORACLESB\ORADATA\ARCH_2\ARCH221.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'F:\ORACLESB\ORADATA\ARCH\ARCH221.ARCH'
Sun Nov 28 13:24:11 2004
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Sun Nov 28 13:24:12 2004
kccrsz: expanded controlfile section 11 from 27 to 55 records
requested to grow by 27 record(s); added 2 block(s) of records
ARC0: Completed archiving log 2 thread 1 sequence 22
Sun Nov 28 13:24:12 2004
Dictionary check beginning
Dictionary check complete
Sun Nov 28 13:24:13 2004
SMON: enabling tx recovery
Sun Nov 28 13:24:13 2004
Database Characterset is WE8MSWIN1252
Updating 9.2.0.2.1 NLS parameters in sys.props$
-- adding 9.2.0.1.0 NLS parameters.
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
e) Alert Log entries in New Standby Database
RFS: Successfully opened standby logfile 4: 'G:\ORACLEPDB\ORADATA\PRIMARY\SREDO03.LOG'
Wed May 12 13:18:35 2004
Media Recovery Log G:\ORACLEPDB\ORADATA\ARCH\ARCH221.ARCH
Media Recovery Waiting for thread 1 seq# 23 (in transit)
Wed May 12 13:21:11 2004
Restarting dead background process QMN0
QMN0 started with pid=17
NOTE:
AT this stage the archive files are being sent from the new primary to new standby database.
So the complete switch over has been done.
*********************************************************
7- Check the data into new Primary database, which were inserted into old Primary Database before switch over:
SQL> select *
2 from scott.test;
ID SUBJECT
---------- --------------------
1 oracle
2 vb
34 c++
This is the same data, which was inserted into old Primary Database before switch over.
*********************************************************
Fail Over Process:
In
fail over the standby database will become the primary database and the
primary database needs to be reconstructed again as a standby database.
1- Make some Object and insert some records into Primary Database:
a) Make a test_new_primary table into primary database.
SQL> create table scott.test_new_primary(id number, name varchar2(20));
Table created.
b) Insert some data into test_new_primary table.
SQL> insert into scott.test_new_primary values (&n,'&name');
Enter value for n: 11
Enter value for name: data guard
old 1: insert into scott.test_new_primary values (&n,'&name')
new 1: insert into scott.test_new_primary values (11,'data guard')
1 row created.
SQL> /
Enter value for n: 22
Enter value for name: replication
old 1: insert into scott.test_new_primary values (&n,'&name')
new 1: insert into scott.test_new_primary values (22,'replication')
1 row created.
SQL> /
Enter value for n: 33
Enter value for name: rac
old 1: insert into scott.test_new_primary values (&n,'&name')
new 1: insert into scott.test_new_primary values (33,'rac')
1 row created.
SQL> commit;
*********************************************************
2- Shutdowns abort the primary database. (Simulation of crashing the database)
SQL> shutdown abort;
ORACLE instance shut down.
a) Alert Log entries in the standby database
Wed May 12 16:25:33 2004
RFS: Possible network disconnect with primary database
Wed May 12 16:25:33 2004
RFS: Possible network disconnect with primary database
Wed May 12 16:26:31 2004
Restarting dead background process QMN0
QMN0 started with pid=9
b) Alert log entries in the primary database
Sun Nov 28 16:31:24 2004
Shutting down instance (abort)
License high water mark = 3
Instance terminated by USER, pid = 4112
*********************************************************
3- Steps performed on the Standby Database:
a) Verify the archive gap
SQL> select *
2 from v$archive_gap;
no rows selected
b) Query the v$managed_standby view
SQL> select process,client_process,sequence#
2 from v$managed_standby;
PROCESS CLIENT_P SEQUENCE#
------- -------- ----------
ARCH ARCH 0
ARCH ARCH 25
MRP0 N/A 26
c) Query the v$database view
SQL> select switchover_status
2 from v$database;
SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE *
d) execute the recover statement
SQL> alter database recover managed standby database finish;
Database altered.
e) Alert Log entries from the Standby Database
alter database recover managed standby database finish
Wed May 12 16:36:29 2004
Terminal Recovery: request posted
Wed May 12 16:36:39 2004
TERMINAL RECOVERY changing datafile format version from 8.0.0.0.0 to 9.0.0.0.0
Switching logfile format version from 8.0.0.0.0 to 9.0.0.0.0
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 26 redo required
Terminal Recovery: G:\ORACLEPDB\ORADATA\PRIMARY\SREDO03.LOG
Identified end-of-REDO for thread 1 sequence 26
Incomplete recovery applied all redo ever generated.
Recovery completed through change 336495
MRP0: Media Recovery Complete
Terminal Recovery: enabled archive destination LOG_ARCHIVE_DEST_2
Switching logfile format version from 9.0.0.0.0 to 8.0.0.0.0
Terminal Recovery: successful completion
Wed May 12 16:36:41 2004
ARC1: Evaluating archive log 4 thread 1 sequence 26
ARC1: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_3 after log switch
ARC1: Beginning to archive log 4 thread 1 sequence 26
Creating archive destination LOG_ARCHIVE_DEST_3: 'standby1'
ARC1: Error 1034 Creating archive log file to 'standby1'
Wed May 12 16:36:41 2004
Errors in file d:\oracle\admin\primary\bdump\primary_arc1_4904.trc:
ORA-01034: ORACLE not available
Creating archive destination LOG_ARCHIVE_DEST_2: 'G:\ORACLEPDB\ORADATA\ARCH_2\ARCH261.ARCH'
Creating archive destination LOG_ARCHIVE_DEST_1: 'G:\ORACLEPDB\ORADATA\ARCH\ARCH261.ARCH'
ARC1: Completed archiving log 4 thread 1 sequence 26
Wed May 12 16:36:56 2004
Resetting standby activation ID 1380889259 (0x524eb2ab)
MRP0: Background Media Recovery process shutdown
Wed May 12 16:36:56 2004
Terminal Recovery: completion detected
Completed: alter database recover managed standby database fi
Wed May 12 16:37:02 2004
Restarting dead background process QMN0
QMN0 started with pid=9
f) Conversion from Standby Database to Primary Database
SQL> alter database commit to switchover to primary;
Database altered.
g) Alert log entries from oldstandby database (New Primary Database)
Wed May 12 16:40:05 2004
alter database commit to switchover to primary
Wed May 12 16:40:05 2004
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
NORESETLOGS after complete recovery through change 336495
Resetting resetlogs activation ID 0 (0x0)
Wed May 12 16:40:20 2004
Online log 6 of thread 0 was previously cleared
Changing control file format version from 8.0.0.0.0 to 9.0.0.0.0
RESETLOGS changing datafile format version from 9.0.0.0.0 to 8.0.0.0.0
Switchover: Complete - Database shutdown required
Completed: alter database commit to switchover to primary
h) Shutdown the database
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
i) Startup the database
SQL> startup
ORACLE instance started.
Total System Global Area 202447564 bytes
Fixed Size 453324 bytes
Variable Size 176160768 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
*********************************************************
4-
Verification to check that whether the data inserted in old crashed
Primary database has been applied into New Primary Database
SQL> select *
2 from scott.test_new_primary;
ID NAME
---------- --------------------
11 data guard
22 replication
33 rac
This
is the same object and data, which were inserted into the crashed
primary database. This has been applied to new Primary Database.
CONCURRENT MANAGER ISSUES IN APPS R12 Status code and Phase code for Concurrent requests Here is what the abbreviation for ...
No comments:
Post a Comment