This posting covers the steps to create a working Logical Standby configuration (Data Guard). This example uses Oracle 10g Release 2 on Linux.

You will need a sample database to use.

In my case, I will create a database named ldg, short for 'Logical Data Guard'.

If you don't already have one, create (and start) a listener for your sample database:

$ORACLE_HOME/network/admin/listener.ora

LISTENER_LDG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
      )
    )
 )

# lsnrctl start LISTENER_LDG

Also, add references for this listener as well as for the instance you are about to create in your networking file:

$ORACLE_HOME/networking/admin/tnsnames.ora

LISTENER_LDG =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
  )

LDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ldg.colestock.test)
    )
  )

Create a bare-bones init.ora file for your sample database:

$ORACLE_HOME/dbs/initldg.ora

*.db_name=ldg
*.db_domain=colestock.test
*.db_block_size=4096
*.service_names='ldg.colestock.test'
*.control_files=("/u01/app/oracle/oradata/ldg/control01.ctl")
*.sga_target=256M
*.pga_aggregate_target=64M
*.processes=100
*.background_dump_dest=/u01/app/oracle/admin/ldg/bdump
*.user_dump_dest=/u01/app/oracle/admin/ldg/udump
*.core_dump_dest=/u01/app/oracle/admin/ldg/cdump
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS1
*.undo_retention=3600
*.local_listener=LISTENER_LDG

Create a passwordfile for the database in question:

# orapwd file=$ORACLE_HOME/dbs/orapwldg password={password}

Append an entry to oratab:

# echo "ldg:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create the database by running the following script:

# export ORACLE_SID=ldg
#. oraenv 

create_ldg.sql

shutdown immediate;
startup nomount;
CREATE DATABASE
CONTROLFILE REUSE
MAXINSTANCES 32
MAXLOGHISTORY 10000
MAXLOGMEMBERS 5
MAXLOGFILES 64
DATAFILE '/u01/app/oracle/oradata/ldg/system01.dbf' SIZE 400M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/ldg/sysaux01.dbf' SIZE 400M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 5G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/ldg/temp01.dbf' SIZE 100M REUSE
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/ldg/undotbs1_01.dbf' SIZE 200M REUSE
CHARACTER SET UTF8
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ldg/redo_1a.dbf',
                 '/u01/app/oracle/oradata/ldg/redo_1b.dbf') SIZE 100M REUSE,
GROUP 2 ('/u01/app/oracle/oradata/ldg/redo_2a.dbf',
         '/u01/app/oracle/oradata/ldg/redo_2b.dbf') SIZE 100M REUSE;
exit;

# sqlplus "/ as sysdba" @create_ldg.sql

If successful, you should see the following output:

ORA-01507: database not mounted

ORACLE instance shut down.
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1261248 bytes
Variable Size              92275008 bytes
Database Buffers          167772160 bytes
Redo Buffers                7127040 bytes

Database created.

Create the Data Dictionary, etc:

create_dd.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catclust.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
exit;

# sqlplus "/ as sysdba" @create_dd.sql

Prepare the primary before creating the standby:

Enable Force Logging:

SQL> alter database force logging;

Create Standby Redo Log Groups:

SQL> alter database add standby logfile group 3 ('/u01/app/oracle/oradata/ldg/redo_3a.dbf','/u01/app/oracle/oradata/ldg/redo_3b.dbf') size 100M;

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ldg/redo_4a.dbf','/u01/app/oracle/oradata/ldg/redo_4b.dbf') size 100M;

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ldg/redo_5a.dbf','/u01/app/oracle/oradata/ldg/redo_5b.dbf') size 100M;

Create a spfile and recycle the database:

SQL> create spfile from pfile;

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

SQL> startup;

Alter necessary initialization parameters:

alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/ldg/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ldg' scope=spfile;
alter system set log_archive_dest_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby' scope=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set log_archive_format='ldg_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=stdby scope=spfile;
alter system set fal_client=ldg scope=spfile;
alter system set db_file_name_convert='/stdby/','/ldg/' scope=spfile;
alter system set log_file_name_convert='/stdby/','/ldg/' scope=spfile;
alter system set standby_file_management=auto scope=spfile; 

Enable Archive Logging:

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

SQL> startup mount;
ORACLE instance started.

SQL> alter database archivelog;

SQL> alter database open;

Create an RMAN backup which we will use later to create the standby:

# rman target=/

RMAN> backup full database format '/u01/app/oracle/oradata/ldg/backups/%d_%U.bckp' plus archivelog format '/u01/app/oracle/oradata/ldg/backups/%d_%U.bckp'; 

Next, create a standby controlfile backup via RMAN:

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/ldg/backups/%U';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

In this simple example, I am backing up the primary database to disk; therefore, I must make the backupsets available to the standby host if I want to use them as the basis for my duplicate operation:

# ls -lart
total 297072
drwxr-xr-x  4 oracle dba      4096 May  1 00:25 ../
-rw-r-----  1 oracle dba   8195072 May  1 00:30 LDG_01jf868f_1_1.bckp
-rw-r-----  1 oracle dba 255270912 May  1 00:30 LDG_02jf868h_1_1.bckp
-rw-r-----  1 oracle dba  20217856 May  1 00:30 LDG_03jf869a_1_1.bckp
-rw-r-----  1 oracle dba      3584 May  1 00:30 LDG_04jf869d_1_1.bckp
-rw-r-----  1 oracle dba  20185088 May  2 00:50 08jfarrc_1_1
drwxr-xr-x  2 oracle dba      4096 May  2 00:59 ./

# scp * oracle@192.168.1.102:/u01/app/oracle/oradata/ldg/backups/
oracle@192.168.1.102's password:
08jfarrc_1_1                                  100%   19MB  19.3MB/s   00:00
LDG_01jf868f_1_1.bckp                         100% 8003KB   7.8MB/s   00:01
LDG_02jf868h_1_1.bckp                         100%  243MB  27.1MB/s   00:09
LDG_03jf869a_1_1.bckp                         100%   19MB  19.3MB/s   00:01
LDG_04jf869d_1_1.bckp                         100% 3584     3.5KB/s   00:00

Before moving on to creating the standby, finish the steps on the primary by adding an entry to the primary's networking file for the standby instance we will create in the next steps.

$ORACLE_HOME/network/admin/tnsnames.ora

STDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stdby.colestock.test)
    )
  )

Now prepare the standby host to support a standby database:

If you don't already have one, create (and start) a listener for your database:

$ORACLE_HOME/network/admin/listener.ora

LISTENER_STDBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
      )
    )
 )

# lsnrctl start LISTENER_STDBY

Add references for this listener as well as for BOTH instances - the primary and the standby - into your networking file.

$ORACLE_HOME/networking/admin/tnsnames.ora

LISTENER_STDBY =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
  )

LDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ldg.colestock.test)
    )
  )

STDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stdby.colestock.test)
    )
  )

Create the initial init.ora file for the standby by copying the primary's and then editing key parameters as appropriate:

SQL> create pfile='/tmp/initstdby.ora' from spfile;

File created.

# scp initstdby.ora oracle@192.168.1.102:/u01/app/oracle/product/10.2/dbs/

After editing the parameter file, it should resemble the following:

$ORACLE_HOME/dbs/initstdby.ora

*.background_dump_dest='/u01/app/oracle/admin/stdby/bdump'
*.cluster_database=FALSE
*.control_files='/u01/app/oracle/oradata/stdby/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/stdby/cdump'
*.db_block_size=4096
*.db_domain='colestock.test'
*.db_file_name_convert='/ldg/','/stdby/'
*.db_name='ldg'
*.db_unique_name='stdby'
*.fal_client='STDBY'
*.fal_server='LDG'
*.local_listener='LISTENER_STDBY'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/stdby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=ldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ldg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='stdby_%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='/ldg/','/stdby/'
*.pga_aggregate_target=64M
*.processes=100
*.service_names='stdby.colestock.test'
*.sga_target=256M
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.undo_retention=3600
*.user_dump_dest='/u01/app/oracle/admin/stdby/udump'

Create a passwordfile for the standby:

# orapwd file=$ORACLE_HOME/dbs/orapwstdby password={password}

Append an entry to oratab:

# echo "stdby:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Nomount the standby instance in preparation for the duplicate operation:

SQL> startup nomount;

Create the necessary RMAN duplicate script:

create_standby.rman

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

Create the standby by running the aforementioned script:

rman target=sys/{password}@ldg auxiliary=/ @create_standby.rman

Create an spfile and mount the standby with it:

SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup mount;

Start the redo apply:

SQL> alter database recover managed standby database disconnect from session;

Test the configuration by generating archive logs from the primary and then querying the standby to see if the logs are being successfully applied.

On the Primary:

SQL> alter system switch logfile;
SQL> alter system archive log current;

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/ldg/arch/
Oldest online log sequence     13
Next log sequence to archive   14
Current log sequence           14

On the Standby:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
  2  ORDER BY SEQUENCE#;

 SEQUENCE# APP
---------- ---
         9 YES
        10 YES
        11 YES
        12 YES
        13 YES

Transition the physical standby to a logical standby

Stop the managed recovery process on the standby:

SQL> alter database recover managed standby database cancel;

Prepare the primary for the role transitions by altering archive destinations as appropriate:

SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/ldg/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=ldg' scope=both;

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/ldg/arch/ valid_for=(online_logfiles,all_roles) db_unique_name=ldg' scope=BOTH;

SQL> alter system set log_archive_dest_state_3=enable scope=both;

Build the LOGMNR dictionary on the primary:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

Convert the physical standby to logical standby:

SQL> alter database recover to logical standby stdby;

Recreate the passwordfile on the standby:

# cd $ORACLE_HOME/dbs
# rm orapwstdby
# orapwd file=$ORACLE_HOME/dbs/orapwstdby password={password}

Prepare the standby for the role transitions by altering archive destinations as appropriate:

SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/stdby/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=stdby' scope=both;
SQL> alter system set log_archive_dest_state_3=enable scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/stdby/arch/ valid_for=(online_logfiles,all_roles) db_unique_name=stdby' scope=both;

Create standby redo logs on the standby:

SQL> alter database add standby logfile group 3 ('/u01/app/oracle/oradata/stdby/redo_3a.dbf','/u01/app/oracle/oradata/stdby/redo_3b.dbf') size 100M;

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/stdby/redo_4a.dbf','/u01/app/oracle/oradata/stdby/redo_4b.dbf') size 100M;

SQL>  alter database add standby logfile group 5 ('/u01/app/oracle/oradata/stdby/redo_5a.dbf','/u01/app/oracle/oradata/stdby/redo_5b.dbf') size 100M;

Start the logical standby:

SQL> shutdown immdiate;
SQL> startup mount;
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;

Once converted to a logical standby, the file conversion parameters no longer work as they do for physical standbys. You can create a handler that performs the equivalent file path replacement:

create_df_skip_handler.sql

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

CREATE OR REPLACE PROCEDURE sys.handle_tbs_ddl (

  old_stmt  IN  VARCHAR2,
  stmt_typ  IN  VARCHAR2,
  schema    IN  VARCHAR2,
  name      IN  VARCHAR2,
  xidusn    IN  NUMBER,
  xidslt    IN  NUMBER,
  xidsqn    IN  NUMBER,
  action    OUT NUMBER,
  new_stmt  OUT VARCHAR2
) AS

BEGIN

  new_stmt := replace(old_stmt, '/ldg/', '/stdby/');

  action := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;

EXCEPTION
  WHEN OTHERS THEN
    action := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
    new_stmt := NULL;
END handle_tbs_ddl;
/

EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', proc_name => 'SYS.HANDLE_TBS_DDL');

SHOW ERRORS;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Run the aforementioned script:

# sqlplus "/ as sysdba" @create_df_skip_handler.sql

Test the newly created handler by performing a tablespace datafile operation on the primary, then check to see whether the operation is properly converted on the standby.

On the primary:

SQL> create tablespace users datafile '/u01/app/oracle/oradata/ldg/users01.dbf' size 100M segment space management auto;

On the standby:

SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;

You should see an entry similar to the following if the replace handler worked:

ORA-16202: Skip procedure requested to replace statement
create tablespace users datafile '/u01/app/oracle/oradata/stdby/users01.dbf' siz

Of course you can check the file system and/or the data dictionary of the standby as well:

# ls -lart /u01/app/oracle/oradata/stdby/users01.dbf
-rw-r-----  1 oracle dba 104861696 May  2 13:14 /u01/app/oracle/oradata/stdby/users01.dbf

SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/stdby/users01.dbf

At this point you should have a working logical dataguard configuration. Confirm via the following queries:

SQL>  SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
----------- ---------- ---------- -----------
     355897     356318     355898      260765

SQL>  SELECT APPLIED_TIME, LATEST_TIME, MINING_TIME, RESTART_TIME FROM V$LOGSTDBY_PROGRESS;

APPLIED_TIME      LATEST_TIME       MINING_TIME       RESTART_TIME
----------------- ----------------- ----------------- -----------------
08-05-02 15:20:56 08-05-02 15:21:13 08-05-02 15:20:56 08-05-02 14:20:26

SQL> SELECT * FROM V$LOGSTDBY_STATS;

NAME                             VALUE
-------------------------------- --------------------------------
number of preparers              1
number of appliers               5
maximum SGA for LCR cache        30
parallel servers in use          9
maximum events recorded          100
preserve commit order            TRUE
transaction consistency          FULL
record skip errors               Y
record skip DDL                  Y
record applied DDL               N
record unsupported operations    N
coordinator state                IDLE
transactions ready               47071
transactions applied             47071
coordinator uptime               352
realtime logmining               Y
apply delay                      0
Log Miner session ID             1
txns delivered to client         48774
DML txns delivered               47487
DDL txns delivered               17
CTAS txns delivered              0
Recursive txns delivered         1270
Rolled back txns seen            11
LCRs delivered to client         194009
bytes of redo processed          44836100
bytes paged out                  0
seconds spent in pageout         0
bytes checkpointed               0
seconds spent in checkpoint      0
bytes rolled back                0
seconds spent in rollback        0
seconds system is idle           0

33 rows selected.

SQL>  SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID STATE
---------- ----------------
         1 APPLYING

Labels: 10g, Data Guard

In order to tune the Interconnect for your RAC cluster, Oracle provides a diagnostic script; you can find this script in the following Metalink Note: 135714.1

In the event that you don't have access to Metalink; here is the script and an example log file from a run against my 2-node, 10.2.0.3 RAC cluster on Enterprise Linux:

racdiag.sql (Rac Diagnostic Script)

Example racdiag.sql log file

In regards to how the Interconnect is performing, the aforementioned script runs a couple of key queries; here are a couple of the more important ones:

-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.  
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds depending 
-- on your system configuration and volume, is the average latency of a 
-- consistent-read request round-trip from the requesting instance to the holding 
-- instance and back to the requesting instance. If your CPU has limited idle time 
-- and your system typically processes long-running queries, then the latency may 
-- be higher. However, it is possible to have an average latency of less than one 
-- millisecond with User-mode IPC. Latency can be influenced by a high value for 
-- the DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process 
-- can issue more than one request for a block depending on the setting of this 
-- parameter. Correspondingly, the requesting process may wait longer.  Also check
-- interconnect badwidth, OS tcp settings, and OS udp settings if 
-- AVG CR BLOCK RECEIVE TIME is high.
--
set numwidth 20
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED", 
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id 
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;

-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time. 
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds.  the elapsed 
-- time for a get includes the allocation and initialization of a new global 
-- enqueue. If the average global enqueue get (global cache get time) or average 
-- global enqueue conversion times are excessive, then your system may be 
-- experiencing timeouts.  See the 'WAITING SESSIONS', 'GES LOCK BLOCKERS', 
-- 'GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM' sections if the 
-- AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS", 
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;

-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM.  If the 
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which could
-- cause a DLM hang.  Make sure that you also have enough TCKT_AVAIL.  
--
set numwidth 5
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;

Oracle also maintains a RAC survival guide - which is quite helpful - here: 203226.1

Labels: 10g, RAC, Tuning

Duplicating a RAC database via RMAN can be a bit tricky. In essence, it is the same as duplicating a single instance to a single instance with a couple of twists.

In my example, I am cloning a 2 instance 10gR2 (10.2.0.3) database to another 10gR2(10.2.0.3) on the same server. In reality, I am cloning a template database that will become my RMAN repository. Therefore, in this example, I will be using a disk backup without an RMAN repository. In order to duplicate a RAC database (RAC to RAC), you need to follow these basic steps:

1.) Create a password file for the new database you wish to create (the auxiliary)

2.) Create a new parameter file for your new database and REMOVE all RAC-centric (multiple-instance) parameters. In effect, you are duplicating to a single instance and then make it a clustered database afterwards.

3.) Create a static listener entry for your new database and reload the listener as appropriate. This step is necessary, because an RMAN duplicate uses an auxiliary database that is in no mount and therefore will not automatically register itself with the listener(s) as specified by local_listener, remote_listener and or the default, 1521.

For example:

      (SID_DESC =   
      (GLOBAL_DBNAME = rman.colestock.test)    
      (ORACLE_HOME = /u03/app/oracle/product/db/10.2)    
      (SID_NAME = rman2) 
      )

4.) Connect to the auxiliary database and put it in no mount

5.) Change the RMAN configuration of the target database to reflect a location for a disk backup:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/%U';

and then take a full backup - including archivelogs - via:

backup database plus archivelog;

The DUPLICATE operation uses this backup, including a connection to the TARGET database in order to create the auxiliary

6.) Create an RMAN script file - similar to the following - that renames files as appropriate:

run {
allocate auxiliary channel ch1 device type disk format '/u03/%U';
set newname for tempfile 1 to '/u02/app/oradata/rman/temp01.dbf';
configure auxname for datafile 1 to '/u02/app/oradata/rman/system01.dbf';
configure auxname for datafile 2 to '/u02/app/oradata/rman/undotbs01.dbf';
configure auxname for datafile 3 to '/u02/app/oradata/rman/sysaux01.dbf';
configure auxname for datafile 4 to '/u02/app/oradata/rman/users01.dbf';
configure auxname for datafile 5 to '/u02/app/oradata/rman/undotbs02.dbf';
duplicate target database to "rman"
  logfile
    group 1 ('/u02/app/oradata/rman/redo01a.log',
             '/u02/app/oradata2/rman/redo01b.log') size 50M reuse,
    group 2 ('/u02/app/oradata/rman/redo02a.log',
             '/u02/app/oradata2/rman/redo02b.log') size 50M reuse;
}

Notice how the auxiliary channel points to where the disk backup is.

7.) In order to avoid 'Internal Bug 4355382 ORA-38856: FAILED TO OPEN DATABASE WITH RESETLOGS WHEN USING RAC BACKUP,' set the following initialization parameter on the auxiliary; shutdown immediate; and then put the database back into no mount:

_no_recovery_through_resetlogs=TRUE

8.) Run the aforementioned script to duplicate the database:

rman target=/ auxiliary=sys/password@rman2

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Sep 24 14:40:29 2007

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

connected to target database: CUBS (DBID=2121269038)
connected to auxiliary database: RMAN (not mounted)

RMAN> @duplicate_cubs.rman

9.) If Step 8 is successful, then turn the auxiliary database into a RAC database by adding back all the necessary parameters, such as cluster_database, cluster_instances, thread, local_listener, etc.

10.) Add the second thread of online redo logs and enable that thread; startup the second instance:

SQL> alter database add logfile thread 2 group 3 ('/u02/app/oradata/rman/redo03a.log',
 '/u02/app/oradata2/rman/redo03b.log') size 50m reuse;

Database altered.

SQL>  alter database add logfile thread 2 group 4  ('/u02/app/oradata/rman/redo04a.log',
 '/u02/app/oradata2/rman/redo04b.log') size 50m reuse;

SQL> alter database enable public thread 2;

Database altered.

SQL> startup;

11.) Optionally, add the new database to srvctl:

srvctl add database -d rman -o /u03/app/oracle/product/db/10.2
srvctl add instance -d rman -i rman2 -n rac2
srvctl add instance -d rman -i rman1 -n rac1

Labels: 10g, RAC, RMAN

I've put together a guide with step by step instructions for installing 10g on Solaris; specifically, my Ultra 60 with Solaris 9 '05. Feel free to contact me with any questions and/or comments:

Installing Oracle on Solaris Step by Step Guide

Labels: 10g, Installation

One of the newest weapons in the Oracle DBA's high-availability arsenal is Oracle's Streams functionality. Streams allows for the propagation of data between multiple database systems at the table, schema, and/or database levels. Complex business rules - including transformations and the like - can be achieved using the product. One of the most common uses I have seen is the use of a bidirectional model in which, one or more databases (with the same structures) can be updated simultaneous and changes from all are propagated universally.

Having said that, first things first. I have created an example (contained within a zip file, consisting of scripts and batch files, etc.), which will build 2 - 10g Release 1 databases and after words create a simple, unidirectional Streams implementation. From this baseline, more complicated implementations can be derived.

The following example assumes the following:

• You are on an Oracle-supported, Windows platform, which has the 10g Release 1 Database software installed.



• You have adequate memory and disk space for the 2 databases, which the script creates - a powerful laptop with a 1 GB of memory and a couple of free GBs of disk space should do.
  

To get started, save the zip archive in question locally (Note: Right-click 'Save Target As'):
10GR1_Unidirectional_Streams.zip

There are 3 batch files whose configuration sections will need to be modified:
drop_create_databases.bat; setup_streams.bat, and remove_streams.bat. Change the following 4 environment variables in each: DB_DOMAIN; ORACLE_HOME (path to Oracle 10g Release 1 software home); ORACLE_LISTENER_SERVICE (name of the listener service created during the software installation process); LSNR_PORT (a valid listening port for automatic service registration of the instances), and DIR_PATH (the directory location, under which the database files as well as configuration files will be kept).

Once the aforementioned environment variables have been set appropriate for your installation - and assuming that the prerequisites described earlier are met - then you are ready to install the databases, set up the streams implementation, and test. Do so by running the setup.bat batch file. This will do all the work and might take up to half an hour to complete, so be patient.

After running the setup, check the test_streams.log log file to see the results. A successful run should
contain the following:

DECODE(TOTAL_BYTES,0,'P                                                         
-----------------------                                                         
PROPAGATION WORKING                                                             

DECODE(COUNT(*)                                                                 
---------------                                                                 
DML WORKING                                                                     

DECODE(COUNT(*)                                                                 
---------------                                                                 
DDL WORKING

The next step is to make this configuration bidirectional.

Save the zip archive in question locally (Note: Right-click 'Save Target As'):
10GR1_Bidirectional_Streams.zip and once again update the values of the environment variables listed prior. Once accomplished, you can make the previous implementation bidirectional by executing the make_streams_bidirectional.bat.

After running the batch file, check the test_streams_bi.log log file to see the results. A successful run should
contain the following:

DECODE(TOTAL_BYTES,0,'P                                                         
-----------------------                                                         
PROPAGATION WORKING                                                             

DECODE(COUNT(DB DBNAME                                                          
--------------- --------------------                                            
DML WORKING     TEST_BI                                                         

DECODE(COUNT(CO TABLE_NAME                     COLUMN_NAME                      
--------------- ------------------------------ ------------------------------   
OWNER                                                                           
------------------------------                                                  
DDL WORKING     HEARTBEAT_TBL                  I_WORK                           
HEARTBEAT                                                                                                

Labels: 10g, Streams