Saturday 6 December 2008

Oracle 9i Data Guard Configuration, Testing and Switch Over/ Fail Over

Objective:

This paper is related to the Oracle 9i Data Guard Configuration, testing, switch over and fail over.

Introduction:

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.

Monday 24 November 2008

Oracle 9i AS R2 Report Server shutting down issue

Introduction:
We faced a problem in Oracle 9i AS R2 report services, the log messages was showing as (URLEngineImpl:shutdown): Shutting down engine.

Resolution:
We tried to restart the report services from command line and from enterprise manager console, but after successfull restart it was shutting down again.

When we ran the following command

./$ORACLE_HOME/bin/rwserver.sh server=rep_server

it was throwing the following error

REP-56033: Job 67192 does not exist.

We checked the XWindows server and it was working fine, so the issue was not with the Xwindows as well.

we did the backup of the rep_server.dat file and removed this file, it is at the path $ORACLE_HOME/reports/server, having removed this file, we again restarted the reports services with the command line and it was all working, we used the following command

nohup $ORACLE_HOME/bin/rwserver.sh server=rep_server &

Thursday 13 November 2008

Oracle 9i Multi Master Advance Replication setup, testing and troubleshooting

Objective:
This paper is related to the Multi Master Replication setup, testing and troubleshooting.

Introduction:
In this Multi Master Replication testing two machines are being used.
There will be two databases on the two different machines i.e.

a) Master1 (Master definition Site)

This site will be used for the administration of the Replication setup.

b) Master2

This will act as a second Master site in the Replication.

Replication Setup Procedure
On Master1 Database:

1- Check the init parameters

a) SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0

It should be same as the Oracle 9i Release which is being used for the Replication

b) SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

Add 110MB in the shared_pool_size

c) SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150

Add 12 to the processes

d) SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

It should be TRUE

e) SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

It is the extension component of the local databases Global Name

f) SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

Add 2 for each master site into open_links

g) distributed_transaction * need to be clarify


h) SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

i) NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

Add 1 per additional Master

k) SQL> show parameter servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 5
parallel_min_servers integer 0

set parallel_max_servers to 10
set parallel_min_servers to 2

2- Check the init parameters after modifications

a) SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0

b) SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 167772160

c) SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 11
log_archive_max_processes integer 2
processes integer 170

d) SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

e) SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string world


f) SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 6
open_links_per_instance integer 4


g) SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

h) SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 11

i) SQL> show parameter parallel_max_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 10
SQL> show parameter parallel_min_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 2

3- Check the Tablespace Free Space

1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 4.25
TOOLS 3.9375
UNDOTBS1 189.9375
USERS 24.9375
XDB .1875

System Tablespace should have 80MB free space
Undo Tablespace should have 60 MB free space


3.1) Increase the required space

a) Check the total space for each tablespace in database

SQL> l
1 select file_name,bytes/1024/1024
2* from dba_data_files
SQL> /

FILE_NAME BYTES/1024/1024
------------------------------------------------------- ---------------
G:\ORACLEPDB\ORADATA\MASTER1\SYSTEM01.DBF 400
G:\ORACLEPDB\ORADATA\MASTER1\UNDOTBS01.DBF 200
G:\ORACLEPDB\ORADATA\MASTER1\CWMLITE01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\DRSYS01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\EXAMPLE01.DBF 148.75
G:\ORACLEPDB\ORADATA\MASTER1\INDX01.DBF 25
G:\ORACLEPDB\ORADATA\MASTER1\ODM01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\TOOLS01.DBF 10
G:\ORACLEPDB\ORADATA\MASTER1\USERS01.DBF 25
G:\ORACLEPDB\ORADATA\MASTER1\XDB01.DBF 38.125

b) Increase the system tablespace

1* alter database datafile 'G:\ORACLEPDB\ORADATA\MASTER1\SYSTEM01.DBF' resize 600m
SQL> /

Database altered.


c) Check the Free space again.


1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 204.25
TOOLS 3.9375
UNDOTBS1 189.8125
USERS 24.9375
XDB .1875

4- Install the Replication catalog:

Oracle 9i does not required to install the Replication Catalog

5- Net9 configuration:


MASTER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master1)
)
)

MASTER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master2)
)
)

6- Creation of Replication Administrator (REPADMIN):

SQL> create user repadmin identified by repadmin
2 default tablespace tools
3 temporary tablespace temp
4 quota unlimited on tools
5 quota unlimited on temp
6 quota 0 on system;

User created.

SQL> grant connect, resource to repadmin;

Grant succeeded.

SQL> exec dbms_repcat_admin.grant_admin_any_schema('REPADMIN')

PL/SQL procedure successfully completed.

SQL> grant comment any table to repadmin;

Grant succeeded.

SQL> grant lock any table to repadmin;

Grant succeeded.


7- Creation of Replication Propagator / Receiver:

The propagator user is responsible to forward the deferred transactions from the source master site to the destination master site.

The receiver user is responsible for applying these transactions at the destination master site.

The replication administrator, propagator and receiver are normally the same user i.e. REPADMIN

Register the REPADMIN as the propagator

SQL> execute dbms_defer_sys.register_propagator('REPADMIN')

PL/SQL procedure successfully completed. SQL> grant execute any procedure to repadmin;

Grant succeeded.

8- Creation of Schema Owner

This user is usually responsible for the day-to-day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN.
SQL> ed
Wrote file afiedt.buf
1 create user repdba identified by repdba
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 quota unlimited on temp
6* quota 0 on system
SQL> /
User created.

9- Create Database Links

All databases participating in the Replication must have a unique global name. The global name should be the same as the database name.
a) Check the global_name

SQL> select *
2 from global_name
3 ;

GLOBAL_NAME
--------------------------------------------------------
MASTER1.US.ORACLE.COM

b) Change the global_name

SQL> update global_name
2 set global_name='MASTER1';
1 row updated.

SQL> commit;
Commit complete.
c) Check the global_name
SQL> select *
2 from global_name;

GLOBAL_NAME
---------------------------------------
MASTER1

10- Create the Public Database Link

a) Check the database links in the database
SQL> select *
2 from dba_db_links;
no rows selected

b) Create the public database link
1 create public database link master2
2* using 'master2'
SQL> /
Database link created.

c) Check the database link
1 select *
2* from dba_db_links

SQL> /

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ ---------- ---------
PUBLIC MASTER2 master2 15-MAY-04

11- Create the Private Database Link for REPADMIN

a) Create the private database link by connecting as REPADMIN

1 create database link master2
2* connect to repadmin identified by repadmin
3 /

Database link created.


b) Check the database link

SQL> l
1 select *
2* from dba_db_links
SQL> /

OWNER DB_LINK USERNAME HOST CREATED
---------- -------------------- ------------------------------ ---------- ---------
PUBLIC MASTER2 master2 17-MAY-04
REPADMIN MASTER2 REPADMIN 17-MAY-04

12- Schedule “push” and “purge” jobs:

a) Connect with REPADMIN

b) Set up the “push” job to push the deferred queue automatically.

1 begin
2 dbms_defer_sys.schedule_push (
3 DESTINATION => 'master2',
4 INTERVAL => '/*1:Mins*/ sysdate + 10/(60*24)',
5 NEXT_DATE => sysdate,
6 STOP_ON_ERROR => FALSE,
7 delay_seconds => 0,
8 PARALLELISM => 1);
9* end;

SQL> /

PL/SQL procedure successfully completed.


c) Set up the “purge” job to purge the deferred queue automatically.

SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date => sysdate,
4 interval => '/*1:Hr*/ sysdate + 1/24',
5 delay_seconds => 0,
6 rollback_segment => '');
7 end;
8 /

PL/SQL procedure successfully completed.


On Master2 Database:

1- Check the init parameters

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0
It should be same as the Oracle 9i Release which is being used for the Replication

b) SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648

Add 110MB in the shared_pool_size

c) SQL> SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150

Add 12 to the processes

d) SQL> SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

It should be TRUE




e) SQL> SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string world

It is the extension component of the local databases Global Name

f) SQL> SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

Add 2 for each master site into open_links

g) distributed_transaction * need to be clarify

h) SQL> SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

i) SQL> show parameter job_queue

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

Add 1 per additional Master
k) SQL> SQL> show parameter servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 5
parallel_min_servers integer 0

set parallel_max_servers to 10
set parallel_min_servers to 2


2- Check the init parameters after modifications

a) SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 9.2.0.0.0

b) SQL> SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 167772160

c) SQL> SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 11
log_archive_max_processes integer 2
processes integer 170

d) SQL> SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

e) SQL> SQL> show parameter db_domain ***

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string


f) SQL> SQL> show parameter open_links

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 6
open_links_per_instance integer 4


g) SQL> SQL> show parameter replication

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE

h) SQL> SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 11

i) SQL> SQL> show parameter parallel_max_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 10
SQL> SQL> show parameter parallel_min_servers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers integer 2


3- Check the Tablespace Free Space

1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 4.25
TOOLS 3.9375
UNDOTBS1 194.375
USERS 24.9375
XDB .1875

System Tablespace should have 80MB free space
Undo Tablespace should have 60 MB free space
3.1) Increase the required space
b) Check the total space for each tablespace in database

SQL> l
1 1 select file_name,bytes/1024/1024
2* from dba_data_files
SQL> /

FILE_NAME BYTES/1024/1024
------------------------------------------------------- ---------------
F:\ORACLESB\ORADATA\MASTER2\SYSTEM01.DBF 400
F:\ORACLESB\ORADATA\MASTER2\UNDOTBS01.DBF 200
F:\ORACLESB\ORADATA\MASTER2\CWMLITE01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\DRSYS01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\EXAMPLE01.DBF 148.75
F:\ORACLESB\ORADATA\MASTER2\INDX01.DBF 25
F:\ORACLESB\ORADATA\MASTER2\ODM01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\TOOLS01.DBF 10
F:\ORACLESB\ORADATA\MASTER2\USERS01.DBF 25
F:\ORACLESB\ORADATA\MASTER2\XDB01.DBF 38.125

b) Increase the system tablespace
SQL> alter database datafile 'F:\ORACLESB\ORADATA\MASTER2\SYSTEM01.DBF' resize 600m;

Database altered.

c) SQL> select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3 group by tablespace_name
4 /

TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 204.25
TOOLS 3.9375
UNDOTBS1 194.375
USERS 24.9375
XDB .1875

4- Install the Replication catalog:

Oracle 9i does not required to install the Replication Catalog

5- Net9 configuration:

MASTER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = master2)
)
)


MASTER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.71)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = master1)
)
)

6- Creation of Replication Administrator (REPADMIN):

1 create user repadmin identified by repadmin
2 default tablespace tools
3 temporary tablespace temp
4 quota unlimited on tools
5 quota unlimited on temp
6* quota 0 on system
SQL> /

User created.
SQL> grant connect, resource to repadmin;

Grant succeeded.

SQL> exec dbms_repcat_admin.grant_admin_any_schema('REPADMIN')

PL/SQL procedure successfully completed.

SQL> grant comment any table to repadmin;

Grant succeeded.

SQL> grant lock any table to repadmin;

Grant succeeded.


7- Creation of Replication Propagator / Receiver:

The propagator user is responsible to forward the deferred transactions from the source master site to the destination master site.

The receiver user is responsible for applying these transactions at the destination master site.

The replication administrator, propagator and receiver are normally the same user i.e. REPADMIN
Register the REPADMIN as the propagator

SQL> execute dbms_defer_sys.register_propagator('REPADMIN')

PL/SQL procedure successfully completed. SQL> grant execute any procedure to repadmin;

Grant succeeded.


8- Creation of Schema Owner

This user is usually responsible for the day-to-day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN.

SQL> ed
Wrote file afiedt.buf

1 create user repdba identified by repdba
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 quota unlimited on temp
6* quota 0 on system
SQL> /

User created.

9- Create Database Links

All databases participating in the Replication must have a unique global name. The global name should be the same as the database name.
a) Check the global_name

SQL> select *
2 from global_name
3 ;

GLOBAL_NAME
--------------------------------------------------------
MASTER2

10-Create the Public Database Link

a) Check the database links in the database

SQL> select *
2 from dba_db_links;

no rows selected

b) Create the public database link

1 create public database link master1
2* using 'master1'
SQL> /

Database link created.

c) Check the database link

1 select *
2* from dba_db_links
SQL> /

OWNER DB_LINK USERNAME HOST CREATED
---------- ------------------------------ ------------------------------ ---------- ---------
PUBLIC MASTER1 master1 03-DEC-04


11-Create the Private Database Link for REPADMIN

a) Create the private database link by connecting as REPADMIN

1 create database link master1
2* connect to repadmin identified by repadmin
3 /

Database link created.


b) Check the database link

SQL> l
1 SQL> select *
2 from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- ------------------------------ ------------------------------ ---------- ---------
PUBLIC MASTER1 master1 03-DEC-04
REPADMIN MASTER1 REPADMIN 17-MAY-04

12-Schedule “push” and “purge” jobs:

a) Connect with REPADMIN

b) Set up the “push” job to push the deferred queue automatically.

1 begin
2 dbms_defer_sys.schedule_push (
3 DESTINATION => 'master1',
4 INTERVAL => '/*10:Mins*/ sysdate + 10/(60*24)',
5 NEXT_DATE => sysdate,
6 STOP_ON_ERROR => FALSE,
7 delay_seconds => 0,
8 PARALLELISM => 1);
9* end;
SQL> /

PL/SQL procedure successfully completed.


c) Set up the “purge” job to purge the deferred queue automatically.

SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date => sysdate,
4 interval => '/*1:Hr*/ sysdate + 1/24',
5 delay_seconds => 0,
6 rollback_segment => '');
7 end;
8 /

PL/SQL procedure successfully completed.

Object Creation on Master1

a) Connect with REPDBA and create the following objects

DROP TABLE DEPT; CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ; DROP TABLE EMP; CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);SQL> conn repdba/repdba@master1
Connected.
SQL> show user
USER is "REPDBA"
SQL> DROP TABLE DEPT;
DROP TABLE DEPT
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
3 DNAME VARCHAR2(14) ,
4 LOC VARCHAR2(13) ) ;

Table created.

SQL> DROP TABLE EMP;
DROP TABLE EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table created.

b) Create Master Replication Object Group at Master1 by connecting as REPADMIN

1 begin
2 dbms_repcat.create_master_repgroup(
3 gname => '"GROUP1"',
4 qualifier => '',
5 group_comment => '');
6* end;
SQL> /
PL/SQL procedure successfully completed.


c) Add Master Database

SQL> begin
2 dbms_repcat.add_master_database(
3 gname => '"GROUP1"',
4 master => 'master2',
5 use_existing_objects => TRUE,
6 copy_rows => TRUE,
7 propagation_mode => 'ASYNCHRONOUS');
8 end;
9 /

PL/SQL procedure successfully completed.

d) Create Objects into Group i.e. “group1”
1 begin
2 dbms_repcat.create_master_repobject(
3 gname => '"GROUP1"',
4 type => 'TABLE',
5 oname => 'DEPT',
6 sname => '"REPDBA"',
7 copy_rows => TRUE,
8 use_existing_object => TRUE);
9* end;
SQL> /
PL/SQL procedure successfully completed.

1 begin
2 dbms_repcat.create_master_repobject(
3 gname => '"GROUP1"',
4 type => 'TABLE',
5 oname => 'EMP',
6 sname => '"REPDBA"',
7 copy_rows => TRUE,

8 use_existing_object => TRUE);
9* end;
SQL> /
PL/SQL procedure successfully completed.

e) Generate the Replication support

SQL> begin
2 dbms_repcat.generate_replication_support(
3 sname => '"REPDBA"',
4 oname => '"DEPT"',
5 type => 'TABLE',
6 min_communication => TRUE);
7 end;
8 /

PL/SQL procedure successfully completed.

1 begin
2 dbms_repcat.generate_replication_support(
3 sname => '"REPDBA"',
4 oname => '"EMP"',
5 type => 'TABLE',
6 min_communication => TRUE);
7* end;
SQL> /

PL/SQL procedure successfully completed.

f) Resume activity on the Master1

SQL> begin
2 dbms_repcat.resume_master_activity(
3 gname => '"GROUP1"');
4 END;
5 /

PL/SQL procedure successfully completed.


Test the Replication

a) Insert rows into DEPT at Master1 site, by connecting REPDBA

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');


b) As soon as the “push” job executes at Master1 site, these inserted records will be pushed into the Master2 site. This behavior is vice versa as this is Multi Master Replication setup.


c) Insert rows into EMP at Master1 site, by connecting REPDBA

SQL> conn repdba/repdba@master1
Connected.

SQL> INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,
to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

1 row created.

SQL> commit;

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

d) Check the EMP table in Master2 site by connecting REPDBA
SQL> conn repdba/repdba@master2
Connected.
SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20


After 10 minutes again apply the SELECT statement.

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

The same records have been pushed to site Master2. So the Multi Master Replication
is working fine.

The same this can be applied vice versa as well.

f) connect repdba/repdba@master2
SQL> conn repdba/repdba@master2
Connected.

g) SELECT the rows from the EMP table

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

h) Insert one new record into EMP table

1 INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',
2* 7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30)
SQL> /

1 row created.

SQL> commit;

Commit complete.
i) Again SELECT from emp

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

j) Now connect repdba/repdba@master1

SQL> conn repdba/repdba@master1
Connected.

k) SELECT records from the emp table

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

The third record has yet not been pushed to Master1 site. This will be pushed as soon as the “push” job is executed at Master2 site.

Again SELECT the records from EMP table.

SQL> select *
2 from emp
3 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30


Now the same records have been pushed to site Master1 from Master2 site. So the Multi Master Replication is working fine from both the ends.



Views to check the status of the propagated transaction

a) Connect to Master1 as REPDBA


SQL> conn repdba/repdba@master1
Connected.

b) Insert one record into EMP

SQL> INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839, to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

1 row created.

SQL> commit;

c) Select the table EMP

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

d) Check the view “defcall”

SQL> select *
2 from defcall;

CALLNO DEFERRED_TRAN_ID SCHEMANAME PACKAGENAME PROCNAME ARGCOUNT
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
0 10.13.263 REPDBA EMP$RP REP_INSERT 10

e) Check the view “deftrandest”

SQL> select *
2 from deftrandest;
DEFERRED_TRAN_ID DELIVERY_ORDER DBLINK
------------------------------ -------------- --------------
10.13.263 302468 MASTER2

f) connect to Master2 as REPDBA

SQL> conn repdba/repdba@master2
Connected.

g) Check the EMP Table

SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

h) connect to Master1 as REPADMIN and execute the following procedure to push the transaction to the Master2

SQL> execute dbms_defer_sys.execute('master2');
PL/SQL procedure successfully completed.

i) Connect to Master 2 as REPDBA
SQL> conn repdba/repdba@master2
Connected.

j) Check the EMP table
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

Wednesday 5 November 2008

login page problem in Oracle APPS 11i

We faced an interesting problem in Oracle APPS 11i, that login page was not displaying when we clicked on the Ebusiness home page on http://appsserver:port, instead showing the login page, it showed us nothing.

When we checked the error_log file for Apache in forms node, it was showing the following error,

[Tue Nov 4 15:55:29 2008] [error] [client 172.21.8.50] client denied by server configuration: /oracle/app/comn/portal/uat_essappuat/oprocmgr-service

We checke almost all the configuration files, we also checked the tnsping from forms node, just to check whether it is able to connect to the database, we used sqlplus on forms node, to connect to the database node, and all was working.

we opened the SR with Oracle Support, and having seen the logs, they referred us to use note 372096.1, And when we checked the hosts file of forms node, the entry for the forms node server was with the following entry.

10.x.x.x servername.wrongdomain.com servername.correctdomain.com sername

what we did, we removed the servername,wrongdomain.com from /etc/hosts file, and restared the forms / appache services with ./adstrtall.sh and when we checked every thing was working and users were able to see the login page and logged in successfully.

cheers

Tuesday 8 July 2008

Deleting bulk rows in batches

There might be severe problems if the bulk number of rows are deleted fromt the production table. As this will lock the table in shared mode. And resource of the server will be used, offcourse it will use the UNDO tablespace heavily.

Following is the procedure which can be used to delete the bulk rows in batches on certain conditions. Also it will run in loops which can be controlled and also once it will delete the records it will put the message into purge_history table.

declare
type table_rowid_Array is table of rowid index by binary_integer;
l_rids table_rowid_Array;
v_counter number := 0;
v_counter_total number := 0;
v_id_counter number := 0;
begin
for i in 1 .. 500
loop
v_id_counter := i;
select rowid
bulk collect into l_rids
from BIG_LOG_TABLE where log_date < '1-JUN-2008' and rownum < 25001;
for i in 1 .. l_rids.count
loop
delete from BIG_LOG_TABLE where rowid = l_rids(i);
v_counter := v_counter +1 ;
end loop;
dbms_output.put_line( v_counter ' rows deleted' );
v_counter_total := v_counter_total + v_counter;
v_counter := 0;
insert into purge_history values (v_id_counter,v_counter_total,sysdate,BIG_LOG_TABLE );
commit;
end loop;
dbms_output.put_line( v_counter_total ' total rows deleted' );
end;

Sunday 30 March 2008

Recover the table data back in time with Flashback Query

Recover the table data back in time with Flashback Query

Flashback is a pretty useful feature in the Oracle 9i. It can save a lot of time and energy. To use this feature the UNDO_MANAGEMENT should be set to AUTO in the database. And offcourse the UNDO_RETENTION should be large enough so the Flashback Query can recove the data back in time.

We recovered one of the table data. Some records were updated incorrectly and was commited. Then we recovered the old data just before the update statement took place. we used the following procedure and we followed the metlaink document number 174425.1. We went 60 minutes back in time from the current time.

Procedure to recover data back in time with Flashback feature of Oracle 9i:

DECLARE
CURSOR FLASH_RECOVER IS
select * from LIST_GENERATION;
list_generation_recover_rec LIST_GENERATION%ROWTYPE;
begin
DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate - 60/1440);
open FLASH_RECOVER;
DBMS_FLASHBACK.DISABLE;
loop
FETCH FLASH_RECOVER INTO list_generation_recover_rec;
EXIT WHEN FLASH_RECOVER%NOTFOUND;
insert into LIST_GENERATION
values
(list_generation_recover_rec.PARTY_ID ,
list_generation_recover_rec.TRANS_DATE ,
list_generation_recover_rec.CALL_FLAG ,
list_generation_recover_rec.CAMPAIGN_SCHEDULE_ID ,
list_generation_recover_rec.LIST_TYPE ,
list_generation_recover_rec.REQUEST_ID ,
list_generation_recover_rec.CREATION_DATE ,
list_generation_recover_rec.CREATED_BY ,
list_generation_recover_rec.LAST_UPDATE_DATE ,
list_generation_recover_rec.LAST_UPDATED_BY );
end loop;
CLOSE FLASH_RECOVER;
commit;
end;

PL/SQL procedure successfully completed.

apps-sql>select count(*) from LIST_GENERATION;

COUNT(*)
----------
5693

Sunday 23 March 2008

Oracle 9i Data Guard Configuration, testing and switch over

Objective:

This paper is related to the Oracle 9i Data Guard Configuration, testing and switch over.

Introduction:

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. 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 Machine .

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 (in new Primary Database)

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 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.

 Oracle Weblogic 12.2.1.3.0 60820: Select a domain which is a compatible with this environment. stopComponent.sh and start Component.sh give...