Physical Standby database Creation, step by step
SQL> show parameters unique
NAME TYPE VALUE
Enable Forced Logging
In order to implement Standby Database we must enable 'Forced Logging'.
Once force logging is enabled than in that the event if a 'nologging' operation is done, force logging takes precedence and all operations are logged. It is better to enable force logging once the database is in mount stage, otherwise in open mode it may take long time
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
Create a Password File
A password file must be created on the Primary database and should be copied to the Standby database server. The sys password must be identical on both sites. This is
a key pre requisite in order to ship and apply archived logs from Primary to Standby.
[oracle@linux1 ~]$ cd $ORACLE_HOME/dbs
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /opt/prmy/datafiles/PRMY/redo03.log
RMAN> BACKUP ARCHIVELOG all format '/opt/rman_bkp_prmy/archive_bkp_%d_%U.bckp';
[oracle@linux1 rman_bkp_prmy]$ pwd
STBY.__db_cache_size=188743680
Physical
Standby database Creation, step by step
primary database name: PRMY on Linux1Enterprise
Linux4 server
Standby database name: STBY on Linux2Enterprise
Creating a Data Guard Physical Standby environment, General Review.
Manually setting up a Physical standby database is a simple task when all prerequisites and setup steps are carefully met and executed.
In this example
The Enviroment
2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
primary database name: PRMY on Linux1
Standby database name: STBY on Linux2
Creating a Data Guard Physical Standby environment, General Review.
Manually setting up a Physical standby database is a simple task when all prerequisites and setup steps are carefully met and executed.
In this example
The Enviroment
2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Mount point is on identical path on both servers Linux1 and Linux2
Implementation notes:
Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters
Having followed these steps to implement the Physical Standby you need to follow these steps:
1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly
Step by Step Implementation of a Physical Standby Environment
Primary Database Steps
SQL> archive log list;
Implementation notes:
Once you have your primary database up and running these are the steps to follow:
1. Enable Forced Logging
2. Create a Password File
3. Configure a Standby Redo Log
4. Enable Archiving
5. Set Primary Database Initialization Parameters
Having followed these steps to implement the Physical Standby you need to follow these steps:
1. Create a Control File for the Standby Database
2. Backup the Primary Database and transfer a copy to the Standby node.
3. Prepare an Initialization Parameter File for the Standby Database
4. Configure the listener and tnsnames to support the database on both nodes
5. Set Up the Environment to Support the Standby Database on the standby node.
6. Start the Physical Standby Database
7. Verify the Physical Standby Database Is Performing Properly
Step by Step Implementation of a Physical Standby Environment
Primary Database Steps
SQL> archive log list;
Database log
mode Archive Mode
Automatic
archival Enabled
Archive
destination /opt/prmy/archive
Oldest online
log sequence 1
Next log
sequence to archive 3
Current log
sequence 3
SQL> !
hostname
linux1.dbs.com
SQL>
select name from v$database;
NAME
NAME
---------
PRMY
SQL> select name from v$datafile;
NAME
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/prmy/datafiles/PRMY/system01.dbf
/opt/prmy/datafiles/PRMY/undotbs01.dbf
/opt/prmy/datafiles/PRMY/sysaux01.dbf
/opt/prmy/datafiles/PRMY/users01.dbf
/opt/prmy/datafiles/PRMY/example01.dbf
SQL> show parameters unique
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_unique_name string
PRMY
Enable Forced Logging
In order to implement Standby Database we must enable 'Forced Logging'.
Once force logging is enabled than in that the event if a 'nologging' operation is done, force logging takes precedence and all operations are logged. It is better to enable force logging once the database is in mount stage, otherwise in open mode it may take long time
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
Create a Password File
A password file must be created on the Primary database and should be copied to the Standby database server. The sys password must be identical on both sites. This is
a key pre requisite in order to ship and apply archived logs from Primary to Standby.
[oracle@linux1 ~]$ cd $ORACLE_HOME/dbs
[oracle@linux1
dbs]$ ls -lrt orapw*
-rw-r----- 1 oracle dba 1536 Feb 3 15:55 orapwPRMY
[oracle@linux1
dbs]$
[oracle@linux1
dbs]$orapwd file=orapwPRMY password=password force=y
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------
----- -----
SYS TRUE TRUE
Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size/more than the size of the online redo logs.
Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size/more than the size of the online redo logs.
SQL>
select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /opt/prmy/datafiles/PRMY/redo03.log
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /opt/prmy/datafiles/PRMY/redo03.log
2 ONLINE /opt/prmy/datafiles/PRMY/redo02.log
1 ONLINE /opt/prmy/datafiles/PRMY/redo01.log
SQL>
select bytes from v$log;
BYTES
BYTES
----------
52428800
52428800
52428800
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
' /opt/prmy/datafiles/PRMY/stbyredo4.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/opt/prmy/datafiles/PRMY/stbyredo4.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
' /opt/prmy/datafiles/PRMY/stbyredo4.log' size 50m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/opt/prmy/datafiles/PRMY/stbyredo4.log' size 50m;
Database
altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/opt/prmy/datafiles/PRMY/stbyredo5.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/opt/prmy/datafiles/PRMY/stbyredo5.log' size 50m;
Database
altered.
SQL> ALTER
DATABASE ADD STANDBY LOGFILE GROUP 6 '/opt/prmy/datafiles/PRMY/stbyredo6.log'
size 50m;
Database
altered.
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /opt/prmy/datafiles/PRMY/redo03.log
2 ONLINE /opt/prmy/datafiles/PRMY/redo02.log
1 ONLINE /opt/prmy/datafiles/PRMY/redo01.log
4 STANDBY /opt/prmy/datafiles/PRMY/stbyredo4.log
5 STANDBY /opt/prmy/datafiles/PRMY/stbyredo5.log
6 STANDBY /opt/prmy/datafiles/PRMY/stbyredo6.log
6 rows selected.
Enabling Primary Database Initialization Parameters
Make sure your database is running from spfile, if not create a spfile from pfile;
. As mentioned below the Primary database parameter file is configured so
that it can work as both ( as Primary or Standby).
SQL> show parameter spfile;
6 rows selected.
Enabling Primary Database Initialization Parameters
Make sure your database is running from spfile, if not create a spfile from pfile;
. As mentioned below the Primary database parameter file is configured so
that it can work as both ( as Primary or Standby).
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string /ora_home/ora_home10g/dbs/spfi
lePRMY.ora
SQL>
SQL> CREATE PFILE from spfile;
File created.
Edit the pfile to add the standby parameters, here shown highlighted:
PRMY.__db_cache_size=188743680
SQL> CREATE PFILE from spfile;
File created.
Edit the pfile to add the standby parameters, here shown highlighted:
PRMY.__db_cache_size=188743680
PRMY.__java_pool_size=4194304
PRMY.__large_pool_size=4194304
PRMY.__shared_pool_size=83886080
PRMY.__streams_pool_size=0
*.audit_file_dest='/ora_home/ora_home10g/admin/PRMY/adump'
*.background_dump_dest='/ora_home/ora_home10g/admin/PRMY/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/prmy/datafiles/PRMY/control01.ctl','/opt/prmy/datafiles/PRMY/control02.ctl','/opt/prmy/datafiles/PRMY/control03.ctl'
*.core_dump_dest='/ora_home/ora_home10g/admin/PRMY/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PRMY'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=PRMYXDB)'
*.job_queue_processes=10
##*.log_archive_dest_1='LOCATION=/opt/prmy/archive'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora_home/ora_home10g/admin/PRMY/udump'
#Standby realted
parameters------------------------------------------
*.db_unique_name='PRMY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMY,STBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/prmy/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRMY'
*.LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.fal_server=STBY
*.fal_client=PRMY
standby_file_management=auto
*.db_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/prmy/datafiles/STBY/'
*.log_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/prmy/datafiles/STBY/'
Once the new init parameter file is ready on PRMY database we need to create the spfile:
SQL> shut immediate;
Once the new init parameter file is ready on PRMY database we need to create the spfile:
SQL> shut immediate;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
SQL> startup nomount
pfile='/ora_home/ora_home10g/dbs/initPRMY.ora'
ORACLE
instance started.
Total System
Global Area 285212672 bytes
Fixed
Size 1267068 bytes
Variable
Size 92277380 bytes
Database
Buffers 188743680 bytes
Redo
Buffers 2924544 bytes
SQL>
create spfile from pfile;
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Enable Archiving if it is not enabled in our case it is already enabled
On 10g you can enable archive log mode by mounting the database and executing the archivelog command:
SQL> startup mount
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Enable Archiving if it is not enabled in our case it is already enabled
On 10g you can enable archive log mode by mounting the database and executing the archivelog command:
SQL> startup mount
ORACLE
instance started.
Total System
Global Area 285212672 bytes
Fixed
Size 1267068 bytes
Variable
Size 92277380 bytes
Database
Buffers 188743680 bytes
Redo
Buffers 2924544 bytes
Database
mounted.
SQL> alter
database archivelog;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database archivelog;
Database
altered.
SQL> Database log mode Archive Mode
SQL> Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination /opt/prmy/archive
Oldest online
log sequence 2
Next log
sequence to archive 4
Current log
sequence 4
SQL>
Standby Database Steps
rman backup of the primary database and controlfile by rman.
Create an RMAN backup which we will use later to create the standby:
[oracle@linux1 rman_bkp_prmy]$ pwd
Standby Database Steps
rman backup of the primary database and controlfile by rman.
Create an RMAN backup which we will use later to create the standby:
[oracle@linux1 rman_bkp_prmy]$ pwd
/opt/rman_bkp_prmy
[oracle@linux1
rman_bkp_prmy]$ rman target=/
Recovery
Manager: Release 10.2.0.4.0 - Production on Fri Feb 3 19:07:56 2012
Copyright (c)
1982, 2007, Oracle. All rights reserved.
connected to
target database: PRMY (DBID=722194705)
RMAN> backup full database format
'/opt/rman_bkp_prmy/%d_%U.bckp' plus archivelog format
'/opt/rman_bkp_prmy/%d_%U.bckp';
Starting
backup at 03-FEB-12
current log
archived
using target
database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=116 devtype=DISK
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input archive
log thread=1 sequence=2 recid=1 stamp=774294184
input archive
log thread=1 sequence=3 recid=2 stamp=774295879
input archive
log thread=1 sequence=4 recid=3 stamp=774299369
channel
ORA_DISK_1: starting piece 1 at 03-FEB-12
channel
ORA_DISK_1: finished piece 1 at 03-FEB-12
piece
handle=/opt/rman_bkp_prmy/PRMY_01n2dnnd_1_1.bckp tag=TAG20120203T190932
comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished
backup at 03-FEB-12
Starting
backup at 03-FEB-12
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting full datafile backupset
channel
ORA_DISK_1: specifying datafile(s) in backupset
input
datafile fno=00001 name=/opt/prmy/datafiles/PRMY/system01.dbf
input
datafile fno=00003 name=/opt/prmy/datafiles/PRMY/sysaux01.dbf
input
datafile fno=00005 name=/opt/prmy/datafiles/PRMY/example01.dbf
input
datafile fno=00002 name=/opt/prmy/datafiles/PRMY/undotbs01.dbf
input
datafile fno=00004 name=/opt/prmy/datafiles/PRMY/users01.dbf
channel
ORA_DISK_1: starting piece 1 at 03-FEB-12
channel
ORA_DISK_1: finished piece 1 at 03-FEB-12
piece
handle=/opt/rman_bkp_prmy/PRMY_02n2dnni_1_1.bckp tag=TAG20120203T190938
comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel
ORA_DISK_1: starting full datafile backupset
channel
ORA_DISK_1: specifying datafile(s) in backupset
including
current control file in backupset
including
current SPFILE in backupset
channel
ORA_DISK_1: starting piece 1 at 03-FEB-12
channel
ORA_DISK_1: finished piece 1 at 03-FEB-12
piece
handle=/opt/rman_bkp_prmy/PRMY_03n2dnp0_1_1.bckp tag=TAG20120203T190938
comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished
backup at 03-FEB-12
Starting
backup at 03-FEB-12
current log
archived
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input archive
log thread=1 sequence=5 recid=4 stamp=774299427
channel
ORA_DISK_1: starting piece 1 at 03-FEB-12
channel
ORA_DISK_1: finished piece 1 at 03-FEB-12
piece
handle=/opt/rman_bkp_prmy/PRMY_04n2dnp3_1_1.bckp tag=TAG20120203T191027
comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished
backup at 03-FEB-12
RMAN>
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY format '/opt/rman_bkp_prmy/stby.ctl' ;
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY format '/opt/rman_bkp_prmy/stby.ctl' ;
Starting
backup at 03-FEB-12
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1:
specifying datafile(s) in backupset
including
standby control file in backupset
channel
ORA_DISK_1: starting piece 1 at 03-FEB-12
channel
ORA_DISK_1: finished piece 1 at 03-FEB-12
piece
handle=/opt/rman_bkp_prmy/stby.ctl tag=TAG20120203T193143 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished
backup at 03-FEB-12
RMAN> BACKUP ARCHIVELOG all format '/opt/rman_bkp_prmy/archive_bkp_%d_%U.bckp';
Starting
backup at 03-FEB-12
current log
archived
using channel
ORA_DISK_1
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input archive
log thread=1 sequence=2 recid=1 stamp=774294184
input archive
log thread=1 sequence=3 recid=2 stamp=774295879
input archive
log thread=1 sequence=4 recid=3 stamp=774299369
input archive
log thread=1 sequence=5 recid=4 stamp=774299427
input archive
log thread=1 sequence=6 recid=5 stamp=774300886
input archive
log thread=1 sequence=7 recid=6 stamp=774301181
channel
ORA_DISK_1: starting piece 1 at 03-FEB-12
channel
ORA_DISK_1: finished piece 1 at 03-FEB-12
piece
handle=/opt/rman_bkp_prmy/archive_bkp_PRMY_08n2dpfu_1_1.bckp
tag=TAG20120203T193941 comment=NONE
channel ORA_DISK_1:
backup set complete, elapsed time: 00:00:02
Finished
backup at 03-FEB-12
[oracle@linux1 rman_bkp_prmy]$ pwd
/opt/rman_bkp_prmy
[oracle@linux1
rman_bkp_prmy]$ ls -lrt
total 634904
-rw-r----- 1 oracle dba
10055680 Feb 3 19:09
PRMY_01n2dnnd_1_1.bckp
-rw-r----- 1 oracle dba 613384192 Feb 3 19:10 PRMY_02n2dnni_1_1.bckp
-rw-r----- 1 oracle dba
7143424 Feb 3 19:10
PRMY_03n2dnp0_1_1.bckp
-rw-r----- 1 oracle dba 3072 Feb
3 19:10 PRMY_04n2dnp3_1_1.bckp
-rw-r----- 1 oracle dba
7110656 Feb 3 19:31 stby.ctl
-rw-r----- 1 oracle dba
11776512 Feb 3 19:39
archive_bkp_PRMY_08n2dpfu_1_1.bckp
[oracle@linux1
rman_bkp_prmy]$ create same directory structure on linux2
As below
[oracle@linux2
~]$ mkdir -p /opt/rman_bkp_prmy
[oracle@linux2
~]$
Now use scp
command to copy backupset from linux1 to linux2 server as below
[oracle@linux1
rman_bkp_prmy]$
Cd /opt/rman_bkp_prmy
[oracle@linux1
rman_bkp_prmy]$ scp * oracle@linux2:/opt/rman_bkp_prmy
oracle@linux2's
password:
archive_bkp_PRMY_08n2dpfu_1_1.bckp
100% 11MB 5.6MB/s
00:02
PRMY_01n2dnnd_1_1.bckp
100% 9820KB 4.8MB/s
00:02
PRMY_02n2dnni_1_1.bckp
100% 585MB 7.6MB/s
01:17
PRMY_03n2dnp0_1_1.bckp
100% 6976KB 6.8MB/s 00:00
PRMY_04n2dnp3_1_1.bckp
100% 3072 3.0KB/s 00:00
stby.ctl
100% 6944KB 3.4MB/s 00:02
[oracle@linux1
rman_bkp_prmy]$0% 1315KB 1.3MB/s 00:01
Note :
On the standby node create the required
directories to get the datafiles
mkdir -p
/ora_home/ora_home10g/admin/STBY/adump
mkdir -p
/ora_home/ora_home10g/admin/STBY/bdump
mkdir -p
/opt/stby/datafiles/STBY/
mkdir -p
/ora_home/ora_home10g/admin/STBY/cdump
mkdir -p
/opt/STBY/archive
mkdir -p
/opt/stby/datafiles/STBY/
mkdir –p /ora_home/ora_home10g/admin/STBY/udump
Prepare an Initialization Parameter File for the Standby Database
Copy from the primary pfile to the standby destination
Prepare an Initialization Parameter File for the Standby Database
Copy from the primary pfile to the standby destination
Copy and edit
the primary init.ora to set it up for the standby role,as here shown below highlighted:
STBY.__db_cache_size=188743680
STBY.__java_pool_size=4194304
STBY.__large_pool_size=4194304
STBY.__shared_pool_size=83886080
STBY.__streams_pool_size=0
*.audit_file_dest='/ora_home/ora_home10g/admin/STBY/adump'
*.background_dump_dest='/ora_home/ora_home10g/admin/STBY/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/stby/datafiles/STBY/control01.ctl','/opt/stby/datafiles/STBY/control02.ctl','/opt/stby/datafiles/STBY/control03.ctl'
*.core_dump_dest='/ora_home/ora_home10g/admin/STBY/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PRMY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STBYXDB)'
*.job_queue_processes=10
##*.log_archive_dest_1='LOCATION=/opt/STBY/archive'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora_home/ora_home10g/admin/STBY/udump'
*.db_unique_name='STBY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RPMY,STBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/STBY/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY'
*.LOG_ARCHIVE_DEST_2='SERVICE=PRMY LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRMY'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
#Standby role parameters------------------------------------------
*.fal_server=PRMY
*.fal_client=STBY
*.standby_file_management=auto
*.db_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/stby/datafiles/STBY/'
*.log_file_name_convert='/opt/prmy/datafiles/PRMY/','/opt/stby/datafiles/STBY/'
Configure the
listener and tnsnames to support the database on both linux1 and linux2
[oracle@linux1
admin]$ cat tnsnames.ora
PRMY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.10.50)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRMY)
)
)
STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.10.52)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STBY)
)
)
[oracle@linux1
admin]$
Start
the listener and check tnsping on both servers to both PRMY and STBY
# [oracle@linux1
admin]$
[oracle@linux1
admin]$ lsnrctl status
LSNRCTL for
Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:05:15
Copyright (c)
1991, 2007, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.50)(PORT=1521)))
STATUS of the
LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version
10.2.0.4.0 - Production
Start
Date 03-FEB-2012 20:02:30
Uptime 0 days 0 hr. 2 min. 44 sec
Trace
Level off
Security ON: Local OS Authentication
SNMP OFF
Listener
Parameter File
/ora_home/ora_home10g/network/admin/listener.ora
Listener Log
File
/ora_home/ora_home10g/network/log/listener.log
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.50)(PORT=1521)))
Services
Summary...
Service
"PRMY" has 1 instance(s).
Instance "PRMY", status UNKNOWN,
has 1 handler(s) for this service...
The command
completed successfully
[oracle@linux1
admin]$
[oracle@linux1 admin]$ tnsping PRMY
[oracle@linux1 admin]$ tnsping PRMY
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production
on 03-FEB-2012 20:05:45
Copyright (c)
1997, 2007, Oracle. All rights reserved.
Used
parameter files:
Used TNSNAMES
adapter to resolve the alias
Attempting to
contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.10.50)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRMY)))
OK (10 msec)
[oracle@linux1
admin]$
[oracle@linux1
admin]$ tnsping STBY
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production
on 03-FEB-2012 20:06:19
Copyright (c)
1997, 2007, Oracle. All rights reserved.
Used
parameter files:
Used TNSNAMES
adapter to resolve the alias
Attempting to
contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.10.52)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STBY)))
OK (40 msec)
[oracle@linux1
admin]$
#on linux2 server
[oracle@linux2 admin]$ lsnrctl status
#on linux2 server
[oracle@linux2 admin]$ lsnrctl status
LSNRCTL for
Linux: Version 10.2.0.4.0 - Production on 03-FEB-2012 20:02:37
Copyright (c)
1991, 2007, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.52)(PORT=1521)))
STATUS of the
LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version
10.2.0.4.0 - Production
Start
Date 03-FEB-2012 20:02:07
Uptime 0 days 0 hr. 0 min. 29 sec
Trace
Level off
Security ON: Local OS Authentication
SNMP OFF
Listener
Parameter File
/ora_home/ora_home10g/network/admin/listener.ora
Listener Log
File
/ora_home/ora_home10g/network/log/listener.log
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.52)(PORT=1521)))
Services
Summary...
Service
"STBY" has 1 instance(s).
Instance "STBY", status UNKNOWN,
has 1 handler(s) for this service...
The command
completed successfully
[oracle@linux2
admin]$
[oracle@linux2
admin]$ tnsping PRMY
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production
on 03-FEB-2012 20:02:59
Copyright (c)
1997, 2007, Oracle. All rights reserved.
Used
parameter files:
Used TNSNAMES
adapter to resolve the alias
Attempting to
contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.10.50)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRMY)))
OK (0 msec)
[oracle@linux2
admin]$
[oracle@linux2
admin]$ tnsping STBY
TNS
Ping Utility for Linux: Version 10.2.0.4.0 -
Production on 03-FEB-2012 20:03:19
Copyright
(c) 1997, 2007, Oracle. All rights reserved.
Used
parameter files:
Used
TNSNAMES adapter to resolve the alias
Attempting
to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
10.10.10.52)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STBY)))
OK
(10 msec)
[oracle@linux2
admin]$
Set
Up the Environment to Support the Standby Database on the standby node.
[oracle@linux1
dbs]$ scp orapwPRMY
oracle@linux2:/ora_home/ora_home10g
oracle@linux2's
password:
orapwPRMY
100% 1536 1.5KB/s 00:00
[oracle@linux1
dbs]$
[oracle@linux1
dbs]$ scp orapwPRMY oracle@linux2:/ora_home/ora_home10g/dbs
oracle@linux2's
password:
orapwPRMY
100% 1536 1.5KB/s 00:00
[oracle@linux1
dbs]$
Login to
linux2
[oracle@linux2
dbs]$ ls -lrt
total 40
-rw-r----- 1 oracle dba
8385 Sep 11 1998 init.ora
-rw-r--r-- 1 oracle dba 12920 May 3 2001
initdw.ora
-rw-r--r-- 1 oracle dba
1665 Feb 3 19:50 initSTBY.ora
-rw-rw---- 1 oracle dba
1544 Feb 3 19:51 hc_STBY.dat
-rw-r----- 1 oracle dba
1536 Feb 3 20:07 orapwPRMY
[oracle@linux2
dbs]$ mv orapwPRMY orapwSTBY
[oracle@linux2 dbs]$ ls -lrt orapwSTBY
-rw-r----- 1 oracle dba 1536 Feb 3 20:07 orapwSTBY
[oracle@linux2
dbs]$
insert the entry in oratab file on linux2 server for standby database name
Startup the Standby database in nomount stage
insert the entry in oratab file on linux2 server for standby database name
Startup the Standby database in nomount stage
[oracle@linux2
dbs]$ echo $ORACLE_SID
STBY
[oracle@linux2
dbs]$
SQL> startup nomount;
ORACLE
instance started.
Total System
Global Area 285212672 bytes
Fixed
Size 1267068 bytes
Variable
Size 92277380 bytes
Database
Buffers 188743680 bytes
Redo
Buffers 2924544 bytes
SQL>
SQL>
SQL> exit
Disconnected
from Oracle Database 10g Enterprise
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Create the standby database using rman:
[oracle@linux2 dbs]$ rman target=sys/password@PRMY auxiliary=/
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Create the standby database using rman:
[oracle@linux2 dbs]$ rman target=sys/password@PRMY auxiliary=/
Recovery
Manager: Release 10.2.0.4.0 - Production on Fri Feb 3 20:16:41 2012
Copyright (c)
1982, 2007, Oracle. All rights reserved.
connected to
target database: PRMY (DBID=722194705)
connected to
auxiliary database: PRMY (not mounted)
RMAN>RMAN>
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
Start the redo apply:
Start the redo apply:
Starting
Duplicate Db at 03-FEB-12
using target
database control file instead of recovery catalog
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of
Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby
database';
}
executing
Memory Script
Starting
restore at 03-FEB-12
using channel
ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: starting datafile backupset restore
channel
ORA_AUX_DISK_1: restoring control file
channel
ORA_AUX_DISK_1: reading from backup piece /opt/rman_bkp_prmy/stby.ctl
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/opt/rman_bkp_prmy/stby.ctl tag=TAG20120203T193143
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output
filename=/opt/stby/datafiles/STBY/control01.ctl
output
filename=/opt/stby/datafiles/STBY/control02.ctl
output
filename=/opt/stby/datafiles/STBY/control03.ctl
Finished
restore at 03-FEB-12
sql statement:
alter database mount standby database
released
channel: ORA_AUX_DISK_1
contents of
Memory Script:
{
set newname for tempfile 1 to
"/opt/stby/datafiles/STBY/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/opt/stby/datafiles/STBY/system01.dbf";
set newname for datafile 2 to
"/opt/stby/datafiles/STBY/undotbs01.dbf";
set newname for datafile 3 to
"/opt/stby/datafiles/STBY/sysaux01.dbf";
set newname for datafile 4 to
"/opt/stby/datafiles/STBY/users01.dbf";
set newname for datafile 5 to
"/opt/stby/datafiles/STBY/example01.dbf";
restore
check readonly
clone database
;
}
executing
Memory Script
executing
command: SET NEWNAME
renamed
temporary file 1 to /opt/stby/datafiles/STBY/temp01.dbf in control file
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
Starting
restore at 03-FEB-12
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: sid=156 devtype=DISK
channel
ORA_AUX_DISK_1: starting datafile backupset restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring
datafile 00001 to /opt/stby/datafiles/STBY/system01.dbf
restoring
datafile 00002 to /opt/stby/datafiles/STBY/undotbs01.dbf
restoring
datafile 00003 to /opt/stby/datafiles/STBY/sysaux01.dbf
restoring
datafile 00004 to /opt/stby/datafiles/STBY/users01.dbf
restoring
datafile 00005 to /opt/stby/datafiles/STBY/example01.dbf
channel
ORA_AUX_DISK_1: reading from backup piece
/opt/rman_bkp_prmy/PRMY_02n2dnni_1_1.bckp
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/opt/rman_bkp_prmy/PRMY_02n2dnni_1_1.bckp tag=TAG20120203T190938
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished
restore at 03-FEB-12
contents of
Memory Script:
{
switch clone datafile all;
}
executing
Memory Script
datafile 1
switched to datafile copy
input datafile
copy recid=7 stamp=774303476 filename=/opt/stby/datafiles/STBY/system01.dbf
datafile 2
switched to datafile copy
input datafile
copy recid=8 stamp=774303476 filename=/opt/stby/datafiles/STBY/undotbs01.dbf
datafile 3
switched to datafile copy
input datafile
copy recid=9 stamp=774303476 filename=/opt/stby/datafiles/STBY/sysaux01.dbf
datafile 4
switched to datafile copy
input datafile
copy recid=10 stamp=774303476 filename=/opt/stby/datafiles/STBY/users01.dbf
datafile 5
switched to datafile copy
input datafile
copy recid=11 stamp=774303476 filename=/opt/stby/datafiles/STBY/example01.dbf
Finished
Duplicate Db at 03-FEB-12
RMAN>
switch archive log on primry as below 20 times
On the Primary:
switch archive log on primry as below 20 times
On the Primary:
SQL> alter
system switch logfile;
SQL> select
name from v$database;
NAME
---------
PRMY
SQL> select
'PRIMARY SEQ IS ' ||sequence# ||chr(10), 'PRIMARY FIRST_TIME IS ' ||
to_char(first_time,'mm/dd/yy hh24:mi:ss')
from v$log_history
where sequence#=(select max(sequence#) from
v$log_history); 2 3
'PRIMARYSEQIS'||SEQUENCE#||CHR(10)
--------------------------------------------------------
'PRIMARYFIRST_TIMEIS'||TO_CHAR(FIRST_TI
---------------------------------------
PRIMARY SEQ IS
27
PRIMARY
FIRST_TIME IS 02/03/12 20:25:39
SQL>
On the Standby database Linux2 (STBY):
SQL>
archive log list;
Database log
mode Archive Mode
Automatic
archival Enabled
Archive
destination /opt/STBY/archive
Oldest online
log sequence 4
Next log
sequence to archive 6
Current log
sequence 6
SQL>
SQL> select
'STANDBY SEQ IS ' ||sequence# ||chr(10), 'STANDBY FIRST_TIME IS ' ||
to_char(first_time,'mm/dd/yy hh24:mi:ss')
from v$log_history
where sequence#=(select max(sequence#) from
v$log_history); 2 3
'STANDBYSEQIS'||SEQUENCE#||CHR(10)
--------------------------------------------------------
'STANDBYFIRST_TIMEIS'||TO_CHAR(FIRST_TI
---------------------------------------
STANDBY SEQ IS
5
STANDBY
FIRST_TIME IS 02/03/12 19:09:29
Stop the
managed recovery process on the standby:
Sqlplus >ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Sqlplus >
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT from session;
I appreciate you sharing this article. Really thank you! Much obliged.
ReplyDeleteThis is one awesome blog article. Much thanks again.
sap online training
software online training
sap sd online training
hadoop online training
sap-crm-online-training
I really enjoy the blog.Much thanks again. Really Great.
ReplyDeleteVery informative article post. Really looking forward to read more. Will read on…
oracle online training
sap fico online training
dotnet online training
qa-qtp-software-testing-training-tutorial