Unknown

Oracle 11g - Active DataGuard









How to setup Active Data Guard in oracle 11gR2

Primary Database Info Collection:





IP Address: 192.168.20.50



Oracle Database Software Version: 11.2.0.3 (Installed with Database Creation)



[root@dbserver1 ~]# hostname



dbserver1



[root@dbserver1 ~]# cat /etc/hosts



127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6



192.168.20.50 dbserver1

192.168.20.60 dbserver2



[oracle@dbserver1 ~]$ vi .bash_profile



# Oracle Settings:





TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=dbserver1; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH





[oracle@dbserver1 ~]$ echo $ORACLE_HOME



/u01/app/oracle/product/11.2.0/db_1



[oracle@dbserver1 ~]$ echo $ORACLE_SID



Orcl



Let’s check listener status:



[oracle@dbserver1 ~]$ lsnrctl status



LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-MAY-2017 15:42:23

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 02-MAY-2017 15:38:59

Uptime 0 days 0 hr. 3 min. 23 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver1)(PORT=1521)))

Services Summary...

Service "orcl" has 2 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully



Let’s check the necessary parameters and directory structures of the database that are required for configuring Oracle Active Data Guard:



[oracle@dbserver1 ~]$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.3.0 Production on Tue May 2 15:43:43 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production Check the name of the database:



SQL> select name from v$database;



NAME

--------------------

ORCL



Check the open_mode of the database:



SQL> select open_mode from v$database;



OPEN_MODE

--------------------

READ WRITE



Check the log_mode of the database:



SQL> select log_mode from v$database;



LOG_MODE

------------

NOARCHIVELOG





Check force_logging status:



SQL> select force_logging from v$database;



FOR

---

NO



Check the Control File location:



SQL> select name from v$controlfile;



NAME

-------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/fast_recovery_area/orcl/control02.ctl





Check the Data File location:

SQL> select file_name from dba_data_files;



FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf





Check the Redo Log File location:



SQL> select group#,member from v$logfile;



GROUP# MEMBER

---------- -------------------------------------------------------------------

3 /u01/app/oracle/oradata/orcl/redo03.log

2 /u01/app/oracle/oradata/orcl/redo02.log

1 /u01/app/oracle/oradata/orcl/redo01.log



Check the Audit File location:





SQL> select value from v$parameter where name = 'audit_file_dest';



VALUE

--------------------------------------------------------------------------------

/u01/app/oracle/admin/orcl/adump



STANDBY Database info collection and directory creation as per PRIMARY database directory structure: At first, let’s collect the info:



[oracle@dbserver2 ~] $ ifconfig



IP Address: 192.168.20.60

Oracle Database Software Version: 11.2.0.3(Software Only Installation)



[oracle@dbserver2 ~]$ cat /etc/hosts



127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6



192.168.20.50 dbserver1

192.168.20.60 dbserver2



[oracle@dbserver2 ~]$ hostname

dbserver2

Let’s check the environment variable:





[oracle@dbserver2 ~]$ vi .bash_profile



# Oracle Settings



TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=dbserver2; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH



[oracle@dbserver2 ~]$ echo $ORACLE_HOME



/u01/app/oracle/product/11.2.0/db_1



[oracle@dbserver2 ~]$ echo $ORACLE_SID



orcl



Let us create the Same directories at the standby node as per the primary database:



Control File :



[oracle@dbserver2 ~]$ mkdir -p /u01/app/oracle/oradata/orcl



[oracle@dbserver2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl



Data File:



[oracle@dbserver2 ~]$ mkdir -p /u01/app/oracle/oradata/orcl

Log File:



[oracle@dbserver2 ~]$ mkdir -p /u01/app/oracle/oradata/orcl

Audit File:



[oracle@dbserver2 ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump



AT PRIMARY Database: We need to alter the database to ARCHIVELOG mode if it is already not:



[oracle@dbserver1 ~]$ sqlplus / as sysdba



SQL> select log_mode from v$database;



LOG_MODE

------------

NOARCHIVELOG



SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 524291456 bytes

Database Buffers 306184192 bytes

Redo Buffers 2396160 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG



We need enable force logging in primary database if it is already not:



SQL > select force_logging from v$database;

FOR

---

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

We need to enable the flashback database option in primary database if it is already not:



SQL> select flashback_on from v$database;



FLASHBACK_ON

------------------

NO



SQL> alter database flashback on;



Database altered.



SQL> select flashback_on from v$database;



FLASHBACK_ON

------------------

YES



We need to alter the "standby_file_management" to AUTO if it is already not:



SQL> show parameter standby_file_management;



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string MANUAL



SQL> alter system set standby_file_management ='auto';

System altered.



SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string auto



We have to create standby logfile if already not created :





1st let us check the current lofiles:





SQL> select group#,member,type from v$logfile;



GROUP# MEMBER TYPE

---------- ------------------------------------------------------------------- --------------------

3 /u01/app/oracle/oradata/orcl/redo03.log ONLINE

2 /u01/app/oracle/oradata/orcl/redo02.log ONLINE

1 /u01/app/oracle/oradata/orcl/redo01.log ONLINE



As we can see that there is no standby redo log file, we need to create them.:





SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

Database altered

.

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;



Database altered.



SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;



Database altered.



SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;



Database altered.



SQL> select group#,member,type from v$logfile;



GROUP# MEMBER TYPE

---------- ------------------------------------------------------------------- --------------------

3 /u01/app/oracle/oradata/orcl/redo03.log ONLINE

2 /u01/app/oracle/oradata/orcl/redo02.log ONLINE

1 /u01/app/oracle/oradata/orcl/redo01.log ONLINE

4 /u01/app/oracle/oradata/orcl/redo04.log STANDBY

5 /u01/app/oracle/oradata/orcl/redo05.log STANDBY

6 /u01/app/oracle/oradata/orcl/redo06.log STANDBY

7 /u01/app/oracle/oradata/orcl/redo07.log STANDBY



We need set "remote_login_passwordfile" parameter to "exclussive" if it is already not:



SQL> show parameter remote_login_passwordfile;



NAME TYPE VALUE

------------------------------------ -------------------- ------------------------------

remote_login_passwordfile string EXCLUSIVE



As the parameter already set to “exclusive” by default we don’t have to set this again. /**not required



**[SQL> alter system set remote_login_passwordfile ='EXCLUSIVE' scope = spfile;]**



We need to create tns alias at primary database for the standby database:



[oracle@dbserver1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@dbserver1 admin]$ ls





listener.ora samples shrept.lst sqlnet.ora tnsnames.ora



[oracle@dbserver1 admin]$ vi tnsnames.ora



ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

ORCLSTBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.60)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)



[oracle@dbserver1 admin]$ pwd



/u01/app/oracle/product/11.2.0/db_1/network/admin



[oracle@dbserver1 admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle



We need to transfer these two files( tnsnames.ora , listener.ora) to the standby database:



[oracle@dbserver1 admin]$ scp tnsnames.ora 192.168.20.60:/u01/app/oracle/product/11.2.0/db_1/network/admin/



oracle@192.168.20.60's password:

tnsnames.ora 100% 516 0.5KB/s 00:00



[oracle@dbserver1 admin]$ scp listener.ora 192.168.20.60:/u01/app/oracle/product/11.2.0/db_1/network/admin/



oracle@192.168.20.60's password:

listener.ora 100% 578 0.6KB/s 00:00



AT STANDBY Database: Here we edit the "tnsnames.ora" and "listener.ora" files that were copied from the primary database as per our standby configuration:



[oracle@dbserver2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/



[oracle@dbserver2 admin]$ ls

listener.ora samples shrept.lst tnsnames.ora



[oracle@dbserver2 admin]$ vi tnsnames.ora



ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.50)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

ORCLSTBY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.60)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

[oracle@dbserver2 admin]$ vi listener.ora





SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle



We have to start the listener in the standby database:



[oracle@dbserver2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-MAY-2017 17:09:46

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/dbserver2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver2)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 02-MAY-2017 17:09:47

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver2/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver2)(PORT=1521)))

Services Summary...

Service "orcl" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully



AT PRIMARY Database: We need to set ‘log_archive_dest_1’ parameter:



SQL> alter system set log_archive_dest_1 = 'location=/u01/app/oracle/fast_recovery_area/ORCL/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl' ;





System altered.

“log_archive_dest_1” is valid only for the primary database role and the logical Oracle instance role. “log_archive_dest_1” designates the physical location for the primary database’s archived redo logs.



Instead of specific location we also can use:



log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'



SQL> show parameter log_archive_dest_1;



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1 string location=/u01/app/oracle/fast_

recovery_area/ORCL/archivelog

valid_for=(all_logfiles,all_roles) db_unique_name=orcl We need to set ‘log_archive_dest_2’ parameter



SQL> alter system set log_archive_dest_2 = 'service=orclstby async valid_for=(online_logfile,primary_role) db_unique_name=orclstby';

System altered.



SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service=orclstby async valid_for=(online_logfile,primary_role) db_unique_name=orclstby



We need to set ‘fal_server’ parameter





SQL> alter system set fal_server ='orclstby';



We need to set ‘log_archive_config’ parameter :





SQL> alter system set log_archive_config = 'dg_config=(orcl,orclstby)';





We need to restart the database to make the changes of the parameters written permanently in the spfile(if any).





SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 524291456 bytes

Database Buffers 306184192 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.



We need to create pfile from spfile:







SQL> create pfile from spfile;

File created.



We need to transfer the pfile from primary to standby database:



[oracle@dbserver1 archivelog]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@dbserver1 dbs]$ ls

hc_orcl.dat init.ora initorcl.ora lkORCL orapworcl spfileorcl.ora



[oracle@dbserver1 dbs]$ scp initorcl.ora 192.168.20.60:/u01/app/oracle/product/11.2.0/db_1/dbs



oracle@192.168.20.60's password:

initorcl.ora 100% 1262 1.2KB/s 00:00



We need to transfer the password file from primary to standby database:





[oracle@dbserver1 dbs]$ scp orapworcl 192.168.20.60:/u01/app/oracle/product/11.2.0/db_1/dbs



oracle@192.168.20.60's password:

orapworcl



AT STANDBY Database:We need to edit the parameter file in STANDBY Database as per our configuration:





[oracle@dbserver2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

[oracle@dbserver2 dbs]$ ls

init.ora initorcl.ora



We need to change the red marked line only(minimum).





[oracle@dbserver2 dbs]$ vi initorcl.ora



orcl.__db_cache_size=306184192

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=335544320

orcl.__sga_target=503316480

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=176160768

orcl.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_unique_name='orclstby'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_server='orcl'

*.log_archive_config='dg_config=(orclstby,orcl)' *.log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/ORCLSTBY/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orclstby'

*.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl'

*.memory_target=835715072

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'



We need to create the archive log (log_archive_dest_1) location in standby as per pfile:





[oracle@dbserver2]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCLSTBY/archivelog



Now we need to start the database in nomount mode with the pfile in standby:





[oracle@dbserver2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

[oracle@dbserver2 ]$ ls

init.ora initorcl.ora



[oracle@dbserver2]$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.3.0 Production on Tue May 2 17:30:51 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.



SQL> startup nomount pfile=initorcl.ora;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 524291456 bytes

Database Buffers 306184192 bytes

Redo Buffers 2396160 bytes

Now we will create a spfile from the pfile.

SQL> create spfile from pfile;

File created.



Now we will shutdown the instance and startup in nomount mode with the spfile:





SQL> shu immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 524291456 bytes

Database Buffers 306184192 bytes

Redo Buffers 2396160 bytes

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfileorcl.ora



AT PRIMARY Database: Now using RMAN duplicate command we will create standby database:





[oracle@dbserver1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 2 17:37:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1470187549)



RMAN> connect auxiliary sys/oracle@orclstby



connected to auxiliary database: ORCL (not mounted)



RMAN> duplicate target database for standby from active database nofilenamecheck;



Starting Duplicate Db at 02-MAY-17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' ;

}

executing Memory Script

Starting backup at 02-MAY-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

Finished backup at 02-MAY-17

<--------------------------

OUTPUT TRIMMED

----------------------------

--------------------------->

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=942946915 file name=/u01/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=942946915 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=942946915 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=942946915 file name=/u01/app/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=942946915 file name=/u01/app/oracle/oradata/orcl/example01.dbf

Finished Duplicate Db at 02-MAY-17



RMAN>



AT STANDBY Database:





SQL> shu immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2232960 bytes

Variable Size 524291456 bytes

Database Buffers 306184192 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

SQL> alter database flashback on;

Database altered.

SQL> select open_mode from v$database;



OPEN_MODE

--------------------

READ ONLY To start apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply using the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement.



SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;



OPEN_MODE

--------------------

READ ONLY WITH APPLY



SQL>

NOW LETS CHECK THE DATA IS BEING SYNCED AUTOMATICALLY FROM PRIMARY TO STANDBY DATABASE:





AT PRIMARY Database:





create an user and insert some test data:





[oracle@dbserver1 ~]$ sqlplus / as sysdba



SQL*Plus: Release 11.2.0.3.0 Production on Tue May 2 17:47:40 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options



SQL> create user Shaikatustc09 identified by 23;

User created.

SQL> grant connect,resource,dba to Shaikatustc09

Grant succeeded.

SQL > conn Shaikatustc09 /23

Connected.

SQL> show user

USER is " Shaikatustc09"

SQL>

SQL> create table t1(id number,name varchar2(100));

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert into t1

4 values(i,'shaikatustc09');

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.



SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)

----------

10000

SQL>



AT STANDBY Database:





Let’s check whether the datas are automatically being synced:



[oracle@dbserver2 ~ ]$ sqlplus Shaikatustc09/23



SQL*Plus: Release 11.2.0.3.0 Production on Tue May 2 17:53:14 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options



SQL> select count(*) from t1;



COUNT(*)

----------

10000





Congratulation you have successfully configured Oracle Active Data Guard 11gR2.

Stay in touch with us for the future posts regarding ADG and more.





***************Thank You******************