RECOVERING THE DATABASE IF SYSTEM DATAFILE IS LOST OR CORRUPTED

[oracle@dayananda ~]$ export ORACLE_SID=qsoft

[oracle@dayananda ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Oct 31 16:26:10 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/qsoft/archive

Oldest online log sequence 0

Next log sequence to archive 1

Current log sequence 1

SQL> select name from v$datafile;

NAME

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

/home/oracle/qsoft/datafiles/system01.dbf

/home/oracle/qsoft/datafiles/undotbs1.dbf

/home/oracle/qsoft/datafiles/sysaux01.dbf

/home/oracle/qsoft/datafiles/users01.dbf

SQL> select name from v$controlfile;

NAME

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

/home/oracle/qsoft/control/c1.ctl

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !

[oracle@dayananda ~]$ mkdir backup

[oracle@dayananda ~]$ cd qsoft/

[oracle@dayananda qsoft]$ cd control/

[oracle@dayananda control]$ ls

c1.ctl

[oracle@dayananda control]$ cp c1.ctl /home/oracle/backup/

[oracle@dayananda control]$ cd ..

[oracle@dayananda qsoft]$ cd datafiles/

[oracle@dayananda datafiles]$ ls

redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@dayananda datafiles]$ cp *.dbf /home/oracle/backup/

[oracle@dayananda datafiles]$ cd ..

[oracle@dayananda qsoft]$ cd archive/

[oracle@dayananda archive]$ ls

[oracle@dayananda archive]$ cd $ORACLE_HOME/dbs

[oracle@dayananda dbs]$ ls

initqsoft.ora spfileqsoft.ora

[oracle@dayananda dbs]$ cp initqsoft.ora /home/oracle/backup/

[oracle@dayananda dbs]$ cp spfileqsoft.ora /home/oracle/backup/

[oracle@dayananda dbs]$ cd /home/oracle/backup/

[oracle@dayananda backup]$ ls

c1.ctl initqsoft.ora sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf spfileqsoft.ora

[oracle@dayananda backup]$ exit

exit

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@dayananda ~]$ cd qsoft/

[oracle@dayananda qsoft]$ cd datafiles/

[oracle@dayananda datafiles]$ ls

redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@dayananda datafiles]$ rm -rf system01.dbf

[oracle@dayananda datafiles]$ cd

[oracle@dayananda ~]$ exit

exit

SQL> startup;

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 – see DBWR trace file

ORA-01110: data file 1: ‘/home/oracle/qsoft/datafiles/system01.dbf’

SQL> !

[oracle@dayananda ~]$ cd backup/

[oracle@dayananda backup]$ ls

c1.ctl initqsoft.ora sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@dayananda backup]$ cp system01.dbf /home/oracle/qsoft/datafiles/

[oracle@dayananda backup]$ cd

[oracle@dayananda ~]$ exit

exit

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 348127232 bytes

Fixed Size 1219328 bytes

Variable Size 289408256 bytes

Database Buffers 50331648 bytes

Redo Buffers 7168000 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/home/oracle/qsoft/datafiles/system01.dbf’

SQL> recover datafile 1;

ORA-00279: change 373322 generated at 10/31/2008 16:30:29 needed for thread 1

ORA-00289: suggestion : /home/oracle/qsoft/archive/arch_1_669569864_1.arc

ORA-00280: change 373322 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

cold cloning

This technical document provides a quick introduction on how to clone a Production database
using the following methods.
1. Cold Cloning.
2. Hot Cloning.
3. Cloning using RMAN.
1. Cold cloning :
Cold cloning is one of the reliable methods that can be utilized for the purpose of duplicating a
database. The only drawback of this method is, the database should be down during the cloning
process.

Steps to be followed:

1. Start the database which is to be cloned.
[daya@training target]$ export ORACLE_SID=target.
[daya@training target]$ sqlplus "/as sysdba"
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.


SQL> sho parameter db_name
NAME TYPE VALUE
db_name string target
2. Take the control file backup.
SQL> alter database backup controlfile to trace;
Database altered.

3. Create a parameter file for the Clone database.
SQL> create pfile='/data/cloning/catalog/initcatalog.ora' from
spfile='$ORACLE_HOME/dbs/spfiletarget.ora';
File created.

4. Shutdown the database and exit from SQL.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

 Copy all data files and redo log files from target database to the clone.
[daya@training target]$ cp ./data/*.* /data/cloning/catalog/data/
[daya@training target]$ cp ./redo/*.* /data/cloning/catalog/redo/

 Copy the resetlogs part from the trace file into an SQL file.
[daya@training udump]$ vi rc.sql

CREATE CONTROLFILE REUSE DATABASE "TARGET" RESETLOGS
ARCHIVELOG
SET
STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/data/cloning/target/redo/redo01.log' SIZE 100M,
GROUP 2 '/data/cloning/target/redo/redo02.log' SIZE 100M,
GROUP 3 '/data/cloning/target/redo/redo03.log' SIZE 100M
STANDBY
LOGFILE
DATAFILE
'/data/cloning/target/data/system01.dbf',
'/data/cloning/target/data/undotbs01.dbf',
'/data/cloning/target/data/cwmlite01.dbf',
'/data/cloning/target/data/drsys01.dbf',
'/data/cloning/target/data/example01.dbf',
'/data/cloning/target/data/indx01.dbf',
'/data/cloning/target/data/odm01.dbf',
'/data/cloning/target/data/tools01.dbf',
'/data/cloning/target/data/users01.dbf',
'/data/cloning/target/data/xdb01.dbf'
CHARACTER SET WE8ISO8859P1
4


7. Change the database name and the datafile and redo log file p aths.
CREATE CONTROLFILE SET DATABASE "CATALOG" RESETLOGS ARCHIVELOG
SET
STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/data/cloning/catalog/redo/redo01.log' SIZE 100M,
GROUP 2 '/data/cloning/catalog/redo/redo02.log' SIZE 100M,
GROUP 3 '/data/cloning/catalog/redo/redo03.log' SIZE 100M
STANDBY
LOGFILE
DATAFILE
'/data/cloning/catalog/data/system01.dbf',
'/data/cloning/catalog/data/undotbs01.dbf',
'/data/cloning/catalog/data/cwmlite01.dbf',
'/data/cloning/catalog/data/drsys01.dbf',
'/data/cloning/catalog/data/example01.dbf',
'/data/cloning/catalog/data/indx01.dbf',
'/data/cloning/catalog/data/odm01.dbf',
'/data/cloning/catalog/data/tools01.dbf',
'/data/cloning/catalog/data/users01.dbf',
'/data/cloning/catalog/data/xdb01.dbf'
CHARACTER SET WE8ISO8859P1
5

 Edit the parameter file of the clone database.

[daya@training catalog]$ vi initcatalog.ora
*.aq_tm_processes=1
*.background_dump_dest='/data/cloning/target/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/data/cloning/target/control/control01.ctl',
'/data/cloning/target/control/control02.ctl',
'/data/cloning/target/control/control03.ctl'
*.core_dump_dest='/data/cloning/target/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain='com'
*.db_file_multiblock_read_count=16
*.db_name='target'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=targetXDB)','(PROTOCOL=TCP)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='target'
*.java_pool_size=83886080
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/data/cloning/target/arch/'
*.log_archive_format='%t_%s.arc'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/data/cloning/target/udump'
9. Set the paths of Control file, Trace files, BD_NAME & INSTANCE_NAME
*.aq_tm_processes=1
*.background_dump_dest='/data/cloning/catalog/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/data/cloning/catalog/control/control01.ctl',
'/data/cloning/catalog/control/control02.ctl',
'/data/cloning/catalog/control/control03.ctl'
*.core_dump_dest='/data/cloning/catalog/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain='com'
*.db_file_multiblock_read_count=16
*.db_name='catalog'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=targetXDB)','(PROTOCOL=TCP)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='catalog'
*.java_pool_size=83886080
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/data/cloning/catalog/arch/'
*.log_archive_format='%t_%s.arc'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=524288


12. Recreate the control file of the Clone.
SQL> @/data/cloning/target/udump/rc.sql
Control file created.

13. Open the clone database using the reset log option.
SQL> alter database open resetlogs;
Database altered.

SQL> sho parameter db_name
NAME TYPE VALUE
db_name string catalog

NOTE:
1. When ever the Cold Cloning method is used to clone a database, the database which is being
cloned need not be in archive log mode.
2. The name of the clone database has to be changed only if both the actual database and the
clone are to be present on the same system.
3. The primary database should be shut down before copying the data files and redo log files,
no recovery required to start the clone.



the password file for the clone database.
[daya@training dbs]$ orapwd
file=/home/srinanda/OraHome1/dbs/orapwcatalog
password=change_on_install

11. Start the Clone database to nomount state.
[daya@training catalog]$ export ORACLE_SID=catalog
[daya@training catalog]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 Production
on Thu Apr 24 16:22:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/data/cloning/catalog/initcatalog.ora'
ORACLE instance started.
Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

12. Recreate the control file of the Clone.
SQL> @/data/cloning/target/udump/rc.sql
Control file created.

13. Open the clone database using the reset log option.
SQL> alter database open resetlogs;
Database altered.
SQL> sho parameter db_name
NAME TYPE VALUE
db_name string catalog

NOTE:
1. When ever the Cold Cloning method is used to clone a database, the database which is being
cloned need not be in archive log mode.
2. The name of the clone database has to be changed only if both the actual database and the
clone are to be present on the same system.
3. The primary database should be shut down before copying the data files and redo log files,
no recovery required to start the clone.