Creating a physical standby database on Oracle 12.1.0.2 :-
OS Version:-Red Hat Enterprise Linux Server release 6.4
Oracle version:- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit
System Configuration
primary server:-
edit the following files
vi /etc/hosts
192.168.2.100 server3.soumya.com server3
192.168.2.101 server4.soumya.com server4
:wq
vi /etc/sysconfig/network
HOSTNAME=server3.soumya.com
:wq
vi /etc/sysconfig/network-scripts/ifcfg-eth0
NETMASK=255.255.255.0
IPADDR=192.168.2.100
GATEWAY=192.168.2.1
:wq
Standby Server:-
edit the following files
vi /etc/hosts
192.168.2.100 server3.soumya.com server3
192.168.2.101 server4.soumya.com server4
:wq
vi /etc/sysconfig/network
HOSTNAME=server4.soumya.com
:wq
vi /etc/sysconfig/network-scripts/ifcfg-eth0
NETMASK=255.255.255.0
IPADDR=192.168.2.101
GATEWAY=192.168.2.1
:wq
In both server-- # service network restart
# service NetworkManager restart
# service iptables stop
# chkconfig iptables off
#vi /etc/selinux/config and disable selinux .
After changing inside the file please restart the server.
We have a database called ora12c on primary server and we will install only oracle binaries on
standby server(192.168.2.101).No database should be running in standby server.
Primary database:- ORA12C
Standby database:- ORASTD
Oracle Net configuration:-
Listener.ora configuration for primary database (ORA12C) in primary server (192.168.2.100)
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORA12C)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = ORA12C)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server3.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
:wq
Listener.ora configuration for standby database (ORASTD) in standby server (192.168.2.101)
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORASTD)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = ORASTD)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server4.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
:wq
On both server(192.168.2.100 & 192.168.2.101), add Oracle Net aliases for both databases and aux alias for RMAN DUPLICATE in
tnsnames.ora:
vi $ORACLE_HOME/network/admin/tnsnames.ora
ORA12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server3.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12C)
)
)
ORASTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server4.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORASTD)
)
)
:wq
Now we will have to start listener in primary and standby server:-
$ lsnrctl start
Check "tnsping ORA12C" & "tnsping ORASTD" in both server. If output of both command is
coming as "ok" that means oracle net service has been added successfully.
In primary database:-
Put the database in archivelog mode.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 2927528 bytes
Variable Size 511706200 bytes
Database Buffers 109051904 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database archive log;
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 2
Next log sequence to archive 4
Current log sequence 4
SQL> alter database open;
In Primary Database:-
Enable force logging The FORCE LOGGING option to ensure that all the changes made in the database
will be captured and available for recovery in the redo logs.
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
We have to enable remote login by setting remote_login_passwordfile to exclusive in password file
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
Create a password file
$ orapwd file=$ORACLE_HOME/dbs/orapwORA12C password=redhat
$ cd $ORACLE_HOME/dbs/
$ chmod 775 orapwORA12C
$ cp orapwORA12C orapwORASTD
Now transfer the password file into standby server using scp command
$ scp orapwORASTD oracle@192.168.2.101:/u01/app/oracle/product/12.1.0.2/db_1/dbs/
Configure standby redo log file with the same size of redo log file in ORA12C database
SYS@ORA12C> select group#, members , bytes /1024 /1024 from v$log;
GROUP# MEMBERS BYTES/1024/1024
---------- ---------- ---------------
1 1 50
2 1 50
3 1 50
SYS@ORA12C> select group# , member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA12C/redo03.log
2 /u01/app/oracle/oradata/ORA12C/redo02.log
1 /u01/app/oracle/oradata/ORA12C/redo01.log
Now adding redolog files :-
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/ORA12C/redos4A.log' size 50M;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/ORA12C/redos5A.log' size 50M;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/ORA12C/redos6A.log' size 50M;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/ORA12C/redos7A.log' size 50M;
SQL> select group# , member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA12C/redo03.log
2 /u01/app/oracle/oradata/ORA12C/redo02.log
1 /u01/app/oracle/oradata/ORA12C/redo01.log
4 /u01/app/oracle/oradata/ORA12C/redos4A.log
5 /u01/app/oracle/oradata/ORA12C/redos5A.log
6 /u01/app/oracle/oradata/ORA12C/redos6A.log
7 /u01/app/oracle/oradata/ORA12C/redos7A.log
Add following parameter in pfile for primary database.
create pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora' from spfile;
vi /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora
*.db_name='ORA12C'
*.db_unique_name='ORA12C'
*.log_archive_config='DG_CONFIG=(ORA12C, ORASTD)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA12C'
*.log_archive_dest_2='SERVICE=ORASTD LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORASTD'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=ORASTD
*.FAL_CLIENT=ORA12C
*.db_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.standby_file_management=auto
:wq
SQL> shut immediate;
SQL> startup mount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora';
Create a standby controlfile.
sql>alter database create standby controlfile as '/u01/app/oracle/oradata/ORA12C/ORASTD.ctl';
Create parameter file for standby database :
$cp /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora
$ vi /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora
ORASTD.__data_transfer_cache_size=0
ORASTD.__db_cache_size=197132288
ORASTD.__java_pool_size=4194304
ORASTD.__large_pool_size=8388608
ORASTD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORASTD.__pga_aggregate_target=251658240
ORASTD.__sga_target=377487360
ORASTD.__shared_io_pool_size=8388608
ORASTD.__shared_pool_size=150994944
ORASTD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORASTD/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/ORASTD/ORASTD.ctl','/u01/app/oracle/fast_recovery_area/ORASTD/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORASTDXDB)'
*.memory_target=600m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_name='ORA12C'
*.db_unique_name='ORASTD'
*.log_archive_config='DG_CONFIG=(ORA12C, ORASTD)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORASTD'
*.log_archive_dest_2='SERVICE=ORASTD LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12C'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=ORA12C
*.FAL_CLIENT=ORASTD
*.db_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.standby_file_management=auto
:wq
In standby server :-
Create the necessary directory structure:-
$ mkdir -p /u01/app/oracle/oradata/ORASTD/
$ mkdir -p /u01/app/oracle/admin/ORASTD/adump
$ mkdir -p /u01/app/oracle/oradata/ORASTD
$ mkdir -p /u01/app/oracle/fast_recovery_area/ORASTD/
$ chmod 775 -Rf /u01
Copy all data files, all redo log files and standby redo log file, standby controlfile, pfile from ORA12C to ORASTD database.
$ cd /u01/app/oracle/oradata/ORA12C
$ ll
-rw-r----- 1 oracle oinstall 10043392 Apr 16 09:57 control01.ctl
-rw-r----- 1 oracle oinstall 10043392 Apr 16 09:47 ORASTD.ctl
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:59 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:41 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:41 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos4A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos5A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos6A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos7A.log
-rw-r----- 1 oracle oinstall 629153792 Apr 15 12:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 817897472 Apr 15 12:59 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 15 12:42 temp01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 15 12:59 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Apr 15 12:59 users01.dbf
$ scp *.log oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
redo01.log 100% 50MB 25.0MB/s 00:02
redo02.log 100% 50MB 50.0MB/s 00:00
redo03.log 100% 50MB 25.0MB/s 00:02
redos4A.log 100% 50MB 50.0MB/s 00:01
redos5A.log 100% 50MB 50.0MB/s 00:01
redos6A.log 100% 50MB 50.0MB/s 00:01
redos7A.log 100% 50MB 50.0MB/s 00:01
$ scp *.dbf oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
sysaux01.dbf 100% 600MB 35.3MB/s 00:17
system01.dbf 100% 780MB 39.0MB/s 00:20
temp01.dbf 100% 60MB 60.0MB/s 00:01
undotbs01.dbf 100% 60MB 30.0MB/s 00:02
users01.dbf 100% 5128KB 5.0MB/s 00:00
$ scp ORASTD.ctl oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
ORASTD.ctl 100% 9808KB 9.6MB/s 00:00
Now copy the pfile
$ cd $ORACLE_HOME/dbs
$ scp initORASTD.ora oracle@192.168.2.101:/u01/app/oracle/product/12.1.0.2/db_1/dbs/
In standby server:-
copy the controlfile into 2nd location and rename it as control02.ctl
$ cd /u01/app/oracle/oradata/ORASTD
$ cp ORASTD.ctl /u01/app/oracle/fast_recovery_area/ORASTD/
$ cd /u01/app/oracle/fast_recovery_area/ORASTD/
$ mv ORASTD.ctl control02.ctl
In standby server:-
Setup .bash_profile for standby database
$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export TMP=/u01/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=server4.soumya.com
export ORACLE_UNQNAME=ORASTD
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=ORASTD
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
:wq
$ . .bash_profile
In primary database:-
Open the primary database
SQL> alter database open;
In standby database:-
Now start the standby database in mount stage
$ sqlplus / as sysdba
SQL> startup mount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora';
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2293832 bytes
Variable Size 595595192 bytes
Database Buffers 230686720 bytes
Redo Buffers 2355200 bytes
Database mounted.
SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora';
Start Physical Standby Database :
SQL> alter database recover managed standby database disconnect from session;
In standby database :
SQL>select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
Also check the above query in primary database also to confirm the archive log no:-
In Primary database:-
SQL> select sequence#, first_time, next_time,dest_id, thread# , standby_dest, applied from v$archived_log order by sequence#;
In primary database :
sql>alter system switch logfile;
Note: if archive logfile is coming to prodcws database, that means configuration is correct till now.
Now for real time apply:-
Steps :
# In Standby Database
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
select open_mode from v$database; (Output : read only with apply)
After commit on primary, it will directly apply on standby database.
+++++ END +++++
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
OS Version:-Red Hat Enterprise Linux Server release 6.4
Oracle version:- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit
System Configuration
primary server:-
edit the following files
vi /etc/hosts
192.168.2.100 server3.soumya.com server3
192.168.2.101 server4.soumya.com server4
:wq
vi /etc/sysconfig/network
HOSTNAME=server3.soumya.com
:wq
vi /etc/sysconfig/network-scripts/ifcfg-eth0
NETMASK=255.255.255.0
IPADDR=192.168.2.100
GATEWAY=192.168.2.1
:wq
Standby Server:-
edit the following files
vi /etc/hosts
192.168.2.100 server3.soumya.com server3
192.168.2.101 server4.soumya.com server4
:wq
vi /etc/sysconfig/network
HOSTNAME=server4.soumya.com
:wq
vi /etc/sysconfig/network-scripts/ifcfg-eth0
NETMASK=255.255.255.0
IPADDR=192.168.2.101
GATEWAY=192.168.2.1
:wq
In both server-- # service network restart
# service NetworkManager restart
# service iptables stop
# chkconfig iptables off
#vi /etc/selinux/config and disable selinux .
After changing inside the file please restart the server.
We have a database called ora12c on primary server and we will install only oracle binaries on
standby server(192.168.2.101).No database should be running in standby server.
Primary database:- ORA12C
Standby database:- ORASTD
Oracle Net configuration:-
Listener.ora configuration for primary database (ORA12C) in primary server (192.168.2.100)
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORA12C)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = ORA12C)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server3.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
:wq
Listener.ora configuration for standby database (ORASTD) in standby server (192.168.2.101)
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORASTD)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = ORASTD)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server4.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
:wq
On both server(192.168.2.100 & 192.168.2.101), add Oracle Net aliases for both databases and aux alias for RMAN DUPLICATE in
tnsnames.ora:
vi $ORACLE_HOME/network/admin/tnsnames.ora
ORA12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server3.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12C)
)
)
ORASTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server4.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORASTD)
)
)
:wq
Now we will have to start listener in primary and standby server:-
$ lsnrctl start
Check "tnsping ORA12C" & "tnsping ORASTD" in both server. If output of both command is
coming as "ok" that means oracle net service has been added successfully.
In primary database:-
Put the database in archivelog mode.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 2927528 bytes
Variable Size 511706200 bytes
Database Buffers 109051904 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database archive log;
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 2
Next log sequence to archive 4
Current log sequence 4
SQL> alter database open;
In Primary Database:-
Enable force logging The FORCE LOGGING option to ensure that all the changes made in the database
will be captured and available for recovery in the redo logs.
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
We have to enable remote login by setting remote_login_passwordfile to exclusive in password file
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
Create a password file
$ orapwd file=$ORACLE_HOME/dbs/orapwORA12C password=redhat
$ cd $ORACLE_HOME/dbs/
$ chmod 775 orapwORA12C
$ cp orapwORA12C orapwORASTD
Now transfer the password file into standby server using scp command
$ scp orapwORASTD oracle@192.168.2.101:/u01/app/oracle/product/12.1.0.2/db_1/dbs/
Configure standby redo log file with the same size of redo log file in ORA12C database
SYS@ORA12C> select group#, members , bytes /1024 /1024 from v$log;
GROUP# MEMBERS BYTES/1024/1024
---------- ---------- ---------------
1 1 50
2 1 50
3 1 50
SYS@ORA12C> select group# , member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA12C/redo03.log
2 /u01/app/oracle/oradata/ORA12C/redo02.log
1 /u01/app/oracle/oradata/ORA12C/redo01.log
Now adding redolog files :-
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/ORA12C/redos4A.log' size 50M;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/ORA12C/redos5A.log' size 50M;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/ORA12C/redos6A.log' size 50M;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/ORA12C/redos7A.log' size 50M;
SQL> select group# , member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA12C/redo03.log
2 /u01/app/oracle/oradata/ORA12C/redo02.log
1 /u01/app/oracle/oradata/ORA12C/redo01.log
4 /u01/app/oracle/oradata/ORA12C/redos4A.log
5 /u01/app/oracle/oradata/ORA12C/redos5A.log
6 /u01/app/oracle/oradata/ORA12C/redos6A.log
7 /u01/app/oracle/oradata/ORA12C/redos7A.log
Add following parameter in pfile for primary database.
create pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora' from spfile;
vi /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora
*.db_name='ORA12C'
*.db_unique_name='ORA12C'
*.log_archive_config='DG_CONFIG=(ORA12C, ORASTD)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA12C'
*.log_archive_dest_2='SERVICE=ORASTD LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORASTD'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=ORASTD
*.FAL_CLIENT=ORA12C
*.db_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.standby_file_management=auto
:wq
SQL> shut immediate;
SQL> startup mount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora';
Create a standby controlfile.
sql>alter database create standby controlfile as '/u01/app/oracle/oradata/ORA12C/ORASTD.ctl';
Create parameter file for standby database :
$cp /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora
$ vi /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora
ORASTD.__data_transfer_cache_size=0
ORASTD.__db_cache_size=197132288
ORASTD.__java_pool_size=4194304
ORASTD.__large_pool_size=8388608
ORASTD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORASTD.__pga_aggregate_target=251658240
ORASTD.__sga_target=377487360
ORASTD.__shared_io_pool_size=8388608
ORASTD.__shared_pool_size=150994944
ORASTD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORASTD/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/ORASTD/ORASTD.ctl','/u01/app/oracle/fast_recovery_area/ORASTD/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORASTDXDB)'
*.memory_target=600m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_name='ORA12C'
*.db_unique_name='ORASTD'
*.log_archive_config='DG_CONFIG=(ORA12C, ORASTD)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORASTD'
*.log_archive_dest_2='SERVICE=ORASTD LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12C'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=ORA12C
*.FAL_CLIENT=ORASTD
*.db_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.standby_file_management=auto
:wq
In standby server :-
Create the necessary directory structure:-
$ mkdir -p /u01/app/oracle/oradata/ORASTD/
$ mkdir -p /u01/app/oracle/admin/ORASTD/adump
$ mkdir -p /u01/app/oracle/oradata/ORASTD
$ mkdir -p /u01/app/oracle/fast_recovery_area/ORASTD/
$ chmod 775 -Rf /u01
Copy all data files, all redo log files and standby redo log file, standby controlfile, pfile from ORA12C to ORASTD database.
$ cd /u01/app/oracle/oradata/ORA12C
$ ll
-rw-r----- 1 oracle oinstall 10043392 Apr 16 09:57 control01.ctl
-rw-r----- 1 oracle oinstall 10043392 Apr 16 09:47 ORASTD.ctl
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:59 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:41 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:41 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos4A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos5A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos6A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos7A.log
-rw-r----- 1 oracle oinstall 629153792 Apr 15 12:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 817897472 Apr 15 12:59 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 15 12:42 temp01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 15 12:59 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Apr 15 12:59 users01.dbf
$ scp *.log oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
redo01.log 100% 50MB 25.0MB/s 00:02
redo02.log 100% 50MB 50.0MB/s 00:00
redo03.log 100% 50MB 25.0MB/s 00:02
redos4A.log 100% 50MB 50.0MB/s 00:01
redos5A.log 100% 50MB 50.0MB/s 00:01
redos6A.log 100% 50MB 50.0MB/s 00:01
redos7A.log 100% 50MB 50.0MB/s 00:01
$ scp *.dbf oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
sysaux01.dbf 100% 600MB 35.3MB/s 00:17
system01.dbf 100% 780MB 39.0MB/s 00:20
temp01.dbf 100% 60MB 60.0MB/s 00:01
undotbs01.dbf 100% 60MB 30.0MB/s 00:02
users01.dbf 100% 5128KB 5.0MB/s 00:00
$ scp ORASTD.ctl oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
ORASTD.ctl 100% 9808KB 9.6MB/s 00:00
Now copy the pfile
$ cd $ORACLE_HOME/dbs
$ scp initORASTD.ora oracle@192.168.2.101:/u01/app/oracle/product/12.1.0.2/db_1/dbs/
In standby server:-
copy the controlfile into 2nd location and rename it as control02.ctl
$ cd /u01/app/oracle/oradata/ORASTD
$ cp ORASTD.ctl /u01/app/oracle/fast_recovery_area/ORASTD/
$ cd /u01/app/oracle/fast_recovery_area/ORASTD/
$ mv ORASTD.ctl control02.ctl
In standby server:-
Setup .bash_profile for standby database
$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export TMP=/u01/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=server4.soumya.com
export ORACLE_UNQNAME=ORASTD
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=ORASTD
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
:wq
$ . .bash_profile
In primary database:-
Open the primary database
SQL> alter database open;
In standby database:-
Now start the standby database in mount stage
$ sqlplus / as sysdba
SQL> startup mount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora';
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2293832 bytes
Variable Size 595595192 bytes
Database Buffers 230686720 bytes
Redo Buffers 2355200 bytes
Database mounted.
SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora';
Start Physical Standby Database :
SQL> alter database recover managed standby database disconnect from session;
In standby database :
SQL>select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
Also check the above query in primary database also to confirm the archive log no:-
In Primary database:-
SQL> select sequence#, first_time, next_time,dest_id, thread# , standby_dest, applied from v$archived_log order by sequence#;
In primary database :
sql>alter system switch logfile;
Note: if archive logfile is coming to prodcws database, that means configuration is correct till now.
Now for real time apply:-
Steps :
# In Standby Database
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
select open_mode from v$database; (Output : read only with apply)
After commit on primary, it will directly apply on standby database.
+++++ END +++++
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
No comments:
Post a Comment