Wednesday 19 December 2012

Active Dataguard Configuration Using RMAN


Oracle Active Dataguard Configuration Using RMAN Duplicate for Standby Database

PRIMARY SITE: (Information)
Machine IP: 192.168.1.1
Machine Name: Linux1
Database name (db_name): ORCL
Database Unique Name (db_unique_name): primary
TNS Service Name: standby (Through this service, the primary machine will be connected to STANDBY machine)

STANDBY SITE: (Information)
Machine IP: 192.168.1.2
Machine Name: Linux2
Database name (db_name): ORCL
Database Unique Name (db_unique_name): standby
TNS Service Name: primary (Through this service, the standby machine will be connected to PRIMARY machine)

CONFIGURATION ON PRIMARY SITE: -

Step1:
Create pfile from spfile on the primary database:
SQL>create pfile= ‘/backup/mypfile.ora’ from spfile;

Step2:
Add following settings in the pfile (/backup/mypfile.ora) on the PRIMARY Machine.

DB_UNIQUE_NAME='primary'
FAL_Client='primary'
FAL_Server='standby'
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG= (primary, standby)'
standby_file_management=auto
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2=
 'SERVICE=standby LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
Service_names='primary'

Step3:
Create password file using ‘Shell prompt’.
[oracle@linux1~]$ orapwd file=’/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl’ password=oracle entries=5 force=y ignorecase=y

Step4:
SQL>startup mount;

Step5:
Take the Primary database to Archive Log Mode.
SQL>alter database archiveLog;
Enable Force Logging.
SQL>alter database force logging;
Now shutdown the database:
SQL>shutdown immediate;
Now startup the database:
SQL>startup;

Step6:
Now add standby Redo Log group to the PRIMARY site.
SQL>Alter database add standby logfile (‘/u01/app/oracle/oradata/ORCL/standbyredo.log’) size 100m;

Step7:
Startup the database from pfile.
SQL>startup pfile='/backup/mypfile.ora';
Create spfile from pfile to startup the database with spfile.
It is necessary for primary database to use spfile in a dataguard environment.
SQL>Create spfile from pfile=‘/backup/mypfile.ora’;
Now bounce the DB to take affect of new parameter from pfile.

Step8:
Restart the database
SQL> shut immediate;
SQL> startup;

Step9:
Configure tnsnames.ora on both servers to hold entries for both databases.
Now on PRIMARY site create following services in TNSnames.ora file. This file will be use on standby site also.
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVER = DEDICATED)
      (SID = orcl)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVER = DEDICATED)
      (SID = orcl)
    )
  )

Note: This tnsnames.ora will be used on both side of dataguard env.

Step10:
Listener.ora for Primary Site.

# Generated by Oracle configuration tools.

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 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Step11:
Listener.ora for Standby Site.
# Generated by Oracle configuration tools.

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 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Step12:
Also configure net service name for primary database on primary site and standby service name on standby site.
[oracle@linux1~]$ netmgr
[oracle@linux1~]$ Lsnrctl status
[oracle@linux1~]$ Lsnrctl stop
[oracle@linux1~]$ Lsnrctl start

CONFIGURATION ON STANDBY SITE:-
Step1:
Create following directories on target standby
mkdir -p /u01/app/oracle/flash_recovery_area/orcl/archivelog
mkdir -p /u01/app/oracle/flash_recovery_area/orcl/onlinelog
mkdir -p /u01/app/oracle/flash_recovery_area/orcl/flashback
mkdir -p /u01/app/oracle/admin/orcl/dpdump
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/admin/orcl/scripts
mkdir -p /u01/app/oracle/admin/orcl/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/orclca/orcl
mkdir -p /u01/app/oracle/cfgtoollogs/emca
mkdir -p /u01/app/oracle/cfgtoollogs/netca
mkdir -p /u01/app/oracle/oradata/orcl     
mkdir -p /u01/app/oracle/admin/orcl/adump

Step2:
Create pfile.
[oracle@linux2~]$ vi /backup/pfile.ora
Make following entry in pfile
Db_name=orcl

Step3:
Start database using above created pfile
SQL>startup nomount pfile=’/backup/pfile.ora’

Step4:
Create password file using ‘Shell prompt’.
[oracle@linux1~]$ orapwd file=’/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl’ password=oracle entries=5 force=y ignorecase=y

Step5:
Use this command to connect to rman
rman target=sys/oracle@primary auxiliary=sys/oracle@standby

Step6:
Run following script in rman prompt to create standby database using rman

run{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate channel prmy5 type disk;

allocate auxiliary channel stby1 type disk; 

duplicate target database for standby from active database 

DORECOVER

spfile 

parameter_value_convert 'orcl','orcl'

set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

set

log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

set 'db_unique_name'='standby'

set

control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/orac

e/flash_recovery_area/orcl/control02.ctl'

set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

set DB_RECOVERY_FILE_DEST_SIZE='52000'

nofilenamecheck;

}

Check Standby Archive Destination
Run following command at primary site to check archive dest_1 for standby .
select status, error from v$archive_dest where dest_id=2
On PRIMARY site enable Log_archive_dest_state_2 to start shipping archived redo logs.

SQL>Alter system set Log_archive_dest_state_2=ENABLE scope=both;

Start Apply Process
Start the apply process on standby server.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
Test Log Transport
On the primary server, check the latest archived redo log and force a log switch.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;
Protection Mode
There are three protection modes for the primary database:
  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum performance mode.
SELECT protection_mode FROM v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
 
SQL>
The mode can be switched using the following commands. Note the alterations in the redo transport attributes.
-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
 
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
 
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Read-Only Standby and Active Data Guard
Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.
Step1:
Cancel the manager recovery.
SQL> recover managed standby database cancel;
Media recovery complete.

Step2:
Open the database in read-only mode.
SQL> alter database open read only;
Database altered.

Step3:
Restarts the Redo apply.
SQL> recover managed standby database disconnect using current logfile;
Media recovery complete.
-------------------------------------------------------------------------------------------------------------------------------
###############################################################################


1 comment:

Subscribe to: Post Comments (Atom)