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@lvgiprod AUXILIARY sys/oracle@prodods
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 a 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.
Add following entries in to the primary server :
ALTER
SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;
ALTER
SYSTEM SET
LOG_ARCHIVE_DEST_3='LOCATION=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\STDBY_PRIMDB
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=PRIMDB' SCOPE=BOTH;
17.
In the stand by server add the following entries :
ALTER
SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;
ALTER
SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\STDBY_SECODB
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=SECODB'SCOPE=BOTH;
18.
Now start the primary server (PRIMDB) and execute the
following command :
alter database add supplemental log data;
execute dbms_logstdby.build;
alter system switch logfile;
19.
Shut down the database and start
the standby server in nomount stage
20.
Now mount the standby database using the following command
:
alter database mount standby database;
alter database recover to logical standby SECODB;
21.
Wait for the command to complete
and after completing shutdown the database and start the standby database in
mount mode :
shut immediate;
startup mount;
22.
Open the database in resetlog mode
:
alter database open resetlogs;
select NAME, OPEN_MODE, DB_UNIQUE_NAME,
DATABASE_ROLE from V$database ;
NAME OPEN_MODE
DB_UNIQUE_NAME DATABASE_ROLE
--------- ---------- ----------------------- ------------------------
SECODB READ WRITE SECODB LOGICAL STANDBY
23.
Start Log apply process
(SECODB):
alter database start logical standby apply
immediate;
IMPORTANT VIEWS:
SELECT * FROM
DBA_LOGSTDBY_UNSUPPORTED;
SELECT * FROM
DBA_LOGSTDBY_NOT_UNIQUE;
SELECT * FROM
DBA_LOGSTDBY_PARAMETERS;
SELECT * FROM
DBA_LOGSTDBY_PROGRESS;
SELECT * FROM
DBA_LOGSTDBY_LOG;
SELECT * FROM
DBA_LOGSTDBY_SKIP_TRANSACTION;
SELECT * FROM
DBA_LOGSTDBY_SKIP;
SELECT * FROM
DBA_LOGSTDBY_EVENTS;
SELECT * FROM
DBA_LOGSTDBY_HISTORY;
SELECT * FROM V$LOGSTDBY;
SELECT * FROM V$LOGSTDBY_STATS;
SELECT THREAD#, SEQUENCE#,
APPLIED FROM V$ARCHIVED_LOG;
SELECT LOCAL.THREAD#,
LOCAL.SEQUENCE# FROM
(SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE LOCAL.SEQUENCE# NOT
IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# =
LOCAL.THREAD#);
SQL> select
applied_thread#,applied_scn,to_char(applied_time,'dd/mm/yyyy
hh12:mi:ss'),newest_scn,applied_sequence#,newes
t_sequence# from DBA_LOGSTDBY_PROGRESS;
APPLIED_THREAD# APPLIED_SCN TO_CHAR(APPLIED_TIM NEWEST_SCN
APPLIED_SEQUENCE# NEWEST_SEQUENCE#
--------------- ----------- ------------------- ----------
----------------- ----------------
1 2159531 16/01/2013 12:47:09 2185744 206 211
Add
a Object to Skip List:
Stop Log Apply Process
alter database stop logical standby;
execute dbms_logstDBY.SKIP(STMT => 'DML',SCHEMA_NAME =>
'INS', OBJECT_NAME =>'SERVICE_TRANSACTION_LOG');
start apply process
Disable/Enable
Guard:
Alter database gurad standby; (standby mode)
Alter database guard none; (None)
Alter database guard all; (enable)
Alter
session disable guard;
Alter
session enable guard;
No comments:
Post a Comment
thedbaportfolio@gmail.com