Category Archives: 11g R2 RMAN

11g R2 RAC : AUTOLOCATE BACKUPS

11g R2 RAC, 11g R2 RMAN, Uncategorized
During a restore operation, RMAN automatically performs the autolocation of backups. A channel connected to a specific node only attempts to restore files that were backed up to the node. For example, assume that example tablespace is backed up to the drive attached to node1, while tablespace users is backed up to the drive attached to node2. If you then allocate channels that connect to each node, then the channel connected to node1 can restore example tablespace (but not users), and the channel connected to node2 can restore users tablespace (but not example).
Let’s demonstrate in the two node RAC setup I have:
Overview :
– Create folder for backups on both the nodes with appropriate permissions.
– Take backup of example and users tablespaces on node1 and node2 by allocating channels explicitly
– Take users and example tablespaces offline in immediate mode so that they will need media recovery to come online.
– Restore both tablespaces from backups on node1 and node2 by allocating channels explicitly to corresponding instances
– Note that RMAN automatically finds out that backup for example is stored on node1 and backup for users is stored on node2
   and restores them from respective nodes.
Implementation:
– Create folder for backups on both the nodes with appropriate permissions.
[oracle]$mkdir /u01/app/oracle/oradata/orcl/backup
-- Take backup of example and users tablespaces on node1 and node2 by allocating channels explicitly
– Note that backup of tablespace example is created on node1 using channel c1
   and backup of tablespace users is created on node1 using channel c2
RMAN>  run {
             allocate channel c1 device type disk 
              format ‘/u01/app/oracle/oradata/orcl/backup/orcl%U.bak’ connect=’sys/oracle@orcl1′;
    allocate channel c2 device type disk 
              format ‘/u01/app/oracle/oradata/orcl/backup/orcl%U.bak’ connect=’sys/oracle@orcl2′;
    backup tablespace users, example;
  }
allocated channel: c1
channel c1: SID=57 instance=orcl1 device type=DISK
allocated channel: c2
channel c2: SID=46 instance=orcl2 device type=DISK
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/orcl/datafile/example.264.799999785
channel c2: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.799999587
channel c1: finished piece 1 at 30-NOV-12
piece handle=/u01/app/oracle/oradata/orcl/backup/orcl0snrivk1_1_1.bak tag=TAG20121130T044136
channel c2: finished piece 1 at 30-NOV-12
piece handle=/u01/app/oracle/oradata/orcl0tnrivk1_1_1.bak tag=TAG20121130T044136
-- Take users and example tablespaces offline in immediate mode so that they will need media recovery to come online.
SQL>alter tablespace users offline immediate;
    alter tablespace example offline immediate;
    alter system switch logfile;
    alter system switch logfile;
    alter system switch logfile;
    alter tablespace users online;
    alter tablespace example online;
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘+DATA/orcl/datafile/users.259.799999587′
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘+DATA/orcl/datafile/example.264.799999785′
– Restore both tablespaces from backups on node1 and node2 by allocating channels explicitly to corresponding instances
– Note that RMAN automatically finds out that backup for example is stored on node1 and backup for users is stored on node2
   and restores them from respective nodes.
RMAN>  run {
             allocate channel c1 device type disk 
               connect=’sys/oracle@orcl1′;
    allocate channel c2 device type disk 
              connect=’sys/oracle@orcl2′;
    restore tablespace users, example;
  }
allocated channel: c1
channel c1: SID=57 instance=orcl1 device type=DISK
allocated channel: c2
channel c2: SID=46 instance=orcl2 device type=DISK
channel c1: restoring datafile 00005 to +DATA/orcl/datafile/example.264.799999785
channel c1: reading from backup piece /u01/app/oracle/oradata/orcl/backup/orcl0snrivk1_1_1.bak
channel c2: restoring datafile 00004 to +DATA/orcl/datafile/users.259.799999587
channel c2: reading from backup piece /u01/app/oracle/oradata/orcl/backup/orcl0tnrivk1_1_1.bak
– Recover tablespaces and bring them online
RMAN>recover tablespace example, users;
SQL>  alter tablespace example online;

             alter tablespace users online;
I hope you found the article useful. Keep visiting the blog …
———————————————————————
Related links:

DUPLICATE DATABASE WITH CONNECTION TO RECOVERY CATALOG

11g R2 RMAN, Uncategorized
In this post, I will demonstrate how to duplicate a database using its backups.
This method requires connection to recovery catalog only to get information about the
backups. Connection with the target database is not needed.
********************************
  source database  orcl (ASM)
  Duplicate database  orclt (ASM)
  Recovery catalog database : rcat
***********************************
Overview:
on the source  host
  • - Create recovery catalog in rcat database
  • - Register source database orcl with recovery catalog
  • - BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE while connected to recovery catalog
  • - Copy these backup files to the server where you want to create the duplicate copy.
  • - CREATE PFILE FROM SOURCE DATABASE

on the target host

  • - Add a line in the file /etc/oratab to reflect the database instance(orclt) you are going
  • to copy
  • - create folders
  • - Copy the initialization parameter file from the source database add edit it.
  • - Copy the password file
  • - Startup the duplicate database (orclt) in nomount mode using modified parameter file
  • - Using RMAN  connect to the catalog database(rcat) as target database and duplicate
  • database (orclt) as auxiliary instance
  • - duplicate the database orcl to orclt
———————-
on the source  host
———————-
- Create recovery catalog in rcat database
  .create tablespace to hold recovery catalog data –
sql>create tablespace rcat datafile '+data/rcat01.dbf' size 15m;
  . create recovery catalog owner
sql>create user rcatowner identified by oracle

    default tablespace rcat quota unlimited on rcat;
    grant recovery_catalog_owner to rcatowner;

  . Create recovery catalog

$rman catalog rcatowner/oracle@rcat
RMAN>CREATE CATALOG;
- Register source database orcl with recovery catalog
$RMAN TARGET sys/oracle@orcl CATALOG rcatowner/oracle@rcat
RMAN>REGISTER DATABASE;
– Make a folder to stage the backup
oracle$mkdir /home/orale/stage
– Take the backup of the source database
oracle$. oraenv orcl
         rman target /
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
     backup database format '/home/oracle/stage/%U.rmb';
     backup archivelog all  format '/home/oracle/stage/arc_%r_%s_%t.log';
The controlfile backup is also required. If you have configured the controlfile
autobackup, the backup would contain the controlfile as well. If you want to be sure,
 ors you have not configured controlfile autobackup, you can backup the controlfile
explicitly.
– CREATE PFILE FROM SOURCE DATABASE
SQL>CREATE PFILE='/u01/app/oracle/oradata/orcl/initsource.ora'
            FROM SPFILE
———————
on the target host.
————————
 — Copy the backup files to the server where you want to create the duplicate copy.
$mkdir -p /home/oracle/stage
 scp sourcehost:/home/oracle/stage/*.rmb desthost::/home/oracle/stage/
– Add a line in the file /etc/oratab to reflect the database instance you are going to    copy:
orclt:/u01/app/oracle/product/11.2.0/db_1:N
– Now set the Oracle SID as the duplicated database SID:
# . oraenv
ORACLE_SID = [orclt] ?
– create folders
 $mkdir -p /u01/app/oracle/oradata/orclt
  mkdir -p /u01/app/oracle/flash_recovery_area/orclt
  mkdir -p /u01/app/oracle/admin/orclt/adump
  mkdir -p /u01/app/oracle/admin/orclt/dpdump
– Copy the initialization parameter file from the main database.
– If u are duplicating on the same host
$cp  /u01/app/oracle/oradata/orcl/initsource.ora
/u01/app/oracle/oradata/orclt/inittarget.ora
OR
– If u are duplicating on the different host
$scp  soucehost:/u01/app/oracle/oradata/orcl/initsource.ora
/u01/app/oracle/oradata/orclt/inittarget.ora
– Edit parameter file
$vi /u01/app/oracle/oradata/orclt/inittarget.ora
   – Change db_name = orclt
   – Edit it to reflect the new locations that might be appropriate
     such as control file locations,audit dump destinations, datafile
     locations, etc.
   – add these lines –
     db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)
     log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)
————————————–
– Copy the password file as well
$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
OR
$ scp sourcehost:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt
– Startup the duplicate database in nomount mode using modified parameter file
$ . oraenv
ORACLE_SID = [orclt] ?
$sqlplus sys/oracle as sysdba
SQL> startup nomount pfile='/u01/app/oracle/oradata/orclt/inittarget.ora';
          create spfile from pfile='/u01/app/oracle/oradata/orclt/inittarget.ora';
- Using RMAN  connect to the catalog database (rcat) and duplicate database   (orclt) as
auxiliary instance
$rman catalog rcatowner/oracle@rcat  auxiliary sys/oracle@orclt




– duplicate the database orcl to orclt


– the command performs the following steps:


    * Creates an SPFILE


    * Shuts down the instance and restarts it with the new spfile


    * Restores the controlfile from the backup


    * Mounts the database


    * Performs restore of the datafiles. In this stage it creates the files in the


      converted names.


    * Recovers the datafiles up to the time specified and opens the database




RMAN>duplicate  database orcl to orclt;


– check that duplicate database is up


$sqlplus / as sysdba




sql>conn hr/hr


    select * from tab;




– Note that  DBID is different from the main database so it can be backed up


   independently and using the same catalog as well.




SQL> select dbid from v$database;


      DBID


———-


3780202035




SQL>conn sys/oracle@orcl as sysdba


    select dbid from v$database;


      DBID


———-


1327232823




——————————————————————————————–


—






Related links:

















——————————————————————————————–



	

	
	


							


	

		
				

			11g R2 RMAN: TRANSPORT DATABASE FROM LINUX TO WINDOWS
		

		
		

			11g R2 RMAN, Uncategorized					

	


		

		




In this post, I will demonstrate transporting a database on Linux 32 bit platform to Windows 32 bit.




Overview 




TDB requires that data files be converted to the target platform format. The data file


conversion can occur on either the source system or the target system. When performing a


source system conversion, TDB creates a second copy of all data files on the source system


in the format of the target system. The converted data files must then be transferred to the


proper location on the target system.


Using TDB to migrate a database to a new platform of the same endian format consists of the following high-level steps:




1. Check prerequisites 


2. Prepare for the platform migration 


3. Start the database in READ ONLY mode 


4. Verify the database is ready for migration 


5. Run the RMAN CONVERT DATABASE command 


6. Move necessary files to the target system 


7. Complete the migration




Implementation:




– Check Prerequisites


– check the platform name of current database –




SQL>SELECT PLATFORM_NAME


          FROM  V$DATABASE;




PLATFORM_NAME


——————————


Linux IA (32-bit)




– Check the endian format of current platform


– Note that ENDIAN format is little




SQL>SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT


          FROM   V$TRANSPORTABLE_PLATFORM 


         WHERE UPPER(PLATFORM_NAME) LIKE ‘%LINUX IA%';




PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT


———– —————————— ————–


         10 Linux IA (32-bit)              Little


         11 Linux IA (64-bit)              Little




– Check that endian format of the target platform i.e. Windows IA(32-bit) is also little


– Hence database can be transported




SQL>SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT


          FROM   V$TRANSPORTABLE_PLATFORM 


          WHERE UPPER(PLATFORM_NAME) LIKE ‘%MICROSOFT WINDOWS%';






PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT


———– —————————— ————–


          7 Microsoft Windows IA (32-bit)  Little


          8 Microsoft Windows IA (64-bit)  Little


         12 Microsoft Windows x86 64-bit   Little




If the target platform does not appear in the output from V$TRANSPORTABLE_PLATFORM, then the database cannot be migrated using TDB.




— Start the database in READ ONLY mode 




SQL> shutdown immediate;


     startup mount;


     alter database open read only;




— Verify the database is ready for migration 




SQL>


    SET  SERVEROUTPUT ON;


    DECLARE


      db_ready BOOLEAN;


    BEGIN


      db_ready := 


         DBMS_TDB.CHECK_DB(‘Microsoft Windows IA (32-bit)’,DBMS_TDB.SKIP_READONLY);


      if db_ready 


          then


             dbms_output.put_line(‘YES your database can be transported to Windows 


platform’);


      else 


         dbms_output.put_line(‘NO your database can not be transported to WIndows 


Platform’);


      end if;


     end;


/




– Make a folder to hold converted datafiles


$mkdir /home/oracle/convertdb




— Run the RMAN CONVERT DATABASE Command 




The CONVERT DATABASE command specified in the example creates


  – a transport script named /home/oracle/convertdb/transport.sql which contains SQL statements used to create the new database on the destination platform,


  – a PFILE initnewdb.ora in file /home/oracle/convertdb  for use with the new database on the destination platform, containing settings used  from the source database. Several entries at the top of the PFILE should be edited when the database is moved to the destination platform


  – a copy of all data files in the =/home/oracle/convertdb  directory in the format of the target platform ‘Microsoft Windows IA (32-bit)




RMAN>CONVERT DATABASE


              NEW DATABASE ‘newdb’


              TRANSPORT SCRIPT ‘/home/oracle/convertdb/transport.sql’


              TO PLATFORM ‘Microsoft Windows IA (32-bit)’


               FORMAT ‘/home/oracle/convertdb/newdb’


               DB_FILE_NAME_CONVERT ‘/u01/app/oracle/oradata/linuxdb/’ ‘/home/oracle/convertdb/';






Starting conversion at source at 18-NOV-12


….


channel ORA_DISK_1: starting datafile conversion


input datafile file number=00001 name=/u01/app/oracle/oradata/linuxdb/system01.dbf


converted datafile=/home/oracle/convertdb/system01.dbf


…


input datafile file number=00004 name=/u01/app/oracle/oradata/linuxdb/users01.dbf


converted datafile=/home/oracle/convertdb/users01.dbf


channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01


Edit init.ora file /home/oracle/convertdb/init_newdb.ora. This PFILE will be used to create


the database on the target platform


Run SQL script /home/oracle/convertdb/transport.sql on the target platform to create


database


To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform


To change the internal database identifier, use DBNEWID Utility


Finished conversion at source at 18-NOV-12




– Review and Edit the PFILE  to change any settings for the destination database.


$vi  /home/oracle/convertdb/init_newdb.ora




# Please change the values of the following parameters:


  control_files            = “c:\app\administrator\oradata\newdb\control01.ctl”


  db_recovery_file_dest=”c:\app\administrator\flash_recovery_area”


  audit_file_dest          = “c:\app\administrator\admin\newdb\adump”




– Review and edit the transport script transport.sql 


$vi  /home/oracle/convertdb/transport.sql




– Modify the following :




STARTUP NOMOUNT PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS


\initnewdb.ora';




LOGFILE


  GROUP 1 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,


  GROUP 2 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO02.LOG’ SIZE 50M BLOCKSIZE 512,


  GROUP 3 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\REDO03.LOG’ SIZE 50M BLOCKSIZE 512


DATAFILE


  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\system01.dbf’,


  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\sysaux01.dbf’,


  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\undotbs01.dbf’,


  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\users01.dbf’,


  ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\example01.dbf’




ALTER TABLESPACE TEMP


ADD TEMPFILE


 ‘C:\APP\ADMINISTRATOR\ORADATA\NEWDB\TEMP01.DBF’


  SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;




STARTUP UPGRADE PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS\initnewdb.ora';


@@ ?\rdbms\admin\utlirp.sql


SHUTDOWN IMMEDIATE


STARTUP PFILE=’C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DBS\initnewdb.ora';




– Make folders on target windows host




c:\>mkdir c:\app\administrator\oradata\newdb


      mkdir c:\app\administrator\admin\newdb\adump”




– Copy all the files in /home/oracle/convertdb folder to c:\app\administrator\oradata\newdb


   i.e. datafiles


        Pfile


        transport script




– Move PFILE to $ORACLE_HOME/database  i.e. c:\app\administrator\product


\11.2.0\dbhome_1\dbs




C:\>copy c:\app\administrator\oradata\newdb\init_newdb.ora c:\app\administrator\product


\11.2.0\dbhome_1\dbs\initnewdb.ora




– create a service for newdb on windows


c:\>oradim -new -sid newdb




– Execute the transport script in SQL*Plus to create the new database on the destination host.


c:\>set oracle_sid=newdb


      sqlplus  / as sysdba




SQL>@c:\app\administrator\oradata\newdb\transport.sql




– check that database has been transported —


SQL>select * from hr.employees;


——————————————————————————-






Related links:




















                                                                                             ———————–







	

	
	


							


	

		
				

			DUPLICATE DATABASE WITHOUT CONNECTION TO TARGET DATABASE
		

		
		

			11g R2 RMAN, Uncategorized					

	


		

		






In this post, I will demonstrate how to duplicate a database from its backups without any


connection to the source database. This method can be used if source database is not


available .




********************************


  source database  orcl


  Duplicate database  orclt


********************************




Overview:


on the source  host


- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE


-  Copy these backup files to the server where you want to create the duplicate copy.


- CREATE PFILE FROM SOURCE DATABASE




on the target host


- Add a line in the file /etc/oratab to reflect the database instance you are going to copy


- create folders


- Copy the backup files from the source database


- Copy the initialization parameter file from the source database add edit it.


- Copy the password file


- Startup the target database in nomount mode using modified parameter file


- Using RMAN  connect to the  duplicate database (orclt) as auxiliary instance


- Specify the location of the backups and duplicate the database orcl to orclt




Implementation


————–


*******************


on the source  host


*******************




- BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE


—-


—–


oracle@source$mkdir/home/oracle/stage


oracle@source$. oraenv orcl


              rman target /


RMAN>backup database format ‘/home/oracle/stage/%U.bak';




     backup archivelog all format ‘/home/oracle/stage/arch_%r%_s_%t.bak’


 ;


     backup current controlfile format ‘/home/oracle/stage/control.bak';




– CREATE PFILE FROM SOURCE DATABASE




SQL>CREATE PFILE=’/home/oracle/stage/initsource.ora’


    FROM SPFILE;




*****************


 on the target host. 


*****************




– Make a staging folder for backups and pfile




oracle@dest$mkdir -p /home/oracle/stage




– create other required  folders




 $mkdir -p /u01/app/oracle/oradata/orclt


  mkdir -p /u01/app/oracle/flash_recovery_area/orclt


  mkdir -p /u01/app/oracle/admin/orclt/adump


  mkdir -p /u01/app/oracle/admin/orclt/dpdump




– Copy backup files from the source host


 # scp source:/home/oracle/stage/*.bak /home/oracle/stage/




– Copy pfile of source database (orcl)


 # scp source:/home/oracle/stage/initsource.ora /home/oracle/stage/inittarget.ora




– Copy the password file as well 


 $ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt


 


– Add a line in the file /etc/oratab to reflect the database instance you are going to  copy:


orclt:/u01/app/oracle/product/11.2.0/db1:N




– Edit the initialization parameter file from the main database. 


$vi /home/oracle/stage/inittarget.ora


   – Change db_name = orclt


   – Edit it to reflect the new locations that might be appropriate


     such as control file locations,audit dump destinations, datafile


     locations, etc.


   – add these lines –


     db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,


                             “/u01/app/oracle/oradata/orclt”)


     log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,


                             “/u01/app/oracle/oradata/orclt”)




In case sorce and destination databases ae ASM, following lines can be added :




db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)


log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)






– Now set the Oracle SID as the duplicated database SID:




$ . oraenv


ORACLE_SID = [orclt] ?




– Startup the target database in nomount mode using modified parameter file


$sqlplus sys/oracle as sysdba




SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';


     create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';




- Using RMAN  connect to the  duplicate database (orclt) as auxiliary instance 


$. oraenv


   orclt


$rman auxiliary /




– duplicate the database orcl to orclt


– the command performs the following steps:


    * Creates an SPFILE


    * Shuts down the instance and restarts it with the new spfile


    * Restores the controlfile from the backup


    * Mounts the database


    * Performs restore of the datafiles. In this stage it creates the files in the


      converted names.


    * Recovers the datafiles up to the time specified and opens the database




– If duplicate database has the same directory structure as source (on a different host)


RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ nofilenamecheck;


OR


– If duplicate database has different directory structure from source


RMAN>duplicate target database to orclt backup location ‘/home/oracle/stage/’ ;




– check that duplicate database is up


$sqlplus / as sysdba




sql>conn hr/hr


    select * from tab;




– Note that  DBID is different from the main database so it can be backed up


   independently and using the same catalog as well.




SQL> select dbid from v$database;


     conn sys/oracle@orcl as sysdba




     select dbid from v$database;


——————————————————————————————–






Related links:















                                                                                                        ——————–



	

	
	


							


	

		
				

			DUPLICATE DATABASE USING BACKUP WITH CONNECTION TO TARGET DATABASE
		

		
		

			11g R2 RMAN, Uncategorized					

	


		

		






In this post, I will demonstrate how to duplicate a database using its backups.


This method requires connection with the target database also to read its controlfile


to get information about the backups.




********************************


  source database  orcl


  Duplicate database  orclt


***********************************


Overview :


on the source  host


- Backup Database , Archivelogs and controlfile


- Copy these backup files to the server where you want to create the duplicate copy.


- Creat Pfile from source database


 


on the target host


- Add a line in the file /etc/oratab to reflect the database instance(orclt) you are going to copy


- create folders


- Copy the initialization parameter file from the source database add edit it.


- Copy the password file


- Startup the duplicate database (orclt) in nomount mode using modified parameter file


- Using RMAN  connect to the source database(orcl) as target database and duplicate database


  (orclt) as auxiliary instance


- duplicate the database orcl to orclt




Implementation :




———————-


on the source  host


———————-


– Make a folder to stage the backup


oracle$mkdir /home/oracle/stage




– Take the backup of the source database


oracle$. oraenv orcl


         rman target /




RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;


     backup database plus archivelog format ‘/home/oracle/stage/%U.rmb';




The controlfile backup is also required. If you have configured the controlfile


autobackup, the backup would contain the controlfile as well. If you want to be sure,


 ors you have not configured controlfile autobackup, you can backup the controlfile


explicitly.




——-


- Creat Pfile from source database


——-


SQL>Create pfile=’/u01/app/oracle/oradata/orcl/initsource.ora’


    from spfile;




———————————————–


The rest of the steps occur on the target host. 


———————————————–


———————————


 Copy the backup files to the server where you want to create the duplicate copy.


——————————-


$mkdir -p /home/oracle/stage


 scp sourcehost:/home/oracle/stage/*.rmb desthost::/home/oracle/stage/




– Add a line in the file /etc/oratab to reflect the database instance you are going to  copy:


orclt:/u01/app/oracle/product/11.2.0/db1:N




– Now set the Oracle SID as the duplicated database SID:


# . oraenv


ORACLE_SID = [orclt] ?




– create folders




 $mkdir -p /u01/app/oracle/oradata/orclt


  mkdir -p /u01/app/oracle/flash_recovery_area/orclt


  mkdir -p /u01/app/oracle/admin/orclt/adump


  mkdir -p /u01/app/oracle/admin/orclt/dpdump




– Copy the initialization parameter file from the main database. 


– If u are duplicating on the same host


$cp  /u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora


OR


– If u are duplicating on the different host


$scp  soucehost:/u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora




– Edit pfile




$vi /u01/app/oracle/oradata/orclt/inittarget.ora


   – Change db_name = orclt


   – Edit it to reflect the new locations that might be appropriate


     such as control file locations,audit dump destinations, datafile


     locations, etc.


   – add these lines –


     db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,


                             “/u01/app/oracle/oradata/orclt”)


     log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,


                             “/u01/app/oracle/oradata/orclt”)




In case source and destination databases ae ASM, following lines can be added :




db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)


log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)






————————————–


– Copy the password file as well 


$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt


OR


$ scp sourcehost:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt


$ . oraenv


ORACLE_SID = [orclt] ?




– Startup the duplicate database in nomount mode using modified parameter file


$sqlplus sys/oracle as sysdba




SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';


     create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';




- Using RMAN  connect to the source database(orcl) as target database and duplicate database


  (orclt) as auxiliary instance 


$. oraenv


   orclt




$rman target sys/oracle@orcl auxiliary /


– duplicate the database orcl to orclt


– the command performs the following steps:


    * Creates an SPFILE


    * Shuts down the instance and restarts it with the new spfile


    * Restores the controlfile from the backup


    * Mounts the database


    * Performs restore of the datafiles. In this stage it creates the files in the


      converted names.


    * Recovers the datafiles up to the time specified and opens the database




RMAN>duplicate target database to orclt;




– check that duplicate database is up


$sqlplus / as sysdba


sql>conn hr/hr


    select * from tab;




– Note that  DBID is different from the main database so it can be backed up


   independently and using the same catalog as well.


SQL> select dbid from v$database; 




      DBID


———-


3779358555




     conn sys/oracle@orcl as sysdba


     


     select dbid from v$database;




      DBID


———-


1326904854






———————————————————————————————–






Related links:















                                                ——————





	

	
	


							


	

		
				

			ORACLE 11G :ACTIVE DATABASE DUPLICATION
		

		
		

			11g R2 RMAN, Uncategorized					

	


		

		








Oracle 11g introduced active database duplication using which we can create a duplicate database of the target database without any backups. Duplication is performed over the network.




Procedure :




Overview:




on the source  host


- Create Pfile from source database


- Create an entry in tnsnames.ora for duplictae database on target host on port 1522




on the target host


- Add a line in the file /etc/oratab to reflect the database instance you are going to copy


- create folders


- Copy the initialization parameter file from the source database add edit it.


- Copy the password file


- Create a listener in database home on port 1522 and register duplicate database statically with it


- Startup the target database in nomount mode using modified parameter file


- Using RMAN  connect to the source database(orcl) as target database and duplicate database (orclt) as auxiliary instance


- duplicate the target database




********************************


  source database  orcl


  Duplicate database  orclt


***********************************




Implementation




– On source host




– CREATE PFILE FROM SOURCE DATABASE




SQL>CREATE PFILE=’/u01/app/oracle/oradata/orcl/initsource.ora’     FROM SPFILE;




– On source database, create a service for orclt on target host on port 1522




The rest of the steps occur on the target host. 






– Add a line in the file /etc/oratab to reflect the database instance you are going to copy


     orclt:/u01/app/oracle/product/11.2.0/db1:N




– Now set the Oracle SID as the duplicated database SID:


# . oraenv


ORACLE_SID = [orclt] ?




– create folders




 $mkdir -p /u01/app/oracle/oradata/orclt


  mkdir -p /u01/app/oracle/flash_recovery_area/orclt


  mkdir -p /u01/app/oracle/admin/orclt/adump


  mkdir -p /u01/app/oracle/admin/orclt/dpdump




– Copy the initialization parameter file from the main database. 


$cp  /u01/app/oracle/oradata/orcl/initsource.ora /u01/app/oracle/oradata/orclt/inittarget.ora




– Edit the initialization parameter file 


$vi /u01/app/oracle/oradata/orclt/inittarget.ora




   – Change db_name = orclt


   – Edit it to reflect the new locations that might be appropriate


     such as control file locations,audit dump destinations, datafile


     locations, etc.


   – add these lines –


     db_file_name_convert = (“/u01/app/oracle/oradata/orcl”,


                             “/u01/app/oracle/oradata/orclt”)


     log_file_name_convert = (“/u01/app/oracle/oradata/orcl”,


                             “/u01/app/oracle/oradata/orclt”)




In case source and destination databases ae ASM, following lines can be added :




db_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”)


log_file_name_convert = (“+DATA/orcl”,”+DATA/orclt”, “+FRA/orcl”,”+FRA/orclt”)






– Copy the password file as well 


$cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/db_1/dbs/orapworclt


 


– Startup the target database in nomount mode using modified parameter file


$ . oraenv


ORACLE_SID = [orclt] ?


$sqlplus sys/oracle as sysdba




SQL> startup nomount pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';


     create spfile from pfile=’/u01/app/oracle/oradata/orclt/inittarget.ora';




– create a listener on port 1522 in database home on target host and statically register service


    orclt with it.


 


– connect to the auxiliary instance




$. oraenv


   orclt


$rman target sys/oracle@orcl auxiliary sys/oracle@orclt




– duplicate the database orcl to orclt from active database


– the command performs the following steps:


    * Creates an SPFILE


    * Shuts down the instance and restarts it with the new spfile


    * Creates the controlfile for standby database


    * Mounts the database


    * Performs restore of the datafiles. In this stage it creates the files in the


      converted names.


    * Recovers the datafiles up to the time specified and opens the database




RMAN>duplicate target database to orclt from active database;




– check that duplicate database is up


$sqlplus / as sysdba




sql>conn hr/hr


    select * from tab;




-- Note that  DBID is different from the main database so it can be backed up


   independently and using the same catalog as well.




SQL> select dbid from v$database; 


      DBID


———-


3779357884




     conn sys/oracle@orcl as sysdba


     


     select dbid from v$database;


      DBID


———-


1326904854




———————————————————————————————–






Related links:

















                                                                                             ————————