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.
Monday, February 2, 2009
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.
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
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
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
Oracle Family of Database Products
Oracle Server/ Standard Edition
Version of Oracle server for a small number of users and a smaller database
Oracle Enterprise Edition
Version of Oracle for a large number of users or a large database with advanced features for extensibility, performance, and management
Oracle Personal Edition
Single-user version of Oracle typically used for development of applications for deployment on other Oracle versions
Oracle Lite
Lightweight database engine for mobile computing on notebooks and handheld devices
Version of Oracle server for a small number of users and a smaller database
Oracle Enterprise Edition
Version of Oracle for a large number of users or a large database with advanced features for extensibility, performance, and management
Oracle Personal Edition
Single-user version of Oracle typically used for development of applications for deployment on other Oracle versions
Oracle Lite
Lightweight database engine for mobile computing on notebooks and handheld devices
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
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.
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.
RMAN-06054: media recovery requesting
RMAN-06054: media recovery requesting unknown log: thread x seq xxx lowscn xxxxxx
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
**********************************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database
Password:
connected to target database: ORCL (DBID=1134976990)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFORCL.ORA'; # default
****************************
RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
*************************************
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup database plus archivelog;
5> }
allocated channel: c1channel c1: sid=200 devtype=DISKStarting backup at 23-NOV-06current log archivedStarting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=25 recid=1 stamp=607282901channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174141_2PC98Y2P_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11
Finished backup at 23-NOV-06Starting backup at 23-NOV-06channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:01:33Finished backup at 23-NOV-06Starting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=26 recid=2 stamp=607283007channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174328_2PC9D278_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02Finished backup at 23-NOV-06Starting Control File and SPFILE Autobackup at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-NOV-06released channel: c1
********************************************************
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.dbf
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.ctl
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.log
**********************************************************
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 23 17:47:56 2006Copyright (c) 1982, 2004,
Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464
bytesFixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
*************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database Password:connected to target database: orcl (not mounted)
**********************************************
RMAN> restore controlfile from autobackup;
Starting restore at 23-NOV-06using channel ORA_DISK_1recovery area destination: D:\oracle\product\10.1.0\flash_recovery_areadatabase name (or lock name space) used for search: ORCLchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPchannel ORA_DISK_1: controlfile restore from autobackup completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 23-NOV-06
****************************************
RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1
****************************************
RMAN> run
2> {
3> restore database;
4> recover database;
5> }
Starting restore at 23-NOV-06Starting implicit crosscheck backup at 23-NOV-06allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 23-NOV-06Starting implicit crosscheck copy at 23-NOV-06using channel ORA_DISK_1Finished implicit crosscheck copy at 23-NOV-06searching for all files in the recovery areacataloging files...cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26unable to find archive logarchive log thread=1 sequence=27RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2006 17:53:44RMAN-06054: media recovery requesting unknown log: thread 1 seq 27 lowscn 434810
**************************************
Note : RMAN-06054: media recovery requesting unknown log: thread string seq string lowscn string Cause: Media recovery is requesting a log whose existance is not recorded in the recovery catalog or control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point in time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.
******************************************************
RMAN> run
2> {
3> SET UNTIL SEQUENCE 27 THREAD 1;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> }
executing command: SET until clauseStarting restore at 23-NOV-06using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26media recovery completeFinished recover at 23-NOV-06database opened
*************************************
RMAN> QUIT
Recovery Manager complete.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list
Database log mode Archive Mode
Automatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> --take complete consistent bkp of ur database.
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
**********************************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database
Password:
connected to target database: ORCL (DBID=1134976990)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFORCL.ORA'; # default
****************************
RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
*************************************
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup database plus archivelog;
5> }
allocated channel: c1channel c1: sid=200 devtype=DISKStarting backup at 23-NOV-06current log archivedStarting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=25 recid=1 stamp=607282901channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174141_2PC98Y2P_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11
Finished backup at 23-NOV-06Starting backup at 23-NOV-06channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:01:33Finished backup at 23-NOV-06Starting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=26 recid=2 stamp=607283007channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174328_2PC9D278_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02Finished backup at 23-NOV-06Starting Control File and SPFILE Autobackup at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-NOV-06released channel: c1
********************************************************
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.dbf
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.ctl
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.log
**********************************************************
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 23 17:47:56 2006Copyright (c) 1982, 2004,
Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464
bytesFixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
*************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database Password:connected to target database: orcl (not mounted)
**********************************************
RMAN> restore controlfile from autobackup;
Starting restore at 23-NOV-06using channel ORA_DISK_1recovery area destination: D:\oracle\product\10.1.0\flash_recovery_areadatabase name (or lock name space) used for search: ORCLchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPchannel ORA_DISK_1: controlfile restore from autobackup completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 23-NOV-06
****************************************
RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1
****************************************
RMAN> run
2> {
3> restore database;
4> recover database;
5> }
Starting restore at 23-NOV-06Starting implicit crosscheck backup at 23-NOV-06allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 23-NOV-06Starting implicit crosscheck copy at 23-NOV-06using channel ORA_DISK_1Finished implicit crosscheck copy at 23-NOV-06searching for all files in the recovery areacataloging files...cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26unable to find archive logarchive log thread=1 sequence=27RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2006 17:53:44RMAN-06054: media recovery requesting unknown log: thread 1 seq 27 lowscn 434810
**************************************
Note : RMAN-06054: media recovery requesting unknown log: thread string seq string lowscn string Cause: Media recovery is requesting a log whose existance is not recorded in the recovery catalog or control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point in time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.
******************************************************
RMAN> run
2> {
3> SET UNTIL SEQUENCE 27 THREAD 1;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> }
executing command: SET until clauseStarting restore at 23-NOV-06using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26media recovery completeFinished recover at 23-NOV-06database opened
*************************************
RMAN> QUIT
Recovery Manager complete.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list
Database log mode Archive Mode
Automatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> --take complete consistent bkp of ur database.
Real Time Exprience about DB Upgrade
Keep in mind there is two types of upgrade procedure
1. From lower oracle verion to higher oracle version
For eg:
Oracle 8i,9i,10g to oracle 11g
or
Oracle 8i,9i to oracle 10g
or
Oracle 8i to oracle 9i
or
so on.
2. patch set or cpu(critical patch update) upgrade.
it is always apply on same oracle version means if we are using oracle 10gr1 then we can apply only latest patchset for 10gr1
For eg:
If we are using oracle 8i (8.1.0) then we can apply only latest patch set for 8i is (8.1.7.4) patchset
If we are using oracle 9ir1(9.0.1) then we can apply only latest patch set for 9ir1 is(9.0.1.4) (not sure about latest patchset )
If we are using oracle 9ir2(9.2.0) then we can apply only latest patch set for 9ir2 is (9.2.0.7) same like10gr1 ---10.1.0.5.0, 10gr2 ---10.2.0.4.0 , 11gr1 ---N/A
according to me apply patchset is easy and safest task compare to upgrade from lower oracle version to higher oracle version.
Oracle enhanced with every new release so some feature in lower version are obsolete & desupported in higher version. Same like in higher version new features are introduced which are not exist in lower version. And upgrade/migrate is one of the important task for DBA. so we have to do it
I always recommended never ever upgrade directly PRODUCTION box without prior testing on TEST/DEV server.
1. From lower oracle verion to higher oracle version
For eg:
Oracle 8i,9i,10g to oracle 11g
or
Oracle 8i,9i to oracle 10g
or
Oracle 8i to oracle 9i
or
so on.
2. patch set or cpu(critical patch update) upgrade.
it is always apply on same oracle version means if we are using oracle 10gr1 then we can apply only latest patchset for 10gr1
For eg:
If we are using oracle 8i (8.1.0) then we can apply only latest patch set for 8i is (8.1.7.4) patchset
If we are using oracle 9ir1(9.0.1) then we can apply only latest patch set for 9ir1 is(9.0.1.4) (not sure about latest patchset )
If we are using oracle 9ir2(9.2.0) then we can apply only latest patch set for 9ir2 is (9.2.0.7) same like10gr1 ---10.1.0.5.0, 10gr2 ---10.2.0.4.0 , 11gr1 ---N/A
according to me apply patchset is easy and safest task compare to upgrade from lower oracle version to higher oracle version.
Oracle enhanced with every new release so some feature in lower version are obsolete & desupported in higher version. Same like in higher version new features are introduced which are not exist in lower version. And upgrade/migrate is one of the important task for DBA. so we have to do it
I always recommended never ever upgrade directly PRODUCTION box without prior testing on TEST/DEV server.
Process Analysis, Process Improvement
Process Analysis
Process Improvement
Improvements in cost, quality, flexibility, and speed are commonly sought. The following lists some of the ways that processes can be improved.
· Reduce work-in-process inventory - reduces lead time.
· Add additional resources to increase capacity of the bottleneck. For example, an additional machine can be added in parallel to increase the capacity.
· Improve the efficiency of the bottleneck activity - increases process capacity.
· Move work away from bottleneck resources where possible - increases process capacity.
· Increase availability of bottleneck resources, for example, by adding an additional shift - increases process capacity.
· Minimize non-value adding activities - decreases cost, reduces lead time. Non-value adding activities include transport, rework, waiting, testing and inspecting, and support activities.
· Redesign the product for better manufacturability - can improve several or all process performance measures.
· Flexibility can be improved by outsourcing certain activities. Flexibility also can be enhanced by postponement, which shifts customizing activities to the end of the process.
In some cases, dramatic improvements can be made at minimal cost when the bottleneck activity is severely limiting the process capacity. On the other hand, in well-optimized processes, significant investment may be required to achieve a marginal operational improvement. Because of the large investment, the operational gain may not generate a sufficient rate of return. A cost-benefit analysis should be performed to determine if a process change is worth the investment. Ultimately, net present value will determine whether a process "improvement" really is an improvement.
Process Improvement
Improvements in cost, quality, flexibility, and speed are commonly sought. The following lists some of the ways that processes can be improved.
· Reduce work-in-process inventory - reduces lead time.
· Add additional resources to increase capacity of the bottleneck. For example, an additional machine can be added in parallel to increase the capacity.
· Improve the efficiency of the bottleneck activity - increases process capacity.
· Move work away from bottleneck resources where possible - increases process capacity.
· Increase availability of bottleneck resources, for example, by adding an additional shift - increases process capacity.
· Minimize non-value adding activities - decreases cost, reduces lead time. Non-value adding activities include transport, rework, waiting, testing and inspecting, and support activities.
· Redesign the product for better manufacturability - can improve several or all process performance measures.
· Flexibility can be improved by outsourcing certain activities. Flexibility also can be enhanced by postponement, which shifts customizing activities to the end of the process.
In some cases, dramatic improvements can be made at minimal cost when the bottleneck activity is severely limiting the process capacity. On the other hand, in well-optimized processes, significant investment may be required to achieve a marginal operational improvement. Because of the large investment, the operational gain may not generate a sufficient rate of return. A cost-benefit analysis should be performed to determine if a process change is worth the investment. Ultimately, net present value will determine whether a process "improvement" really is an improvement.
Subscribe to:
Posts (Atom)
