Cloning database with RMAN Catalog

 Source database name = prod
Catalog database name = catalog
Target database name = clone

1) Copy Oracle home from source(prod) database machine to target(clone) database machine:-

$ tar -czvf home.tgz db_1
$ scp -r hmoe.tgz oracle@192.168.0.12:/u01
$ tar -xzvf home.tgz

2) Create new Init.ora file for auxiliary database (target database):-

* New changes in init.ora file:-

db_name= clone

DB_FILE_NAME_CONVERT: This parameter transforms the target data file names to the duplicate database data file names.
• LOG_FILE_NAME_CONVERT: This parameter converts the target database redo log file names to the duplicate database redo log file names.

*.Db_file_name_convert='/oradata/oracle/datafiles/prod/','/oradata/datafile'
If you have multiple datafile locations at Source database then:-
*.Db_file_name_convert='/oradata/oracle/datafiles/prod/','/oradata/datafile','/u01/datafiles/prod','/oradata/datafile'*.log_file_name_convert='/oradata/oracle/datafiles/prod/','/oradata/datafile'

3) Create listner.ora on auxiliary(clone) database:-

CLONE =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
       )
 )

SID_LIST_CLONE =
(SID_LIST =
   (SID_DESC =
     (SID_NAME = clone)
       (SID = clone)
          (ORACLE_HOME = /oradata/oracle/oracle/product/10.2.0/db_1)
        )
   )
4) Create Password file on auxiliary(clone) database:-

$ orapwd file=orapwclone password=manager force=y

5) Configure tnsnames.ora on catalog database for auxiliary(clone) database:-
TO_PROD =
         (DESCRIPTION =
             (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
               )
(CONNECT_DATA =
          (SID_NAME = prod)
                (SID = prod)
             )
       )

TO_CLONE =
             (DESCRIPTION =
                  (ADDRESS_LIST =
                         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
                       )
(CONNECT_DATA =
              (SID_NAME = clone)
                     (SID = clone)
                     )
                )
6) Export the Oracle Home on auxiliary(clone) database:-

$export $ORACLE_HOME=/oradata/oracle/oracle/product/10.2.0/db_1
$export ORACLE_SID=clone

7) Start the Auxiliary database in nomount stage:-

$sqlplus 'as sysdba'

SQL> startup nomount

ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
8) Take RMAN backup of Source database(prod):-
* Login to Catalog database and connect to source database(prod)

$ rman catalog rman/rman target test/test@to_prod

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 18 19:46:40 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PROD (DBID=687831768)
connected to recovery catalog database

RMAN> backup database plus archivelog;
RMAN>backup current controlfile;

9) After backing up Source(prod) database SCP the backup to target(clone)database:-

$scp -r /u01/rman_backp oracle@192.168.0.12:/u01/

10) Connect catalog to Source(prod) and Target(clone) database:-
 login to Catalog database

$rman catalog rman/rman target test/test@to_catalog auxiliary sys/manager@to_clone

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 18 19:53:33 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=687831768)
connected to recovery catalog database
connected to auxiliary database: CLONE (DBID=1041372533)
11) After connecting issue the following command to clone Source(prod) database to Target(clone):-

RMAN>duplicate target database to 'clone';                    (here 'clone' is a SID of the auxiliary database)

Starting Duplicate Db at 18-MAR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=155 devtype=DISK
==
==
==
==
==
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 18-MAR-14
12) Go to Cloned database and check the status:-

SQL> select status from v$instance;

STATUS
------------
OPEN

Oracle Tables

• Heap-organized tables: 

A heap-organized table is nothing but the normal Oracle table, where data is stored in no particular order.

• Index-organized tables:

 An index-organized table stores data sorted in a B-tree indexed structure.

• Clustered tables:

 A clustered table is part of a group of tables that shares the same data blocks, because columns of the clustered tables are often requested together.

• Partitioned tables:

 A partitioned tables lets you divide a large amount of data into sub tables, called partitions, according to various criteria. Partitioning is especially useful in a data warehouse environment.

Create Table:-

sql>create table test3 (
       numb number(5),
       name varchar2(20) not null,
       addr varchar2(30)
       constraint test3_fkey primary key)
       tablespace test01;
                  OR
sql>create table test as select * from emp;

Add Column to the Table:-

sql>alter table test add (job varchar2(20));    ('Job' New Column name)

Droping a Column from Tables:-

sql>alter table test drop (job);
Where 'Job' is Column name.

Rename a Column:-

sql>alter tables test rename column job to designation;

Insert Rows in a Table:-

sql>insert into test values (3,'manager','local','business');

Rename a Table:-

sql>alter table test rename to final;

Removing all data from Table:-

sql>truncat table test;          (you cannot rollback data)

Move Table to new Tablespace:-

sql>alter table test move to (New Tablespace);

Dropping a Table:-

sql>drop table emp;            (It will move table to recycle bin)

sql>drop table emp Purge;       (It will permanently drop table)

sql>drop table emp cascade constraints;             (It will drop constraints eg, primary key foreign key etc.)

Bring back a deleted table (no purged):-

sql>flashback table emp to before drop;

Recyclebin:-

sql>select * from recyclebin;
sql>select owner, object_name, ts_name from dba_recyclebin;
sql>select * from "BIN$8sL/jMU2hOrgQKjACQADrQ==$0";
sql>purge table "BIN$8sL/jMU2hOrgQKjACQADrQ==$0";   (It will delete table from Recycle bin)

Learn Oracle Application dba and more in Pune

We are established in training Oracle technologies such as Linux, SQL, PL/SQL, Oracle Applicaitons,Database,RAC,Golden Gate.


We are starting new batch for Oracle Apps DBA.

Course :

1. Linux
2. SQL
3. PL/SQL
4. Oracle DBA (11g)
5. Oracle RAC (11g)
6. Oracle Apps DBA ( 11i/R12)

Duration : 6 months


Workshops on weblogic server,database upgrade,application upgrade,performance tuning,golden gate, RMAN,advanced topologies in oracle applications, guest lectures for our students.


We are starting new batch for Oracle Core DBA.

Course :

1. Linux
2. SQL
3. PL/SQL
4. Oracle DBA (11g)
5. Oracle RAC (11g)

Duration : 4 months

Oracle RAC weekend batch starting soon.

Learn oracle from Real Time experts.

please

Pune
Contact: 9665521484