Error Description:
-----------------------
Whenever I try to access a objects it fails with errors.
The error message stack follows:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'
Possible Causes and Solutions:
----------------------------------------
A.Tablespace or Datafile Offline:
-------------------------------------------------
As you see the error return with affected file_id and file_name. Here file_id is 9 and file_name is /oradata2/test_tbs02.dbf.
1)Check the tablespace status with,
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id);
Enter value for give_file_id: 9
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST_TBS ONLINE
If it would offline you should bring it online to make work with it,
SQL> alter tablespace TEST_TBS online;
So there is no problem with the tablespace as it is already online state.
2)Check the affected datafile.
To know all, SQL> select FILE# from v$datafile where status in ('OFFLINE','RECOVER');
Check the status of the affected data file as it is returned with error message.
SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
RECOVER DISABLED
If status is recover then media recovery is needed.
If status is offline then open the datafile.
SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
RECOVER DISABLED
SQL> recover datafile &file_id;
Enter value for file_id: 9
Media recovery complete.
SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
OFFLINE DISABLED
SQL> alter database datafile 9 online;
Database altered.
SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
ONLINE DISABLED
If you see status DISABLED then affected tablespace make online.
SQL> alter tablespace TEST_TBS online;
Tablespace altered.
SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
ONLINE READ WRITE
If the status id RECOVER and ENABLED is READ WRITE then simply make the affect datafile online.
B.Datafile does not exist at the OS level.
---------------------------------------------------
Someone has deleted datafile from OS. In that case You many follow Recover lost file if you don't have backup or if you have backup then restore and recover the tablespace Restore and Recover Datafile
C.Backup Software is locking the file and Hence Error Comes.
----------------------------------------------------------------
The backup software might be locking the datafiles, preventing Oracle from reading the datafiles.
Check if there are any backup software running and stop them, thereby releasing the locks and try starting up the database again.
Saturday, May 10, 2008
Working an object that resides on multiple datafile
An object can span in multiple datafiles within a single tablespace. In this case if I make one datafile offline then the contents within that datafile will be affected, other datafile is online and can be possible to query which may return rows with errors. To illustarte this scenario I will make two datafiles inside a tablespace.
1)Create Tablespace.
SQL> CREATE TABLESPACE TEST_TBS DATAFILE '/oradata2/test_tbs01.dbf' SIZE 100K;
Tablespace created.
2)Add datafile to the Tablespace.
SQL> ALTER TABLESPACE TEST_TBS ADD DATAFILE '/oradata2/test_tbs02.dbf' SIZE 1M;
Tablespace altered.
3)Create Table inside the Tablespace.
SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_TBS AS SELECT LEVEL B1 FROM DUAL CONNECT BY LEVEL<9999;
Table created.
4)Check which datafile this table belong to.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TABLE');
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEST_TBS /oradata2/test_tbs01.dbf
TEST_TBS /oradata2/test_tbs02.dbf
5)Query the table it will be ok.Now make later datafile offline and query from table.
SQL> alter database datafile '/oradata2/test_tbs02.dbf' OFFLINE;
Database altered.
SQL> select * from TEST_TABLE;
1
.
.
3270
ERROR:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'
3270 rows selected.
1)Create Tablespace.
SQL> CREATE TABLESPACE TEST_TBS DATAFILE '/oradata2/test_tbs01.dbf' SIZE 100K;
Tablespace created.
2)Add datafile to the Tablespace.
SQL> ALTER TABLESPACE TEST_TBS ADD DATAFILE '/oradata2/test_tbs02.dbf' SIZE 1M;
Tablespace altered.
3)Create Table inside the Tablespace.
SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_TBS AS SELECT LEVEL B1 FROM DUAL CONNECT BY LEVEL<9999;
Table created.
4)Check which datafile this table belong to.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TABLE');
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEST_TBS /oradata2/test_tbs01.dbf
TEST_TBS /oradata2/test_tbs02.dbf
5)Query the table it will be ok.Now make later datafile offline and query from table.
SQL> alter database datafile '/oradata2/test_tbs02.dbf' OFFLINE;
Database altered.
SQL> select * from TEST_TABLE;
1
.
.
3270
ERROR:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'
3270 rows selected.
Troubleshooting ORA-00942: ORA-04043:
Whenever you get ORA-00942 or ORA-04043, don't be disappointed or blame oracle that it may produce something wrong. Trust oracle and diagnosis the problem. I will try to write a list of actions that may help you to solve these problems.
The error comes in this format,
ORA-00942: table or view does not exist
ORA-04043: object %s does not exist
Cause of The Problem:
--------------------------
1)The object actually does not exist. That means you have done spell mistake. Review your object name. There may be a lower-upper case problem.
2)The table may exist but it is in different schema.
3)The user don't have necessary permission to read the object.
Solution of The problem:
--------------------------
1)Correct the name of the object if there is any spelling mistake. Be aware about case. I will write one example about case.
SQL> create table "TesT" ( a NUMBER);
Table created.
SQL> drop table TesT;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table "TesT";
Table dropped.
Here I created a table "TesT". Whenever I wrote TesT it showed it does not exist. Because in SQL*Plus whenever we write any command it takes as all upper case if you don't specify any quote. So to make it case sensitive we explicitly need to write text within double quote. ""
Also check whether blank line exist in the table name.
SQL> create table "TEST "( a number);
Table created.
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist
You can whether ay blank line exist or not by checking its length or in following way.
SQL> select '*'||table_name||'*' from user_tables where upper(table_name) like '%TEST%';
*TEST *
SQL> select table_name,length(table_name) from user_tables where upper(table_name) like '%TEST%';
TABLE_NAME LENGTH(TABLE_NAME)
------------------------------ ------------------
TEST 5
To drop it or use it you have to use "TEST "
Like,
SQL> drop table "TEST ";
Table dropped.
2)If you have not done any spell mistake then check the owner of the table. You can do it by quring all_tables view.
SQL> SELECT OBJECT_NAME,OWNER,OBJECT_TYPE FROM ALL_OBJECTS WHERE UPPER(OBJECT_NAME)=UPPER('&Type_Object_name_here');
Then access the object as owner.object_name
3)If you fail then it is possibly the reason that you don't have permission on it. So query in DBA_OBJECTS view in order to find that who is the owner and check session privilege from SESSION_PRIVS view.
SQL> SELECT OBJECT_NAME,OWNER,OBJECT_TYPE FROM DBA_OBJECTS WHERE UPPER(OBJECT_NAME)=UPPER('&Type_Object_name_here');
SQL> SELECT * FROM SESSION_PRIVS;
Related Documents:
-------------------------------
Select any Table Select Any Dictionary Permission
The error comes in this format,
ORA-00942: table or view does not exist
ORA-04043: object %s does not exist
Cause of The Problem:
--------------------------
1)The object actually does not exist. That means you have done spell mistake. Review your object name. There may be a lower-upper case problem.
2)The table may exist but it is in different schema.
3)The user don't have necessary permission to read the object.
Solution of The problem:
--------------------------
1)Correct the name of the object if there is any spelling mistake. Be aware about case. I will write one example about case.
SQL> create table "TesT" ( a NUMBER);
Table created.
SQL> drop table TesT;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table "TesT";
Table dropped.
Here I created a table "TesT". Whenever I wrote TesT it showed it does not exist. Because in SQL*Plus whenever we write any command it takes as all upper case if you don't specify any quote. So to make it case sensitive we explicitly need to write text within double quote. ""
Also check whether blank line exist in the table name.
SQL> create table "TEST "( a number);
Table created.
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist
You can whether ay blank line exist or not by checking its length or in following way.
SQL> select '*'||table_name||'*' from user_tables where upper(table_name) like '%TEST%';
*TEST *
SQL> select table_name,length(table_name) from user_tables where upper(table_name) like '%TEST%';
TABLE_NAME LENGTH(TABLE_NAME)
------------------------------ ------------------
TEST 5
To drop it or use it you have to use "TEST "
Like,
SQL> drop table "TEST ";
Table dropped.
2)If you have not done any spell mistake then check the owner of the table. You can do it by quring all_tables view.
SQL> SELECT OBJECT_NAME,OWNER,OBJECT_TYPE FROM ALL_OBJECTS WHERE UPPER(OBJECT_NAME)=UPPER('&Type_Object_name_here');
Then access the object as owner.object_name
3)If you fail then it is possibly the reason that you don't have permission on it. So query in DBA_OBJECTS view in order to find that who is the owner and check session privilege from SESSION_PRIVS view.
SQL> SELECT OBJECT_NAME,OWNER,OBJECT_TYPE FROM DBA_OBJECTS WHERE UPPER(OBJECT_NAME)=UPPER('&Type_Object_name_here');
SQL> SELECT * FROM SESSION_PRIVS;
Related Documents:
-------------------------------
Select any Table Select Any Dictionary Permission
Types of Oracle Error Message.
A typical oracle error message follow the following form.
Prefix-ErrorNumber: Error Text
An example of error message is,
ORA-00942: table or view does not exist
Here ORA is prefix and error number is 00942 , The error text is "table or view does not exist".
The types of Error Message in oracle are,
ORA Oracle RDBMS errors
AMD Oracle OLAP Catalog Metadata Messages
AUD interMedia Audio Messages
DBV DBVERIFY Messages
DRG Oracle Text Messages
EPC Oracle Trace Collection Services Messages
EXP Export Messages
IMG interMedia Image Messages
IMP Import Messages
KUP External Tables Messages
LCD Parameter Messages
LFI BFILE-related messages
LPX XML Parser Messages
LRM Parameter Messages
LSX XML Schema Processor Messages
MOD SQL*Module Messages
NCR Remote Operation Messages
NID DBNEWID Messages
NMP Simple Network Management Protocol Messages
NNC Oracle Names Client Messages
NNF External Naming Messages
NNL Oracle Names Control Utility Messages
NNO Oracle Names Server Messages
NPL Oracle Names Server Network Presentation Layer Messages
NZE Network Security Messages
O2F Object Type Translator Type File Messages
O2I Object Type Translator Initialization Messages
O2U Object Type Translator Unparser Messages
PCB Pro*COBOL Messages
PCF PCF FIPS Messages
PLS PL/SQL and FIPS Messages
QSM Summary Advisor, Explain Rewrite, and Explain Materialized View Messages
RMAN Recovery Manager Messages
SQL SQL Runtime Messages
SQL*LOADER SQL*Loader Messages
TNS Oracle Net Messages
VID interMedia Video Messages
Related Documents
How to get Oracle Error Message from Database or OS
Prefix-ErrorNumber: Error Text
An example of error message is,
ORA-00942: table or view does not exist
Here ORA is prefix and error number is 00942 , The error text is "table or view does not exist".
The types of Error Message in oracle are,
ORA Oracle RDBMS errors
AMD Oracle OLAP Catalog Metadata Messages
AUD interMedia Audio Messages
DBV DBVERIFY Messages
DRG Oracle Text Messages
EPC Oracle Trace Collection Services Messages
EXP Export Messages
IMG interMedia Image Messages
IMP Import Messages
KUP External Tables Messages
LCD Parameter Messages
LFI BFILE-related messages
LPX XML Parser Messages
LRM Parameter Messages
LSX XML Schema Processor Messages
MOD SQL*Module Messages
NCR Remote Operation Messages
NID DBNEWID Messages
NMP Simple Network Management Protocol Messages
NNC Oracle Names Client Messages
NNF External Naming Messages
NNL Oracle Names Control Utility Messages
NNO Oracle Names Server Messages
NPL Oracle Names Server Network Presentation Layer Messages
NZE Network Security Messages
O2F Object Type Translator Type File Messages
O2I Object Type Translator Initialization Messages
O2U Object Type Translator Unparser Messages
PCB Pro*COBOL Messages
PCF PCF FIPS Messages
PLS PL/SQL and FIPS Messages
QSM Summary Advisor, Explain Rewrite, and Explain Materialized View Messages
RMAN Recovery Manager Messages
SQL SQL Runtime Messages
SQL*LOADER SQL*Loader Messages
TNS Oracle Net Messages
VID interMedia Video Messages
Related Documents
How to get Oracle Error Message from Database or OS
Thursday, May 8, 2008
Performing Tablespace Point-in-time Recovery.
With an example I will make you understand RMAN Fully Automated TSPITR. Workaround I will truncate a table which resides on USERS tablespace and later I will recover the tablespace to get back my data.
Note that my interaction with the RMAN client is shown as BOLD.
A)Get the rows of my_table.
SQL> select count(*) from my_table;
COUNT(*)
----------
49792
B)Truncate the Table.
SQL> TRUNCATE TABLE MY_TABLE;
Table truncated.
C)Determine the Tablespace which is belong my Dropped Table.
SQL> SELECT TABLESPACE_NAME from DBA_TABLES WHERE TABLE_NAME='MY_TABLE';
TABLESPACE_NAME
------------------------------
USERS
D)Connect to rman and perform TSPITR operation on USERS Tablespace.
SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 04:49:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
RMAN> RECOVER TABLESPACE USERS UNTIL TIME 'SYSDATE-1/24/60*5' AUXILIARY DESTINATION '/export/home/oracle';
Starting recover at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time
List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
Creating automatic instance, with SID='jDiz'
initialization parameters used for automatic instance:
db_name=DBASE
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DBASE_jDiz
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/export/home/oracle
control_files=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
starting up automatic instance DBASE
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 2019576 bytes
Variable Size 146804488 bytes
Database Buffers 50331648 bytes
Redo Buffers 6365184 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until time "SYSDATE-1/24/60*5";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
Finished restore at 08-MAY-08
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "SYSDATE-1/24/60*5";
plsql <<<-- tspitr_2 declare sqlstatement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539); begin sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement); exception when offline_not_needed then null; end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 4 to
"/oradata2/data1/dbase/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace USERS offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_temp_%u_.tmp in control file
Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_%u_.dbf
restoring datafile 00002 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /oradata2/data1/dbase/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 08-MAY-08
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_425hqsjn_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_425hqsjr_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
Starting recover at 08-MAY-08
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc
archive log thread 1 sequence 4 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc thread=1 sequence=3
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:07
Finished recover at 08-MAY-08
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\
(PROGRAM=/oracle/app/oracle/product/10.2.0/db_1/bin/oracle\)\
(ARGV0=oraclejDiz\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\
(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=jDiz^'\)\)\(CONNECT_DATA=\(SID=jDiz\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.1.0 - Production on Thu May 8 04:51:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DEPT
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
EXP-00091: Exporting questionable statistics.
. . exporting table MY_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.1.0 - Production on Thu May 8 04:52:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
. . importing table "EMP"
. . importing table "BONUS"
. . importing table "SALGRADE"
. importing ARJU's objects into ARJU
. . importing table "MY_TABLE"
. importing SCOTT's objects into SCOTT
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace USERS online
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /export/home/oracle/cntrl_tspitr_DBASE_jDiz.f deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_system_425hqsjn_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_undotbs1_425hqsjr_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_temp_425hs9ot_.tmp deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_1_425hs5oy_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_2_425hs6hl_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_3_425hs7kc_.log deleted
Finished recover at 08-MAY-08
E)Make the backup of Tablespace and make it Online.
RMAN> SQL'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE
RMAN> exit;
Recovery Manager complete.
F)Check the Objects.
SQL> select count(*) from my_table;
COUNT(*)
----------
49792
Related Documents:
---------------------
When you use TSPITR
Limitations of TSPITR
Note that my interaction with the RMAN client is shown as BOLD.
A)Get the rows of my_table.
SQL> select count(*) from my_table;
COUNT(*)
----------
49792
B)Truncate the Table.
SQL> TRUNCATE TABLE MY_TABLE;
Table truncated.
C)Determine the Tablespace which is belong my Dropped Table.
SQL> SELECT TABLESPACE_NAME from DBA_TABLES WHERE TABLE_NAME='MY_TABLE';
TABLESPACE_NAME
------------------------------
USERS
D)Connect to rman and perform TSPITR operation on USERS Tablespace.
SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 04:49:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
RMAN> RECOVER TABLESPACE USERS UNTIL TIME 'SYSDATE-1/24/60*5' AUXILIARY DESTINATION '/export/home/oracle';
Starting recover at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time
List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
Creating automatic instance, with SID='jDiz'
initialization parameters used for automatic instance:
db_name=DBASE
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DBASE_jDiz
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/export/home/oracle
control_files=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
starting up automatic instance DBASE
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 2019576 bytes
Variable Size 146804488 bytes
Database Buffers 50331648 bytes
Redo Buffers 6365184 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until time "SYSDATE-1/24/60*5";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
Finished restore at 08-MAY-08
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "SYSDATE-1/24/60*5";
plsql <<<-- tspitr_2 declare sqlstatement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539); begin sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement); exception when offline_not_needed then null; end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 4 to
"/oradata2/data1/dbase/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace USERS offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_temp_%u_.tmp in control file
Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_%u_.dbf
restoring datafile 00002 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /oradata2/data1/dbase/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 08-MAY-08
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_425hqsjn_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_425hqsjr_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
Starting recover at 08-MAY-08
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc
archive log thread 1 sequence 4 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc thread=1 sequence=3
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:07
Finished recover at 08-MAY-08
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\
(PROGRAM=/oracle/app/oracle/product/10.2.0/db_1/bin/oracle\)\
(ARGV0=oraclejDiz\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\
(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=jDiz^'\)\)\(CONNECT_DATA=\(SID=jDiz\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.1.0 - Production on Thu May 8 04:51:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DEPT
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
EXP-00091: Exporting questionable statistics.
. . exporting table MY_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.1.0 - Production on Thu May 8 04:52:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
. . importing table "EMP"
. . importing table "BONUS"
. . importing table "SALGRADE"
. importing ARJU's objects into ARJU
. . importing table "MY_TABLE"
. importing SCOTT's objects into SCOTT
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace USERS online
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /export/home/oracle/cntrl_tspitr_DBASE_jDiz.f deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_system_425hqsjn_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_undotbs1_425hqsjr_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_temp_425hs9ot_.tmp deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_1_425hs5oy_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_2_425hs6hl_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_3_425hs7kc_.log deleted
Finished recover at 08-MAY-08
E)Make the backup of Tablespace and make it Online.
RMAN> SQL'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE
RMAN> exit;
Recovery Manager complete.
F)Check the Objects.
SQL> select count(*) from my_table;
COUNT(*)
----------
49792
Related Documents:
---------------------
When you use TSPITR
Limitations of TSPITR
Limitations of Tablespace point-in-time Recovery
•If you have dropped a tablespace then TSPITR can't be used to recover that tablespace.
•You cannot recover a renamed tablespace to a point in time before it was renamed.
•You cannot recover tables without their associated constraints, or constraints
without the associated tables.
•Tablespaces containing undo or rollback segments cannot be recovered.
•Tablespaces that contain objects owned by SYS can't be recovered.
•In case of partitioning table which spread over multiple tablespaces in that case a single tablespace(only a partition/subset of partition) can't be recovered. You must recover all of the tablespace where partitioning table resides.
•If a tablespace is recovered by TSPITR statistics are not gathered of recovered objects. Therefore you must manually gather statistics of the recovered objects.
•Tablespace containing tables with VARRAY columns, nested tables, or external files can't be recovered with TSPITR.
•The tablespace containing replicated master tables, snapshot logs, snapshot tables can't be recovered with TSPITR.
•If a datafile was added after the point to which RMAN is recovering, an empty datafile by the same name will be included in the tablespace after RMAN TSPITR.
•You cannot recover a renamed tablespace to a point in time before it was renamed.
•You cannot recover tables without their associated constraints, or constraints
without the associated tables.
•Tablespaces containing undo or rollback segments cannot be recovered.
•Tablespaces that contain objects owned by SYS can't be recovered.
•In case of partitioning table which spread over multiple tablespaces in that case a single tablespace(only a partition/subset of partition) can't be recovered. You must recover all of the tablespace where partitioning table resides.
•If a tablespace is recovered by TSPITR statistics are not gathered of recovered objects. Therefore you must manually gather statistics of the recovered objects.
•Tablespace containing tables with VARRAY columns, nested tables, or external files can't be recovered with TSPITR.
•The tablespace containing replicated master tables, snapshot logs, snapshot tables can't be recovered with TSPITR.
•If a datafile was added after the point to which RMAN is recovering, an empty datafile by the same name will be included in the tablespace after RMAN TSPITR.
When to use RMAN TSPITR?
RMAN tablespace point-in-time recovery (abbreviated as TSPITR) enables you to quickly recover one or more tablespace in an oracle database to a previous time, without affecting the rest of the tablespaces in your database.
When you can use TSPITR
---------------------------
We need to remember that with RMAN TSPITR the whole data set inside the tablespace will be recover to an earlier time rather than single object.
In the following cases you might choose to use TSPITR.
1)Erroneously TRUNCATE TABLE statement is execute on a table.
2)Wrong update is done on a table and committed.
3)In a database there is several schema each in different tablespace. Now I want to get back a single schema to an earlier time.
It is good to remember that if a database run on a NOARCHIVELOG mode then TSPITR can't be performed. In other word I can say if I don't have archived redo logs then TSPITR can't be performed.
When you can use TSPITR
---------------------------
We need to remember that with RMAN TSPITR the whole data set inside the tablespace will be recover to an earlier time rather than single object.
In the following cases you might choose to use TSPITR.
1)Erroneously TRUNCATE TABLE statement is execute on a table.
2)Wrong update is done on a table and committed.
3)In a database there is several schema each in different tablespace. Now I want to get back a single schema to an earlier time.
It is good to remember that if a database run on a NOARCHIVELOG mode then TSPITR can't be performed. In other word I can say if I don't have archived redo logs then TSPITR can't be performed.
Wednesday, May 7, 2008
Know how many database register in Recovery Catalog Views
All of the information that we can see in RMAN prompt by SHOW, LIST or REPORT command can also be easily seen by query recovery catalog views. Recovery catalog views are prefixed by RC_.
Most of the catalog views have a corresponding dynamic performance view (or V$view) in the database server. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding server view is that each catalog view contains information about all the databases registered in the catalog, whereas the database server view contains information only about itself.
How to Find DB_KEY and DBID from Recovery Catalog View?
------------------------------------------------------------
SQL> conn catalog_user/catalog_pwd@netservice_name_of_recovery_catalog
Connected.
SQL> SELECT DB_KEY,DBID,NAME,RESETLOGS_TIME FROM RC_DATABASE;
DB_KEY DBID NAME RESETLOGS
---------- ---------- -------- ---------
88 1509380669 DBASE 06-MAY-08
Determining the Schema Version of the Recovery Catalog
------------------------------------------------------------
SQL> SELECT * FROM rcver;
VERSION
------------
10.02.00.00
You can query RC_BACKUP_SET,RC_BACKUP_CONTROLFILE,RC_BACKUP_DATAFILE,RC_BACKUP_CORRUPTION,
RC_STORED_SCRIPT,RC_BACKUP_FILES views as of your interest.
Related Documents:
---------------------
Working with Recovery Catalog
How to Use Recovery Catalog
How to Create Recovery Catalog
Most of the catalog views have a corresponding dynamic performance view (or V$view) in the database server. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding server view is that each catalog view contains information about all the databases registered in the catalog, whereas the database server view contains information only about itself.
How to Find DB_KEY and DBID from Recovery Catalog View?
------------------------------------------------------------
SQL> conn catalog_user/catalog_pwd@netservice_name_of_recovery_catalog
Connected.
SQL> SELECT DB_KEY,DBID,NAME,RESETLOGS_TIME FROM RC_DATABASE;
DB_KEY DBID NAME RESETLOGS
---------- ---------- -------- ---------
88 1509380669 DBASE 06-MAY-08
Determining the Schema Version of the Recovery Catalog
------------------------------------------------------------
SQL> SELECT * FROM rcver;
VERSION
------------
10.02.00.00
You can query RC_BACKUP_SET,RC_BACKUP_CONTROLFILE,RC_BACKUP_DATAFILE,RC_BACKUP_CORRUPTION,
RC_STORED_SCRIPT,RC_BACKUP_FILES views as of your interest.
Related Documents:
---------------------
Working with Recovery Catalog
How to Use Recovery Catalog
How to Create Recovery Catalog
Working with RMAN stored scripts in Recovery Catalog
Within RMAN you can save commands and execute it whenever you wish. Stored scripts bring this facility where we should not bother about OS scripts whether RMAN client has proper permission on it or not.
Stored Scripts can be two types.
1)Global Stored Scripts:A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.
2)Local Stored Scripts:A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database.
How to Created Stored Script:
---------------------------------
To create local stored script.
CREATE SCRIPT query_backup
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}
To create global stored with a comment added to it,
CREATE GLOBAL SCRIPT global_query_backup
COMMENT 'This is a sample global script which returns some query'
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}
Alternatively you can create script from a text file. To create local script from text file in '/oradata2' just use,
CREATE SCRIPT test_script FROM FILE '/oradata2/my_script_file.txt';
How to run Stored Scripts:
------------------------------
To run stored script use,
RUN{
EXECUTE SCRIPT query_backup;
}
It will first search for local stored script with name query_backup. If not found then will check global stored script with the name. If you have same name in both local and global script then to run global script explicitly use,
RUN{
EXECUTE GLOBAL SCRIPT global_query_backup;
}
Displaying a Stored Script:
----------------------------------
PRINT SCRIPT query_backup;
PRINT GLOBAL SCRIPT global_query_backup;
RMAN> PRINT GLOBAL SCRIPT global_query_backup;
printing stored global script: global_query_backup
{SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}
Listing Stored Scripts:
-----------------------------
To view both global and local, for the currently connected target database use,
LIST SCRIPT NAMES;
To view only global script names use,
LIST GLOBAL SCRIPT NAMES;
To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use,
LIST ALL SCRIPT NAMES;
Remember that to run LIST SCRIPT NAMES RMAN must be connected to target database.
To run LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES RMAN need not to be connected to target database.
Example:
---------------
-bash-3.00$ rman CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 01:14:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> LIST GLOBAL SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
Global Scripts
Script Name
Description
-----------------------------------------------------------------------
global_query_backup
This is a sample global script which returns some query
Updating Stored Scripts:
-------------------------
To update a script use,
REPLACE [GLOBAL]SCRIPT
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Here like,
REPLACE SCRIPT query_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
If the script does not already exist, then RMAN creates it.
Deleting Stored Scripts
--------------------------------
DELETE SCRIPT query_bakcup;
DELETE GLOBAL SCRIPT global_query_backup;
If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists.
Related Documents:
-----------------------
How to Create Recovery Catalog and Use it
Stored Scripts can be two types.
1)Global Stored Scripts:A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.
2)Local Stored Scripts:A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database.
How to Created Stored Script:
---------------------------------
To create local stored script.
CREATE SCRIPT query_backup
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}
To create global stored with a comment added to it,
CREATE GLOBAL SCRIPT global_query_backup
COMMENT 'This is a sample global script which returns some query'
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}
Alternatively you can create script from a text file. To create local script from text file in '/oradata2' just use,
CREATE SCRIPT test_script FROM FILE '/oradata2/my_script_file.txt';
How to run Stored Scripts:
------------------------------
To run stored script use,
RUN{
EXECUTE SCRIPT query_backup;
}
It will first search for local stored script with name query_backup. If not found then will check global stored script with the name. If you have same name in both local and global script then to run global script explicitly use,
RUN{
EXECUTE GLOBAL SCRIPT global_query_backup;
}
Displaying a Stored Script:
----------------------------------
PRINT SCRIPT query_backup;
PRINT GLOBAL SCRIPT global_query_backup;
RMAN> PRINT GLOBAL SCRIPT global_query_backup;
printing stored global script: global_query_backup
{SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}
Listing Stored Scripts:
-----------------------------
To view both global and local, for the currently connected target database use,
LIST SCRIPT NAMES;
To view only global script names use,
LIST GLOBAL SCRIPT NAMES;
To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use,
LIST ALL SCRIPT NAMES;
Remember that to run LIST SCRIPT NAMES RMAN must be connected to target database.
To run LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES RMAN need not to be connected to target database.
Example:
---------------
-bash-3.00$ rman CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 01:14:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> LIST GLOBAL SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
Global Scripts
Script Name
Description
-----------------------------------------------------------------------
global_query_backup
This is a sample global script which returns some query
Updating Stored Scripts:
-------------------------
To update a script use,
REPLACE [GLOBAL]SCRIPT
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Here like,
REPLACE SCRIPT query_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
If the script does not already exist, then RMAN creates it.
Deleting Stored Scripts
--------------------------------
DELETE SCRIPT query_bakcup;
DELETE GLOBAL SCRIPT global_query_backup;
If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists.
Related Documents:
-----------------------
How to Create Recovery Catalog and Use it
Working with Recovery Catalog.
Registering Multiple Databases in a Recovery Catalog
You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.
So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog. In that case if you want to do so you have to change DBID. The method of changing DBID is described here Change DBID.
Unregistering a Target Database from the Recovery Catalog
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.
-bash-3.00$ rman target / CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 23:53:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database
2)issue UNREGISTER DATABASE command.
RMAN> UNREGISTER DATABASE;
database name is "DBASE" and DBID is 1509380669
Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog
But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the
time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.
Resynchronizing the Recovery Catalog
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.
RMAN> RESYNC CATALOG;
starting full resync of recovery catalog
full resync complete
Related Documents:
Change DBID
You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.
So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog. In that case if you want to do so you have to change DBID. The method of changing DBID is described here Change DBID.
Unregistering a Target Database from the Recovery Catalog
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.
-bash-3.00$ rman target / CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 23:53:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database
2)issue UNREGISTER DATABASE command.
RMAN> UNREGISTER DATABASE;
database name is "DBASE" and DBID is 1509380669
Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog
But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the
time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.
Resynchronizing the Recovery Catalog
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.
RMAN> RESYNC CATALOG;
starting full resync of recovery catalog
full resync complete
Related Documents:
Change DBID
How to Use Recovery Catalog
You have already created recovery catalog described in How to Create Recovery Catalog. Now you want this recovery catalog for your target database.
To do so you have to follow the following steps.
1)-Make sure that the recovery catalog database is open,
-Connect RMAN to the target database and recovery catalog database.
For example in target Database dbase , you want to use recovery catalog created of ARJU database.
SQL> SELECT NAME, DBID FROM V$DATABASE;
NAME DBID
--------- ----------
DBASE 1509380669
SQL> !rman target / catalog catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 06:16:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database
2)Register the target database in the connected recovery catalog:
By REGISTER DATABASE RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
c)Be sure that you are in RECOVERY CATALOG schema.
Some commands need to be in recovery catalog schema like,
RMAN> report schema at time 'SYSDATE-1';
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 470 SYSTEM YES /oradata2/data1/dbase/system01.dbf
2 25 UNDOTBS1 YES /oradata2/data1/dbase/undotbs01.dbf
3 240 SYSAUX YES /oradata2/data1/dbase/sysaux01.dbf
4 5 USERS YES /oradata2/data1/dbase/users01.dbf
To do so you have to follow the following steps.
1)-Make sure that the recovery catalog database is open,
-Connect RMAN to the target database and recovery catalog database.
For example in target Database dbase , you want to use recovery catalog created of ARJU database.
SQL> SELECT NAME, DBID FROM V$DATABASE;
NAME DBID
--------- ----------
DBASE 1509380669
SQL> !rman target / catalog catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 06:16:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database
2)Register the target database in the connected recovery catalog:
By REGISTER DATABASE RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
c)Be sure that you are in RECOVERY CATALOG schema.
Some commands need to be in recovery catalog schema like,
RMAN> report schema at time 'SYSDATE-1';
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 470 SYSTEM YES /oradata2/data1/dbase/system01.dbf
2 25 UNDOTBS1 YES /oradata2/data1/dbase/undotbs01.dbf
3 240 SYSAUX YES /oradata2/data1/dbase/sysaux01.dbf
4 5 USERS YES /oradata2/data1/dbase/users01.dbf
How to create recovery catalog and use it
Recovery Catalog Concepts:
Recovery catalog holds the RMAN repository information(i.e backup information) in a separate database schema in addition to control files. Though you can use target database as a recovery catalog database(The database where recovery catalog resides) but you will not do that because recovery catalog must be protected in the event of the loss of target database.
Before proceed it is good to understand about recovery catalog that recovery catalog is nothing but a schema that owns a list of tables. SYS user can't be owner of recovery catalog.
How to Create Recovery Catalog:
Creating a recovery catalog is a three steps process.They are,
A)Configure Recovery Catalog Database.
B)Create the Recovery Catalog owner.
C)Create Recovery Catalog itself.
A)Configure Recovery Catalog Database:
1)Choose any database to select as recovery catalog database rather than target database(where you will perform backup).
2)Create a tablespace where recovery catalog information will be populated.
SQL> CREATE TABLESPACE catalog_tbs DATAFILE '/oradata2/catalog01.dbf' SIZE 100M;
Tablespace created.
B)Create the Recovery Catalog owner.
1)Create a user in the recovery catalog database who actually owns the recovery catalog schema. Also assign default tablespace catalog_tbs to this user.
SQL> CREATE USER catalog_user IDENTIFIED BY catalog_pwd TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE catalog_tbs QUOTA UNLIMITED ON catalog_tbs;
User created.
2)Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog.
SQL> GRANT RECOVERY_CATALOG_OWNER TO catalog_user;
Grant succeeded.
C)Create Recovery Catalog itself:
At this stage you have only a tablespace and a user in recovery catalog database. There is no objects in the recovery catalog schema.
SQL> conn catalog_user/catalog_pwd
Connected.
SQL> select * from user_tables;
no rows selected
Now you have to populate tables in the schema. To do this,
1)Through RMAN connect to the database that will contain the catalog as the catalog owner.
SQL> !rman CATALOG catalog_user/catalog_pwd
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 05:51:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
2)Run the CREATE CATALOG command to create the catalog.
RMAN>CREATE CATALOG;
recovery catalog created
You can also create catalog in other tablespace like USERS if you specify that just like below,
RMAN>CREATE CATALOG TABLESPACE USERS;
In our example we created catalog tablespace catalog_tbs so our command will be,
RMAN>CREATE CATALOG TABLESPACE catalog_tbs;
3)Check the recovery catalog tables, objects, view, package etc.
SQL> SELECT COUNT(*) FROM USER_OBJECTS;
COUNT(*)
----------
195
Now the next step is how to use it. It is discussed in How to Use Recovery Catalog
Related Documents:
How to Use Recovery Catalog
Recovery catalog holds the RMAN repository information(i.e backup information) in a separate database schema in addition to control files. Though you can use target database as a recovery catalog database(The database where recovery catalog resides) but you will not do that because recovery catalog must be protected in the event of the loss of target database.
Before proceed it is good to understand about recovery catalog that recovery catalog is nothing but a schema that owns a list of tables. SYS user can't be owner of recovery catalog.
How to Create Recovery Catalog:
Creating a recovery catalog is a three steps process.They are,
A)Configure Recovery Catalog Database.
B)Create the Recovery Catalog owner.
C)Create Recovery Catalog itself.
A)Configure Recovery Catalog Database:
1)Choose any database to select as recovery catalog database rather than target database(where you will perform backup).
2)Create a tablespace where recovery catalog information will be populated.
SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME DBID
--------- ----------
ARJU 2869417476
SQL> CREATE TABLESPACE catalog_tbs DATAFILE '/oradata2/catalog01.dbf' SIZE 100M;
Tablespace created.
B)Create the Recovery Catalog owner.
1)Create a user in the recovery catalog database who actually owns the recovery catalog schema. Also assign default tablespace catalog_tbs to this user.
SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME DBID
--------- ----------
ARJU 2869417476
SQL> CREATE USER catalog_user IDENTIFIED BY catalog_pwd TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE catalog_tbs QUOTA UNLIMITED ON catalog_tbs;
User created.
2)Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog.
SQL> GRANT RECOVERY_CATALOG_OWNER TO catalog_user;
Grant succeeded.
C)Create Recovery Catalog itself:
At this stage you have only a tablespace and a user in recovery catalog database. There is no objects in the recovery catalog schema.
SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME DBID
--------- ----------
ARJU 2869417476
SQL> conn catalog_user/catalog_pwd
Connected.
SQL> select * from user_tables;
no rows selected
Now you have to populate tables in the schema. To do this,
1)Through RMAN connect to the database that will contain the catalog as the catalog owner.
SQL> !rman CATALOG catalog_user/catalog_pwd
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 05:51:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
2)Run the CREATE CATALOG command to create the catalog.
RMAN>CREATE CATALOG;
recovery catalog created
You can also create catalog in other tablespace like USERS if you specify that just like below,
RMAN>CREATE CATALOG TABLESPACE USERS;
In our example we created catalog tablespace catalog_tbs so our command will be,
RMAN>CREATE CATALOG TABLESPACE catalog_tbs;
3)Check the recovery catalog tables, objects, view, package etc.
SQL> SELECT COUNT(*) FROM USER_OBJECTS;
COUNT(*)
----------
195
Now the next step is how to use it. It is discussed in How to Use Recovery Catalog
Related Documents:
How to Use Recovery Catalog
Recover a Lost Datafile without backup.
In my database I have added one data file. I have not back up that data file. Now someone accidentally drop that datafile. Can I get back the my data file now? The answer is yes if my database run in archivelog mode and I have the available archive logs science the creation of datafile.
With an example I will demonstrate the procedure.
1)I create tablespace data with one datafile and after some time I have added another datafile. Both data file don't have any backup.
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
SQL> create tablespace data datafile '/oradata2/data.dbf' size 2M;
Tablespace created.
SQL> alter tablespace data add datafile '/oradata2/data1/data02.dbf' size 2M;
Tablespace altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/users01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
6 rows selected.
2)Creating Tables to Populate the Datafiles.
SQL> create table before_delete tablespace data as select level a1 from dual connect by level <9999; Table created.
SQL> insert into before_delete select level a1 from dual connect by level <9999; 9998 rows created.
SQL> commit;
Commit complete.
SQL> select file_name from dba_data_files where file_id in (select file_id from dba_extents where segment_name='BEFORE_DELETE');
FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data.dbf
/oradata2/data1/data02.dbf
3)Now delete both datafile by OS command.
SQL> !rm /oradata2/data.dbf
SQL> !rm /oradata2/data1/data02.dbf
4)Now I want to get back both datafile. Connect to RMAN and make the affected Tablespace offline immediate.
SQL> !rman target /
RMAN> sql'ALTER TABLESPACE DATA OFFLINE IMMEDIATE';
5)Perform Recovery of The tablespace.
RMAN> RECOVER TABLESPACE DATA;
6)Make the status online of the tablespace.
RMAN> sql'ALTER TABLESPACE DATA ONLINE';
In this case oracle at first create and empty datafile and then apply all archived redo logs and online redo logs on the tablespace up to the current time.
With an example I will demonstrate the procedure.
1)I create tablespace data with one datafile and after some time I have added another datafile. Both data file don't have any backup.
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
SQL> create tablespace data datafile '/oradata2/data.dbf' size 2M;
Tablespace created.
SQL> alter tablespace data add datafile '/oradata2/data1/data02.dbf' size 2M;
Tablespace altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/users01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
6 rows selected.
2)Creating Tables to Populate the Datafiles.
SQL> create table before_delete tablespace data as select level a1 from dual connect by level <9999; Table created.
SQL> insert into before_delete select level a1 from dual connect by level <9999; 9998 rows created.
SQL> commit;
Commit complete.
SQL> select file_name from dba_data_files where file_id in (select file_id from dba_extents where segment_name='BEFORE_DELETE');
FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data.dbf
/oradata2/data1/data02.dbf
3)Now delete both datafile by OS command.
SQL> !rm /oradata2/data.dbf
SQL> !rm /oradata2/data1/data02.dbf
4)Now I want to get back both datafile. Connect to RMAN and make the affected Tablespace offline immediate.
SQL> !rman target /
RMAN> sql'ALTER TABLESPACE DATA OFFLINE IMMEDIATE';
5)Perform Recovery of The tablespace.
RMAN> RECOVER TABLESPACE DATA;
6)Make the status online of the tablespace.
RMAN> sql'ALTER TABLESPACE DATA ONLINE';
In this case oracle at first create and empty datafile and then apply all archived redo logs and online redo logs on the tablespace up to the current time.
Tuesday, May 6, 2008
Information about Temporary Segments.
A)The users who is Performing Sort operation in Temp Segments:
--------------------------------------------------------------------
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
----------- ------------- ---------- ---------- ---------- ---------- ------ ------ -------
TEMP 201 217865 748928 277 1185 PROD7 oracle ACTIVE
B)Information about Tablespace Containing sort Segments.
SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;
TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ ------------ -------------
TEMP 128 11896 5851 6045 11322
Here,
Extent_size : size of one extent, in number of Oracle blocks
Total_extents: total number of extents in the segment (free or in use)
Used_extents : total number of extents currently in use
Free_extents : total number of extents currently marked as free
Max_used_size: maximum number of extents ever needed by an operation
C)If you want to keep interest of how much space is used in temporary segments then query as,
SELECT EXTENT_SIZE*DB_BLOCK_SIZE*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment ;
If your database block size is 8192 (Query from select value from v$parameter where NAME='db_block_size';) then the space used by temp segments is ,
SQL>SELECT EXTENT_SIZE*8192*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment;
Space used in GigaByte
----------------------
11.6181641
From V$SORT_SEGMENT you can also query like,
SQL> SELECT TABLESPACE_NAME,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,MAX_USED_BLOCKS,TOTAL_BLOCKS from V$SORT_SEGMENT;
TABLESPACE EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_BLOCKS TOTAL_BLOCKS
---------- ----------- ------------- ------------ ------------ --------------- ------------
TEMP 128 11896 4526 7370 1449216 1522688
D)In order to know SQL ID and which type of Sort Segment issue,
SQL>SELECT USERNAME, SQLADDR,SQL_ID,TABLESPACE,SEGTYPE,EXTENTS,BLOCKS FROM V$TEMPSEG_USAGE;
USERNAME SQLADDR SQL_ID TABLESP SEGTYPE EXTENTS BLOCKS
------------ ---------------- ------------- ------- --------- ---------- ----------
SYSTEM 00000004129E28C0 b058ymxj1rvkg TEMP LOB_DATA 1 128
PROD7 0000000412E1C9F8 1t1v0wvyzwzuj TEMP SORT 5193 664704
E)To know which SQL is is using Temporary Segments query,
SQL> SELECT s.SQL_TEXT, t.USERNAME,t.TABLESPACE,t.SEGTYPE,t.BLOCKS,t.EXTENTS from V$SQL s, V$TEMPSEG_USAGE t WHERE t.SQL_ID=s.SQL_ID;
SQL_TEXT
------------------------------------------------------------------------------------
-------------------------------------------------------------
USERNAME TABLESP SEGTYPE BLOCKS EXTENTS
------------------------------ ------- --------- ---------- ----------
SELECT sql_id,sql_text from v$sql WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
SYSTEM TEMP LOB_DATA 128 1
select * from user_activity order by ACTION,COOKIE_INFO
PROD7 TEMP SORT 36736 287
--------------------------------------------------------------------
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
----------- ------------- ---------- ---------- ---------- ---------- ------ ------ -------
TEMP 201 217865 748928 277 1185 PROD7 oracle ACTIVE
B)Information about Tablespace Containing sort Segments.
SQL> SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;
TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
------------------------------- ----------- ------------- ------------ ------------ -------------
TEMP 128 11896 5851 6045 11322
Here,
Extent_size : size of one extent, in number of Oracle blocks
Total_extents: total number of extents in the segment (free or in use)
Used_extents : total number of extents currently in use
Free_extents : total number of extents currently marked as free
Max_used_size: maximum number of extents ever needed by an operation
C)If you want to keep interest of how much space is used in temporary segments then query as,
SELECT EXTENT_SIZE*DB_BLOCK_SIZE*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment ;
If your database block size is 8192 (Query from select value from v$parameter where NAME='db_block_size';) then the space used by temp segments is ,
SQL>SELECT EXTENT_SIZE*8192*USED_EXTENTS/1024/1024/1024 "Space used in GigaByte" FROM v$sort_segment;
Space used in GigaByte
----------------------
11.6181641
From V$SORT_SEGMENT you can also query like,
SQL> SELECT TABLESPACE_NAME,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,MAX_USED_BLOCKS,TOTAL_BLOCKS from V$SORT_SEGMENT;
TABLESPACE EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_BLOCKS TOTAL_BLOCKS
---------- ----------- ------------- ------------ ------------ --------------- ------------
TEMP 128 11896 4526 7370 1449216 1522688
D)In order to know SQL ID and which type of Sort Segment issue,
SQL>SELECT USERNAME, SQLADDR,SQL_ID,TABLESPACE,SEGTYPE,EXTENTS,BLOCKS FROM V$TEMPSEG_USAGE;
USERNAME SQLADDR SQL_ID TABLESP SEGTYPE EXTENTS BLOCKS
------------ ---------------- ------------- ------- --------- ---------- ----------
SYSTEM 00000004129E28C0 b058ymxj1rvkg TEMP LOB_DATA 1 128
PROD7 0000000412E1C9F8 1t1v0wvyzwzuj TEMP SORT 5193 664704
E)To know which SQL is is using Temporary Segments query,
SQL> SELECT s.SQL_TEXT, t.USERNAME,t.TABLESPACE,t.SEGTYPE,t.BLOCKS,t.EXTENTS from V$SQL s, V$TEMPSEG_USAGE t WHERE t.SQL_ID=s.SQL_ID;
SQL_TEXT
------------------------------------------------------------------------------------
-------------------------------------------------------------
USERNAME TABLESP SEGTYPE BLOCKS EXTENTS
------------------------------ ------- --------- ---------- ----------
SELECT sql_id,sql_text from v$sql WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)
SYSTEM TEMP LOB_DATA 128 1
select * from user_activity order by ACTION,COOKIE_INFO
PROD7 TEMP SORT 36736 287
Block Media Recovery with RMAN ORA-01578: ORA-01110:
Suppose in the alert.log, or trace files, or a media management interface you got the following output.
ORA-01578: ORACLE data block corrupted (file # 4, block # 45)
ORA-01110: data file 4: '/oradata2/users01.dbf'
In this case you can use the BLOCKRECOVER command to restore and recover individual datablocks within a datafile. Here datafile 4 and block 45. You should keep in mind that if you want to BLOCKRECOVER command you had to have a previous backup of the datafile or database.
Block media recovery is not useful in cases where the extent of data loss or corruption is not known; in this case, use datafile recovery instead.
Suppose if I got above error message data block corrupted then I can use BLOCKRECOVER as following,
1)$rman TARGET /
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45;
2)If you want from backup set or image copy then use,
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 FROM BACKUPSET;
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 FROM IMAGECOPY;
3)From a Specified backup,
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 FROM TAG level0;
4)Limiting to a certain point,
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 RESTORE UNTIL 'SYSDATE-2';
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 RESTORE UNTIL SCN 1111;
Remember if you use UNTIL clasue then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.
Whenever you use BACKUP command to BACKUP your database or use BACKUP VALIDATE command Use RMAN to Validate Backup the V$DATABASE_BLOCK_CORRUPTION view is populated. Set MAXCORRUPT to continue backup or backup validate and thus continue populate the V$DATABASE_BLOCK_CORRUPTION view.
If you want to recover all marked corrupt block in the V$DATABASE_BLOCK_CORRUPTION view then just use,
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-2';
Related Documents:
--------------------
Use RMAN to Validate Backup
ora-19566
ORA-01578: ORACLE data block corrupted (file # 4, block # 45)
ORA-01110: data file 4: '/oradata2/users01.dbf'
In this case you can use the BLOCKRECOVER command to restore and recover individual datablocks within a datafile. Here datafile 4 and block 45. You should keep in mind that if you want to BLOCKRECOVER command you had to have a previous backup of the datafile or database.
Block media recovery is not useful in cases where the extent of data loss or corruption is not known; in this case, use datafile recovery instead.
Suppose if I got above error message data block corrupted then I can use BLOCKRECOVER as following,
1)$rman TARGET /
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45;
2)If you want from backup set or image copy then use,
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 FROM BACKUPSET;
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 FROM IMAGECOPY;
3)From a Specified backup,
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 FROM TAG level0;
4)Limiting to a certain point,
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 RESTORE UNTIL 'SYSDATE-2';
RMAN>BLOCKRECOVER DATAFILE 4 BLOCK 45 RESTORE UNTIL SCN 1111;
Remember if you use UNTIL clasue then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.
Whenever you use BACKUP command to BACKUP your database or use BACKUP VALIDATE command Use RMAN to Validate Backup the V$DATABASE_BLOCK_CORRUPTION view is populated. Set MAXCORRUPT to continue backup or backup validate and thus continue populate the V$DATABASE_BLOCK_CORRUPTION view.
If you want to recover all marked corrupt block in the V$DATABASE_BLOCK_CORRUPTION view then just use,
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-2';
Related Documents:
--------------------
Use RMAN to Validate Backup
ora-19566
Restore and Recover database to a new host
In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.
1)In neptune machine(Source)
RMAN> backup database;
Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08
2)Transfer this two backup pieces to target machine(From Neptune)
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 525 MB 00:59
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_s_654016132_42 100% |***********************************************| 6976 KB 00:00
3)Determine the DBID of source machine(Neptune here)
SQL> select dbid from v$database;
DBID
----------
3386862614
4)Now perform task on target machine(Saturn here).
First set ORACLE_SID,
-bash-3.00$export ORACLE_SID=dbase1
Then connect to rman,
-bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location
6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo Buffers 6365184 bytes
7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiece
backup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
recid=33 stamp=65398295
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 525.67M DISK 00:01:31 06-MAY-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;
database opened.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.
1)In neptune machine(Source)
RMAN> backup database;
Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08
2)Transfer this two backup pieces to target machine(From Neptune)
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 525 MB 00:59
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_s_654016132_42 100% |***********************************************| 6976 KB 00:00
3)Determine the DBID of source machine(Neptune here)
SQL> select dbid from v$database;
DBID
----------
3386862614
4)Now perform task on target machine(Saturn here).
First set ORACLE_SID,
-bash-3.00$export ORACLE_SID=dbase1
Then connect to rman,
-bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location
6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo Buffers 6365184 bytes
7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiece
backup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
recid=33 stamp=65398295
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 525.67M DISK 00:01:31 06-MAY-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;
database opened.
Monday, May 5, 2008
Restore and Recover database in Noarchivelog Mode
Before proceed we need to know while restoring data in noarchivelog mode that,
1)Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.
2)Media recovery is not possible, because there are no archived logs.
3)If we exclude any tablespace except read-only or offline from taking database backup then those tablespaces will be lost.
In this scenario I have lost all the data files, control files, redo log file and spfile. I have also forgot DBID of the database. The procedure of restore and recovery of database in noarchivelog mode in as below.
A)Find the DBID of the Database from Backup Piece.
bash-3.00$ strings /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp |grep MAXVALUE,
3386862614, MAXVALUE,
We got DBID here 3386862614. For more details please visit How to Discover DBID
B)Connect to target database with RMAN
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 12:50:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
C)Set the DBID.
RMAN> set dbid 3386862614
executing command: SET DBID
D)Start the database in nomount stage with dummy Parameter.
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 byte
E)Restore spfile
RMAN> restore spfile from '/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
F)Mount the Database.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 100667112 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
G)Restore controlfile.
RMAN> restore controlfile from autobackup;
Starting restore at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /oradata2/flash_recovery_area
database name (or database unique name) used for search: DBASE1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oradata2/data1/dbase1/control01.ctl
output filename=/oradata2/data1/dbase1/control02.ctl
output filename=/oradata2/data1/dbase1/control03.ctl
Finished restore at 06-MAY-08
H)Mount the Database.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
I)Restore Database.
As we have flash recovery area configured so it automatically cataloged backup sets.
RMAN> restore database;
Starting restore at 06-MAY-08
Starting implicit crosscheck backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 06-MAY-08
Starting implicit crosscheck copy at 06-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
restoring datafile 00002 to /oradata2/data1/dbase1/undotbs01.dbf
restoring datafile 00003 to /oradata2/data1/dbase1/sysaux01.dbf
restoring datafile 00004 to /oradata2/data1/dbase1/users01.dbf
restoring datafile 00005 to /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
restoring datafile 00006 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
restoring datafile 00007 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
restoring datafile 00008 to /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19870: error reading backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19612: datafile 1 not restored due to missing or corrupt data
failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp tag=LEVEL_0_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 06-MAY-08
J)Recover Database:
If the current online logs contain all changes since the last backup , then you can run RECOVER DATABASE without specifying NOREDO. Otherwise you have to specify RECOVER DATABASE NOREDO.
RMAN> recover database;
Starting recover at 06-MAY-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 11 is already on disk as file /oradata2/data1/dbase1/redo01.log
archive log thread 1 sequence 12 is already on disk as file /oradata2/data1/dbase1/redo02.log
archive log thread 1 sequence 13 is already on disk as file /oradata2/data1/dbase1/redo03.log
archive log filename=/oradata2/data1/dbase1/redo01.log thread=1 sequence=11
archive log filename=/oradata2/data1/dbase1/redo02.log thread=1 sequence=12
archive log filename=/oradata2/data1/dbase1/redo03.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:12
Finished recover at 06-MAY-08
K)Open the database with RESETLOGS options.
RMAN> alter database open resetlogs;
database opened.
Related Documents:
-------------------
How to Discover or Find DBID
How to Restore Spfile from Backup
How to Restore Controlfile
1)Only cold backups (that is, backups created when the database was shut down normally) can be used in restoring a database in NOARCHIVELOG mode.
2)Media recovery is not possible, because there are no archived logs.
3)If we exclude any tablespace except read-only or offline from taking database backup then those tablespaces will be lost.
In this scenario I have lost all the data files, control files, redo log file and spfile. I have also forgot DBID of the database. The procedure of restore and recovery of database in noarchivelog mode in as below.
A)Find the DBID of the Database from Backup Piece.
bash-3.00$ strings /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp |grep MAXVALUE,
3386862614, MAXVALUE,
We got DBID here 3386862614. For more details please visit How to Discover DBID
B)Connect to target database with RMAN
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 12:50:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
C)Set the DBID.
RMAN> set dbid 3386862614
executing command: SET DBID
D)Start the database in nomount stage with dummy Parameter.
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 byte
E)Restore spfile
RMAN> restore spfile from '/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp';
Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_653998009_420shstm_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08
F)Mount the Database.
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 100667112 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
G)Restore controlfile.
RMAN> restore controlfile from autobackup;
Starting restore at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /oradata2/flash_recovery_area
database name (or database unique name) used for search: DBASE1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oradata2/data1/dbase1/control01.ctl
output filename=/oradata2/data1/dbase1/control02.ctl
output filename=/oradata2/data1/dbase1/control03.ctl
Finished restore at 06-MAY-08
H)Mount the Database.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
I)Restore Database.
As we have flash recovery area configured so it automatically cataloged backup sets.
RMAN> restore database;
Starting restore at 06-MAY-08
Starting implicit crosscheck backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 06-MAY-08
Starting implicit crosscheck copy at 06-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-08
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654001832_420xbfr6_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
restoring datafile 00002 to /oradata2/data1/dbase1/undotbs01.dbf
restoring datafile 00003 to /oradata2/data1/dbase1/sysaux01.dbf
restoring datafile 00004 to /oradata2/data1/dbase1/users01.dbf
restoring datafile 00005 to /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
restoring datafile 00006 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
restoring datafile 00007 to /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
restoring datafile 00008 to /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19870: error reading backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T111057_420x821v_.bkp
ORA-19612: datafile 1 not restored due to missing or corrupt data
failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/data1/dbase1/system01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_05/o1_mf_nnnd0_LEVEL_0_BACKUP_41yo0jl9_.bkp tag=LEVEL_0_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 06-MAY-08
J)Recover Database:
If the current online logs contain all changes since the last backup , then you can run RECOVER DATABASE without specifying NOREDO. Otherwise you have to specify RECOVER DATABASE NOREDO.
RMAN> recover database;
Starting recover at 06-MAY-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 11 is already on disk as file /oradata2/data1/dbase1/redo01.log
archive log thread 1 sequence 12 is already on disk as file /oradata2/data1/dbase1/redo02.log
archive log thread 1 sequence 13 is already on disk as file /oradata2/data1/dbase1/redo03.log
archive log filename=/oradata2/data1/dbase1/redo01.log thread=1 sequence=11
archive log filename=/oradata2/data1/dbase1/redo02.log thread=1 sequence=12
archive log filename=/oradata2/data1/dbase1/redo03.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:12
Finished recover at 06-MAY-08
K)Open the database with RESETLOGS options.
RMAN> alter database open resetlogs;
database opened.
Related Documents:
-------------------
How to Discover or Find DBID
How to Restore Spfile from Backup
How to Restore Controlfile
How to Discover find DBID
We need DBID whenever we lost our all database files, or whenever we need to restore spfile or control file.
After some analysis on discovering DBID I got several ways to find DBID. I will try to demonstrate the procedure.
A)If the database is up: You can query V$database and get the DBID and record it in somewhere.
or,if the database is down and you have control file then you can mount the database and query from V$SATABASE.
SQL> SELECT DBID FROM V$DATABASE;
DBID
----------
2869417476
B) If you log the RMAN backup or if you preserve output of RMAN session then you can get DBID from that output.
$rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 01:25:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ARJU (DBID=2869417476)
C)If you have configured AUTOBACKUP ON,
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now we take BACKUP DATAFILE 4,
RMAN>BACKUP DATAFILE 4;
.
.
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/c-2869417476-20080506-04
The autobackup format is by default c-IIIIIIIIII-YYYYMMDD-QQ,(when it is %F) where:
IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF
I have seen that this format works when we set specifically/explicitly configure as
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
Now in the file from c-2869417476-20080506-04 we get DBID is 2869417476.
D)If you did not set Autobackup on which is by default. After many analysis I found that DBID can be seen from backup piece or any image copy that holds either SYSTEM or SYSAUX or UNDO datafiles.
Though if you backup your database as backup as compressed then with this method you will not be able to discover DBID.
If you have SYSTEM datafile or UNDO datafile either as image copy or as backup piece then you can use,
strings file_name |grep MAXVALUE, (In case of SYSTEM datafile)
strings file_name |grep MAXVALUE (In case of UNDO datafile)
to find DBID.
If you have SYSAUX datafile either as image copy or as backup piece then you can use,
strings file_name |grep DBID= to find DBID.
Examples:
-----------------
Both of these example is based on UNIX scenario.
1)
RMAN> backup datafile 1;
Starting backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata1/arju/datafiles/ARJU/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp
.
.
RMAN> exit;
Recovery Manager complete.
bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp |grep MAXVALUE,
.
.
2869417476, MAXVALUE,
So here 2869417476 is the DBID.
2)
RMAN> backup as copy datafile 2;
Starting backup at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oradata1/arju/datafiles/ARJU/undotbs01.dbf
output filename=/oradata2/arju/flash_recovery_area/ARJU/datafile/o1_mf_undotbs1_41zwjtx6_.dbf
bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/datafile/o1_mf_undotbs1_41zwjtx6_.dbf |grep MAXVALUE
2869417476, MAXVALUE
Here 2869417476 is the DBID
3)From physical data file you can also follow the same method.
From sysaux datafile,
bash-3.00$ strings /oradata1/arju/datafiles/ARJU/sysaux01.dbf |grep DBID=
connected to target database: ARJU (DBID=2869417476, not open)
Here 2869417476 is the DBID.
4)From whole database backup you can also follow same method.
RMAN> BACKUP DATABASE;
Starting backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
.
.
piece handle=/oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp
bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp |grep MAXVALUE,
.
.
2869417476, MAXVALUE,
Here 2869417476 is the DBID.
Enjoy!!!
Related Documents
How to Change Database Name and DBID?
After some analysis on discovering DBID I got several ways to find DBID. I will try to demonstrate the procedure.
A)If the database is up: You can query V$database and get the DBID and record it in somewhere.
or,if the database is down and you have control file then you can mount the database and query from V$SATABASE.
SQL> SELECT DBID FROM V$DATABASE;
DBID
----------
2869417476
B) If you log the RMAN backup or if you preserve output of RMAN session then you can get DBID from that output.
$rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 01:25:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ARJU (DBID=2869417476)
C)If you have configured AUTOBACKUP ON,
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now we take BACKUP DATAFILE 4,
RMAN>BACKUP DATAFILE 4;
.
.
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/c-2869417476-20080506-04
The autobackup format is by default c-IIIIIIIIII-YYYYMMDD-QQ,(when it is %F) where:
IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF
I have seen that this format works when we set specifically/explicitly configure as
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
Now in the file from c-2869417476-20080506-04 we get DBID is 2869417476.
D)If you did not set Autobackup on which is by default. After many analysis I found that DBID can be seen from backup piece or any image copy that holds either SYSTEM or SYSAUX or UNDO datafiles.
Though if you backup your database as backup as compressed then with this method you will not be able to discover DBID.
If you have SYSTEM datafile or UNDO datafile either as image copy or as backup piece then you can use,
strings file_name |grep MAXVALUE, (In case of SYSTEM datafile)
strings file_name |grep MAXVALUE (In case of UNDO datafile)
to find DBID.
If you have SYSAUX datafile either as image copy or as backup piece then you can use,
strings file_name |grep DBID= to find DBID.
Examples:
-----------------
Both of these example is based on UNIX scenario.
1)
RMAN> backup datafile 1;
Starting backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata1/arju/datafiles/ARJU/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp
.
.
RMAN> exit;
Recovery Manager complete.
bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp |grep MAXVALUE,
.
.
2869417476, MAXVALUE,
So here 2869417476 is the DBID.
2)
RMAN> backup as copy datafile 2;
Starting backup at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oradata1/arju/datafiles/ARJU/undotbs01.dbf
output filename=/oradata2/arju/flash_recovery_area/ARJU/datafile/o1_mf_undotbs1_41zwjtx6_.dbf
bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/datafile/o1_mf_undotbs1_41zwjtx6_.dbf |grep MAXVALUE
2869417476, MAXVALUE
Here 2869417476 is the DBID
3)From physical data file you can also follow the same method.
From sysaux datafile,
bash-3.00$ strings /oradata1/arju/datafiles/ARJU/sysaux01.dbf |grep DBID=
connected to target database: ARJU (DBID=2869417476, not open)
Here 2869417476 is the DBID.
4)From whole database backup you can also follow same method.
RMAN> BACKUP DATABASE;
Starting backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
.
.
piece handle=/oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp
bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp |grep MAXVALUE,
.
.
2869417476, MAXVALUE,
Here 2869417476 is the DBID.
Enjoy!!!
Related Documents
How to Change Database Name and DBID?
How to Restore spfile from backup
1.Connect to target Database with RMAN.
i)If you have lost your spfile while your database is running then,
RMAN>CONNECT TARGET /
ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614
2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT
3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;
If you want to restore to a pfile then use,
RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';
4)Start the instance.
RMAN>STARTUP;
Related Documents:
----------------------------
How Spfile can be Recovered
i)If you have lost your spfile while your database is running then,
RMAN>CONNECT TARGET /
ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614
2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT
3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;
If you want to restore to a pfile then use,
RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';
4)Start the instance.
RMAN>STARTUP;
Related Documents:
----------------------------
How Spfile can be Recovered
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML