RMAN: Oracle Database Cloning

Operating Environment Windows XP Prof. SP-2
Oracle Release / Version 10.1.0.2.0 Enterprise Edition
ORACLE_BASE d:\oracle
ORACLE_HOME d:\oracle\product\10.1.0
Target Database SID ORA101
Duplicate Database SID TESTDB
RMAN Catalog Database No recovery catalog. Using control file.
Archive Log Mode Enabled
-----------------------------------------------------------------------------------------------
1.Create password File for duplicate database


C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdtestdb.ora password=oracle entries=5 force=y


----------------------------------------------------------------
2.Create Init.ora file for duplicate database. copy init.ora file from target database (ora101) and edit.


SQL> create pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ora' from spfile;
File created.


#Minimum Changes in init.ora file
db_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product\10.1.0\oradata\TESTDB')
log_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product\10.1.0\oradata\TESTDB')
background_dump_dest='D:\oracle\product\10.1.0\admin\testdb\bdump'
control_files='D:\oracle\product\10.1.0\oradata\testdb\control01.ctl','D:\oracle\product\10.1.0\oradata\testdb\control02.ctl','D:\oracle\product\10.1.0\oradata\testdb\control03.ctl'
core_dump_dest='D:\oracle\product\10.1.0\admin\testdb\cdump'
db_name='testdb'
dispatchers='(PROTOCOL=TCP)(SERVICE=testdbXDB)'
user_dump_dest='D:\oracle\product\10.1.0\admin\testdb\udump'

---------------------------------------------------------------------------------------------
3.Create / Start the Auxiliary Instance (Duplicate Database)


Create all required directory.
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\bdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\cdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\udump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\pfile
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\scripts
C:\>mkdir d:\oracle\product\10.1.0\oradata\TESTDB

C:\>ORADIM -NEW -SID TESTDB
Instance created.
C:\>set ORACLE_SID=TESTDB
C:\>SQLPLUS "/ AS SYSDBA"
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 12:15:14 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ORA';
File created.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
SQL>


----------------------------------------------------------------
4.Mount or Open Target Database.(ora101)


C:\>sqlplus sys/oracle@ora101 as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 16:33:33 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
------------
OPEN


-------------------------------------------------
5.Make sure you have valid Target Database backup and Archive redo logs.


C:\>rman target sys@ora101
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:connected to target database: ORA101 (DBID=5128390)
RMAN> configure controlfile autobackup on;
using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> run
2> {
3> backup database;
4> backup archivelog all;
5> }
Starting backup at 25-DEC-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 25-DEC-06
Starting backup at 25-DEC-06
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=1 stamp=610113930
input archive log thread=1 sequence=8 recid=2 stamp=610117104
input archive log thread=1 sequence=9 recid=3 stamp=610120818
input archive log thread=1 sequence=10 recid=4 stamp=610124804
input archive log thread=1 sequence=11 recid=5 stamp=610129547
input archive log thread=1 sequence=12 recid=6 stamp=610130542
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_ANNNN_TAG20061225T164224_2RZKSLN0_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
Finished backup at 25-DEC-06
Starting Control File and SPFILE Autobackup at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\AUTOBACKUP\2006_12_25\O1_MF_S_610130562_2RZKT33X_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-06
RMAN>


--------------------------------------------------------------------------
6.Configure TNSNAMES.ORA file for duplicate database.


#TNSNAMES.ORA (ORACLEHOME/NETWORK/ADMIN
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
#LISTENER.ORA ( ORACLEHOME/NETWORK/ADMIN
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=d:\oracle\product\10.1.0\db_2)
(SID_NAME=testdb)
)

cmd>lsnrctl reload


-------------------------------------------------------------------------
7.Connect to RMAN. (Duplicate Database with Different directory structure.)


C:\>rman TARGET=sys/oracle@ora101 AUXILIARY=sys/oracle@testdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORA101 (DBID=5128390)
connected to auxiliary database: testdb (not mounted)

RMAN>
run
2> {
3> allocate auxiliary channel c1 device type DISK;
4> allocate auxiliary channel c2 device type DISK;
5> allocate auxiliary channel c3 device type DISK;
6> DUPLICATE target database to TESTDB;
7>
}
allocated channel: c1channel c1: sid=160 devtype=DISK
allocated channel: c2channel c2: sid=159 devtype=DISK
allocated channel: c3channel c3: sid=158 devtype=DISK
Starting Duplicate Db at 25-DEC-06
contents of Memory Script:
{
set until scn 380106;
set newname for datafile 1 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
set newname for datafile 3 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
set newname for datafile 5 to "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-DEC-06
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
channel c1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP tag=TAG20061225T164042channel c1: restore complete
Finished restore at 25-DEC-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
switch clone datafile all
;
}executing Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=610136064 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
set until scn 380106;
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-DEC-06
starting media recovery
archive log thread 1 sequence 12 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARCarchive log thread 1 sequence 13 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_13_2RZPF26C_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARC thread=1 sequence=12archive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_13_2RZPF26C_.ARC thread=1 sequence=13
media recovery complete
Finished recover at 25-DEC-06
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF recid=1 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF recid=2 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF recid=3 stamp=610136101
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF recid=4 stamp=610136102
datafile 2 switched to datafile copyinput datafilecopy recid=1 stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=2 stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=3 stamp=610136101 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBFdatafile 5 switched to datafile copyinput datafilecopy recid=4 stamp=610136102 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-DEC-06
RMAN>


----------------------------------------------------------
8.Create Tempfile for Temporary Tbs.


C:\>SQLPLUS SYS/ORACLE@TESTDB AS SYSDBA
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 18:25:16 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
SQL> alter tablespace temp add tempfile
2 'd:\oracle\product\10.1.0\oradata\TESTDB\temp01.dbf' size 10m;
Tablespace altered.

Table Copy From OneDB to AnotherDB

You can use Three Method.
1.Database link
2.Export or Import
3.Copy Command.

------------------------------------------
1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command.
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.

Recovery Catalog

Recovey Catalog holds RMAN repository data for one or more databases in a separate database schema, in addition to using the control files of the databases.
Three step to create recovery catalog.
1.configure database which contain recovery catalog.

2.create owner of recovery catalog

3.create recovery catalog itself.
1.sys cann't be owner of the recovery catalog.
2.for recovery catalog maintain seprate database. you should not install catalog on existing database.

4.Creating Recovery Catalog Schema and grant catalog privileges.


E:\>sqlplus "sys@catdb as sysdba"
SQL*Plus: Release 9.0.1.0.1 - Production on Thu Dec 28 09:59:04 2006
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter password:
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining
options

SQL> create user rman identified by cat
temporary tablespace temp
default tablespace users
quota unlimited on users;
User created.

SQL> grant recovery_catalog_owner to rman;
Grant succeeded.

2.Creating Recovery Catalog
SQL> host rman catalog=rman/cat@catdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
recovery catalog is not installed

RMAN> create catalog;
recovery catalog created

RMAN> exit
Recovery Manager complete.

SQL> conn rman/cat@catdb
Connected.

SQL> select count(*) from user_tables;
count(*)
-----------------
34
rows selected.

SQL> host rman target=sys/oracle@orcl
catalog=rman/cat@catdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1138083117)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalogstarting full resync of recovery catalog
full resync complete

RMAN> report schema;
Report of database schema
File K-bytes Tablespace RB segs Datafile Name
---- ---------- -------------------- ------- -------------------
1 450560 SYSTEM YES C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 30720 UNDOTBS1 YES C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 215040 SYSAUX NO C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 5120 USERS NO C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
5 153600 EXAMPLE NO C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
6 1024 TEST NO D:\TEST01.DBF


3.Register Target database in the recovery catalog.

Drop Recovery Catalog.


E:\>rman target / catalog rman/cat@catdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: CATDB (DBID=2156049726)
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 09.00.00 in RCVCAT database is

RMAN> drop catalog;
recovery catalog owner is RMANenter DROP CATALOG command again to confirm catalog removal

RMAN> drop catalog;
recovery catalog dropped
E:\>sqlplus sys@catdb as sysdba
Drop Recovery Catalog user.

SQL> drop user rman cascade;
User dropped.



NOTE : You will lose any RMAN repository records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file.
you have to connect with LIKE "cmd>rman
TARGET=sys/oracle@orcl
CATALOG=rman/cat@catdb
or SET DBID of target database.



SQL> select dbid from v$database;
DBID
----------
1138083117

RMAN> SET DBID = 1138083117;
executing command: SET DBIDdatabase name is "ORCL" and DBID is 1138083117

RMAN> unregister database;
database name is "ORCL" and DBID is 1138083117
Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

utl_file plsql package.

Note:

1.2 use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.

2.Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in
the init.ora file.
In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.

3.First Availability
Oracle Version : 7.3.4

4.The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned using PL/SQL exceptions.

5.Open Mode.
1.A append
2.R read
3.W write

History of Oracle Technology Introductions

1979

Oracle Release 2--the first commercially available relational database to use SQL

1983

Single code base for Oracle across multiple platforms

1984

Portable toolset

1986

Client/server Oracle relational database

1987

CASE and 4GL toolset

1988

Oracle Financial Applications built on relational database

1989

Oracle6 1991 Oracle Parallel Server on massively parallel platforms

1993

Oracle7 with cost-based optimizer

1994

Oracle Version 7.1 generally available: parallel operations including query, load, and create index

1996

Universal database with extended SQL via cartridges, thin client, and application server

1997

Oracle8 generally available: including object-relational and Very Large Database (VLDB) features

1999

Oracle8i generally available: Java Virtual Machine ( JVM) in the database

2000

Oracle9i Application Server generally available: Oracle tools integrated in middle tier

2001

Oracle9i Database Server generally available: Real Application Clusters, Advanced Analytic Services

Manually Database Creation.

Manually Database Creation.

Based On Windows Enviourments
----------------------------------------

Follow below procedure.
-----------------------------

1. Create Script for database creation through "DBCA".( database configuration assistant)
start>run>dbca
or
start>program>oracle_home>configuration_and_migration>DATABASE CONFIGURATION ASSISTANT

2. In DBCA ( windows) select "custom" option in template. ( no datafiles option)
then next >>> next >>> and database creation option remove check mark on "create database" and select ( mark a check) on Generate Database Creation Scripts.

Note: Script generate in :"oracle-home/admin/scripts"

3. Create folder in below mention path.
1. Oracle-home/admin/1.udump...2.bdump...3.cdump
2. Oracle-home/oradata/2."folder name is some instance name" in my case instance name is "orcl1" then folder name is "orcl1".

mkdir D:\oracle\product\10.1.0\admin\orcl1\create
mkdir D:\oracle\product\10.1.0\admin\orcl1\pfile
mkdir D:\oracle\product\10.1.0\admin\orcl1\bdump
mkdir D:\oracle\product\10.1.0\admin\orcl1\udump
mkdir D:\oracle\product\10.1.0\admin\orcl1\cdump
mkdir D:\oracle\product\10.1.0\oradata\orcl1

4.Set oracle_sid enviourment variable or create password file.

C:\>set oracle_sid=orcl1
C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdorcl1.ora password=oracle entries=5 force=y

Note: Password file create only "database folder".

5.Create services & start for new database.

C:\>D:\oracle\product\10.1.0\db_2\bin\oradim.exe -new -sid ORCL1 -startmode manual -spfileInstance created.
C:\>D:\oracle\product\10.1.0\db_2\bin\oradim.exe -edit -sid ORCL1 -startmode auto -srvcstart system

or
C:\>oradim -new -sid ORCL1 -startmode manual -spfile
C:\>oradim -edit -sid ORCL1 -startmode auto -srvcstart
C:\>D:\oracle\product\10.1.0\db_2\bin\sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Sat Nov 11 17:51:44 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL>

6.Run Create database scripts

SQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDB.sql
Enter value for syspassword: oracleConnected to an idle instance.
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\CreateDB.log
SQL> startup nomount pfile="D:\oracle\product\10.1.0\admin\orcl1\scripts\init.ora";ORACLE instance started.
Total System Global Area
171966464 bytesFixed Size
787988 bytesVariable Size
145488364 bytesDatabase Buffers
25165824 bytesRedo Buffers
524288 bytes
SQL> CREATE DATABASE "orcl1"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 100
7 DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\sysaux01.dbf' SIZE120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\oracle\product\10.1.0\oradata\orcl1\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
11 UNDO TABLESPACE "UNDOTBS1" DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
12 CHARACTER SET WE8MSWIN1252
13 NATIONAL CHARACTER SET AL16UTF16
14 LOGFILE GROUP 1 ('D:\oracle\product\10.1.0\oradata\orcl1\redo01.log') SIZE10240K,
15 GROUP 2 ('D:\oracle\product\10.1.0\oradata\orcl1\redo02.log') SIZE 10240K,
16 GROUP 3 ('D:\oracle\product\10.1.0\oradata\orcl1\redo03.log') SIZE 10240K
17 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";Enter value for systempassword: oracleold
17: USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"new
17: USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle"
Database created.
SQL> spool offSQL>

7. Run Datafile's scripts

SQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDBFiles.sql
SQL> connect SYS/&&sysPassword as SYSDBAConnected.
SQL> set echo on
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\CreateDBFiles.log
SQL> CREATE TABLESPACE "USERS" LOGGING DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";
Database altered.
SQL> spool offSQL>

8.Run Catalog Script of Database dictionary views.

SQL>@D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDBCatalog.sql
instead of you can only run below three scripts.
@D:\oracle\product\10.1.0\db_2\rdbms\admin\catalog.sql;
@D:\oracle\product\10.1.0\db_2\rdbms\admin\catproc.sql;
connect SYSTEM/&&systemPassword
@D:\oracle\product\10.1.0\db_2\sqlplus\admin\pupbld.sql;

above three scripts are parts of "CreateDBCatalog.sql" scripts.

9. Create last script for pfile creation

SQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\postDBCreation.sql
SQL> connect SYS/&&sysPassword as SYSDBAConnected.
SQL> set echo on
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\postDBCreation.log
SQL> connect SYS/&&sysPassword as SYSDBAConnected.
SQL> set echo on
SQL> create spfile='D:\oracle\product\10.1.0\db_2\database\spfileorcl1.ora' FROM pfile='D:\oracle\product\10.1.0\admin\orcl1\scripts\init.ora';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect SYS/&&sysPassword as SYSDBA
Connected to an idle instance.
SQL> startup ;
ORACLE instance started.
Total System Global Area
171966464 bytesFixed Size
787988 bytesVariable Size
145488364 bytesDatabase Buffers
25165824 bytesRedo Buffers
524288 bytes
Database mounted.
Database opened.
SQL> select 'utl_recomp_begin: ' to_char(sysdate, 'HH:MI:SS') from dual;
'UTL_RECOMP_BEGIN:'TO_CH--------------------------utl_recomp_begin: 06:22:46
1 row selected.
SQL> execute utl_recomp.recomp_serial();
PL/SQL procedure successfully completed.
SQL> select 'utl_recomp_end: ' to_char(sysdate, 'HH:MI:SS') from dual;
'UTL_RECOMP_END:'TO_CH------------------------utl_recomp_end: 06:22:48
1 row selected.
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\postDBCreation.log
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
C:\>



Hope this helps.

Oracle DBVERIFY Command

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

Because DBVERIFY can be run against an offline database, integrity checks are significantly faster.

dbv help=yes


dbv file='D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' blocksize=8192



DBVERIFY: Release 9.2.0.1.0 - Production on Thu Jan 1 15:13:45 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE =

D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF



DBV-00200: Block, dba 4245143, already marked corrupted



DBVERIFY - Verification complete



Total Pages Examined : 55040

Total Pages Processed (Data) : 38573

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 4901

Total Pages Failing (Index): 0

Total Pages Processed (Other): 1631

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 9935

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Oracle Sorting Enhancements (workarea_size_policy)

A serious problem in Oracle8i was the requirement that all dedicated connections use a one-size-fits-all sort_area_size . Oracle 9i and beyond now has the option of running automatic PGA memory management.
Oracle has introduced a new Oracle parameter called pga_aggregate_target . When the pga_aggregate_target parameter is set and you are using dedicated Oracle connections, Oracle9i will ignore all of the PGA parameters in the Oracle file, including sort_area_size, hash_area_size and sort_area_retained_size . Oracle recommends that the value of pga_aggregate_target be set to the amount of remaining memory (less a 10 percent overhead for other UNIX tasks) on the UNIX server after the instance has been started.
Once the pga_aggregate_target has been set, Oracle will automatically manage PGA memory allocation based upon the individual needs of each Oracle connection. Oracle9i allows the pga_aggregate_target parameter to be modified at the instance level with the alter system command, thereby allowing the DBA to dynamically adjust the total RAM region available to Oracle9i.
Oracle9i also introduces a new parameter called workarea_size_policy . When this parameter is set to automatic, all Oracle connections will benefit from the shared PGA memory. When workarea_size_policy is set to manual, connections will allocate memory according to the values for the sort_area_size parameter. Under the automatic mode, Oracle tries to maximize the number of work areas that are using optimal memory and uses one-pass memory for the others.
In addition to increasing the amount of memory devoted to sorting, you should also hunt down inefficient SQL that cause needless sorts. For example, union all does not cause a sort, whereas union does in a SQL query (to eliminate duplicate rows). The distinct keyword is oftentimes coded inappropriately (especially by folks transferring from Microsoft Access, which used to use distinct for nearly every query).