Wednesday, May 9, 2012

Create physical standby database with Oracle 11gr2 from active database

I've created a physical standby database from active database
Here are the steps to do that

primary database asm451p1 server ota5070
standby database asm452p1 server ota5071
listener run in our enviroment under the user grid on linux


on the primary database asm451p1 server ota5070
create standby redolog files with the same size as the redo log files
always create 1 extra standby redo logfile then the normal redo logfiles
In this example whe have 3 redo logfiles so we create 4 standby redolog files.

alter database add standby logfile group 4 size 100M;
alter database add standby logfile group 5 size 100M;
alter database add standby logfile group 6 size 100M;
alter database add standby logfile group 7 size 100M;

alter database add standby logfile member '+FRA' to group 4;
alter database add standby logfile member '+DATA' to group 5;
alter database add standby logfile member '+FRA' to group 6;
alter database add standby logfile member '+DATA' to group 7;

on the primary database asm451p1 set the archivelog destinations

ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=+FRA DB_UNIQUE_NAME=ASM451P VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope=both;

ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=ASM452P1 ASYNC DB_UNIQUE_NAME=ASM452P VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope =both;

after that set the following parameters on the primary database asm451p1

ALTER SYSTEM SET log_archive_dest_state_1 = 'ENABLE';
ALTER SYSTEM SET log_archive_dest_state_2 = 'ENABLE';
ALTER SYSTEM SET standby_file_management = 'AUTO';
ALTER SYSTEM SET log_archive_config = 'DG_CONFIG=(ASM451P,ASM452P)';

Put in the tnsnames.ora the following databases. This must be done on the ota5070

ASM451P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5070)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)
    )
  )
ASM452P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5071)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)(UR=A)
    )
  )


 
On the primary database host ota5070 you must set the following lines in the listener.ora
The listener runs under the user grid so logon as grid
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ota5070.business.finl.fortis)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ASM451P)
      (ORACLE_HOME = /oracle/home/11.2.0.3.0)
      (SID_NAME = ASM451P1)
    )
  )

ADR_BASE_LISTENER = /grid/home
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


On the standby host ota5071 put the following lines in the listener.ora. The listener runs under the user grid
so logon as grid

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ota5071.business.finl.fortis)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ASM451P)
      (ORACLE_HOME = /oracle/home/11.2.0.3.0)
      (SID_NAME = ASM452P1)
    )
  )


ADR_BASE_LISTENER = /grid/home

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


on the standby host ota5071 under user oracle set the following lines in the tnnames.ora
 ASM451P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5070)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)
    )
  )

ASM452P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5071)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)
    )
  )


 
copy password file from primary host to standby host
scp orapwASM451P1 ota5071:/tmp
cp /tmp/ orapwASM451P1 /oracle/home/11.2.0.3.0/dbs
cd /oracle/home/11.2.0.3.0/dbs
chmod 640 orapwASM451P1
mv orapwASM451P1 orapwASM452P1


because we use audting we nust create adump directory on standby host ota5071
/oracle/home/admin/ASM451P/adump
Set standby database name in oratab
ASM452P1:/oracle/home/11.2.0.3.0:N
 
Create pfile in /oracle/home/11.2.0.3.0/dbs
we need only 1 parameter in it
$ vi initASM452P1.ora
db_name=ASM451P

now start the standby database asm452p1
SQL> startup nomount;

Now go to the primary host ota5070 and start RMAN
RMAN>connect target sys@asm451p1
RMAN> connect auxiliary sys@asm452p1

now you can start the following script
RMAN>DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
set control_files='+DATA','+FRA'
SET db_unique_name='ASM452P'
SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA DB_UNIQUE_NAME=ASM452P VALID_FOR=
(ALL_LOGFILES,ALL_ROLES)'
SET LOG_ARCHIVE_DEST_2='SERVICE=ASM451P ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM451P'
SET FAL_SERVER='ASM452P'
SET db_recovery_file_dest='+FRA'
SET db_create_file_dest='+DATA'
SET standby_archive_dest='+FRA'
NOFILENAMECHECK;

Here is the the output of the logfile
Starting Duplicate Db at 04-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=111 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/oracle/home/11.2.0.3.0/dbs/orapwASM451P1' auxiliary format 
 '/oracle/home/11.2.0.3.0/dbs/orapwASM452P1'   targetfile 
 '+DATA/spfileasm451p.ora' auxiliary format 
 '/oracle/home/11.2.0.3.0/dbs/spfileASM452P1.ora'   ;
   sql clone "alter system set spfile= ''/oracle/home/11.2.0.3.0/dbs/spfileASM452P1.ora''";
}
executing Memory Script
Starting backup at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
Finished backup at 04-MAY-12
sql statement: alter system set spfile= ''/oracle/home/11.2.0.3.0/dbs/spfileASM452P1.ora''
contents of Memory Script:
{
   sql clone "alter system set  control_files = 
 '' +DATA'', ''+FRA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''ASM452P'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_2 = 
 ''SERVICE=ASM451P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM451P'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_SERVER = 
 ''ASM452P'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest = 
 ''+FRA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_file_dest = 
 ''+DATA'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  control_files =  '' +DATA'', ''+FRA'' comment= '''' scope=spfile
sql statement: alter system set  db_unique_name =  ''ASM452P'' comment= '''' scope=spfile
sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''SERVICE=ASM451P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM451P'' comment= '''' scope=spfile
sql statement: alter system set  FAL_SERVER =  ''ASM452P'' comment= '''' scope=spfile
sql statement: alter system set  db_recovery_file_dest =  ''+FRA'' comment= '''' scope=spfile
sql statement: alter system set  db_create_file_dest =  ''+DATA'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     680607744 bytes
Fixed Size                     2231472 bytes
Variable Size                352322384 bytes
Database Buffers             318767104 bytes
Redo Buffers                   7286784 bytes
contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.256.782404223'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '/oracle/home/11.2.0.3.0/dbs/ +DATA';
   restore clone controlfile to  '+FRA/asm452p/controlfile/current.257.782404223' from 
 '/oracle/home/11.2.0.3.0/dbs/ +DATA';
   sql clone "alter system set  control_files = 
  ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.257.782404223'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  control_files =   ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.256.782404223'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/home/11.2.0.3.0/dbs/snapcf_ASM451P1.f tag=TAG20120504T143024 RECID=9 STAMP=782404224
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
Starting restore at 04-MAY-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=265 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-MAY-12
sql statement: alter system set  control_files =   ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.257.782404223'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     680607744 bytes
Fixed Size                     2231472 bytes
Variable Size                352322384 bytes
Database Buffers             318767104 bytes
Redo Buffers                   7286784 bytes
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   ;
   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
executing command: SET NEWNAME
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/asm451p/datafile/system.260.782311383
output file name=+DATA/asm452p/datafile/system.256.782404245 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/asm451p/datafile/sysaux.261.782311387
output file name=+DATA/asm452p/datafile/sysaux.257.782404251 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/asm451p/datafile/undo.262.782311389
output file name=+DATA/asm452p/datafile/undo.258.782404259 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/asm451p/datafile/tools.264.782311397
output file name=+DATA/asm452p/datafile/tools.259.782404261 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/asm451p/datafile/users.265.782311399
output file name=+DATA/asm452p/datafile/users.260.782404265 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
sql statement: alter system archive log current
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/asm451p/archivelog/2012_05_04/thread_1_seq_96.346.782404265" auxiliary format 
 "/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc"   ;
   catalog clone archivelog  "/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=96 RECID=78 STAMP=782404265
output file name=/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
cataloged archived log
archived log file name=/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc RECID=1 STAMP=782404267
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=782404267 file name=+DATA/asm452p/datafile/system.256.782404245
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=782404267 file name=+DATA/asm452p/datafile/sysaux.257.782404251
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=782404267 file name=+DATA/asm452p/datafile/undo.258.782404259
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=782404267 file name=+DATA/asm452p/datafile/tools.259.782404261
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=782404267 file name=+DATA/asm452p/datafile/users.260.782404265
contents of Memory Script:
{
   set until scn  780488;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-MAY-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=263 device type=DISK
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file /oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc
archived log file name=/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc thread=1 sequence=96
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAY-12
Finished Duplicate Db at 04-MAY-12


Check archivelog number on primary database asm451p1
SQL> SELECT sequence#, first_time, next_time, applied
           FROM   v$archived_log
            ORDER BY sequence#;

Check if transport of archivelog works on the primary database asm451p1

SQL> select status, error from v$archive_dest where dest_id=2;


to stop media recovery on the standby database run the following command
SQL> alter database recover managed standby database cancel;


As we use standby redolog files it is better to start media recovery  this way

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


As of Oracle 11gr2 we can use active dataguard. Which means that we can read only on the standby database while in the meantime media recovery is running keeping the standby up to date.




SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Next blog a will setup dataguard broker and setup observer for fast fail over.

Thats it.

No comments:

Post a Comment