ROAD-2-ORACLE : Oracle 11.2.0.3.0 Active Dataguard Configuration

DG ON SINGLE TOUCH

Oracle version :- '11.2.0.3.0'

Inside PROD Database :-)

SQL>archive log list;

SQL>select name, force_logging from v$database;

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

SQL> alter database force logging;

Database altered.

SQL> select name, force_logging from v$database;

NAME FOR

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

PROD YES

SQL> alter system set standby_file_management = 'AUTO';

System altered.

SQL> select GROUP#,MEMBER from v$logfile;

GROUP#

----------

MEMBER

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

/u02/PROD/DATAFILE/PROD/redo03.log

/u02/PROD/DATAFILE/PROD/redo02.log

/u02/PROD/DATAFILE/PROD/redo01.log

PRODDG

SQL> alter database add standby logfile group 4

('/u02/PROD/DATAFILE/PROD/g11m01.sdo','/u02/PROD/DATAFILE/PROD/g11m02.sdo') size 524288000;

Database altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> show parameter db_unique_name

NAME TYPE VALUE

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

db_unique_name string PROD

SQL> alter system set db_recovery_file_dest='/u02/PROD/FRA';

FLASHBACK_ON

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

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

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

db_recovery_file_dest string /u02/PROD/FRA

db_recovery_file_dest_size big integer 4122M

SQL> SHOW PARAMETER db_recovery_file_dest

SP2-0734: unknown command beginning "db_recover..." - rest of line ignored.

SQL> alter system set db_recovery_file_dest='/u02/PROD/FRA';

System altered.

SQL> alter system set db_recovery_file_dest_size=400g;

System altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

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

db_recovery_file_dest string /u02/PROD/FRA

db_recovery_file_dest_size big integer 400G

SQL> alter system set db_recovery_file_dest_size=4g;

System altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

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

db_recovery_file_dest string /u02/PROD/FRA

db_recovery_file_dest_size big integer 4G

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

EDIT listener.ora:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PRODDG)

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

(SID_NAME = PRODDG)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

ADR_BASE_LISTENER = /u01/app/oracle

EDIT: tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DG =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = PRODDG)

(UR = A)

)

PROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PROD)

(UR = A)

)

SQL> create pfile from spfile;

Copy password file

$ORACLE_HOME/dbs/orapwPROD

And Rename it as orapwPRODDG

Configuring Redo Transport In PROD:

SQL>alter system set log_archive_dest_1 = 'location=use_db_recovery_file_dest

valid_for=(all_logfiles, all_roles) db_unique_name=PROD';

SQL>alter system set log_archive_dest_2 = 'service=DG async

valid_for=(online_logfile,primary_role) db_unique_name=PRODDG';

SQL> alter system set fal_server = 'PRODDG';

SQL> alter system set log_archive_config = 'dg_config=(PROD,PRODDG)';

Preparing the Standby environment:

EDIT tnsnames.ora:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PROD)

(UR = A)

)

DG =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME =PRODDG)

(UR = A)

)

EDIT listener.ora:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PROD)

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

(SID_NAME = PROD)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

ADR_BASE_LISTENER = /u01/app/oracle

Rename Password file and put the same directory :

Copy password file

$ORACLE_HOME/dbs/orapwPRODDG .

Rename The initPROD.ora file to initPRODDG.ora

and put it /u01/app/oracle/product/11.2.0/db_1/dbs

EDIT initPRODDG.ora:

· Depending on your configuration and file locations on the standby you might needed to change the

AUDIT_FILE_DEST, CONTROL_FILES and DISPATCHERS parameters (there may be others, but be

careful, not everything should be changed this way).

· In LOG_ARCHIVE_DEST_1 change db_unique_name to the standby SID (in this case PRODDG).

· In LOG_ARCHIVE_DEST_2 change the service and db_unique_name to the primary SID (in this case

PROD).

· Change FAL_SERVER to the primary SID (PROD).

· Add the following parameters:

. db_unique_name=PRODDG

. db_file_name_convert and log_file_name_convert if you are changing the location of the

datafiles and/or logfiles. I like to do this to make it obvious whose datafiles they are (e.g.

/oradata/PROD versus /oradata/PRODDG by using

*.db_file_name_convert='/u02/PROD/DATAFILE/PROD/system01.dbf','/u02/PROD/DATAFILE/PRODDG/system01','/u02/PROD/DATAFILE/PROD/sysaux01.dbf','/u02/PROD/DATAFILE/PRODDG/sysaux01','/u02/PROD/DATAFILE/PROD/undotbs01.dbf','/u02/PROD/DATAFILE/PRODDG/undotbs01',

'/u02/PROD/DATAFILE/PROD/users01.dbf','/u02/PROD/DATAFILE/PRODDG/users01','/u02/PROD/DATAFILE/PROD/temp01.dbf','/u02/PROD/DATAFILE/PRODDG/temp01','/u02/PROD/DATAFILE/PROD/example01.dbf','/u02/PROD/DATAFILE/PRODDG/example01')

Now, on your standby server create the directory structures and modify files to support the new database. These

should at least be:

· $ORACLE_BASE/admin/$ORACLE_SID

· $ORACLE_BASE/admin/$ORACLE_SID/adump (or whatever your audit_file_dest is)

· Datafile directories

· Controlfile directories

· Logfile directories

· DB Recovery File Destination (Fast Recovery Area)

· Add your standby to the /etc/oratab file.

sample of pfile:

PROD.__db_cache_size=620756992

PROD.__java_pool_size=16777216

PROD.__large_pool_size=16777216

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

PROD.__pga_aggregate_target=654311424

PROD.__sga_target=956301312

PROD.__shared_io_pool_size=0

PROD.__shared_pool_size=268435456

PROD.__streams_pool_size=16777216

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u02/PRODDG/DATAFILE/PROD/control01.ctl','/u02/PRODDG/FRA/PROD/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_unique_name='PRODDG'

*.db_name='PROD'

*.db_recovery_file_dest='/u02/PRODDG/FRA'

*.db_recovery_file_dest_size=4294967296

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

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

*.fal_server='PROD'

*.log_archive_config='dg_config=(PROD,PRODDG)'

*.log_archive_dest_1='location=use_db_recovery_file_dest

valid_for=(all_logfiles, all_roles) db_unique_name=PRODDG'

*.log_archive_dest_2='service=DG async

valid_for=(online_logfile,primary_role) db_unique_name=PROD'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='DEFER'

*.memory_target=1603272704

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

THEN CONNECT TO STANDBY DB.

SQLPLUS /NOLOG

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initPRODDG.ora

SQL> create spfile from pfile;

SQL> shutdown

SQL> startup nomount

SQL> show parameter spfile

SQL> exit

Creating the Standby Database:

$RMAN

RMAN> connect target sys/oracle@PROD

RMAN> connect auxiliary sys/oracle@DG

RMAN> duplicate target database for standby from active database;

Wait for the magic moment to come :

Here it is .........

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=864548434 file name=/u02/PROD/DATAFILE/PRODDG/system01

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=864548434 file name=/u02/PROD/DATAFILE/PRODDG/sysaux01

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=864548434 file name=/u02/PROD/DATAFILE/PRODDG/undotbs01

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=864548434 file name=/u02/PROD/DATAFILE/PRODDG/users01

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=864548434 file name=/u02/PROD/DATAFILE/PRODDG/example01

Finished Duplicate Db at 25-NOV-14

Then Connect to Standby db as sys user.

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

FEEDBACK PLZ :-)

ROAD-2-ORACLE

Tuesday, 25 November 2014

Oracle 11.2.0.3.0 Active Dataguard Configuration

No comments: