Ask Me

Data-Guard Set up using ASM On Oracle 11GR2


:: Data-Guard Set up using ASM On Oracle 11GR2
              
               Oracle Data Guard is one of the most effective and comprehensive data availability, data protection and disaster recovery solutions available today for enterprise data.Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.Data Guard maintains these standby databases as transitional consistent copies of the production database. These standby databases can be located at remote disaster recovery sites thousands of miles away from the production data center, or they may be located in the same city, same campus, or even in the same building. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime associated with the outage, and preventing any data loss.
Available as a feature of the Enterprise Edition of the Oracle Database, Data Guard can be used in combination with other Oracle High Availability (HA) solutions such as Real Application Clusters (RAC), Oracle Flashback and Oracle Recovery Manager (RMAN), to provide a very high level of data protection and data availability that is unprecedented in the industry.

Step By Step Guide to Configure 11gR2 dataguard Primary Database Side Set Up

SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.

SQL> alter database add standby logfile '+DATA' SIZE 50M;
Database altered.
SQL> alter database add standby logfile '+DATA' SIZE 50M;
Database altered.
SQL> alter database add standby logfile '+DATA' SIZE 50M;
Database altered.
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> alter system set log_archive_config='dg_config=(colldb_pm,colldb_st)';
System altered.
SQL> alter system set log_archive_dest_2='service=colldb_st async valid_for=(online_logfile,primary_role) db_unique_name=colldb_st';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=colldb_st
  2  ;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET log_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL>
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
Database altered.
SQL> CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
File created.
SQL>
SQL> alter system set FAL_SERVER=colldb_st;
System altered.
SQL> alter system set FAL_CLIENT=colldb_pm;
System altered.
SQL>



Stand by Database Set up:

#############################################
Database Name :- COLLDB
Primary db_unique_name :- COLLDB_PM
Standby db_unique_name :- COLLDB_ST
############################################
*On standby* Add listener entry   Listener file will be found in GRID_HOME/network/admin
SID_LIST_LISTENER =
   (SID_LIST =
       (SID_DESC =
          (ORACLE_HOME= /u/oracle/server/database203/)
          (SID_NAME = colldb)
          (GLOBAL_DBNAME= colldb)
   )
   (SID_DESC =
      (global_dbname = colldb)
      (ORACLE_HOME = /u/oracle/server/database203)
      (sid_name = colldb)
       )
    )
After that tnsping each service name  on both the servers
Add entries to tnsnames.ora to both nodes  TNSNAMES.ORA will be found in ORACLE_HOME/network/admin  on both servers
COLLDB_PM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Test1.imslab.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = colldb_pm)
    )
  )
COLLDB_ST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Test2.imslab.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = COLLDB)
    )
  )
Step 9::
 Copy Password file onto standby or create password file with same password
copy password file primary to stand by
orapwd file=/u/oracle/server/database203/dbs/orapwcolldb force=y entries=5 password=sys
orapwd file=/u/oracle/server/database203/dbs/orapwcolldb force=y entries=5 password=sys
Step 10:: 
Location ORACLE_HOME/dbs/initcolldb
Create  Standby Pfile
 Only one parameter needed - > DB_NAME
 DB_NAME=TESTMP2
Step 11:: 

Startup Standby
sqlplus / as sysdba
startup nomount

Stand Db creation:
From Standby database run these commands
[oracle@Test2 ~]$ rman target sys/sys@colldb_pm auxiliary sys/sys@colldb_st
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Nov 27 16:52:41 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: COLLDB (DBID=3477737225)
connected to auxiliary database: COLLDB (not mounted)
RMAN>
run 
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'colldb_pm','colldb_st'
set db_unique_name='stbydv1'
set db_file_name_convert='+DATA','+DATA'
set log_file_name_convert='+DATA','+DATA'
set control_files='+DATA'
set log_archive_max_processes='5'
set fal_client='colldb_st'
set fal_server='colldb_pm'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(colldb_pm,colldb_st)'
set log_archive_dest_2='service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'
nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}
OUTPUT 
RMAN> run
2> {
3> allocate channel prmy1 type disk;
4> allocate channel prmy2 type disk;
5> allocate channel prmy3 type disk;
6> allocate channel prmy4 type disk;
7> allocate auxiliary channel stby type disk;
8> duplicate target database for standby from active database
9> spfile
10> parameter_value_convert 'colldb_pm','colldb_st'
11> set db_unique_name='colldb_st'
12> set db_file_name_convert='+DATA','+DATA'
13> set log_file_name_convert='+DATA','+DATA'
14> set control_files='+DATA'
15> set log_archive_max_processes='5'
16> set fal_client='colldb_st'
17> set fal_server='colldb_pm'
18> set standby_file_management='AUTO'
19> set log_archive_config='dg_config=(colldb_pm,colldb_st)'
20> set log_archive_dest_2='service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'
21> nofilenamecheck;
22> release channel prmy1;
23> release channel prmy2;
24> release channel prmy3;
25> release channel prmy4;
26> }
allocated channel: prmy1
channel prmy1: SID=85 device type=DISK
allocated channel: prmy2
channel prmy2: SID=73 device type=DISK
allocated channel: prmy3
channel prmy3: SID=83 device type=DISK
allocated channel: prmy4
channel prmy4: SID=79 device type=DISK
allocated channel: stby
channel stby: SID=20 device type=DISK
Starting Duplicate Db at 27-NOV-13
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u/oracle/server/database203/dbs/orapwcolldb' auxiliary format
 '/u/oracle/server/database203/dbs/orapwcolldb'   targetfile
 '+DATA/colldb/spfilecolldb.ora' auxiliary format
 '/u/oracle/server/database203/dbs/spfilecolldb.ora'   ;
   sql clone "alter system set spfile= ''/u/oracle/server/database203/dbs/spfilecolldb.ora''";
}
executing Memory Script
Starting backup at 27-NOV-13
Finished backup at 27-NOV-13
sql statement: alter system set spfile= ''/u/oracle/server/database203/dbs/spfilecolldb.ora''
contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''colldb_st'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''+DATA'', ''+DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''+DATA'', ''+DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''colldb_st'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''colldb_pm'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(colldb_pm,colldb_st)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_unique_name =  ''colldb_st'' comment= '''' scope=spfile
sql statement: alter system set  db_file_name_convert =  ''+DATA'', ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set  log_file_name_convert =  ''+DATA'', ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set  control_files =  ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile
sql statement: alter system set  fal_client =  ''colldb_st'' comment= '''' scope=spfile
sql statement: alter system set  fal_server =  ''colldb_pm'' comment= '''' scope=spfile
sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_config =  ''dg_config=(colldb_pm,colldb_st)'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_dest_2 =  ''service=colldb_pm ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=colldb_pm'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    1657225216 bytes
Fixed Size                     1345324 bytes
Variable Size               1040189652 bytes
Database Buffers             603979776 bytes
Redo Buffers                  11710464 bytes
allocated channel: stby
channel stby: SID=23 device type=DISK
contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/colldb_st/controlfile/current.258.832613087'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '+DATA/colldb_st/controlfile/current.259.832613087';
   sql clone "alter system set  control_files =
  ''+DATA/colldb_st/controlfile/current.259.832613087'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  control_files =   ''+DATA/colldb_st/controlfile/current.258.832613087'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 27-NOV-13
channel prmy1: starting datafile copy
copying standby control file
output file name=/u/oracle/server/database203/dbs/snapcf_colldb.f tag=TAG20131127T172446 RECID=5 STAMP=832613087
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-NOV-13
sql statement: alter system set  control_files =   ''+DATA/colldb_st/controlfile/current.259.832613087'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    1657225216 bytes
Fixed Size                     1345324 bytes
Variable Size               1040189652 bytes
Database Buffers             603979776 bytes
Redo Buffers                  11710464 bytes
allocated channel: stby
channel stby: SID=23 device type=DISK
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   backup as copy reuse
   datafile  1 auxiliary format
 "+data"   datafile
 2 auxiliary format
 "+data"   datafile
 3 auxiliary format
 "+data"   datafile
 4 auxiliary format
 "+data"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 27-NOV-13
channel prmy1: starting datafile copy
input datafile file number=00001 name=+DATA/colldb/datafile/system.256.832173859
channel prmy2: starting datafile copy
input datafile file number=00002 name=+DATA/colldb/datafile/sysaux.257.832173861
channel prmy3: starting datafile copy
input datafile file number=00003 name=+DATA/colldb/datafile/undotbs1.258.832173861
channel prmy4: starting datafile copy
input datafile file number=00004 name=+DATA/colldb/datafile/users.259.832173861
output file name=+DATA/colldb_st/datafile/undotbs1.263.832613107 tag=TAG20131127T172500
channel prmy3: datafile copy complete, elapsed time: 00:00:15
output file name=+DATA/colldb_st/datafile/users.262.832613107 tag=TAG20131127T172500
channel prmy4: datafile copy complete, elapsed time: 00:00:15
output file name=+DATA/colldb_st/datafile/system.257.832613101 tag=TAG20131127T1
channel prmy1: datafile copy complete, elapsed time: 00:00:45
output file name=+DATA/colldb_st/datafile/sysaux.256.832613101 tag=TAG20131127T1
channel prmy2: datafile copy complete, elapsed time: 00:00:45
Finished backup at 27-NOV-13
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=5 STAMP=832613147 file name=+DATA/colldb_st/datafile/s
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=832613147 file name=+DATA/colldb_st/datafile/s
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=832613147 file name=+DATA/colldb_st/datafile/u
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=832613147 file name=+DATA/colldb_st/datafile/u
Finished Duplicate Db at 27-NOV-13
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

Popular Posts