User Managed (Hot Backup) Scenarios :
This scenarios is just used to practice.
1. How you will identify that the database was clean shutdown last time?
Solution :
check in alert log file ,redo thread is completely closed then the database is clean shutdown.
2. Recover a lost datafile in Noarchive Mode - data is present in Redolog
i. Restore damaged datafile
ii. Recover it
Solution :
datafile is recovered using redo and command recover datafile 'loation';
[oracle@localhost 2018_06_01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 1 14:11:11 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 461375360 bytes
Database Buffers 293601280 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u02/app/oracle/oradata/Navy/users01.dbf'
SQL> recover datafile '/u02/app/oracle/oradata/Navy/users01.dbf';
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
3. Recover a lost datafile in Noarchive Mode - data is not present in redologfile (Incomplete Recovery)
i. Restore cold backup
4. Recover System Datafile in Archive Mode (Database was Closed initially)
Note: In case of system datafile, we cannot take the system datafile offline, so we have to go to mount and restore and recover the datafile
i. Startup mount
ii. Restore damaged datafile
iii. Recover datafile
iv. Alter databse open;
5. Recover Non-System Datafile in Archive Mode (Database was closed initially)
Recover Non-system datafile to new location (Database was closed initially)
i. Startup mount
ii. alter tablespace .. offline
iii. alter databse open
iv. restore datafile (to new location)
alter databse rename datafile ... to ...
v. recover tabclespace
vi. alter tabclespace ... online
6. Recover a datafile in Begin Backup mode (While taking backup of tablespace - Power goes off. how to recover it)
i. Startup
ii. check v$Backup & V$recover_file
iii. recover datafile
iv. alter database open
solution :
startup
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> inser into ins.n1 select * from ins.n1;
SP2-0734: unknown command beginning "inser into..." - rest of line ignored.
SQL> insert into ins.n1 select * from ins.n1;
37 rows created.
SQL> /
74 rows created.
SQL> /
148 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> show user
USER is "SYS"
###on another terminal##
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dev
SQL> shu abort
ORACLE instance shut down.
SQL>
SQL> select * from ins.n1;
select * from ins.n1
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 24588
Session ID: 125 Serial number: 5
SQL> startup
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 465569664 bytes
Database Buffers 289406976 bytes
Redo Buffers 6914048 bytes
Database mounted.
ORA-10873: file 4 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 4: '/u02/app/oracle/oradata/dev/users01.dbf'
SQL> desc v$backup
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
STATUS VARCHAR2(18)
CHANGE# NUMBER
TIME DATE
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 1021310 01-JUN-18
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 1024922 01-JUN-18
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 1021310 01-JUN-18
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 1024922 01-JUN-18
SQL> alter database open;
Database altered.
########now with recovery option#### dont end backup .
SQL> startup
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2226872 bytes
Variable Size 1157629256 bytes
Database Buffers 419430400 bytes
Redo Buffers 7421952 bytes
Database mounted.
ORA-10873: file 4 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 4: '/u01/app/oracle/oradata/dev/users01.dbf'
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 1817029 01-JUN-18
7. Recover a datafile, no backup exists but controlfile contains the name of datafile.
(Controlfile should be older than datafile otherwise you cannot recover the datafile) (Scenario 5)
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 1 19:56:38 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
dev
SQL> alter database begin backup;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system chechpoint;
alter system chechpoint
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter systemcheckpoint;
alter systemcheckpoint
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter system checkpoint;
System altered.
SQL> select status,group# from v$log;
STATUS GROUP#
---------------- ----------
INACTIVE 1
CURRENT 2
INACTIVE 3
SQL> insert into INA.INA1 SELECT * FROM INA.INA1;
15 rows created.
SQL> /
30 rows created.
SQL> /
60 rows created.
SQL> COMMIT
2 ;
Commit complete.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SHU IMMEDIATE;
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: '/u02/app/oracle/oradata/dev/system01.dbf'
SQL> ALTER DATABASE END BACKUP;
Database altered.
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 465569664 bytes
Database Buffers 289406976 bytes
Redo Buffers 6914048 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u02/app/oracle/oradata/dev/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> recover database using backup controlfile;
ORA-00279: change 1047410 generated at 06/01/2018 20:01:33 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_01/o1_mf_1_35_fk2phrw
7_.arc
ORA-00280: change 1047410 for thread 1 is in sequence #35
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1047514 generated at 06/01/2018 20:06:40 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_01/o1_mf_1_36_%u_.arc
ORA-00280: change 1047514 for thread 1 is in sequence #36
ORA-00278: log file
'/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_01/o1_mf_1_35_fk2phr
w7_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_01/o1_mf_1_36_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 1047514 generated at 06/01/2018 20:06:40 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_01/o1_mf_1_36_%u_.arc
ORA-00280: change 1047514 for thread 1 is in sequence #36
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/u02/app/oracle/oradata/dev/redo03.log'
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dev
SQL>
8. Recover a Datafile belong to temporary tablespace.
We can not recover temporary tablespace because we don’t need that data it’s just used for sorting,merging etc( read question 9 you will get answer )
9. I am trying to take hot backup for all the table spaces to have a full backup of database with 9i...I took backup of all table spaces went ok....but for the temp it doesnt work and i dont think theres any diffrent cammand for that i used the following command and getting the ERROR massage:
>Alter tablespace temp begin backup;
ORA-03217: Invalid option for alter temporary tablespace
(Don't back up TEMP, it cannot be recovered anyway. If you have to do a restore, just recreate the TEMP tablespace as part of the restore process)
There is no need to backup the temporary locally managed tablespaces because:
i. Locally managed tempfiles are always set to NOLOGGING mode. So thus will have no undo.
ii. Extents are managed by bitmap in each datafile to keep track of free or used status of blocks in that datafile.
iii. The data dictionary does not manage the tablespace.
iv. Rollback information is not generated because there is no update on the data dictionary.
v. Media recovery does not recognize tempfiles.
10. Recovering from a lost datafile in a UNDO Tablespace - ID 1013221.6
11. Recover database after disk loss – 230829.1
12. How to Recover a Database Having Added a Datafile Since Last Backup [ID 29430.1]
13. Took a Cold Backup. Created a tablespace. How to recover
i. If controlfile exists
ii. If controlfile is also lost
14. Recover Inactive Redologfile (Scenario 7)
Lost Active Redolog File in Archive Mode & in NoArchive Mode
Lost Current Redolog files in Archive & NOArchive Mode
Lost all redolog files (Scenario 9)
Lost one of the Multiplexed Redolog Files
SQL> select status,group# from v$log;
STATUS GROUP#
---------------- ----------
INACTIVE 1
INACTIVE 2
CURRENT 3
SQL> alter system switch logfile;
System altered.
SQL> select status,group# from v$log;
STATUS GROUP#
---------------- ----------
CURRENT 1
INACTIVE 2
ACTIVE 3
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startp mount
SP2-0734: unknown command beginning "startp mou..." - rest of line ignored.
SQL> startup mount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL>
14.2) Lost Active Redolog File
ANS>>>>>>>>>>>>>>>
SQL> select status,group# from v$log;
STATUS GROUP#
---------------- ----------
ACTIVE 1
CURRENT 2
INACTIVE 3
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5933
Session ID: 125 Serial number: 5
SQL> !
[oracle@localhost ~]$ !s
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 4 11:32:05 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database clear logfile group 1;
Database altered.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open;
Database altered.
SQL>
14.3)Lost Current Redolog files in Archive
ANS>>>>>>>>>>>>>>>>>>>
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance dev (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u02/app/oracle/oradata/dev/redo03.log'
SQL> recover datafile '/u02/app/oracle/oradata/dev/redo03.log';
ORA-01179: file /u02/app/oracle/oradata/dev/redo03.log does not exist
SQL> alter database clear unarchive logfile group 3;
alter database clear unarchive logfile group 3
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
14.4)...Lost all redolog files
ANS>>>>>>>>>>>>>>>>>>>>>>>>
SQL> startup
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6832
Session ID: 125 Serial number: 5
SQL> startup mount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7016
Session ID: 125 Serial number: 5
####need to do same for all groups...###
SQL> startup mount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
Database mounted.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> c/2/3
1* alter database clear unarchived logfile group 3
SQL> /
Database altered.
SQL> alter database open;
Database altered.
14.5)Lost one of the Multiplexed Redolog Files
same as above find out member group n use the command
SQL>alter database clear logfile group 2;
if that missing file belongs to group 2 the it it will get generated n do >alter database open;
15. Recover Controlfile - No Multiplex Exists (Scenario 14)
SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> CREATE PFILE FROM SPFILE;
File created.
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> alter database mount
2 ;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u02/app/oracle/oradata/dev/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SQL> recover database using backup controlfile;
ORA-00279: change 1140887 generated at 06/04/2018 12:00:31 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_11_fk9q86n
v_.arc
ORA-00280: change 1140887 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1140909 generated at 06/04/2018 12:02:38 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_12_%u_.arc
ORA-00280: change 1140909 for thread 1 is in sequence #12
ORA-00278: log file
'/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_11_fk9q86
nv_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_12_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile;
ORA-00279: change 1140909 generated at 06/04/2018 12:02:38 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_12_%u_.arc
ORA-00280: change 1140909 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/u02/app/oracle/oradata/dev/redo01.log'
ORA-00310: archived log contains sequence 10; sequence 12 required
ORA-00334: archived log: '/u02/app/oracle/oradata/dev/redo01.log'
SQL> recover database using backup controlfile;
ORA-00279: change 1140909 generated at 06/04/2018 12:02:38 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_12_%u_.arc
ORA-00280: change 1140909 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/u02/app/oracle/oradata/dev/redo02.log'
ORA-00310: archived log contains sequence 11; sequence 12 required
ORA-00334: archived log: '/u02/app/oracle/oradata/dev/redo02.log'
SQL> recover database using backup controlfile;
ORA-00279: change 1140909 generated at 06/04/2018 12:02:38 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/fast_recovery_area/dev/archivelog/2018_06_04/o1_mf_1_12_%u_.arc
ORA-00280: change 1140909 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/u02/app/oracle/oradata/dev/redo03.log'
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size 2230400 bytes
Variable Size 490735488 bytes
Database Buffers 264241152 bytes
Redo Buffers 6914048 bytes
ORA-00214: control file '/u02/app/oracle/oradata/dev/control01.ctl' version
1728 inconsistent with file
'/u02/app/oracle/fast_recovery_area/dev/control02.ctl' version 1725
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
16. Performing Incomplete Recovery [ID 114199.1]
tips:
Incomplete recovery recovers the database to a point in time in the past.
There are three ways to perform incomplete recovery: change-based recovery,
cancel-based recovery, and time-based recovery.
eg.
Oracle wants archived redo log file with sequence number 387. Looking at the
archivelog destination, you notice that file arch_387.arc is not available:
arch_382.arc arch_386.arc
arch_383.arc arch_388.arc
arch_384.arc arch-389.arc
arch_385.arc arch-390.arc
Thus the only option available is to perform incomplete recovery of the database.
You need to restore the database from a backup completed prior to the point in
time of your recovery. Perform recover using any of the above
17. How to recover the database if Archive files are missing - ID 163434.1
18. Recover a dropped table - ID 96197.1] (Scenario 8) - INCOMPLETE RECOVERY
19. Recover Datafile after resetlogs (Scenario 10)
20. Recover dropped tablespace (Scenario 11)
21. Recovering READONLY tablespace backups made before a RESETLOGS Open - [ID 266991.1]
22. Recover a tablespace, Control file does not contain the tablespace info. (Scenario 12)
23. A table test was created at 10am and dropped at 10.30 am, another table test1was created at 10.45 was dropped at 11.45am.
Recover the tables without losing any records in both the table. (Scenario 13)
24. Recover new information that was not in the backup and was only stored in the archivelog files. (Scenario 16)
25. How to recover a database having added a datafile since the last backup. (Scenario 17)
26. If the database crashes during a hot backup. (Scenario 18)
27. Re-creating controlfiles from a trace script where Read-Only Offlined tablespaces are present. (What will happen)
28. Recover a datafile containing Index segments.
29. Cloning using cold backup to same node / to another node
How to Manually Clone a Database to Another Node [ID 562556.1]
30. Cloning using hot backup (Same as 31)
31. Database is in Archive mode. You have a Cold backup (Consistent) & Archive Files. You lost everything i.e. Redologs, Controlfile and Datafiles. How to Recover.
32. How To Make A Copy Of An Open Database For Duplication To A Different Machine [ID 224274.1]
33. Doing Incomplete Recovery and Moving Redo Logs From Corrupted Disk [ID 77643.1]
34. One of the databases crashed. After some investigation it was found that only data files and initialization file intact. All other files i.e. control files and redo log files were lost. The control files and log files were not multiplexed (First mistake). With only data files, how would you recover a database with minimal loss?
35. We even did not have consistent backup for the database (Second mistake). After doing some research, we finally decided to give a go to recover the database with available data files and initialization file.
Up the instance using pfile
recreate controlfile from trace
alter database open resetlogs
36. Database Startup Fails with ORA-01113, ORA-01110 [ID 146039.1]
37. HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE [ID 286355.1]
38. ORA-600 [3668] "Media Recovery Required After CREATE CONTROLFILE" [ID 93665.1]
39. How to clone/duplicate a database with added datafile with no backup. [ID 292947.1]
40. Can I restore or duplicate my previous version database using a later version of Oracle? Y
Can I restore or duplicate between two different patchset levels? Y
Can I restore or duplicate between two different versions of the same operating system? Y
Is it possible to restore or duplicate when the bit level (32 bit or 64 bit) of Oracle does not match? OS Specific
Can I restore or duplicate my RMAN backup between two different platforms such as Solaris to Linux? N
No comments:
Post a Comment