PRIMARY RAC DATABASE INSTANCE PRIMDB1 IN HOST MACHINE DC1
PRIMARY RAC DATABASE INSTANCE PRIMDB2 IN HOST MACHINE DC2
CREATING A STANDBY DATABASE IN SERVER DC2 WITH NAME STBYDB
ASSUMPTION:
Primary:
· The RAC Primary Database is in fully operating condition in Archivelog mode using ASM storage (shared location) as archivelog destination.
· The Primary Database is using ASM as Storage and OMFs.
· Listener LISTENER has already been created.
steps in primary database Servers:-
# Database must in Archivelog mode.
# Enable Force Logging.
# Create the Standby Redo logs.
# Create a password file for all instances( Password must same in all nodes).
# Update listener.ora file to include SID information on each node in cluster.
# Verify cluster Service is available.
# Verify TNSNAMES.ORA file.
# Modify init.ora Parameters For DataGuard Configuration.
# Create temporary directory to hold the RMAN backup of this database.
# Backup the Primary Database for Standby.
# copy the Primary Database backup to the standby database server to create standby database.
steps in Standby database server:-
# create required folders and directories.
# Modify the init.ora parameter on standby database.
# create a password file(Password must same as primary database instances)
# Update listener.ora file to include SID information
# Add required TNS entries for standby database on all servers.
# Verify TNSNAMES.ORA file.
# Create standby database.
[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [black] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 03:41:53 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 17
Current log sequence 17
SQL> alter database force logging;
Database altered.
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
primdb1 OPEN
primdb2 OPEN
SQL> select name,db_unique_name,database_role from gv$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB primdb PRIMARY
PRIMDB primdb PRIMARY
IN PRIMARY DATABASE(any one of the node)
=====================================================================================
alter system set log_file_name_convert='+DATA/stbydb,'+DATA/primdb' scope=spfile sid='*';
alter system set db_file_name_convert='+DATA/stbydb','+DATA/primdb' scope=spfile sid='*';
alter system set fal_client='primdb1' sid='primdb1';
alter system set fal_client='primdb2' sid='primdb2';
alter system set fal_server='stbydb' sid='*';
alter system set log_archive_config='dg_config=(primdb,stbydb)' sid='*';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_2='ENABLE' sid='*';
alter system set log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' sid='*';
alter system set log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_1='enable' sid='*';
alter system set log_archive_dest_state_3='enable' sid='*';
alter system set log_archive_max_processes=7 sid='*';
alter system set log_archive_min_succeed_dest=2 sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' sid='*';
alter system set standby_file_management='auto' sid='*';
primdb2.__db_cache_size=520093696
primdb1.__db_cache_size=520093696
primdb2.__java_pool_size=16777216
primdb1.__java_pool_size=16777216
primdb2.__large_pool_size=16777216
primdb1.__large_pool_size=16777216
primdb2.__pga_aggregate_target=587202560
primdb1.__pga_aggregate_target=587202560
primdb2.__sga_target=872415232
primdb1.__sga_target=872415232
primdb2.__shared_io_pool_size=0
primdb1.__shared_io_pool_size=0
primdb2.__shared_pool_size=301989888
primdb1.__shared_pool_size=301989888
primdb2.__streams_pool_size=0
primdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/primdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/stbydb','+DATA/primdb'
*.db_name='primdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)'
primdb1.fal_client='primdb1'
primdb2.fal_client='primdb2'
*.fal_server='stbydb'
primdb2.instance_number=2
primdb1.instance_number=1
*.log_archive_config='dg_config=(primdb,stbydb)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb'
*.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_archive_dest='+DATA/primdb/STANDBYLOG'
*.standby_file_management='auto'
primdb1.thread=1
primdb2.thread=2
primdb2.undo_tablespace='UNDOTBS1'
primdb1.undo_tablespace='UNDOTBS2'
SQL> select force_logging from v$database;
FOR
---
YES
SQL> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=spfile sid='*';
System altered.
[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle force=y entries=5 ignorecase=y
[oracle@dc1 dbs]$
[oracle@dc2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle entries=5 force=y ignorecase=y
SQL> sho parameter password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
create standby logfile on primary database
=====================================================================================
SQL> col member for a56
SQL> select group#,member,type from v$logfile order by 1;
GROUP# MEMBER TYPE
---------- -------------------------------------------------------- -------
1 +DATA/primdb/onlinelog/group_1.468.773206197 ONLINE
1 +DATA/primdb/onlinelog/group_1.467.773206195 ONLINE
2 +DATA/primdb/onlinelog/group_2.470.773206197 ONLINE
2 +DATA/primdb/onlinelog/group_2.469.773206197 ONLINE
3 +DATA/primdb/onlinelog/group_3.473.773206427 ONLINE
3 +DATA/primdb/onlinelog/group_3.474.773206427 ONLINE
4 +DATA/primdb/onlinelog/group_4.475.773206429 ONLINE
4 +DATA/primdb/onlinelog/group_4.476.773206429 ONLINE
8 rows selected.
SQL> select group#,bytes from v$Log;
GROUP# BYTES
---------- ----------
1 5242880
2 5242880
3 5242880
4 5242880
SQL>
alter database add standby logfile thread 1
group 5 size 5M,
group 6 size 5M;
Database altered.
SQL> alter database add standby logfile thread 2
group 7 size 5M,
group 8 size 5M;
Database altered.
SQL> select group#,member ,type from v$Logfile order by 1;
GROUP# MEMBER TYPE
---------- -------------------------------------------------------- -------
1 +DATA/primdb/onlinelog/group_1.467.773206195 ONLINE
1 +DATA/primdb/onlinelog/group_1.468.773206197 ONLINE
2 +DATA/primdb/onlinelog/group_2.469.773206197 ONLINE
2 +DATA/primdb/onlinelog/group_2.470.773206197 ONLINE
3 +DATA/primdb/onlinelog/group_3.473.773206427 ONLINE
3 +DATA/primdb/onlinelog/group_3.474.773206427 ONLINE
4 +DATA/primdb/onlinelog/group_4.475.773206429 ONLINE
4 +DATA/primdb/onlinelog/group_4.476.773206429 ONLINE
5 +DATA/primdb/onlinelog/group_5.481.773209067 STANDBY
5 +DATA/primdb/onlinelog/group_5.482.773209067 STANDBY
6 +DATA/primdb/onlinelog/group_6.483.773209067 STANDBY
6 +DATA/primdb/onlinelog/group_6.484.773209069 STANDBY
7 +DATA/primdb/onlinelog/group_7.485.773209079 STANDBY
7 +DATA/primdb/onlinelog/group_7.486.773209079 STANDBY
8 +DATA/primdb/onlinelog/group_8.487.773209079 STANDBY
8 +DATA/primdb/onlinelog/group_8.488.773209081 STANDBY
16 rows selected.
create pfile for standby database
=====================================================================================
SQL> create pfile='$ORACLE_HOME/dbs/initstbydb.ora' from spfile;
File created.
SQL>
edit the required parameters
remove the red coloured lines for the standby database initstbydb.ora file
edit/change the blue coloured lines as per standby database required.
primdb2.__db_cache_size=520093696 primdb1.__db_cache_size=520093696 primdb2.__java_pool_size=16777216 primdb1.__java_pool_size=16777216 primdb2.__large_pool_size=16777216 primdb1.__large_pool_size=16777216 primdb2.__pga_aggregate_target=587202560 primdb1.__pga_aggregate_target=587202560 primdb2.__sga_target=872415232 primdb1.__sga_target=872415232 primdb2.__shared_io_pool_size=0 primdb1.__shared_io_pool_size=0 primdb2.__shared_pool_size=301989888 primdb1.__shared_pool_size=301989888 primdb2.__streams_pool_size=0 primdb1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/primdb/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.0.0' *.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/stbydb','+DATA/primdb' *.db_name='primdb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)' primdb1.fal_client='primdb1' primdb2.fal_client='primdb2' *.fal_server='stbydb' primdb2.instance_number=2 primdb1.instance_number=1 *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' *.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.memory_target=1449132032 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.sec_case_sensitive_logon=FALSE *.standby_file_management='auto' primdb1.thread=1 primdb2.thread=2 primdb2.undo_tablespace='UNDOTBS1' primdb1.undo_tablespace='UNDOTBS2'
*************************************************************************************
after editing the initstbydb.ora file
*************************************************************************************
db_cache_size=520093696 java_pool_size=16777216 large_pool_size=16777216 pga_aggregate_target=587202560 sga_target=872415232 shared_pool_size=301989888 streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/stbydb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+DATA/stbydb/controlfile/control01.ctl' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/primdb/','+DATA/stbydb/' *.db_name='primdb' db_unique_name='stbydb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)' fal_client='stbydb' *.fal_server='primdb' *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_2='SERVICE=primdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_3='LOCATION=+DATA/stbydb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.standby_file_management='auto' undo_tablespace='UNDOTBS1' SEC_CASE_SENSITIVE_LOGON=FALSE
================================================================================references:-
create required folder for stbydb database and also add an entry in the /etc/oratab for stbydb database ,
create a password file for stbydb database.(must have similar password like primary RAC database )
add required tns entries in the tnsnames.ora and listener entry in the listener.ora file
take a RMAN backup of target database and target database controlfile from any one of the rac node
and scp the backup to the standby database host machine to create standby database.
*************************************************************************************
[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$ . oraenv
ORACLE_SID = [oracle] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 04:37:01 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMDB (DBID=447762734)
RMAN> configure channel device type disk format '/u01/app/oracle/backup/%U';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/%U';
new RMAN configuration parameters are successfully stored
RMAN> backup as compressed backupset database plus archivelog;
RMAN> backup current controlfile for standby;
RMAN> backup archivelog all;
creating directories
=====================================================
[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/admin/primdb/adump
[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/backup
[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@dc2 dbs]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd data
ASMCMD> mkdir STBYDB
ASMCMD> cd STBYDB
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE STANDBYLOG TEMPFILE
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
STANDBYLOG/
TEMPFILE/
ASMCMD>
create password file for standby database.
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle entries=5 ignorecase=y
[oracle@dc2 dbs]$
copy the backup to standby server.
[oracle@dc1 backup]$ ls -ltr
total 290584
-rw-r----- 1 oracle oinstall 4157952 Jan 22 04:38 01n1cg5n_1_1
-rw-r----- 1 oracle oinstall 259842048 Jan 22 04:40 02n1cg5t_1_1
-rw-r----- 1 oracle oinstall 1114112 Jan 22 04:40 03n1cg9g_1_1
-rw-r----- 1 oracle oinstall 157184 Jan 22 04:40 04n1cg9u_1_1
-rw-r----- 1 oracle oinstall 18546688 Jan 22 04:46 05n1cglv_1_1
-rw-r----- 1 oracle oinstall 13416448 Jan 22 04:47 06n1cgn8_1_1
[oracle@dc1 backup]$ scp * oracle@dc2:/u01/app/oracle/backup/
01n1cg5n_1_1 100% 4061KB 4.0MB/s 00:00
02n1cg5t_1_1 100% 248MB 4.3MB/s 00:58
03n1cg9g_1_1 100% 1088KB 1.1MB/s 00:00
04n1cg9u_1_1 100% 154KB 153.5KB/s 00:00
05n1cglv_1_1 100% 18MB 2.0MB/s 00:09
06n1cgn8_1_1 100% 13MB 2.6MB/s 00:05
[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$
add an entry in the /etc/oratab file
vi /etc/oratab
stbydb:/u01/app/oracle/product/11.2.0/db_1:N
add tns entry in all nodes
example:-
[oracle@dc1 backup]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@dc1 admin]$ vi tnsnames.ora
PRIMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primdb)
)
)
STBYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbydb)
)
)
[oracle@dc1 admin]$ tnsping primdb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:33
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (40 msec)
[oracle@dc1 admin]$ tnsping stbydb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:37
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (0 msec)
[oracle@dc1 admin]$
[oracle@dc2 admin]$ tnsping primdb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:52
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (0 msec)
[oracle@dc2 admin]$ tnsping stbydb
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:56
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (10 msec)
[oracle@dc2 admin]$
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
start the standby database in nomount mode
[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ? stbydb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:00:18 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [stbydb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ rman target sys/oracle@PRIMDB auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 05:33:25 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMDB (DBID=447762734)
connected to auxiliary database: PRIMDB (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
*************************************************************************************
alert log file inforamtion
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Using STANDBY_ARCHIVE_DEST parameter default value as +DATA/stbydb/standbylog
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;
Sun Jan 22 05:52:53 2012
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:52:53 2012
MRP0 started with pid=29, OS id=5844
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: alter database recover managed standby database disconnect from session
Sun Jan 22 05:54:16 2012
alter database recover managed standby database cancel
Sun Jan 22 05:54:17 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Waiting for MRP0 pid 5844 to terminate
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (stbydb)
Managed Standby Recovery Canceled (stbydb)
Completed: alter database recover managed standby database cancel
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:56:07 2012
MRP0 started with pid=29, OS id=6135
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Completed: ALTER DATABASE MOUNT
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC1]: Error 16191 connecting to primdb for fetching gap sequence
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
ARC6: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
work around:-
======================================
create password file again on both rac node database and also on standby database
[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle ignorecase=y force=y entries=5
add the parameter on all databases
SEC_CASE_SENSITIVE_LOGON=FALSE
SQL> show parameter SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@dc2 dbs]$ vi initstbydb.ora
[oracle@dc2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:59:26 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 870457344 bytes
Fixed Size 1339964 bytes
Variable Size 335547844 bytes
Database Buffers 528482304 bytes
Redo Buffers 5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> show parameter SEC_CASE_SENSITIVE_LOGON=FALSE
SQL> show parameter SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 870457344 bytes
Fixed Size 1339964 bytes
Variable Size 335547844 bytes
Database Buffers 528482304 bytes
Redo Buffers 5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 37
Next log sequence to archive 0
Current log sequence 38
SQL>
verification
=============================================
(on primary database)
SQL> create tablespace new datafile size 2m;
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW
7 rows selected.
(on standby database)
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 0
Current log sequence 40
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
6 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
(on both RAC primary instances)
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 0
Current log sequence 40
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 39
Next log sequence to archive 0
Current log sequence 40
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW
7 rows selected.
SQL> select name,db_unique_name,database_role from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB stbydb PHYSICAL STANDBY
Thanks and Regards,
Rajesh Kumar Govindarajan.
http://www.oracledba.org/10g/dr/10gR2_dataguard_RAC_to_RAC.html
http://jhdba.wordpress.com/2008/04/11/how-not-to-set-up-remote-log-shipping-dataguard-and-streams/
http://www.dba-oracle.com/t_ora_16191_primary_log_shipping_client_not_logged_on_standby_tips.htm
3 comments:
Hi,
Can you please explain the background processes (GCS, GES, LMON, LMD, LCK0 etc) in brief and in a simple language.
hi swathi,
please refer the below links:-
http://dbaregistry.blogspot.in/2010/02/oracle-rac-interview-questions.html
http://www.datadisk.co.uk/html_docs/rac/architecture.htm
still you have doubts, please email me. grk.oracle@gmail.com
Thanks and Regards,
Rajesh Kumar Govindarajan
Hi ,
This is an excellent post. If interested I have my own blog on easyoradba.com
Post a Comment