How to recover database in case of tablespace are corrupted
and redo and control files are intact.
First delete SYSTEM01.DBF file
Now see the SYSTEM01.DBF is missing or might be corrupted.
Now try to login
Now let’s play a game.
C:\Documents and Settings\mars>rman
catalog=rman/rman@k6 target=sys/sys@k5
Recovery
Manager: Release 10.2.0.1.0 - Production on Wed Sep 3 16:56:23 2014
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected to
target database: K5 (DBID=3069599529, not open)
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554:
initialization of internal recovery manager package failed
RMAN-04004:
error from recovery catalog database: ORA-12514: TNS:listener does not
currently know of service requested in connect descriptor
C:\Documents and Settings\mars>set
Oracle_Sid=k6
C:\Documents and Settings\mars>rman catalog=rman/rman@k6
target=sys/sys@k5
Recovery
Manager: Release 10.2.0.1.0 - Production on Wed Sep 3 16:57:23 2014
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected to
target database: K5 (DBID=3069599529, not open)
RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554:
initialization of internal recovery manager package failed
RMAN-04004:
error from recovery catalog database: ORA-12514: TNS:listener does not
currently know of service requested in connect descriptor
C:\Documents and Settings\mars>set
ORACLE_SID=K5
C:\Documents and Settings\mars>rman
catalog=rman/rman@k6 target=sys/sys@k5
Recovery
Manager: Release 10.2.0.1.0 - Production on Wed Sep 3 16:57:32 2014
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected to
target database: K5 (DBID=3069599529, not open)
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554:
initialization of internal recovery manager package failed
RMAN-04004:
error from recovery catalog database: ORA-12514: TNS:listener does not
currently know of service requested in connect descriptor
C:\Documents and Settings\mars>lsnrctl
stop
LSNRCTL for
32-bit Windows: Version 10.2.0.1.0 - Production on 03-SEP-2014 16:59:46
Copyright
(c) 1991, 2005, Oracle. All rights
reserved.
Connecting
to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command
completed successfully
C:\Documents and Settings\mars>lsnrctl
start
LSNRCTL for
32-bit Windows: Version 10.2.0.1.0 - Production on 03-SEP-2014 16:59:50
Copyright
(c) 1991, 2005, Oracle. All rights
reserved.
Starting
tnslsnr: please wait...
TNSLSNR for
32-bit Windows: Version 10.2.0.1.0 - Production
System
parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages
written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening
on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening
on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pluto)(PORT=1521)))
Connecting
to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of
the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows:
Version 10.2.0.1.0 - Produ
ction
Start
Date 03-SEP-2014 16:59:51
Uptime 0 days 0 hr. 0 min. 1 sec
Trace
Level off
Security ON: Local OS Authentication
SNMP OFF
Listener
Parameter File
C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log
File
C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pluto)(PORT=1521)))
Services
Summary...
Service
"K5" has 1 instance(s).
Instance "K5", status UNKNOWN, has
1 handler(s) for this service...
The command
completed successfully
C:\Documents and Settings\mars>set
ORACLE_SID=K5
C:\Documents and Settings\mars>rman
catalog=rman/rman@k6 target=sys/sys@k5
Recovery
Manager: Release 10.2.0.1.0 - Production on Wed Sep 3 17:00:04 2014
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected to
target database: K5 (DBID=3069599529, not open)
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554:
initialization of internal recovery manager package failed
RMAN-04004:
error from recovery catalog database: ORA-12514: TNS:listener does not
currently know of service requested in connect descriptor
NOTE: NOW IN THIS SITUATION START RMAN
DATABASE SERVICE, THAT I FORGET TO START
C:\Documents
and Settings\mars>net start OracleServiceK6
The
OracleServiceK6 service is starting.......
The
OracleServiceK6 service was started successfully.
C:\Documents
and Settings\mars>rman catalog=rman/rman@k6 target=sys/sys@k5
Recovery
Manager: Release 10.2.0.1.0 - Production on Wed Sep 3 17:02:40 2014
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected to
target database: K5 (DBID=3069599529, not open)
connected to
recovery catalog database
RMAN>
RMAN>
restore database;
Starting
restore at 03-SEP-14
new
incarnation of database registered in recovery catalog
starting
full resync of recovery catalog
full resync
complete
starting
full resync of recovery catalog
full resync
complete
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=156 devtype=DISK
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of restore command at 09/03/2014 17:07:48
RMAN-06026:
some targets not found - aborting restore
RMAN-06023:
no backup or copy of datafile 5 found to restore
RMAN-06023:
no backup or copy of datafile 4 found to restore
RMAN-06023:
no backup or copy of datafile 3 found to restore
RMAN-06023:
no backup or copy of datafile 2 found to restore
RMAN-06023:
no backup or copy of datafile 1 found to restore
RMAN>
show configuration
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00558:
error encountered while parsing input commands
RMAN-01009:
syntax error: found "identifier": expecting one of: "all, archivelog
, auxiliary,
auxname, backup, channel, controlfile, datafile, device, default, e
xclude,
encryption, maxsetsize, retention, snapshot"
RMAN-01008:
the bad identifier was: configuration
RMAN-01007:
at line 1 column 7 file: standard input
RMAN> show config
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00558:
error encountered while parsing input commands
RMAN-01009:
syntax error: found "identifier": expecting one of: "all,
archivelog
, auxiliary,
auxname, backup, channel, controlfile, datafile, device, default, e
xclude,
encryption, maxsetsize, retention, snapshot"
RMAN-01008:
the bad identifier was: config
RMAN-01007:
at line 1 column 7 file: standard input
RMAN> restore database;
Starting
restore at 03-SEP-14
using
channel ORA_DISK_1
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of restore command at 09/03/2014 17:08:34
RMAN-06026:
some targets not found - aborting restore
RMAN-06023:
no backup or copy of datafile 5 found to restore
RMAN-06023:
no backup or copy of datafile 4 found to restore
RMAN-06023:
no backup or copy of datafile 3 found to restore
RMAN-06023:
no backup or copy of datafile 2 found to restore
RMAN-06023:
no backup or copy of datafile 1 found to restore
RMAN> list backup;
could not
read file header for datafile 1 error reason 4
List of
Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
95 Full
1.19G DISK 00:03:15 07-OCT-14
BP Key: 99 Status: EXPIRED Compressed: NO Tag: TAG20141007T162611
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_04PKFJL3_1_1_%S_%P
List of Datafiles in backup set 95
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
1
Full 2624567 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\SYSTEM01.DBF
2
Full 2624567 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\UNDOTBS01.DBF
3
Full 2624567 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\SYSAUX01.DBF
4
Full 2624567 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\USERS01.DBF
5
Full 2624567 07-OCT-14 C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\RMAN01.DBF
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
96 51.50K
DISK 00:00:01 07-OCT-14
BP Key: 100 Status: EXPIRED Compressed: NO Tag: TAG20141007T162939
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_05PKFJRK_1_1_%S_%P
List of Archived Logs in backup set 96
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
67 2624541 07-OCT-14 2624657 07-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
135 Full
1.19G DISK 00:03:17 07-OCT-14
BP Key: 139 Status: AVAILABLE Compressed: NO Tag: TAG20141007T162138
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_08PKFJCI_1_1_%S_%P
List of Datafiles in backup set 135
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
1
Full 2628863 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\SYSTEM01.DBF
2
Full 2628863 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\UNDOTBS01.DBF
3
Full 2628863 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\SYSAUX01.DBF
4
Full 2628863 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\USERS01.DBF
5
Full 2628863 07-OCT-14
C:\ORACLE\PRODUCT\10.2.0\ORADATA\K5\RMAN01.DBF
BS Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
154 3.00K
DISK 00:00:01 07-OCT-14
BP Key: 160 Status: AVAILABLE Compressed: NO Tag: TAG20141007T162506
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_09PKFJJ3_1_1_%S_%P
List of Archived Logs in backup set 154
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
69 2628846 07-OCT-14 2628934 07-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
200 23.40M
DISK 00:00:04 07-OCT-14
BP Key: 203 Status: EXPIRED Compressed: NO Tag: TAG20141007T162655
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0BPKFJMG_1_1_%S_%P
List of Archived Logs in backup set 200
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
66 2616226 25-AUG-14 2624541 07-OCT-14
1
67 2624541 07-OCT-14 2624657 07-OCT-14
1
68 2624657 07-OCT-14 2628846 07-OCT-14
1
69 2628846 07-OCT-14 2628934 07-OCT-14
1
70 2628934 07-OCT-14 2629171 07-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
274 Full
6.80M DISK 00:00:02 07-OCT-14
BP Key: 277 Status: AVAILABLE Compressed: NO Tag: TAG20141007T164533
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\K5\AUTOBACKUP\2014_10_07\O1_MF_S_860345134_B37NLZWB_.BKP
Control File Included: Ckp SCN: 2631144 Ckp time: 07-OCT-14
SPFILE Included: Modification time: 07-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
284 23.40M
DISK 00:00:04 07-OCT-14
BP Key: 287 Status: AVAILABLE Compressed: NO Tag: TAG20141007T161320
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0EPKFIT0_1_1_%S_%P
List of Archived Logs in backup set 284
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
66 2616226 25-AUG-14 2624541 07-OCT-14
1
67 2624541 07-OCT-14 2624657 07-OCT-14
1
68 2624657 07-OCT-14 2628846 07-OCT-14
1
69 2628846 07-OCT-14 2628934 07-OCT-14
1
70 2628934 07-OCT-14 2629171 07-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
809 24.21M
DISK 00:00:04 07-OCT-14
BP Key: 814 Status: AVAILABLE Compressed: NO Tag: TAG20141007T164556
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0IPKFKQ5_1_1_%S_%P
List of Archived Logs in backup set 809
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
66 2616226 25-AUG-14 2624541 07-OCT-14
1
67 2624541 07-OCT-14 2624657 07-OCT-14
1
68 2624657 07-OCT-14 2628846 07-OCT-14
1
69 2628846 07-OCT-14 2628934 07-OCT-14
1
70 2628934 07-OCT-14 2629171 07-OCT-14
1
71 2629171 07-OCT-14 2633128 07-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
810 53.00K
DISK 00:00:01 07-OCT-14
BP Key: 815 Status: AVAILABLE Compressed: NO Tag: TAG20141007T164556
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0JPKFKQD_1_1_%S_%P
List of Archived Logs in backup set 810
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
1 2633129 07-OCT-14 2634252 07-OCT-14
1
2 2634252 07-OCT-14 2634521 07-OCT-14
BS Key Size
Device Type Elapsed Time
Completion Time
-------
---------- ----------- ------------ ---------------
811 2.00K
DISK 00:00:01 07-OCT-14
BP Key: 816 Status: AVAILABLE Compressed: NO Tag: TAG20141007T164556
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0KPKFKQF_1_1_%S_%P
List of Archived Logs in backup set 811
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
1 2628935 07-OCT-14 2633128 07-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
848 Full
6.80M DISK 00:00:03 07-OCT-14
BP Key: 850 Status: AVAILABLE Compressed: NO Tag: TAG20141007T164610
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\K5\AUTOBACKUP\2014_10_07\O1_MF_S_860345170_B37NN400_.BKP
Control File Included: Ckp SCN: 2634542 Ckp time: 07-OCT-14
SPFILE Included: Modification time: 07-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
926 24.21M
DISK 00:00:05 10-OCT-14
BP Key: 931 Status: AVAILABLE Compressed: NO Tag: TAG20141010T164704
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0MPKNI08_1_1_%S_%P
List of Archived Logs in backup set 926
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
66 2616226
25-AUG-14 2624541 07-OCT-14
1
67 2624541 07-OCT-14 2624657 07-OCT-14
1
68 2624657 07-OCT-14 2628846 07-OCT-14
1
69 2628846 07-OCT-14 2628934 07-OCT-14
1
70 2628934 07-OCT-14 2629171 07-OCT-14
1
71 2629171 07-OCT-14 2633128 07-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
927 75.00K
DISK 00:00:01 10-OCT-14
BP Key: 932 Status: AVAILABLE Compressed: NO Tag: TAG20141010T164704
Piece Name:
C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0NPKNI0H_1_1_%S_%P
List of Archived Logs in backup set 927
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
1 2633129 07-OCT-14 2634252 07-OCT-14
1
2 2634252 07-OCT-14 2634521 07-OCT-14
1
3 2634521 07-OCT-14 2634607 10-OCT-14
BS Key Size
Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
928 2.00K
DISK 00:00:02 10-OCT-14
BP Key: 933 Status: AVAILABLE Compressed: NO Tag: TAG20141010T164704
Piece Name: C:\ORACLE\RMANBACKUP\BACKUPK5_DB_0OPKNI0J_1_1_%S_%P
List of Archived Logs in backup set 928
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
1 2628935 07-OCT-14 2633128 07-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
969 Full
6.80M DISK 00:00:04 10-OCT-14
BP Key: 1032 Status: AVAILABLE Compressed: NO Tag: TAG20141010T16471
9
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\K5\AUTOBACKUP\2014_10_10\O1_MF_S_860604439_B3HKTBHN_.BKP
Control File Included: Ckp SCN: 2634747 Ckp time: 10-OCT-14
SPFILE Included: Modification time: 10-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
1167 Full
6.80M DISK 00:00:04 10-OCT-14
BP Key: 1169 Status: AVAILABLE Compressed: NO Tag: TAG20141010T170159
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\K5\AUTOBACKUP\2014_10_10\O1_MF_S_860605319_B3HLOSXS_.BKP
Control File Included: Ckp SCN: 2634827 Ckp time: 10-OCT-14
SPFILE Included: Modification time: 10-OCT-14
RMAN>
