Duplicate a RAC database without connecting to it but using only its backups.

Title : 
Duplicate a RAC database without connecting to it but using only its backups.

Comment: 
It's never to late to learn new stuff, let's get started, shall we ? 

Following you'll find a complete procedure to duplicate a RAC database using only a RMAN backupset and what's more how to add its instances to a RAC cluster already created. Each stuff will be done manually.

Be careful : 
1) Duplicate process could overwrite online log files of the source database.
2) If present disable encryption before duplicate on the source database.
3) if present disable block tracking option on the source database.


Procedure steps :

1) Take a backup of the spfile, controlfile, database, and archivelogs as follows:
   
export ORACLE_SID=ISTANCE1
       
RMAN> connect target /
connected to target database: ISTANCE1 (DBID=9999999)
   
RMAN> backup spfile;
Starting backup at 02-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=ISTANCE1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-DEC-15
channel ORA_DISK_1: finished piece 1 at 02-DEC-15
piece handle=/orafra/ISTANCE1/backupset/2015_12_02/o1_mf_nnsnf_TAG20151202T100902_c5xf9h96_.bkp tag=TAG20151202T100902 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-DEC-15
Starting Control File and SPFILE Autobackup at 02-DEC-15
piece handle=/orafra/ISTANCE1/autobackup/2015_12_02/o1_mf_s_897386944_c5xf9kt7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-DEC-15

RMAN> backup database include current controlfile plus archivelog ;


2) Create a password file for the auxiliary instance:    

Copy the password file from the target database to $ORACLE_HOME/dbs and rename it.
  
cd $ORACLE_HOME/dbs
cp orapwINSTANCE1 orapwCLONE1

3) Drop any previous created clone database:
  
export ORACLE_SID=CLONE1
tail -f /u01/app/oracle/diag/rdbms/CLONE1/CLONE1/trace/alert_CLONE1.log
sqlplus / as sysdba
SQL> shutdown immediate;

SQL> startup pfile='/home/oracle/dbascript/pfileCLONE1.ora' mount restrict;
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
CLONE1

SQL> drop database;
 

4) Verify that all files have been removed from ASM diskgroup:

ASMCMD> cd DG_DBF/
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
Y    CLONE/

Then create the directory for the CLONE database:
  
ASMCMD> mkdir clone
ASMCMD> cd clone

Below I'll explain why that directory is required.
ASMCMD> mkdir instance1

5) Create a initialization parameter for the auxiliary instance with the following parameters:

vi /home/oracle/dbascript/pfileCLONE.ora

DB_NAME=CLONE
db_unique_name=CLONE
control_files='+DG_DBF/clone/control1.ctl'
db_file_name_convert='+DG_DBF','+DG_DBF/clone'
log_file_name_convert='+DG_LOG1','+DG_DBF/clone'

The first value of the parameter db_file_name_convert and log_file_name_convert comes up from the following queries:

select name from v$datafile;
select member from v$logfile;

6) Due to the following Bug 11063122 RMAN "DUPLICATE" RESTORES AN AUXILIARY CONTROLFILE WITH PRIMARY REDOLOGS, it's better to shutdown the source database:

export ORACLE_SID=INSTANCE1
[oracle@test ]$ srvctl stop instance -d INSTANCE -i INSTANCE1
[oracle@test ]$ srvctl stop instance -d INSTANCE -i INSTANCE2

6) Duplicate the database executing the following commands:
export ORACLE_SID=CLONE1
    
sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/dbascript/pfileclone.ora';
SQL> create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileclone.ora' from  pfile='/home/oracle/dbascript/pfileclone.ora';
shutdown immediate;
SQL> startup nomount

Startup Log :

System parameters with non-default values:
control_files                  = "+DG_DBF/clone/control1.ctl"
db_file_name_convert     = "+DG_DBF"
db_file_name_convert     = "+DG_DBF/clone"
log_file_name_convert    = "+DG_LOG1"
log_file_name_convert    = "+DG_DBF/clone"
db_name                      = "CLONE"
db_unique_name            = "CLONE"

Execute RMAN command and connect as auxiliary

rman auxiliary /
duplicate database to clone  backup location '/orafra/INSTANCE';

It's required to specify the directory location of the source DB backup ('/orafra/INSTANCE')

WARNING : OK

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

Log restore controlfile: OK

Starting restore at 02-DEC-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=149 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DG_DBF/clone/control1.ctl
Finished restore at 02-DEC-15

Log restore datafiles : OK

Full restore complete of datafile 31 to datafile copy +DG_DBF/clone/datafile/tbs1381.897390253.  Elapsed time: 0:04:13
Full restore complete of datafile 100 to datafile copy +DG_DBF/clone/datafile/tbs2.378.897390263.  Elapsed time: 0:05:55
ecc...

Log files restore: OK

Online log +DG_DBF/clone/onlinelog/group_4.417.897403979: Thread 2 Group 4 was previously cleared
Online log +DG_DBF/clone/onlinelog/group_5.465.897403985: Thread 2 Group 5 was previously cleared
Online log +DG_DBF/clone/onlinelog/group_6.400.897403989: Thread 2 Group 6 was previously cleared
Online log +DG_DBF/clone/onlinelog/group_7.408.897404005: Thread 1 Group 7 was previously cleared
Online log +DG_DBF/clone/onlinelog/group_8.451.897404011: Thread 1 Group 8 was previously cleared
Online log +DG_DBF/clone/onlinelog/group_9.392.897404017: Thread 1 Group 9 was previously cleared

Temp file : OK
Re-creating tempfile +DG_DBF as +DG_DBF/clone/tempfile/temp_rac.488.897392673

Finished Duplicate Db at 02-DEC-15


7) Restart the source Instances:
 
[oracle@test ]$ srvctl start instance  -d INSTANCE -i INSTANCE1
[oracle@test ]$ srvctl start instance  -d INSTANCE -i INSTANCE2

8) Start the cloned instance: CLONE1:
 
SQL> startup nomount pfile='/home/oracle/dbascript/pfileCLONE1.ora';
ORACLE instance started.

8) Verify the cloned instance:

Logfiles : MEMBER
--------------------------------------------------------------------------------
+DG_DBF/clone/onlinelog/group_9.487.897392667
+DG__DBF/clone/onlinelog/group_8.486.897392663
+DG_DBF/clone/onlinelog/group_7.485.897392655
+DG_DBF/clone/onlinelog/group_4.482.897392633
+DG_DBF/clone/onlinelog/group_5.483.897392637
+DG_DBF/clone/onlinelog/group_6.484.897392641

Datafiles : NAME                                                                            
--------------------------------------------------------------------------------
+DG_DBF/clone/datafile/system.414.897402601
+DG_DBF/clone/datafile/tbs1381.897390253
+DG_DBF/clone/datafile/tbs2.378.897390263

ControlFile : NAME                                                                            
------- -----------------------------------------------------------------------
+DG_DBF/clone/control1.ctl                                             

Tempfile: NAME                                                                            
--------------------------------------------------------------------------------
+DG_DBF/clone/tempfile/temp_rac.488.897392673                           


That's all, the instance has been cloned and it's online.

The last step is to add it to the RAC...

Byeeee....

References :
RMAN 11GR2 : DUPLICATE Without Target And Recovery Catalog Connection (Doc ID 874352.1)
How to avoid a failed duplicate to overwrite the online redologs of the target Database? (Doc ID 1282984.1).
This has been taken care of in 11.2.0.2 onwards as discussed in Bug 11063122 RMAN "DUPLICATE" RESTORES AN AUXILIARY CONTROLFILE WITH PRIMARY REDOLOGS

HOW TO REPLICATE ROWS FROM ORACLE DATABASE TO IQ SYBASE DATABASE - Part 2

Before going ahead, now let’s look at the SYBASE RTL Server quickly , shall we ?

The Replication Server Real-Time Loading Edition (RTLE) is a special edition of SYBASE Replication Server features and includes components necessary to replicate to a Sybase IQ database from Adaptive Server (ASE) database  or Oracle to Sybase IQ.

It uses many asynchronous thread to communicate with and transfer data to and from the target and the source data server.

RTLE is made up of Replication Server and what’s more Replication Agent for Oracle dealt with before.

Now let’s look at SYBASE IQ database, shall we ?

SYBASE IQ database is  a column based database that has been built to hold large amount of data at a low cost and high availability environment. It stores data that you see as tables in columns of data rather than as rows data.

Doing so Sysbase IQ is extremely quick at searching and data retrieval on large amount of data.


Looking at the following web site you’ll find lots of handy stuff: 

http://infocenter.sybase.com/help/index.jsp

Now let’s get down to replicate Oracle data rows to IQ database , shall we ?

To make it clearer I’m going to explain only how carry out the replication process of an Oracle table to IQ table, without explain how to install or start for example the RAO Agent , RTL Server or IQ database too. 

By the way I’ll do so soon.

Replication process step by step :

1)    Create a table on Oracle database

CREATE TABLE TEST_REPLIC (campo1 VARCHAR2(100));
 
2)    Create the same table on IQ :

CREATE TABLE TEST_REPLIC (campo1 varchar(100) null)
 
3)    Create a replication definition on RTL Replication Server

create replication definition rep_ora_iq.TEST_REPLIC
with primary at ORACLE_DB.ORACLE_SID
with all tables named TEST_REPLIC
(campo1 varchar(100) null)
primary key(campo1)
replicate all columns
go



4)    Create a subscription definition on RTL Replication Server

create subscription sub_ora.TEST_REPLIC
for rep_ora_iq.TEST_REPLIC
with replicate at IQ_DB.IQ_DB
without materialization
go

5)    Mark the table on the RAO Instance Agent

pdb_setreptable TEST_REPLIC  ,mark,owner
go
   
pdb_setreptable TEST_REPLIC 
go

Name                  Marked  Enabled  Replicate                    Send Owner Shadow Table Shadow Row
--------------------- ------- -------- ---------------------------- ---------- ------------ ----------
TEST_REPLIC    marked  enabled  TEST_REPLIC      enabled    N/A          N/A

 
6)    Insert a row into Oracle and check if it has been replicated into IQ database.


I know it’s too simple and lots of other stuff has to be written but it’s only the beginning of our trip into SYBASE product.

See you in my next blog

HOW TO REPLICATE ROWS FROM ORACLE DATABASE TO IQ SYBASE DATABASE - Part 1

Every now and again It is necessary to work with lots of heterogeneous systems and what’s more being able to replicate rows among them.

Today I’m going to look at how oracle database rows  can be replicated on IQ SyBASE database.

First of all it is necessary to recap all the components. Let’s get started, shall we ?

1)    Oracle Database
2)    SYBASE RAO Agent
3)    SYBASE RTL Server
4)    SYBASE IQ database.

Everyone knows Oracle database, so I’m going to look at the second point, the  SYBASE RAO Agent.

The Replication Agent (RAO Agent) is a Java-based application that requires a Java Runtime Environment (JRE), JDBC driver for Oracle database and it is necessary to install it on the Oracle Database Server Machine too.

Each Replication Agent Java program uses an embedded Replication Agent System Database (RASD) to manage its stuff, I’ve been writing on it lately and you can find some handy information about it in my previous blog. 

Let’s see how it works, shall we ?

Using a Log Transfer Language (LTL), RAO Agent reads the primary Oracle database transaction log (archived logs),  and  transfers log records for replicated table to the Replication Server (RTL) , then it converts  and forwards LTL log records to the remote site (SYBASE IQ Database). 

To be continued....

SWITCH DATABASE TO COPY

In the previous post we  looked at the incremental image copy backup, today it’ time to try the recovery process out.    

There are two different ways to do so, let’s see how, shall we ?

1)Using the conventional restore and recovery process, where you have to restore the datafiles copy to the original path and what’s more recover them using the level 1 image copy backup and archived logs too.

2) Using the  ‘switch database to copy’ command where you don’t need to restore any datafiles because, they are already available in the backup directory.

It is only required to apply on them the level 1 image copy and all the archived logs available.

If it is necessary to recover the database immediately, it is the best way to do so.

Let’s get started, shall we ?

1)    Startup the database in a MOUNT state :

SQL > startup mount

2)    Execute the following command :

RMAN> switch database to copy;

Short log file:

Using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/backup/datafile/o1_mf_system.dbf"
datafile 2 switched to datafile copy "/backup/datafile/o1_mf_undotbs1.dbf"
ecc
 
As you can verify on Oracle view, now the datafiles path are different, indeed our instance has started to work with the image copy datafiles backup.

SQL> select NAME from v$datafile;
NAME
--------------------------------------------------------------------------------
/backup/datafile/o1_mf_system_.dbf
/backup/datafile/o1_mf_undotbs1..dbf
Ecc

Also the Alert log files can confirm it as you can see below :

WARNING: switching recovery area datafile copy
/backup/datafile/ o1_mf_system.dbf" as database area datafile 

3)    Now it is time to recover those image copy datafiles :

RMAN > recover database;

Log :

Starting recover at 13-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=236 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /backup/datafile/o1_mf_system_.dbf

As I mentioned  before RMAN is going to apply the last level 1 image copy backup and archived logs too (if available).

4)    Open the database and keep your fingers crossed !!!

SQL> alter database open;

All those steps end up starting the database infact we ‘ve been able to figure the problem out and what’s more in a short time.

Well done ! 

Remember to try it out on a test environment and please redo a complete database backup , since the previous one is now online and no longer available to restore it.

Byeeeee

INCREMENTAL IMAGE COPY BACKUP

Every now and again it is necessary to speed up the recovery phase of a Oracle Database and what’s more to reduce the time of a backup, for example when  it can’t be completed during the night because it’s too big.

To achieve the first request you can execute a RMAN image copy backup and an incrementally updated image copy backup to achieve the second request too.

Let’s see how, shall we ?

• Execute the RMAN command and connect it to the database as sysdba :

RMAN > connect target /

• Execute the following command :

RMAN > RUN {

BACKUP device type disk INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'test_incr_backup' DATABASE;

RECOVER COPY OF DATABASE WITH TAG 'test_incr_backup';

}

If the level 0 image copy doesn’t exist,RMAN creates it, following you’ll find a short log file:

Starting backup at 08-OCT-15

using channel ORA_DISK_1

no parent backup or copy of datafile 2 found

no parent backup or copy of datafile 100 found

no parent backup or copy of datafile 98 found

input datafile fno=00002 name=/u01/undotbs01.dbf

ecc

Since the first image copy doesn’t exist, RMAN can’t produce a level 1 backup set and what’s more the RECOVER phase doesn’t start too.

NB : The RECOVER phase can only get started  if a level 1 incremental image copy backup is found.

NB : The backup of the first IMAGE COPY can take a very long time especially if your 

       database is really huge, so be careful !!

•  Execute again the previous command :

RMAN > RUN {

BACKUP device type disk INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'test_incr_backup' DATABASE;

 RECOVER COPY OF DATABASE WITH TAG 'test_incr_backup';

}

Since we’ve already got a level 0 image copy, the second execution of the backup command is going to produce our first level 1 incremental image copy.

Following you’ll find a short log file :

Starting backup 08-OCT-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00100 name=data.dbf

channel ORA_DISK_1: starting piece 1 at 08-OCT-15 channel ORA_DISK_1: finished piece..

Since our first level 1 incremental image copy is now ready, the RECOVER command can now apply that on the first level 0 image copy and forward it up to now.

Following you’ll find a short log file :

Starting recover at 09-OCT-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy fno=00002 name=/undotbs1_.dbf

Be careful : Performance issues  come up if the block change tracking isn’t available during the incremental image backup.  So it’s necessary to enable it, executing the following command :

If OMF is enabled :

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Otherwise :

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/PATH/FILE.TRACK.F’;

Those commands end up enabling the block change tracking and what’s more tracking changed block.

You’ll try, won’t you ?

Remember to try it out on a test environment and good luck ! 

Byeeeeeeeee

SyBASE Replication Agent System Database (RASD) got corrupted

Every now and again problems crop up leaving us with few possibilities to figure it out, this can happen also at work, where you have to come up with a solution immediately.

Let me explain how, shall we ?

Due to system hung we had to reboot a production database server, which was made up of an Oracle Database and what’s more a Sybase Replication Agent (RAO) which replicates  Oracle data rows through a Real-Time loading Server to a Sybase IQ database.

The Oracle database started up without any problems instead when I was trying to start the RAO Agent which is made up of a java process and also Replication Agent System Database (RASD)  process I got the following error :

 E. 09/11 20:58:17. *** ERROR *** Assertion failed: 201129 (11.0.1.2044)
File is shorter than expected

The Replication Agent system Database  got corrupted !!

This was a fairly serious issue !!

It was necessary to contact the Sybase support  and following you’ll find the procedure to figure out the corruption and restart the RAO agent :
export  LD_LIBRARY_PATH=/directorty/RAX-XX/RAO/lib

cd $RAO_HOME/repository

Make sure the RAO.log is not there

Make sure the RAO.db file is there.

Execute the following command : dbeng12 –f RAO.db
NB : this command tells the database to come up wihout a log file. 

Execute the following command :dblog –t RAO.log  RAO.db
NB: this command tells the database to assign a new log file to the database.


Restart the RAO using normal procedures.
 

The procedure ended up bringing the RAO again online and what’s more in ready to replicate data.

See you in my next blog.

Byeeeee