Primary Database Name = PRIMDB
Secondary Database Name = SECODB
1.
Make sure that the primary
database in archive log mode :
Shut immediate;
Startup mount;
Alter database archive log;
Alter database open;
2.
Make sure that force logging is
enabled in the primary server :
Alter database force logging;
3.
Configure TNSNAMES.ora on the
Primary Server :
PRIMDB
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.x.x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMDB)
)
)
SECODB
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.x.x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SECODB)
(UR=A)
)
)
4.
Following parameters needs to be
added to the Primary Database(PRIMDB) Pfile :
PRIMDB.__db_cache_size=251658240
PRIMDB.__java_pool_size=4194304
PRIMDB.__large_pool_size=4194304
PRIMDB.__shared_pool_size=142606336
PRIMDB.__streams_pool_size=8388608
*.audit_file_dest='D:\oracle\product\10.2.0/admin/PRIMDB/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/PRIMDB/bdump'
*.compatible='10.2.0.3.0'
*.control_files='D:\oracle\product\10.2.0/oradata/PRIMDB/\control01.ctl','D:\oracle\product\10.2.0/oradata/PRIMDB/\control02.ctl','D:\oracle\product\10.2.0/oradata/PRIMDB/\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/PRIMDB/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PRIMDB'
*.db_unique_name='PRIMDB'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=PRIMDBXDB)'
*.FAL_CLIENT='PRIMDB' (Deprecated Parameter in 11g)
*.FAL_SERVER='SECODB'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB,SECODB)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\archive\PRIMDB
VALID_FOR=(all_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'
*.LOG_ARCHIVE_DEST_2='SERVICE=SECODB
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SECODB'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='Arc_%d_%s_%t_%r'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='D:\ORACLE\PRODUCT\10.2.0\ORADATA\SECODB','D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMDB'
*.DB_FILE_NAME_CONVERT='D:\ORACLE\PRODUCT\10.2.0\ORADATA\SECODB','D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMDB'
*.nls_date_format='dd/MM/yyyy'
*.open_cursors=300
*.pga_aggregate_target=148897792
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=419430400
*.sga_target=419430400
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/PRIMDB/udump'
5.
Create spfile from the newly
created pfile and start the database
using spfile :
Shut immediate;
Startup nomount;
Create spfile from pfile;
Shut immediate;
Startup
6.
If you have no password file the
create a password file by using orapwd utility :
orapwd file=PWDPRIMDB.ora password=oracle entries=5
ignorecase=y
7.
Now connect to database using rman
and backup the database using following script.
rman target /
configure
default device type to disk;
configure device type disk parallelism
1;
configure retention policy to redundancy
1;
configure channel device type disk
maxpiecesize=1 G format
'D:\RMAN\PRIMDB_FULL_%t_%s_%p.rec';
configure controlfile autobackup on;
configure controlfile autobackup format
for device type disk to 'D:\RMAN\PRIMDB_C_%F.rec';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
run {
allocate channel c1 type disk;
backup database plus
archivelog;
backup current controlfile for standby ;
sql 'alter system archive log current';
backup
archivelog all;
release channel c1;
}
8.
Now copy the backup files
generated in the D:\RMAN folder and keep it in same directory in the secondary
server (SECODB). The directory structure in both the primary and standby server
should be same.
9.
Configure TNSNAMES.ora on the
Secondary Server (SECODB) :
SECODB
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.x.x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SECODB)
)
)
PRIMDB
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.x.x)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMDB)
(UR=A)
)
)
10.
Now change the secondary
database(SECODB) parameter file :
PRIMDB.__db_cache_size=251658240
PRIMDB.__java_pool_size=4194304
PRIMDB.__large_pool_size=4194304
PRIMDB.__shared_pool_size=142606336
PRIMDB.__streams_pool_size=8388608
*.audit_file_dest='D:\oracle\product\10.2.0/admin/SECODB/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/SECODB/bdump'
*.compatible='10.2.0.3.0'
*.control_files='D:\oracle\product\10.2.0/oradata/SECODB/\control01.ctl','D:\oracle\product\10.2.0/oradata/SECODB/\control02.ctl','D:\oracle\product\10.2.0/oradata/SECODB/\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/SECODB/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PRIMDB'
*.db_unique_name='SECODB'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=SECODBXDB)'
*.FAL_CLIENT='SECODB' (Deprecated Parameter in 11g)
*.FAL_SERVER='PRIMDB'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SECODB,PRIMDB)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\archive\SECODB
VALID_FOR=(all_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SECODB'
*.LOG_ARCHIVE_DEST_2='SERVICE=PRIMDB
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMDB'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='Arc_%d_%s_%t_%r'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMDB','D:\ORACLE\PRODUCT\10.2.0\ORADATA\SECODB'
*.DB_FILE_NAME_CONVERT='D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMDB','D:\ORACLE\PRODUCT\10.2.0\ORADATA\SECODB'
*.nls_date_format='dd/MM/yyyy'
*.open_cursors=300
*.pga_aggregate_target=148897792
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=419430400
*.sga_target=419430400
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/SECODB/udump'
11.
At the standby server create an
oracle service using oradim :
Windows:
oradim
–new –sid SECODB –intpwd oracle –startmode manual -pfile
D:\oracle\product\10.2.0\db_1\database\initSECODB.ora
Linux
export ORACLE_SID=SECODB
12.
Create the directory structure for
the secondary database (i.e. BDUMP,UDUMP etc) and Startup the secondary
database in nomount mode.
startup nomount;
13.
At secondary server end in command
prompt run the following script :
rman
TARGET sys/oracle@PRIMDB AUXILIARY /
LIST
BACKUP OF CONTROLFILE;
LIST
COPY OF CONTROLFILE;
RUN
{
DUPLICATE TARGET DATABASE FOR STANDBY
NOFILENAMECHECK
DORECOVER;
}
You can restore the database
without taking the backup (as shown in Step 7) in 11gr2 by the following command:
rman
TARGET sys/oracle@PRIMDB AUXILIARY
sys/oracle@SECODB
Duplicate
target database for standby from active database;
*********************************************************************************************************************************************************
Note
:
As
of 11.2.0.2.0 you can connect to the target with “connect target”; however, if
you don’t specify the username duplication to standby will fail later with
“invalid username/password”.
While
this command is running I like to tail the standby alert log and see what is
going and watch for errors. Note that it is normal and OK to get “ORA-27037:
unable to obtain file status” on the online and standby log files. To perform
the duplication in parallel to improve performance you can allocate primary and
standby channels and then run the duplicate command.
run
{
allocate
channel chan1 type disk;
allocate
channel chan2 type disk;
allocate
channel chan3 type disk;
allocate
channel chan4 type disk;
allocate
auxiliary channel aux1 type disk;
allocate
auxiliary channel aux2 type disk;
allocate
auxiliary channel aux3 type disk;
allocate
auxiliary channel aux4 type disk;
duplicate
target database for standby from active database;
}
***********************************************************************************************************************************************************
14. If
the script completes without any error then the standby server (Physical
Standby) is created and in mount mode. Check the log sequence number of both
the server are matching or not (it might take some time) :
archive log list;
select NAME, OPEN_MODE, DB_UNIQUE_NAME,
DATABASE_ROLE from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE
------ ------------------
--------------- -----------------------
PRIMDB MOUNT SECODB PHYSICAL STANDBY
15.
If log sequence match then add
standby redo log files(SECODB). you need to calculate no of logfiles as
(#_logfiles_in_primary+1). I have 3 logfiles in Primary so I need to add 4
standby logfiles in secondary. You can also add standby logfiles in primary server
as well.
alter database add standby logfile group 4
('D:\oracle\product\10.2.0\oradata\SECODB\REDO04.log') size 50M;
16. To
start the apply process in Physical Standby
alter database recover managed standby database disconnect from
session;
alter database recover managed standby database using current
logfile disconnect from session;
Switch over:
A database can be in one of two
mutually exclusive modes (primary or standby).
In the primary database (PRIMDB):
select status, gap_status from
v$archive_dest_status where dest_id = 2;
STATUS
GAP_STATUS
--------- ------------------------
VALID
NO GAP
alter database commit to
switchover to physical standby;
shut immediate
startup nomount
alter database mount standby
database;
alter database recover managed standby
database disconnect from session;
In
Secondary Database (SECODB):
alter database commit to switchover to
primary;
shut immediate
startup
Important Views:
SELECT NAME, VALUE, DATUM_TIME FROM
V$DATAGUARD_STATS;
SELECT * FROM V$STANDBY_EVENT_HISTOGRAM
WHERE NAME = 'apply lag' AND COUNT > 0;
No comments:
Post a Comment
thedbaportfolio@gmail.com