I. RENAME OR MOVE DATAFILE(S) WITH THE DATABASE OPEN
----------------------------------------------------
Datafiles can be renamed or moved while the database is open. However, the
tablespace must be made READ-ONLY. This will allow users to select from the
tablespace, but prevents them from doing inserts, updates, and deletes. The
amount of time the tablespace is required to be read only will depend on how
large the datafile(s) are and how long it takes to copy the datafile(s) to the
new location.
Making the tablespace read only freezes the file header, preventing updates
from being made to the file header. Since this datafile is then at a read
only state, it is possible to copy the file while the database is open.
To do this you must follow these steps:
1. Determine how many datafiles are associated with the tablespace.
> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '';
TESTING1 >select file_name,status from dba_data_files where tablespace_name='TEST';
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS
---------
/data/oracle/TESTING1/u01/test.dbf
AVAILABLE
2. Make sure that all datafiles returned have the status AVAILABLE.
3. Make the tablespace is read only.
ALTER TABLESPACE READ ONLY;
TESTING1 >alter tablespace test read only;
Tablespace altered.
4. Make sure that the tablespace is defined as read only in the data
dictionary.
> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '';
TABLESPACE_NAME STATUS
------------------------------ ---------
READ ONLY
TESTING1 >select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST READ ONLY
5. Copy the datafile(s) to the new location using the operating system copy
command. Once the datafile(s) have been copied to the new location compare
the sizes of the datafiles. Make sure that the sizes match.
cp test.dbf /data/oracle/TESTING1/test/
6. Once the datafiles have been copied to the new location alter the
tablespace offline.
> ALTER TABLESPACE OFFLINE;
TESTING1 >alter tablespace test offline;
Tablespace altered.
* At this point the tablespace is not accessible to users.
7. Once the tablespace is offline you will need to rename the datafile(s) to
the new location. This updates the entry for the datafile(s) in the
controlfile.
> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
* You will need to do this for all datafiles associated with
this tablespace. You can use the ALTER TABLESPACE ... RENAME DATAFILE
syntax as well.
TESTING1 >alter database rename file '/data/oracle/TESTING1/u01/test.dbf' to '/data/oracle/TESTING1/test/test.dbf';
Database altered.
8. Once the alter database statement has been processed for the datafile(s)
you can bring the tablespace online.
ALTER TABLESPACE YOUR_TABLESPACE_NAME ONLINE;
TESTING1 >alter tablespace test online;
Tablespace altered.
9. After you bring the tablespace back online you can make the tablespace
read/write again.
ALTER TABLESPACE YOUR_TABLESPACE_NAME READ WRITE;
TESTING1 >alter tablespace test read write;
Tablespace altered.
10. You can check the status of the tablespace to make sure it is read/write.
You can also verify that the controlfile has been updated by doing the
following:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This will produce a readable copy of the contents of your controlfile
which will be placed in your user_dump_dest directory.
Optionally, you can query V$DATAFILE, which gets information from the
controlfile as well.
11. Remove the datafile(s) from the old location at the O/S level.
II. RENAME AND OR MOVE DATAFILE(S) WITH THE DATABASE SHUT DOWN
--------------------------------------------------------------
1. If the database is up, shut it down.
2. Copy the datafile to the new name/location at operating system level.
cp test.dbf /data/oracle/TESTING1/test/
3. Mount the database.
> STARTUP MOUNT
This command will read the control file but will not mount the datafiles.
4. Rename the file inside Oracle.
> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
TESTING1 >alter database rename file '/data/oracle/TESTING1/test/test.dbf' to '/data/oracle/TESTING1/u01/test.dbf';
Database altered.
Do this for all the datafiles that were renamed or moved at the operating
system level.
5. Open the database.
> ALTER DATABASE OPEN;
TESTING1 >alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/data/oracle/TESTING1/u01/test.dbf'
TESTING1 >recover datafile 4;
Media recovery complete.
TESTING1 >alter database open;
6. Query v$dbfile to confirm that the changes made were correct.
> SELECT * FROM V$DBFILE;
TESTING1 >select * from v$dbfile;
FILE#
----------
NAME
----------------------------------------------------------------------------------------------------
3
/data/oracle/TESTING1/u01/sysaux.dbf
2
/data/oracle/TESTING1/u01/undotbs01.dbf
1
/data/oracle/TESTING1/u01/system.dbf
4
/data/oracle/TESTING1/u01/test.dbf
7. Remove the datafile(s) from the old location at the operating system level.
NOTE: To rename or relocate datafiles in the SYSTEM tablespace you must use
option II, 'Renaming or Moving a Datafile with the Database Shut Down',
because you cannot take the SYSTEM tablespace offline.
Sunday, May 16, 2010
Metalink recovery scenario docs..
Metalink recovery scenario docs..
Note:103176.1 - How to Recover Having Lost Controlfiles and Online Redo Logs
Note:184327.1 - Common Causes and Solutions on ORA-1157 Error Found in B&R
Note.198640.1 - How to Recover from a Lost Datafile with Different Scenarios
Note:183327.1 - Common Causes and Solutions on ORA-376 Error Found in B&R
Note:183367.1 - Common Causes and Solutions on ORA-1113 Error Found in B&R
Note:117481.1 - Loss Of Online Redo Log And ORA-312 And ORA-313
online redolog missing..
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
Recovery with missing System tablespace.
Recover database after disk loss [ID 230829.1]
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
How to Recover from a Lost Datafile with Different Scenarios [ID 198640.1]
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=198640.1
Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery [ID 184327.1]
https://support.oracle.com/CSP/main/article?cmd=show&id=184327.1&type=NOT
Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g [ID 28814.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=28814.1
Note:103176.1 - How to Recover Having Lost Controlfiles and Online Redo Logs
Note:184327.1 - Common Causes and Solutions on ORA-1157 Error Found in B&R
Note.198640.1 - How to Recover from a Lost Datafile with Different Scenarios
Note:183327.1 - Common Causes and Solutions on ORA-376 Error Found in B&R
Note:183367.1 - Common Causes and Solutions on ORA-1113 Error Found in B&R
Note:117481.1 - Loss Of Online Redo Log And ORA-312 And ORA-313
online redolog missing..
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
Recovery with missing System tablespace.
Recover database after disk loss [ID 230829.1]
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
How to Recover from a Lost Datafile with Different Scenarios [ID 198640.1]
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=198640.1
Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery [ID 184327.1]
https://support.oracle.com/CSP/main/article?cmd=show&id=184327.1&type=NOT
Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g [ID 28814.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=28814.1
Friday, May 14, 2010
Scenarios
1. Loss of Inactive/current/Active Online Redo Log group/file..
Redo logs
---------
In normal cases, we would not have backups of online redo log files. But the
inactive logfile changes could already have been checkpointed on the datafiles
and even archive log files may be available.
SQL> startup mount
Oracle Instance Started
Database mounted
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
** Verify if the lost redolog file is Current or not.
SQL> select * from v$log;
SQL> select * from v$logfile;
--> If the lost redo log is an Inactive logfile, you can clear the logfile:
SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
Alternatively, you can drop the logfile if you have atleast two other
logfiles:
SQL> alter database drop logfile group 1;
--> If the logfile is the Current logfile, then do the following:
SQL> recover database until cancel;
Type Cancel when prompted
SQL>alter database open resetlogs;
The 'recover database until cancel' command can fail with the following
errors:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ORACLE/ORADATA/H817/SYSTEM01.DBF'
In this case , restore an old backup of the database files and apply the
archive logs to perform incomplete recovery.
--> restore old backup
SQL> startup mount
SQL> recover database until cancel using backup controlfile;
SQL> alter database open resetlogs;
If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and ORA-1110 errors occur, then you would have restore from an old backup and start the database.
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
Losing an Active Online Redo Log Group
If the database is still running and the lost active log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, your active log is rendered inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, perform one of these procedures, depending on the archiving mode.
Note that the current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.
To recover from loss of an active online redo log group in NOARCHIVELOG mode:
1. If the media failure is temporary, correct the problem so that Oracle can reuse the group when required.
2. Restore the database from a whole database backup using an operating system utility. For example, enter:
NT > !copy c:\backup\*.dbf c:\data\
3. Mount the database:
SVRMGR> startup mount;
4. Open the database using the RESETLOGS option:
SVRMGR> alter database open resetlogs;
5. Shut down the database normally:
SVRMGR> shutdown immediate;
6. Perform a closed whole backup
To recover from loss of an active online redo log group in ARCHIVELOG mode:
1. If the media failure is temporary, then correct the problem so that Oracle can reuse the group when required.
2. Perform incomplete media recovery. Use the procedure given in Loss of Current Redo Logs, recovering up through the log before the damaged log.
Loss of Current Redo Logs -- database is Closed.
If the Database is closed, media failure may have occurred or a background process may have terminated. Used the steps below to rectify this situation:
1. Attempting to open the database will immediately notify you of the current Redo Log group is missing.
SVRMGR> startup;
ORACLE instance started.
Total System Global Area 73418780 bytes
Fixed Size 75804 bytes
Variable Size 56487936 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\INFOMAX\REDO03.LOG'
2. Since the log group 3 is the current log group, it will not have been archived. Using the "Clear logfile" command is no use since:
SVRMGR> alter database clear unarchived logfile group 3;
ORA-01624: log 3 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\INFOMAX\REDO03.LOG'
3. Incomplete recovery is therefore required. First, you must note the current log sequence number:
SQL> select * from v$log;
GROUP#.... SEQUENCE# BYTES MEMBERS ARC STATUS ........ FIRST_TIME
------------ ---------------- ----------- --------------- ------- -------------- ------------------
1 ..... 448 1048576 1 YES INACTIVE ..... 09-MAR-01
2 ..... 449 1048576 1 YES INACTIVE ..... 09-MAR-01
3 ..... 450 1048576 1 NO CURRENT ..... 09-MAR-01
4. Restore all datafiles from a previous backup:
UNIX> !cp /disk1/backup/*.dbf /disk2/data/
5. Recover using "Until Cancel"
SVRMGR> recover database until cancel';
6. NOTE: When it comes to apply the Redo Logs # 450, type cancel.
7. Open the database using "resetlogs" options:
SVRMGR> alter database open resetlogs;
8. The database should now be operational, since any missing logfiles will be recreated.
NOTE: If the logfiles need to be recreated on another disk due to media failure, use the "alter database drop logfile group" and "alter database add log group" commands to manually create the log files. See Re-creating Redo Logs.
9. Since you just performed incomplete recovery, the database should now be backed up.
The following information is useful for implementation of recovery on redolog group
How to recover using the online redo log
========================================
PURPOSE
-------
To easily and quickly find out if the online redo log files can be used to recover a database.
AUDIENCE
--------
This document is addressed to DBAs that want to quickly find the best recovery solution in case of a database crash.
HOW TO
------
Many databases today are run without archive logging enabled, this reduces the available options to quickly recover a database. Basically 2 options are available:
a) restore from a backup
b) recover the database using the online redo logs.
Option a) is straight forward and will not be covered here. Only important
thing to mention is that option a) WILL cause loss of data if there has
been updates/inserts to the database since the backup was taken.
Let us instead take a look at option b):
In case of a database crash or a database that will not startup
due to ORA-1110, ORA-1113 or both, we first need to identify which
files need to be recovered.
1) First we mount the database then issue the following query:
select * from v$recover_file;
This will give us a list of the files that need media recovery. It
will also give us CHANGE#, i.e. the SCN where the media recovery
must start. To get the name of the file use the FILE# column and
query against v$datafile like this:
select name from v$datafile
where file# =
or like this in 9i:
select substr(d.name,1,30) fname
, r.online_status
, r.error
, r.change#
, r.time
from v$datafile d, v$recover_file r
where d.file# = r.file#
2) Next we do:
archive log list
This will give us the current log sequence. We need the current
log sequence -1. This will give us the last redo log file that
was in use.
3) As the last step we do:
select recid
, stamp
, sequence#
, first_change#
, next_change#
from v$log_history
where recid = less than symbol current log sequence -1)
This will show us the NEXT_CHANGE#, i.e.the highest SCN, in the redo
log file. It will also give us the FIRST_CHANGE# SCN in
this redo log file. We need these 2 SCN numbers to find out if we
can use the redo log file for recovery against the file(s) found
in 1). If the CHANGE# from 1) is between the FIRST_CHANGE# and the
NEXT_CHANGE# then we can use the redo log file for recovery.
If the CHANGE# from 1) is lower than FIRST_CHANGE# we need to
investigate an earlier online redo log file.
When we have identified which redo log files to use for the recovery we
perform the recovery using the redo log file(s) in the following way:
- mount the database
- recover database
The recover process will now display something similar to the following:
ORA-00279: change 12599 generated at 08/18/98 13:25:48 needed for thread 1
ORA-00289: suggestion : /oracle/OFA_base/app/oracle/admin/NE804DB1/arch/129.arc
ORA-00280: change 12599 for thread 1 is in sequence #129
Specify log: {=suggested | filename | AUTO | CANCEL}
As the database is not in ARCHIVELOG mode we will not have the 129.arc
file. Instead the name of the redo log file must be entered on the
command line. The filename must include the full path. After the redo
log file has been applied the recover process will return: Log applied.
At this stage 2 things can happend:
1) Media Recovery Completed
2) or additional redo log files must be applied
If 2) then just specify the next redo log file on the command line and
continue to do so until Media Recovery Completed is displayed. Then the
database can be opened with: alter database open;
If we do not find any online redo log files which covers the CHANGE#
from 1) we cannot do a recover of the database or datafile(s). This
means we are left with only 2 options of bringing the database back online:
1) restore from a valid backup taken before the crash. Doing so and
running the database in NOARCHIVELOG MODE will cause a loss of data.
This is unavoidable.
2) force the database open. This will override Oracle's internal
datafile synchronisation and consistency check. The result is an
inconsistent database. The database MUST now be exported and rebuild
as an inconsistent database is unreliable, and hence not supported.
This last option should only be used in cooperation with Oracle Support
ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT:
Question: Will "alter system switch logfile" trigger checkpoint?
Answer: It will only trigger a complete checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system switch logfile" may trigger complete checkpoint, but not always
http://www.dbaglobe.com/2010/02/alter-system-switch-logfile-and.html
1) Alter system switch logfile.
Well this you can fire even when your database is not in archive log mode.What it does is switch your log from one log file to other even though its not filled.
Now if the database is in archive log mode then after the log switch the archive for the logfile is created. This will archive only inactive logs..
2) Alter system switch archive log all ;
Will archive all your inactive redo log files but not your current redo log file.
In certain scenario where you need to manually archive your existin gredo log file you use alter system archive log all;
3) While alter system swich archive log current;
Will archive all inactive redolog files and also our current redo logfile.
**Now while firing alter system swich logfile and alter system switch archive log current the database has to be open
while firing alter system archive log all it can be done even in mount state
http://forums.oracle.com/forums/thread.jspa?messageID=3296310�
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SYSTEM ARCHIVE LOG CURRENT and SCN
TESTING1 >select max(next_change#) from v$archived_log;
MAX(NEXT_CHANGE#)
-----------------
4095858
TESTING1 >select current_scn from v$database;
CURRENT_SCN
-----------
4097620
Current changes are written to archived log triggered by "alter system archive log current".
TESTING1 >alter system archive log current;
System altered.
TESTING1 >select max(next_change#) from v$archived_log;
MAX(NEXT_CHANGE#)
-----------------
4097634
TESTING1 >select current_scn from v$database;
CURRENT_SCN
-----------
4097639
http://www.dbaglobe.com/search/label/Oracle
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
Redo logs
---------
In normal cases, we would not have backups of online redo log files. But the
inactive logfile changes could already have been checkpointed on the datafiles
and even archive log files may be available.
SQL> startup mount
Oracle Instance Started
Database mounted
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
** Verify if the lost redolog file is Current or not.
SQL> select * from v$log;
SQL> select * from v$logfile;
--> If the lost redo log is an Inactive logfile, you can clear the logfile:
SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
Alternatively, you can drop the logfile if you have atleast two other
logfiles:
SQL> alter database drop logfile group 1;
--> If the logfile is the Current logfile, then do the following:
SQL> recover database until cancel;
Type Cancel when prompted
SQL>alter database open resetlogs;
The 'recover database until cancel' command can fail with the following
errors:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ORACLE/ORADATA/H817/SYSTEM01.DBF'
In this case , restore an old backup of the database files and apply the
archive logs to perform incomplete recovery.
--> restore old backup
SQL> startup mount
SQL> recover database until cancel using backup controlfile;
SQL> alter database open resetlogs;
If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and ORA-1110 errors occur, then you would have restore from an old backup and start the database.
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
Losing an Active Online Redo Log Group
If the database is still running and the lost active log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, your active log is rendered inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, perform one of these procedures, depending on the archiving mode.
Note that the current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.
To recover from loss of an active online redo log group in NOARCHIVELOG mode:
1. If the media failure is temporary, correct the problem so that Oracle can reuse the group when required.
2. Restore the database from a whole database backup using an operating system utility. For example, enter:
NT > !copy c:\backup\*.dbf c:\data\
3. Mount the database:
SVRMGR> startup mount;
4. Open the database using the RESETLOGS option:
SVRMGR> alter database open resetlogs;
5. Shut down the database normally:
SVRMGR> shutdown immediate;
6. Perform a closed whole backup
To recover from loss of an active online redo log group in ARCHIVELOG mode:
1. If the media failure is temporary, then correct the problem so that Oracle can reuse the group when required.
2. Perform incomplete media recovery. Use the procedure given in Loss of Current Redo Logs, recovering up through the log before the damaged log.
Loss of Current Redo Logs -- database is Closed.
If the Database is closed, media failure may have occurred or a background process may have terminated. Used the steps below to rectify this situation:
1. Attempting to open the database will immediately notify you of the current Redo Log group is missing.
SVRMGR> startup;
ORACLE instance started.
Total System Global Area 73418780 bytes
Fixed Size 75804 bytes
Variable Size 56487936 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\INFOMAX\REDO03.LOG'
2. Since the log group 3 is the current log group, it will not have been archived. Using the "Clear logfile" command is no use since:
SVRMGR> alter database clear unarchived logfile group 3;
ORA-01624: log 3 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\INFOMAX\REDO03.LOG'
3. Incomplete recovery is therefore required. First, you must note the current log sequence number:
SQL> select * from v$log;
GROUP#.... SEQUENCE# BYTES MEMBERS ARC STATUS ........ FIRST_TIME
------------ ---------------- ----------- --------------- ------- -------------- ------------------
1 ..... 448 1048576 1 YES INACTIVE ..... 09-MAR-01
2 ..... 449 1048576 1 YES INACTIVE ..... 09-MAR-01
3 ..... 450 1048576 1 NO CURRENT ..... 09-MAR-01
4. Restore all datafiles from a previous backup:
UNIX> !cp /disk1/backup/*.dbf /disk2/data/
5. Recover using "Until Cancel"
SVRMGR> recover database until cancel';
6. NOTE: When it comes to apply the Redo Logs # 450, type cancel.
7. Open the database using "resetlogs" options:
SVRMGR> alter database open resetlogs;
8. The database should now be operational, since any missing logfiles will be recreated.
NOTE: If the logfiles need to be recreated on another disk due to media failure, use the "alter database drop logfile group" and "alter database add log group" commands to manually create the log files. See Re-creating Redo Logs.
9. Since you just performed incomplete recovery, the database should now be backed up.
The following information is useful for implementation of recovery on redolog group
How to recover using the online redo log
========================================
PURPOSE
-------
To easily and quickly find out if the online redo log files can be used to recover a database.
AUDIENCE
--------
This document is addressed to DBAs that want to quickly find the best recovery solution in case of a database crash.
HOW TO
------
Many databases today are run without archive logging enabled, this reduces the available options to quickly recover a database. Basically 2 options are available:
a) restore from a backup
b) recover the database using the online redo logs.
Option a) is straight forward and will not be covered here. Only important
thing to mention is that option a) WILL cause loss of data if there has
been updates/inserts to the database since the backup was taken.
Let us instead take a look at option b):
In case of a database crash or a database that will not startup
due to ORA-1110, ORA-1113 or both, we first need to identify which
files need to be recovered.
1) First we mount the database then issue the following query:
select * from v$recover_file;
This will give us a list of the files that need media recovery. It
will also give us CHANGE#, i.e. the SCN where the media recovery
must start. To get the name of the file use the FILE# column and
query against v$datafile like this:
select name from v$datafile
where file# =
or like this in 9i:
select substr(d.name,1,30) fname
, r.online_status
, r.error
, r.change#
, r.time
from v$datafile d, v$recover_file r
where d.file# = r.file#
2) Next we do:
archive log list
This will give us the current log sequence. We need the current
log sequence -1. This will give us the last redo log file that
was in use.
3) As the last step we do:
select recid
, stamp
, sequence#
, first_change#
, next_change#
from v$log_history
where recid = less than symbol current log sequence -1)
This will show us the NEXT_CHANGE#, i.e.the highest SCN, in the redo
log file. It will also give us the FIRST_CHANGE# SCN in
this redo log file. We need these 2 SCN numbers to find out if we
can use the redo log file for recovery against the file(s) found
in 1). If the CHANGE# from 1) is between the FIRST_CHANGE# and the
NEXT_CHANGE# then we can use the redo log file for recovery.
If the CHANGE# from 1) is lower than FIRST_CHANGE# we need to
investigate an earlier online redo log file.
When we have identified which redo log files to use for the recovery we
perform the recovery using the redo log file(s) in the following way:
- mount the database
- recover database
The recover process will now display something similar to the following:
ORA-00279: change 12599 generated at 08/18/98 13:25:48 needed for thread 1
ORA-00289: suggestion : /oracle/OFA_base/app/oracle/admin/NE804DB1/arch/129.arc
ORA-00280: change 12599 for thread 1 is in sequence #129
Specify log: {
As the database is not in ARCHIVELOG mode we will not have the 129.arc
file. Instead the name of the redo log file must be entered on the
command line. The filename must include the full path. After the redo
log file has been applied the recover process will return: Log applied.
At this stage 2 things can happend:
1) Media Recovery Completed
2) or additional redo log files must be applied
If 2) then just specify the next redo log file on the command line and
continue to do so until Media Recovery Completed is displayed. Then the
database can be opened with: alter database open;
If we do not find any online redo log files which covers the CHANGE#
from 1) we cannot do a recover of the database or datafile(s). This
means we are left with only 2 options of bringing the database back online:
1) restore from a valid backup taken before the crash. Doing so and
running the database in NOARCHIVELOG MODE will cause a loss of data.
This is unavoidable.
2) force the database open. This will override Oracle's internal
datafile synchronisation and consistency check. The result is an
inconsistent database. The database MUST now be exported and rebuild
as an inconsistent database is unreliable, and hence not supported.
This last option should only be used in cooperation with Oracle Support
ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT:
Question: Will "alter system switch logfile" trigger checkpoint?
Answer: It will only trigger a complete checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system switch logfile" may trigger complete checkpoint, but not always
http://www.dbaglobe.com/2010/02/alter-system-switch-logfile-and.html
1) Alter system switch logfile.
Well this you can fire even when your database is not in archive log mode.What it does is switch your log from one log file to other even though its not filled.
Now if the database is in archive log mode then after the log switch the archive for the logfile is created. This will archive only inactive logs..
2) Alter system switch archive log all ;
Will archive all your inactive redo log files but not your current redo log file.
In certain scenario where you need to manually archive your existin gredo log file you use alter system archive log all;
3) While alter system swich archive log current;
Will archive all inactive redolog files and also our current redo logfile.
**Now while firing alter system swich logfile and alter system switch archive log current the database has to be open
while firing alter system archive log all it can be done even in mount state
http://forums.oracle.com/forums/thread.jspa?messageID=3296310�
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SYSTEM ARCHIVE LOG CURRENT and SCN
TESTING1 >select max(next_change#) from v$archived_log;
MAX(NEXT_CHANGE#)
-----------------
4095858
TESTING1 >select current_scn from v$database;
CURRENT_SCN
-----------
4097620
Current changes are written to archived log triggered by "alter system archive log current".
TESTING1 >alter system archive log current;
System altered.
TESTING1 >select max(next_change#) from v$archived_log;
MAX(NEXT_CHANGE#)
-----------------
4097634
TESTING1 >select current_scn from v$database;
CURRENT_SCN
-----------
4097639
http://www.dbaglobe.com/search/label/Oracle
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
Wednesday, May 5, 2010
11 & 10g New Backgroud processes..
11g:
The following process are added in 11g as new background processes.
1 dbrm DB resource manager
2 dia0 Diagnosability process
3 fbda Flashback data archiver process
4 vktm Virtual Timekeeper
5 w000 Space Management Co-ordination process
6 smc0 Space Manager process
NOTE : The above six are mandatory processes.
But 11g has 56 new processes added which can be queried using
select name,description from V$bgprocess;
10g background processes:
MMAN
Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.
RVWR
Process responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.
CTWR
Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.
MMNL
The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)
MMON
The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.
M000
MMON background slave (m000) processes.
RBAL
RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.
ARBx
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
ASMB
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.
The following process are added in 11g as new background processes.
1 dbrm DB resource manager
2 dia0 Diagnosability process
3 fbda Flashback data archiver process
4 vktm Virtual Timekeeper
5 w000 Space Management Co-ordination process
6 smc0 Space Manager process
NOTE : The above six are mandatory processes.
But 11g has 56 new processes added which can be queried using
select name,description from V$bgprocess;
10g background processes:
MMAN
Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.
RVWR
Process responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.
CTWR
Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.
MMNL
The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)
MMON
The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.
M000
MMON background slave (m000) processes.
RBAL
RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.
ARBx
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
ASMB
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.
SAN Vs NAS
Good Article on SAN Vs NAS
http://www.nas-san.com/differ.html
A SAN commonly used Fibre Channel
Any computer that connects to LAN act as NAS using NFS or CIFS
NAS uses TCP/IP and NFS/CIFS/HTTP protcols
SAN uses Encapsulated SCSI protcol
The basic diffence b/w SAN, NAS is
"The kind of I/O Operation ? if it is an Block I/O then it is SAN,
if it is an File I/O then it is an NAS"
http://www.nas-san.com/differ.html
A SAN commonly used Fibre Channel
Any computer that connects to LAN act as NAS using NFS or CIFS
NAS uses TCP/IP and NFS/CIFS/HTTP protcols
SAN uses Encapsulated SCSI protcol
The basic diffence b/w SAN, NAS is
"The kind of I/O Operation ? if it is an Block I/O then it is SAN,
if it is an File I/O then it is an NAS"
Subscribe to:
Posts (Atom)