[oracle@linux1 database]$ sqlplus /"As sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Mar 2 19:10:41 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- ----------
ORCL orcl PRIMARY READ WRITE
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
NOARCHIVELOG NO
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery _area
db_recovery_file_dest_size big integer 2G
change archivelog_dest:-
SQL> !mkdir -p /u01/app/oracle/oradata/chicago/arch
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/chicago/arch' scope=both;
System altered.
enable archivelog with new destination:
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/oradata/chicago/arch
Oldest online log sequence 1
Current log sequence 3
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/chicago/arch
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/chicago/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> !ls -al /u01/app/oracle/oradata/chicago/arch
total 23704
drwxr-xr-x 2 oracle oinstall 4096 Mar 2 19:32 .
drwxr-xr-x 3 oracle oinstall 4096 Mar 2 19:22 ..
-rw-r----- 1 oracle oinstall 24233984 Mar 2 19:32 1_3_809031038.dbf
SQL> select force_logging from v$database;
FOR
---
YES
SQL> select max(bytes),count(1) from v$log;
MAX(BYTES) COUNT(1)
---------- ----------
52428800 3
adding standby logfiles at same location as of archivelog:
SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog01.log' size 52M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog02.log' size 52M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog03.log' size 52M;
Database altered.
SQL> select group#,type,member from v$logfile where type='STANDBY';
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
4 STANDBY
/u01/app/oracle/oradata/chicago/sbylog01.log
5 STANDBY
/u01/app/oracle/oradata/chicago/sbylog02.log
6 STANDBY
/u01/app/oracle/oradata/chicago/sbylog03.log
To check the status of standby logfiles created
SQL> select group#,dbid,thread#,sequence#,status from v$standby_log;
GROUP# DBID THREAD# SEQUENCE#
---------- ---------------------------------------- ---------- ----------
STATUS
----------
4 UNASSIGNED 0 0
UNASSIGNED
5 UNASSIGNED 0 0
UNASSIGNED
6 UNASSIGNED 0 0
UNASSIGNED
setting DG_CONFIG:
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
SQL> alter system set log_archive_config='DG_CONFIG=(chicago,boston)' scope=spfile;
System altered.
setting log archive dest for primary database for all logfiles and all roles:
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/chicago/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' scope=spfile;
System altered.
setting log archive transport from primary destination to standby database visa service name called boston :
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' scope=spfile;
System altered.
enable the archive dests
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
System altered.
setting db_convert to change the location stdby when a datafile is added on primary location use this if you are using different path from primary:
SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orclstdby/' scope=spfile;
addding below will add redologfiles on standby with changed location:
SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orclstdby/' scope=spfile;
System altered.
making the file management auto this will add datafiles added on primary to standby:
SQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile;
System altered.
SQL> alter system set FAL_SERVER=boston scope=spfile;
System altered.
SQL> alter system set FAL_CLIENT=chicago scope=spfile;
System altered.
restart the db to take these parameters effect:
add listener config on standby as primary already has its settings:
[oracle@linux2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boston)
(ORACLE_HOME = /u01/app/oracle/11.1.0/db_1)
(SID_NAME = boston)
)
)
tnsnames.ora on both should have values like:
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BOSTON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = boston)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
create a initboston.ora file with db_name=primarydb (orcl)
create directory structure on standby, same as primary:
[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/admin/boston/adump
[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/oradata/boston
[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/oradata/boston/arch
[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/flash_recovery_area/boston
check the database connectivity between both the databases through the sqlplus:
SQL> conn sys/oracle@chicago as sysdba
SQL> conn sys/oracle@boston as sysdba
[oracle@linux2 ~]$ rman target sys/oracle@chicago auxiliary sys/oracle@boston
Recovery Manager: Release 11.1.0.6.0 - Production on Sun Mar 3 10:57:18 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1336811768)
connected to auxiliary database (not started)
RMAN> run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert'chicago','boston'
set db_unique_name='boston'
set db_file_name_convert='/orcl/','/boston/'
set log_file_name_convert='/orcl/','/boston/'
set control_files='/u01/app/oracle/oradata/boston/control01.ctl','/u01/app/oracle/oradata/boston/control02.ctl'
set log_archive_max_processes='5'
set fal_client='boston'
set fal_server='chicago'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(chicago,boston)'
set log_archive_dest_1='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'
;
}
created physical db
SQL> select name,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ ---------- ----------------
ORCL boston MOUNTED PHYSICAL STANDBY
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 0
Current log sequence 8
SQL> !ps -ef|grep mrp
oracle 3973 3965 0 12:05 pts/1 00:00:00 /bin/bash -c ps -ef|grep mrp
[oracle@linux2 trace]$ pwd
/u01/app/oracle/diag/rdbms/boston/boston/t
[oracle@linux2 trace]$ vi alert_boston.log
[oracle@linux2 trace]$ tail -f alert_boston.log
start applying the archive logs :
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
open database in read only mode
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL> alter database open read only;
start applying the logs again back to database (if you forget to add disconnect from session the mrp process with stop applying after you close session )
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
