Database administrator workshop: 07/12/12

Thursday, July 12, 2012

How to create a 11gR2 physical standby database using Active Database Duplication and virtual machines on Oracle VM 3.0.3: step 5

Previous steps are here:
step 1
step 2
step 3
step 4
- Net Service configuration on your primary to reach your standby database:
Now it's time to configure the Oracle Net Service Name on the primary database to reach the physical standby database. In a later step I will copy this tnsnames.ora file also to the physical standby machine
because of course that machine has to know where is the primary database.

[oracle@vsi08devpom admin]$ vi /opt/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
DB01PRMR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi08devpom.MYDOMAIN.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB01prmr)
    )
  )

DB01SBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi10devpom.MYDOMAIN.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB01sby1)
    )
  )

- Copy the password file and tnsnames.ora to the physical standby database:
Just remember to rename the password file (located in $ORACLE_HOME/dbs/orapw) on the physical standby database machine with the SID used on that machine (in my case is DB01sby1)
Check on both machines the file /etc/hosts.

[root@vsi08devpom ~]# vi /etc/hosts
127.0.0.1               vsi08devpom.MYDOMAIN.it vsi08devpom localhost.localdomain localhost

172.16.151.78               vsi08devpom.MYDOMAIN.it vsi08devpom
172.16.151.80               vsi10devpom.MYDOMAIN.it vsi10devpom

[root@vsi10devpom ~]# vi /etc/hosts
127.0.0.1               vsi10devpom.MYDOMAIN.it vsi10devpom localhost.localdomain localhost

172.16.151.80               vsi10devpom.MYDOMAIN.it vsi10devpom
172.16.151.78               vsi08devpom.MYDOMAIN.it vsi08devpom

Copy the password file from the primary database machine ($ORACLE_HOME/dbs/orapwDB01prmr) and rename it to the physical standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby.

The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@vsi08devpom dbs]$ cp /opt/app/oracle/product/11.2.0/db_1/dbs/orapwDB01prmr /opt/app/oracle/product/11.2.0/db_1/dbs/orapwDB01sby1
[oracle@vsi08devpom dbs]$ scp /opt/app/oracle/product/11.2.0/db_1/dbs/orapwDB01sby1 vsi10devpom:/opt/app/oracle/product/11.2.0/db_1/dbs/
The authenticity of host 'vsi10devpom (172.16.151.80)' can't be established.
RSA key fingerprint is 8c:0a:81:5e:42:09:5a:60:cd:32:3e:5f:49:35:41:6c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'vsi10devpom,172.16.151.80' (RSA) to the list of known hosts.
oracle@vsi10devpom's password: 
orapwDB01sby1                                                                                     100% 1536     1.5KB/s   00:00    
[oracle@vsi08devpom dbs]$ rm /opt/app/oracle/product/11.2.0/db_1/dbs/orapwDB01sby1

Copy the tnsnames.ora file from the primary database machine to physical standby database

[oracle@vsi08devpom dbs]$ scp /opt/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora vsi10devpom:/opt/app/oracle/product/11.2.0/db_1/network/admin/
oracle@vsi10devpom's password: 
tnsnames.ora

- Listener and initialization parameter file configurations on the physical standby database:
Because you have to start your standby database on NO MOUNT mode you have to configure your listener.ora in the following way:

[oracle@vsi10devpom admin]$ vi /opt/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
   (SID_LIST=
       (SID_DESC =
         (SID_NAME = PLSExtProc)
          (ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1)
         (PROGRAM = extproc)
       )
       (SID_DESC=
          (GLOBAL_DBNAME=DB01SBY1)
          (ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1)
          (SID_NAME=DB01SBY1)
        )
    )

ADR_BASE_LISTENER = /opt/app/oracle

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi10devpom.MYDOMAIN.it)(PORT = 1521))
  )

and put only the following initialization parameters:

[oracle@vsi10devpom dbs]$ echo "db_name='DB01prmr'" > pfile_DB01prmr.ora
[oracle@vsi10devpom dbs]$ echo "db_unique_name='DB01sby1'" >> pfile_DB01prmr.ora
[oracle@vsi10devpom dbs]$ echo "db_block_size=8192" >> pfile_DB01prmr.ora
[oracle@vsi10devpom dbs]$ cat pfile_DB01prmr.ora
db_name='DB01prmr'
db_unique_name='DB01sby1'
db_block_size=8192

- Create the directory structures on the physical standby:
Under /opt/app/oracle ($ORACLE_BASE) create the audit trail directory, the flash recovery area directory
and the directory for the datafiles:

[oracle@vsi10devpom ~]$ cd $ORACLE_BASE 
[oracle@vsi10devpom oracle]$ ll
total 12
drwxr-xr-x 2 oracle oinstall 4096 Jun  6 15:40 checkpoints
drwxrwx--- 7 oracle oinstall 4096 Jun  6 15:34 oraInventory
drwxrwx--- 4 oracle oinstall 4096 Jun  6 14:41 product
[oracle@vsi10devpom oracle]$ mkdir -p admin/DB01sby1/adump
[oracle@vsi10devpom oracle]$ mkdir -p oradata/DB01sby1
[oracle@vsi10devpom oracle]$ mkdir -p flash_recovery_area/DB01sby1

- Start the listener on physical standby:

[oracle@vsi10devpom admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-JUL-2012 15:18:16

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

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

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /opt/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/vsi10devpom/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vsi10devpom.MYDOMAIN.it)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                10-JUL-2012 15:18:16
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/vsi10devpom/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vsi10devpom.MYDOMAIN.it)(PORT=1521)))
Services Summary...
Service "DB01sby1" has 2 instance(s).
  Instance "DB01sby1", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

- Set the ORACLE_SID env variable on the standby oracle user and start the standby-instance
Start up the physical standby database in NO MOUNT mode with the previuosly created initialization pfile (export ORACLE_SID=DB01sby1):

SQL> startup nomount pfile=$ORACLE_HOME/dbs/pfile_DB01prmr.ora

- Verify the connection 'AS SYSDBA' is working from and to both machines:

[oracle@vsi08devpom ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 12 13:10:54 2012

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

SQL> connect sys/oracle@DB01prmr as sysdba
Connected.
SQL> connect sys/oracle@DB01sby1 as sysdba
Connected.

[oracle@vsi10devpom ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 12 13:12:28 2012

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

SQL> connect sys/oracle@DB01prmr as sysdba
Connected.
SQL> connect sys/oracle@DB01sby1 as sysdba
Connected.

- Invoking the RMAN script:
From a rman command line on the primary database machine run the following script:

[oracle@vsi08devpom DB01prmr]$ rman target sys/oracle@db01prmr auxiliary sys/oracle@db01sby1

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 11 14:53:09 2012

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

connected to target database: DB01PRMR (DBID=2874530197)
connected to auxiliary database: DB01PRMR (not mounted)

Copy and paste your RMAN script. My script was the following:

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE nofilenamecheck
spfile
parameter_value_convert 'DB01prmr','DB01sby1'
set db_unique_name='DB01sby1'
set db_file_name_convert='/DB01prmr/','/DB01sby1/'
set log_file_name_convert='/DB01prmr/','/DB01sby1/'
set control_files='/opt/app/oracle/oradata/DB01sby1.ctl'
set log_archive_max_processes='5'
set fal_client='DB01sby1'
set fal_server='DB01prmr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(DB01prmr,DB01sby1)'
set log_archive_dest_2='SERVICE=DB01prmr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB01prmr';
}

And here is the output generated:

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=27 device type=DISK

allocated channel: prmy2
channel prmy2: SID=24 device type=DISK

allocated channel: stby
channel stby: SID=10 device type=DISK

Starting Duplicate Db at 11-JUL-12

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwDB01prmr' auxiliary format 
 '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwDB01sby1'   targetfile 
 '/opt/app/oracle/product/11.2.0/db_1/dbs/spfileDB01prmr.ora' auxiliary format 
 '/opt/app/oracle/product/11.2.0/db_1/dbs/spfileDB01sby1.ora'   ;
   sql clone "alter system set spfile= ''/opt/app/oracle/product/11.2.0/db_1/dbs/spfileDB01sby1.ora''";
}
executing Memory Script

Starting backup at 11-JUL-12
Finished backup at 11-JUL-12

sql statement: alter system set spfile= ''/opt/app/oracle/product/11.2.0/db_1/dbs/spfileDB01sby1.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest = 
 ''/opt/app/oracle/admin/DB01sby1/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers = 
 ''(PROTOCOL=TCP) (SERVICE=DB01sby1XDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''DB01sby1'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert = 
 ''/DB01prmr/'', ''/DB01sby1/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert = 
 ''/DB01prmr/'', ''/DB01sby1/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''/opt/app/oracle/oradata/DB01sby1.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes = 
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client = 
 ''DB01sby1'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server = 
 ''DB01prmr'' 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=(DB01prmr,DB01sby1)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 = 
 ''SERVICE=DB01prmr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB01prmr'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/opt/app/oracle/admin/DB01sby1/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=DB01sby1XDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB01sby1'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/DB01prmr/'', ''/DB01sby1/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/DB01prmr/'', ''/DB01sby1/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/opt/app/oracle/oradata/DB01sby1.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''DB01sby1'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''DB01prmr'' 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=(DB01prmr,DB01sby1)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''SERVICE=DB01prmr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB01prmr'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1686925312 bytes

Fixed Size                     2213976 bytes
Variable Size                989857704 bytes
Database Buffers             687865856 bytes
Redo Buffers                   6987776 bytes
allocated channel: stby
channel stby: SID=396 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/opt/app/oracle/oradata/DB01sby1.ctl';
}
executing Memory Script

Starting backup at 11-JUL-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB01prmr.f tag=TAG20120711T145336 RECID=5 STAMP=788367217
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JUL-12

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 tempfile  1 to 
 "/opt/app/oracle/oradata/DB01sby1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/DB01sby1/system01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/DB01sby1/sysaux01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/DB01sby1/undotbs01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/DB01sby1/users01.dbf";
   set newname for datafile  5 to 
 "/opt/app/oracle/oradata/DB01sby1/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/opt/app/oracle/oradata/DB01sby1/system01.dbf"   datafile 
 2 auxiliary format 
 "/opt/app/oracle/oradata/DB01sby1/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/opt/app/oracle/oradata/DB01sby1/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/opt/app/oracle/oradata/DB01sby1/users01.dbf"   datafile 
 5 auxiliary format 
 "/opt/app/oracle/oradata/DB01sby1/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /opt/app/oracle/oradata/DB01sby1/temp01.dbf 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 11-JUL-12
channel prmy1: starting datafile copy
input datafile file number=00002 name=/opt/app/oracle/oradata/DB01prmr/sysaux01.dbf
channel prmy2: starting datafile copy
input datafile file number=00001 name=/opt/app/oracle/oradata/DB01prmr/system01.dbf
output file name=/opt/app/oracle/oradata/DB01sby1/system01.dbf tag=TAG20120711T145343
channel prmy2: datafile copy complete, elapsed time: 00:00:45
channel prmy2: starting datafile copy
input datafile file number=00005 name=/opt/app/oracle/oradata/DB01prmr/example01.dbf
output file name=/opt/app/oracle/oradata/DB01sby1/sysaux01.dbf tag=TAG20120711T145343
channel prmy1: datafile copy complete, elapsed time: 00:00:52
channel prmy1: starting datafile copy
input datafile file number=00003 name=/opt/app/oracle/oradata/DB01prmr/undotbs01.dbf
output file name=/opt/app/oracle/oradata/DB01sby1/example01.dbf tag=TAG20120711T145343
channel prmy2: datafile copy complete, elapsed time: 00:00:07
channel prmy2: starting datafile copy
input datafile file number=00004 name=/opt/app/oracle/oradata/DB01prmr/users01.dbf
output file name=/opt/app/oracle/oradata/DB01sby1/undotbs01.dbf tag=TAG20120711T145343
channel prmy1: datafile copy complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/DB01sby1/users01.dbf tag=TAG20120711T145343
channel prmy2: datafile copy complete, elapsed time: 00:00:03
Finished backup at 11-JUL-12

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=788367279 file name=/opt/app/oracle/oradata/DB01sby1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=788367279 file name=/opt/app/oracle/oradata/DB01sby1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=788367279 file name=/opt/app/oracle/oradata/DB01sby1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=788367279 file name=/opt/app/oracle/oradata/DB01sby1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=788367279 file name=/opt/app/oracle/oradata/DB01sby1/example01.dbf
Finished Duplicate Db at 11-JUL-12
released channel: prmy1
released channel: prmy2
released channel: stby

- Starting redo apply (on the standby database):

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

Database altered.

On the primary database enable the transmission of redo log to the physical standby database;

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;                                 

System altered.

Redo log are transmitted after a log switch so you can anticipate it using the command ALTER SYSTEM SWITCH LOGFILE From primary database machine:

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

STATUS   ERROR
--------- -----------------------------------------------------------------
VALID

SQL> alter system switch logfile;

System altered.

Now check the alert logs on both machines. From primary alert log:

[oracle@vsi08devpom ~]$ cd /opt/app/oracle/diag/rdbms/db01prmr/DB01prmr/trace/
[oracle@vsi08devpom trace]$ tail -f alert_DB01prmr.log 
...
Wed Jul 11 15:54:20 2012
Starting background process SMCO
Wed Jul 11 15:54:20 2012
SMCO started with pid=26, OS id=2732 
Wed Jul 11 15:54:24 2012
Thread 1 advanced to log sequence 157 (LGWR switch)
  Current log# 1 seq# 157 mem# 0: /opt/app/oracle/oradata/DB01prmr/redo01.log
Wed Jul 11 15:54:24 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Jul 11 15:54:24 2012
Archived Log entry 20 added for thread 1 sequence 156 ID 0xab54ff95 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 157 for destination LOG_ARCHIVE_DEST_2
...

From physical standby database machine alert log, you can see the logs are arriving:

[oracle@vsi10devpom ~]$ cd /opt/app/oracle/diag/rdbms/db01sby1/DB01sby1/trace/
[oracle@vsi10devpom trace]$ tail -f alert_DB01sby1.log 
Wed Jul 11 15:54:24 2012
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Archived Log entry 5 added for thread 1 sequence 156 ID 0xab54ff95 dest 1:
Wed Jul 11 15:54:24 2012
Media Recovery Waiting for thread 1 sequence 157
Wed Jul 11 15:54:24 2012
RFS[12]: Assigned to RFS process 15681
RFS[12]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 2618
Primary database is in MAXIMUM PERFORMANCE mode
RFS[12]: Selected log 4 for thread 1 sequence 157 dbid -1420437099 branch 786034072
Recovery of Online Redo Log: Thread 1 Group 4 Seq 157 Reading mem 0
  Mem# 0: /opt/app/oracle/oradata/DB01sby1/standby_redo04.log
Wed Jul 11 15:54:44 2012
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Wed Jul 11 15:55:14 2012
RFS[13]: Assigned to RFS process 15686
RFS[13]: Identified database type as 'physical standby': Client is ARCH pid 2606

How to create a 11gR2 physical standby database using Active Database Duplication and virtual machines on Oracle VM 3.0.3: step 4

Previous steps are here:
step 1
step 2
step 3
So we have a running database on a machine and a simply Oracle database software installation on the second machine. My goal is to create a physical standby database on the second machine using the Active Database Duplication copying the live database over the network.

My dataguard environment will be formed by the following machines:
primary database machine: database unique name DB01prmr on vsi08devpom (hostname)
physical standby machine: database unique name DB01sby1 on vsi10devpom (hostname)
Both on release: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Let's start.
There are some steps to execute in order to prepare the primary database to be part of a data guard configuration.

- Enable Forced Logging
First step, recommended for physical and logical standby database, is to enable forced logging. In this way Oracle logs every changes on the database, even for those tables created with the NOLOGGING option. So on the primary database:

[oracle@vsi08devpom ~]$ sqlplus / as sysdba
SQL> set linesize 180
SQL> SELECT DBID, NAME, CREATED, LOG_MODE, OPEN_MODE, GUARD_STATUS, FORCE_LOGGING, FLASHBACK_ON
  2  FROM V$DATABASE;

      DBID NAME      CREATED   LOG_MODE     OPEN_MODE   GUARD_S FOR FLASHBACK_ON
---------- --------- --------- ------------ -------------------- ------- --- ------------------
2874530197 DB01PRMR  15-JUN-12 NOARCHIVELOG READ WRITE   NONE  NO  NO


SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT DBID, NAME, CREATED, LOG_MODE, OPEN_MODE, GUARD_STATUS, FORCE_LOGGING, FLASHBACK_ON
  2  FROM V$DATABASE;

      DBID NAME      CREATED   LOG_MODE     OPEN_MODE   GUARD_S FOR FLASHBACK_ON
---------- --------- --------- ------------ -------------------- ------- --- ------------------
2874530197 DB01PRMR  15-JUN-12 NOARCHIVELOG READ WRITE   NONE  YES NO

- Configuring STANDBY REDO LOG:

Next step is to create the STANDBY REDO LOGS on the primary database: they are used when the redo logs are shipped from the primary database to the standby and are recommended in the role reversal and required on synchronous transport mode at real time apply;

If configured on the primary database (as recommended), they are also created (automatically I mean) on the standby when you use the rman command DUPLICATE TARGET DATABASE, as I will do; each standby redo log must be as large as the largest redo log of the primary database and on the standby there should be at least one more redo log group than the primary database;

As you can see I have three groups (and yes I know... formed by a single member... but this is a test environment); every member has the same size and it's 50MB. So I need to add four standby redo log file of the same size.

SQL> Select group# "GROUP", thread# thread, sequence# sequence, 
  2  bytes/1024/1024 MB, members, archived, status, first_change# first_change, first_time
  3  from sys.v_$log order by group#, thread#;

     GROUP     THREAD SEQUENCE  MB    MEMBERS ARC STATUS     FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
  1     1      136  50      1 NO  INACTIVE   3937272 09-JUL-12
  2     1      137  50      1 NO  CURRENT   3969353 10-JUL-12
  3     1      135  50      1 NO  INACTIVE   3927184 09-JUL-12

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo04.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo05.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo06.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/opt/app/oracle/oradata/DB01prmr/standby_redo07.log' size 50m;

Database altered.

The following is my new configuration: three redo log groups, seven logfile member (four of them are STANDBY type), four standby redo log groups:

SQL> Select group# "GROUP", thread# thread, sequence# sequence,
  2  bytes/1024/1024 MB, members, archived, status, first_change# first_change, first_time
  3  from sys.v_$log order by group#, thread#;

     GROUP     THREAD SEQUENCE  MB    MEMBERS ARC STATUS     FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
  1     1      136  50      1 NO  INACTIVE   3937272 09-JUL-12
  2     1      137  50      1 NO  CURRENT   3969353 10-JUL-12
  3     1      135  50      1 NO  INACTIVE   3927184 09-JUL-12

SQL> set pagesize 999
SQL> col member format a60
SQL> select * from sys.v_$logfile;

    GROUP# STATUS  TYPE    MEMBER       IS_
---------- ------- ------- ------------------------------------------------------------ ---
  3    ONLINE  /opt/app/oracle/oradata/DB01prmr/redo03.log   NO
  2    ONLINE  /opt/app/oracle/oradata/DB01prmr/redo02.log   NO
  1    ONLINE  /opt/app/oracle/oradata/DB01prmr/redo01.log   NO
  4    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo04.log  NO
  5    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo05.log  NO
  6    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo06.log  NO
  7    STANDBY /opt/app/oracle/oradata/DB01prmr/standby_redo07.log  NO

7 rows selected.

SQL> select group#, dbid, thread#, sequence#, status from v$standby_log;

    GROUP# DBID            THREAD# SEQUENCE# STATUS
---------- ---------------------------------------- ---------- ---------- ----------
  4 UNASSIGNED          0  0 UNASSIGNED
  5 UNASSIGNED          0  0 UNASSIGNED
  6 UNASSIGNED          0  0 UNASSIGNED
  7 UNASSIGNED          0  0 UNASSIGNED

- Set Primary Database Initialization Parameters
Now it's time to set some initialization parameters on the primary database. Don't miss this step and set them correctly and with care.
Let's start with LOG_ARCHIVE_CONFIG: for each database in the dataguard environment it specifies with the DG_CONFIG attribute the unique database name list; it's used also to enable and disable sending and receiving of redolog. Sending and receiving redo log are enabled by default; in a physical standby scenario the database name is the same but not the unique database name. On a logical standby scenario the database names are usually different;

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB01prmr,DB01sby1)' scope=both;
System altered.

The LOG_ARCHIVE_DEST_n parameter controls the redo transport services and should include at least one of the following parameters: LOCATION which is a local path name or SERVICE which is an Oracle Net Service name to reference the standby database. I won't use LOCATION parameter, but I will use only the SERVICE one.
The attributes SYNC and ASYNC are used to specify the transport mode; when using SYNC every redo generated have to be received at the destination before the commit is executed, ASYNC is the default so I won't specify it on the alter system command; the attributes AFFIRM and NOAFFIRM are used to let the redo transport destination acknowledges it received the redo data after or before the redo data are written to the standby redo log. When using SYNC transport mode AFFIRM is the default, when using ASYNC, NOAFFIRM is the default; in my case so I will use ASYNC and NOAFFIRM;

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=DB01sby1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB01sby1' scope=both;
System altered.

The LOG_ARCHIVE_DEST_STATE_n parameter is used to specify the destination state. Possible values are: ENABLE and DEFER; I will enable it after have completed the standby setup.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both;                                
System altered.

The DB_LOST_WRITE_PROTECT parameter checks for data corruption on the primary database during redo transportations and on the standby database during redo apply. Default is NONE, so I willl set it to TYPICAL;

SQL> alter system set DB_LOST_WRITE_PROTECT=TYPICAL scope=both;
System altered.

The DB_FILE_NAME_CONVERT parameter is configured only on a physical standby database and if you have the datafiles in different paths. The same concept applies on
LOG_FILE_NAME_CONVERT parameter: configured only on a physical standby database and if you have the redo logs in different paths. My paths will differ just for the two unique database names.

SQL> alter system set db_file_name_convert='/DB01sby1/','/DB01prmr/' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/DB01sby1/','/DB01prmr/' scope=spfile;
System altered.

The default value of STANDBY_FILE_MANAGEMENT parameter is MANUAL: it means that when you add a datafile on the primary you have to manually add it on the standby... every standby databases!!! You can use AUTO to create it automatically but consider that some kind of ALTER commands are no more allowed on the standby. DataGuard broker configure it as AUTO: I'll do the same and then test it in my environment.

SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

The DB_UNIQUE_NAME parameter specifies a unique name for the database; during a reverse roles this parameter doesn't change. In my case DB_UNIQUE_NAME will be DB01prmr for the primary database and DB01sby1 for the standby database; the DB_NAME will be on both machines always DB01prmr

SQL> alter system set DB_UNIQUE_NAME=DB01prmr scope=spfile;
System altered.

FAL_SERVER and FAL_CLIENT specifies the Oracle Net service name where and to whom request missing archived redo log.

SQL> alter system set FAL_SERVER=DB01sby1 scope=both;
System altered.
SQL> alter system set FAL_CLIENT=DB01prmr scope=both;
System altered.

To specify the format of archived redo log use LOG_ARCHIVE_FORMAT parameter.

SQL> alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
System altered.

- Enable Archiving
Another important step to rpepare the primary database is to put it in archivelog mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size      2213976 bytes
Variable Size   1291847592 bytes
Database Buffers   385875968 bytes
Redo Buffers      6987776 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

We have not finished... it just continues on this next step

Pages

Thursday, July 12, 2012

How to create a 11gR2 physical standby database using Active Database Duplication and virtual machines on Oracle VM 3.0.3: step 5

How to create a 11gR2 physical standby database using Active Database Duplication and virtual machines on Oracle VM 3.0.3: step 4