According to oracle documentation you already heard/aware that during an Oracle tablespace hot backup, a script or program or command puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode. We can check the backup mode from V$BACKUP view. In case of user managed backup, backup process starts after issuing ALTER TABLESPACE tbs_name BEGIN BACKUP; or ALTER DATABASE BEGIN BACKUP; command and backup process ends by ALTER TABLESPACE tbs_name END BACKUP; or ALTER DATABASE END BACKUP; command.
Hot backup is demonstrated inside the topic http://arjudba.blogspot.com/2008/05/user-managed-hot-backup-of-oracle.html.
Although the process is very clear and well understood but there are many misconception around hot backup. The misconception start what is actually done during hot backup, is data file opens writeable during backup process? or changes are stored somewhere in the SGA, the redologs, the rollback/undo segments or some combination thereof, and then written back into the datafile when the tablespace is taken out of backup mode?
Well, around the writeable issue inside datafile there is other misconception like "During hot backup process there is generated huge amount of redo data which in fact slows down the database dramatically if the database is in archivelog mode."
Now let's know what actually happens during hot backup. The hot backup steps are,
1)The corresponding tablespace is checkpointed.
2)The checkpoint SCN marker in the datafile headers cease to increment with checkpoints.
3)Full images of changed DB blocks are written to the redologs.
Whenever you issue,
ALTER TABLESPACE tbs_name BEGIN BACKUP;
command, at that point a checkpoint is performed against the target tablespace and the datafile header is frozen, so no more updates are allowed on it (the datafile header), this is for the database to know which was the last time the tablespace had a consistent image of the data.
But during backup process, the corresponding datafiles in the tablespace allow just normal read/write operations, that is I/O activity is not frozen.
In case of redo log generation, each block will be recorded into the redo log files, the first time it the block is changed. So if a row is modified for the first time inside date block since hot backup started the complete block image is recorded in the redo log files but subsequent transactions on the block will only record the transaction just as normal.
Above three steps are required to guarantee consistency during the file is restored and recovery. By freezing the checkpoint SCN in the file headers, any subsequent recovery on that backup copy of the file will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the redolog file containing that SCN, and apply recovery starting there. Note that checkpoints to datafiles in hot backup mode are not suppressed during the backup, only the incrementing of the main checkpoint SCN flag. A "hot backup checkpoint" SCN marker in the file header continues to increment as periodic or incremental checkpoints progress normally.
By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be available in the redologs in case they are ever used for a recovery.
Now many one claims that during hot backup process there is excessive redo log generation than in normal mode. It actually depends on the amount of blocks changes during hot backup process. Because the first time a block is changed logging of full images of changed blocks in these tablespaces are recorded to the redo logs. Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. But during the hot backup process by logging full images of changed DB blocks to the redologs, Oracle eliminates the possibility of the backup containing irresolvable split blocks. To understand this reasoning, you must first understand what a split block is.
Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most windows filesystems have a default block size of 512 bytes and unix filesystems have a default blocksize 2k, while Oracle’s default block size is 8k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 2k chunks, or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time that your script is reading that block’s constituent O/S blocks, your backup copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block.
By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the redologs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved through application of full block images from the redologs.
Related Documents
Showing posts with label Recovery. Show all posts
Showing posts with label Recovery. Show all posts
Monday, August 31, 2009
Monday, October 6, 2008
How to clone database on the same host with different name
In this post I will show how we can clone database on windows system without using RMAN duplicate. You can clone database by using RMAN duplicate which is discussed on Creating a Duplicate Database on a Remote Host -Part1. You can also clone database by taking backup of source database and restore it which is discussed in Restore and Recover database to a new host
Note that cloning in this way which is shown next part in this post will not change DBID as it was in source database. In order to change DBID after clone operation you have to use nid tool which is discussed on How to Change Database Name and DBID?
Step by steps cloning operation is described below. The scenario is,
-Source database arju will be cloned as arjucl
-Datafile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.dbf will be cloned as F:\ORACLE\ARJUCL\*.dbf
-Controlfile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.ctl will be cloned as
F:\ORACLE\*.CTL
-In cloned database adump,bdump,cdump,udump will be located in F:\ORACLE\*dump
Step 01:
In source database identify the datafile location and redo logfile location.
On windows machine,
C:\Documents and Settings\Queen>set ORACLE_SID=arju
On linux based machine,
$export ORACLE_SID=arju
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 23:19:45 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
SQL> col file_name format a70
SQL> set linesize 160
SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\USERS01.DBF 4
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSAUX01.DBF 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\UNDOTBS01.DBF 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSTEM01.DBF 1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\EXAMPLE01.DBF 5
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\TEST_TBS01.DBF 6
6 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO01.LOG
Step 02:
Create a pfile from source spfile and controfile trace from source controlfile.
SQL> create pfile='f:\pfile.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'f:\controlfile.ctl';
Database altered.
On linux based system as linux file structure to save controlfile. Like instead of f:\controlfile.ctl use /oracle/controlfile.ctl
Step 03:
Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 04:
Create the required directory for the cloned database. You should do this step in previous.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Queen>mkdir f:\oracle
C:\Documents and Settings\Queen>mkdir f:\oracle\arjucl
On unix based machine adjust the location as you wish to create clone database.
Step 05:
Copy datafiles and redo log files to the location of f:\oracle\arjucl
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.dbf f:\oracle\arjucl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\TEMP01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\TEST_TBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\USERS01.DBF
7 file(s) copied.
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.log f:\oracle\arjucl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO01.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO03.LOG
3 file(s) copied.
Step 06:
At this stage task with the source database is finished. Edit the pfile f:\pfile.ora and change parameter control_files, db_name, different dump directory in order to reflect cloned database name and location.
On windows,
C:\Documents and Settings\Queen>notepad f:\pfile.ora
arjucl.__db_cache_size=25165824
arjucl.__java_pool_size=4194304
arjucl.__large_pool_size=4194304
arjucl.__shared_pool_size=62914560
arjucl.__streams_pool_size=0
*.audit_file_dest='F:\oracle\adump'
*.audit_trail='DB','EXTENDED'
*.background_dump_dest='F:\oracle\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\control01.ctl',
'F:\oracle\control02.ctl','F:\oracle\control03.ctl'
*.core_dump_dest='F:\oracle\cdump'
*.db_block_size=8192
*.db_create_file_dest='C:\'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='arjucl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='c:\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=arjuclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=c:\test'
*.log_archive_dest_2='LOCATION=g:\'
*.log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=202375168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\udump'
On linux based system use as, $vi pfile.ora
Step 07:
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE database_name in the controlfile creation script.
On windows,
C:\Documents and Settings\Queen>notepad f:\CONTROLFILE.CTL
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "arjucl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\ARJUCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\ARJUCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\ARJUCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\ARJUCL\SYSTEM01.DBF',
'F:\ORACLE\ARJUCL\UNDOTBS01.DBF',
'F:\ORACLE\ARJUCL\SYSAUX01.DBF',
'F:\ORACLE\ARJUCL\USERS01.DBF',
'F:\ORACLE\ARJUCL\EXAMPLE01.DBF',
'F:\ORACLE\ARJUCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
On unix based system open the controlfile with any editor software and then edit.
Step 08:Avoid this step if you are on unix based system. On windows, with oradim create new oracle service arjucl. However if you are on unix machine then simply ignore this step.
C:\Documents and Settings\Queen>oradim -new -sid arjucl
Instance created.
Step 09:
Set environmental variable to arjucl and connect to instance as sysdba.
C:\Documents and Settings\Queen>set ORACLE_SID=arjucl
On unix based system, export ORACLE_SID=arjucl
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 00:04:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step 10:
Run the controlfile creation script.
On windows,
SQL> @f:\controlfile.ctl
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1247516 bytes
Variable Size 71304932 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Control file created.
On unix based system run the modified controlfile script where you saved.
Step 11:
Open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Check the cloned database name.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string arjucl
You can check the cloned database datafile location.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
F:\ORACLE\ARJUCL\TEST_TBS01.DBF
F:\ORACLE\ARJUCL\EXAMPLE01.DBF
F:\ORACLE\ARJUCL\USERS01.DBF
F:\ORACLE\ARJUCL\SYSAUX01.DBF
F:\ORACLE\ARJUCL\UNDOTBS01.DBF
F:\ORACLE\ARJUCL\SYSTEM01.DBF
6 rows selected.
Note that cloning in this way which is shown next part in this post will not change DBID as it was in source database. In order to change DBID after clone operation you have to use nid tool which is discussed on How to Change Database Name and DBID?
Step by steps cloning operation is described below. The scenario is,
-Source database arju will be cloned as arjucl
-Datafile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.dbf will be cloned as F:\ORACLE\ARJUCL\*.dbf
-Controlfile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.ctl will be cloned as
F:\ORACLE\*.CTL
-In cloned database adump,bdump,cdump,udump will be located in F:\ORACLE\*dump
Step 01:
In source database identify the datafile location and redo logfile location.
On windows machine,
C:\Documents and Settings\Queen>set ORACLE_SID=arju
On linux based machine,
$export ORACLE_SID=arju
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 23:19:45 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
SQL> col file_name format a70
SQL> set linesize 160
SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\USERS01.DBF 4
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSAUX01.DBF 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\UNDOTBS01.DBF 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSTEM01.DBF 1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\EXAMPLE01.DBF 5
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\TEST_TBS01.DBF 6
6 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO01.LOG
Step 02:
Create a pfile from source spfile and controfile trace from source controlfile.
SQL> create pfile='f:\pfile.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'f:\controlfile.ctl';
Database altered.
On linux based system as linux file structure to save controlfile. Like instead of f:\controlfile.ctl use /oracle/controlfile.ctl
Step 03:
Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 04:
Create the required directory for the cloned database. You should do this step in previous.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Queen>mkdir f:\oracle
C:\Documents and Settings\Queen>mkdir f:\oracle\arjucl
On unix based machine adjust the location as you wish to create clone database.
Step 05:
Copy datafiles and redo log files to the location of f:\oracle\arjucl
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.dbf f:\oracle\arjucl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\TEMP01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\TEST_TBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\USERS01.DBF
7 file(s) copied.
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\*.log f:\oracle\arjucl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO01.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\REDO03.LOG
3 file(s) copied.
Step 06:
At this stage task with the source database is finished. Edit the pfile f:\pfile.ora and change parameter control_files, db_name, different dump directory in order to reflect cloned database name and location.
On windows,
C:\Documents and Settings\Queen>notepad f:\pfile.ora
arjucl.__db_cache_size=25165824
arjucl.__java_pool_size=4194304
arjucl.__large_pool_size=4194304
arjucl.__shared_pool_size=62914560
arjucl.__streams_pool_size=0
*.audit_file_dest='F:\oracle\adump'
*.audit_trail='DB','EXTENDED'
*.background_dump_dest='F:\oracle\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\control01.ctl',
'F:\oracle\control02.ctl','F:\oracle\control03.ctl'
*.core_dump_dest='F:\oracle\cdump'
*.db_block_size=8192
*.db_create_file_dest='C:\'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='arjucl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='c:\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=arjuclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=c:\test'
*.log_archive_dest_2='LOCATION=g:\'
*.log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=202375168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\udump'
On linux based system use as, $vi pfile.ora
Step 07:
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE database_name in the controlfile creation script.
On windows,
C:\Documents and Settings\Queen>notepad f:\CONTROLFILE.CTL
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "arjucl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\ARJUCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\ARJUCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\ARJUCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\ARJUCL\SYSTEM01.DBF',
'F:\ORACLE\ARJUCL\UNDOTBS01.DBF',
'F:\ORACLE\ARJUCL\SYSAUX01.DBF',
'F:\ORACLE\ARJUCL\USERS01.DBF',
'F:\ORACLE\ARJUCL\EXAMPLE01.DBF',
'F:\ORACLE\ARJUCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
On unix based system open the controlfile with any editor software and then edit.
Step 08:Avoid this step if you are on unix based system. On windows, with oradim create new oracle service arjucl. However if you are on unix machine then simply ignore this step.
C:\Documents and Settings\Queen>oradim -new -sid arjucl
Instance created.
Step 09:
Set environmental variable to arjucl and connect to instance as sysdba.
C:\Documents and Settings\Queen>set ORACLE_SID=arjucl
On unix based system, export ORACLE_SID=arjucl
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 00:04:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step 10:
Run the controlfile creation script.
On windows,
SQL> @f:\controlfile.ctl
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1247516 bytes
Variable Size 71304932 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Control file created.
On unix based system run the modified controlfile script where you saved.
Step 11:
Open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Check the cloned database name.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string arjucl
You can check the cloned database datafile location.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
F:\ORACLE\ARJUCL\TEST_TBS01.DBF
F:\ORACLE\ARJUCL\EXAMPLE01.DBF
F:\ORACLE\ARJUCL\USERS01.DBF
F:\ORACLE\ARJUCL\SYSAUX01.DBF
F:\ORACLE\ARJUCL\UNDOTBS01.DBF
F:\ORACLE\ARJUCL\SYSTEM01.DBF
6 rows selected.
Wednesday, September 10, 2008
How to recover or recreate temporary tablespace in 10g
In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.
Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.
In order to do that follow the steps here.
1)Find out the temporary datafiles.
2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.
3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp01 tempfile '/oradata2/temp.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
3)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also.
4)Explicitly assign temporary tablespace for users TEST2 and ARJU.
SQL> alter user arju temporary tablespace temp01;
User altered.
SQL> alter user test2 temporary tablespace temp01;
User altered.
3)Drop the old temporary tablespace.
SQL> drop tablespace temp;
Tablespace dropped.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> drop tablespace temp3;
Tablespace dropped.
Related Documents
Drop Temporary Tablespace Hangs
ORA-12906 cannot drop default temporary tablespace
Free space in Temporary Tablespace
Implicitly Assigned temp tablespace changes after database default tablespace change
Information about Temporary Segments.
The operation that require sort area or Temporary Tablespace
Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.
In order to do that follow the steps here.
1)Find out the temporary datafiles.
SQL> col file_name format a50
SQL> set linesize 200
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/oradata2/temp2.dbf 1 TEMP2
/oradata2/temp.dbf 2 TEMP
/oradata2/temp3.dbf 4 TEMP3
2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.
3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp01 tempfile '/oradata2/temp.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
3)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also.
SQL> select temporary_tablespace , username from dba_users where temporary_tablespace<>'TEMP01';
TEMPORARY_TABLESPACE USERNAME
------------------------------ ------------------------------
TEMP TEST2
TEMP2 ARJU
4)Explicitly assign temporary tablespace for users TEST2 and ARJU.
SQL> alter user arju temporary tablespace temp01;
User altered.
SQL> alter user test2 temporary tablespace temp01;
User altered.
3)Drop the old temporary tablespace.
SQL> drop tablespace temp;
Tablespace dropped.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> drop tablespace temp3;
Tablespace dropped.
Related Documents
Drop Temporary Tablespace Hangs
ORA-12906 cannot drop default temporary tablespace
Free space in Temporary Tablespace
Implicitly Assigned temp tablespace changes after database default tablespace change
Information about Temporary Segments.
The operation that require sort area or Temporary Tablespace
Sunday, September 7, 2008
Restore operation fails with RMAN-11003 ORA-01511 ORA-01516
Error Description And Symptoms
I am performing disaster recovery operation. The new host path are different than from original host. So for the datafile I used SET NEWNAME .. clause. And for the online redo log file I used SQL "ALTER DATABASE RENAME FILE .. " clause to rename the online logfiles to a valid location on to a new host. I have already ran the restore srcipt in previous but it fails to rename third online redo logfiles due to invalid location. And after fixing location whenever I ran the script again it fails with serveral RMAN and Oracle error as below.
RMAN> @/backup03/webkey/rman_script
In previous ran of this script it could rename successfully of the first two online redo logfile and it is recorded on the controlfile but the restore operation fails. Now subsequent run of the script will fail because no log file actually exist in the controlfile as they already renamed.
In previous run m,y online redo logfile is rename from /d00/oradata/db101db/redo01a.log to /backup03/webkey/redo01a.log. In the next run(in this example) it again try to search for online redo log in the location /d00/oradata/db101db/redo01a.log inside the controlfile but did not find any as they already modified. So error appear of nonexistent log file, datafile, or tempfile.
Solution of The Problem
While make database in mount state log on to SQL*Plus and query the member column of v$logfile and see the location of the member. Suppose only 1 member is modified then in the SQL*Plus you can issue,
SQL>ALTER DATABASE RENAME FILE '/backup03/webkey/redo01a.log' TO '/d00/oradata/db101db/redo01a.log';
to rename reflected new location to old one and thus you can re-run the script without modifying any line.
If you notice that are redolog files are renamed successfully to the new location then you can simply omit/comment the lines of rename redo log command. Like in below.
tdev> vi /backup03/webkey/rman_script
run{
set newname for datafile 1 to '/backup03/webkey/system01.dbf';
set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf';
set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf';
set newname for datafile 4 to '/backup03/webkey/users01.dbf';
set newname for datafile 5 to '/backup03/webkey/data01.dbf';
set newname for datafile 6 to '/backup03/webkey/data02.dbf';
set newname for datafile 7 to '/backup03/webkey/indx01.dbf';
set newname for datafile 8 to '/backup03/webkey/indx02.dbf';
set newname for datafile 9 to '/backup03/webkey/tools01.dbf';
set newname for datafile 10 to '/backup03/webkey/splex_data.dbf';
set newname for datafile 11 to '/backup03/webkey/splex_indx.dbf';
set newname for datafile 12 to '/backup03/webkey/columnbia.dbf';
set newname for datafile 13 to '/backup03/webkey/data03.dbf';
set newname for datafile 14 to '/backup03/webkey/data03b.dbf';
set newname for datafile 15 to '/backup03/webkey/data_01_16K_01.dbf';
set newname for datafile 16 to '/backup03/webkey/indx_01_16K_01.dbf';
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log''
# TO ''/backup03/webkey/redo01a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo01b.log''
# TO ''/backup03/webkey/redo01b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo01c.log''
# TO ''/backup03/webkey/redo01c.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo02a.log''
# TO ''/backup03/webkey/redo02a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo02b.log''
# TO ''/backup03/webkey/redo02b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo02c.log''
# TO ''/backup03/webkey/redo02c.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo03a.log''
# TO ''/backup03/webkey/redo03a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo03b.log''
# TO ''/backup03/webkey/redo03b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo03c.log''
# TO ''/backup03/webkey/redo03c.log'' ";
SET UNTIL TIME '06/06/2008 15:15:00';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
"/backup03/webkey/rman_script" 41 lines, 2179 characters
And then run the script again.
I am performing disaster recovery operation. The new host path are different than from original host. So for the datafile I used SET NEWNAME .. clause. And for the online redo log file I used SQL "ALTER DATABASE RENAME FILE .. " clause to rename the online logfiles to a valid location on to a new host. I have already ran the restore srcipt in previous but it fails to rename third online redo logfiles due to invalid location. And after fixing location whenever I ran the script again it fails with serveral RMAN and Oracle error as below.
RMAN> @/backup03/webkey/rman_script
RMAN> run{
2> set newname for datafile 1 to '/backup03/webkey/system01.dbf';
3> set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf';
4> set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf';
5> set newname for datafile 4 to '/backup03/webkey/users01.dbf';
6> set newname for datafile 5 to '/backup03/webkey/data01.dbf';
7> set newname for datafile 6 to '/backup03/webkey/data02.dbf';
8> set newname for datafile 7 to '/backup03/webkey/indx01.dbf';
9> set newname for datafile 8 to '/backup03/webkey/indx02.dbf';
10> set newname for datafile 9 to '/backup03/webkey/tools01.dbf';
11> set newname for datafile 10 to '/backup03/webkey/splex_data.dbf';
12> set newname for datafile 11 to '/backup03/webkey/splex_indx.dbf';
13> set newname for datafile 12 to '/backup03/webkey/columnbia.dbf';
14> set newname for datafile 13 to '/backup03/webkey/data03.dbf';
15> set newname for datafile 14 to '/backup03/webkey/data03b.dbf';
16> set newname for datafile 15 to '/backup03/webkey/data_01_16K_01.dbf';
17> set newname for datafile 16 to '/backup03/webkey/indx_01_16K_01.dbf';
18> SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log''
19> TO ''/backup03/webkey/redo01a.log'' ";
20> SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo01b.log''
21> TO ''/backup03/webkey/redo01b.log'' ";
22> SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo01c.log''
23> TO ''/backup03/webkey/redo01c.log'' ";
24> SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo02a.log''
25> TO ''/backup03/webkey/redo02a.log'' ";
26> SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo02b.log''
27> TO ''/backup03/webkey/redo02b.log'' ";
28> SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo02c.log''
29> TO ''/backup03/webkey/redo02c.log'' ";
30> SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo03a.log''
31> TO ''/backup03/webkey/redo03a.log'' ";
32> SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo03b.log''
33> TO ''/backup03/webkey/redo03b.log'' ";
34> SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo03c.log''
35> TO ''/backup03/webkey/redo03c.log'' ";
36> SET UNTIL TIME '06-JUN-08 15:15:00';
37> RESTORE DATABASE;
38> SWITCH DATAFILE ALL;
39> RECOVER DATABASE;
40> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
sql statement: ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log'' TO ''/backup03/webkey/redo01a.log''
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 09/08/2008 02:30:42
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE RENAME FILE '/d00/oradata/db101db/redo01a.log' TO '/backup03/webkey/redo01a.log'
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "/d00/oradata/db101db/redo01a.log"
In previous ran of this script it could rename successfully of the first two online redo logfile and it is recorded on the controlfile but the restore operation fails. Now subsequent run of the script will fail because no log file actually exist in the controlfile as they already renamed.
In previous run m,y online redo logfile is rename from /d00/oradata/db101db/redo01a.log to /backup03/webkey/redo01a.log. In the next run(in this example) it again try to search for online redo log in the location /d00/oradata/db101db/redo01a.log inside the controlfile but did not find any as they already modified. So error appear of nonexistent log file, datafile, or tempfile.
Solution of The Problem
While make database in mount state log on to SQL*Plus and query the member column of v$logfile and see the location of the member. Suppose only 1 member is modified then in the SQL*Plus you can issue,
SQL>ALTER DATABASE RENAME FILE '/backup03/webkey/redo01a.log' TO '/d00/oradata/db101db/redo01a.log';
to rename reflected new location to old one and thus you can re-run the script without modifying any line.
If you notice that are redolog files are renamed successfully to the new location then you can simply omit/comment the lines of rename redo log command. Like in below.
tdev> vi /backup03/webkey/rman_script
run{
set newname for datafile 1 to '/backup03/webkey/system01.dbf';
set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf';
set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf';
set newname for datafile 4 to '/backup03/webkey/users01.dbf';
set newname for datafile 5 to '/backup03/webkey/data01.dbf';
set newname for datafile 6 to '/backup03/webkey/data02.dbf';
set newname for datafile 7 to '/backup03/webkey/indx01.dbf';
set newname for datafile 8 to '/backup03/webkey/indx02.dbf';
set newname for datafile 9 to '/backup03/webkey/tools01.dbf';
set newname for datafile 10 to '/backup03/webkey/splex_data.dbf';
set newname for datafile 11 to '/backup03/webkey/splex_indx.dbf';
set newname for datafile 12 to '/backup03/webkey/columnbia.dbf';
set newname for datafile 13 to '/backup03/webkey/data03.dbf';
set newname for datafile 14 to '/backup03/webkey/data03b.dbf';
set newname for datafile 15 to '/backup03/webkey/data_01_16K_01.dbf';
set newname for datafile 16 to '/backup03/webkey/indx_01_16K_01.dbf';
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo01a.log''
# TO ''/backup03/webkey/redo01a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo01b.log''
# TO ''/backup03/webkey/redo01b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo01c.log''
# TO ''/backup03/webkey/redo01c.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo02a.log''
# TO ''/backup03/webkey/redo02a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo02b.log''
# TO ''/backup03/webkey/redo02b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo02c.log''
# TO ''/backup03/webkey/redo02c.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d00/oradata/db101db/redo03a.log''
# TO ''/backup03/webkey/redo03a.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d01/oradata/db101db/redo03b.log''
# TO ''/backup03/webkey/redo03b.log'' ";
# SQL "ALTER DATABASE RENAME FILE ''/d02/oradata/db101db/redo03c.log''
# TO ''/backup03/webkey/redo03c.log'' ";
SET UNTIL TIME '06/06/2008 15:15:00';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
"/backup03/webkey/rman_script" 41 lines, 2179 characters
And then run the script again.
Tuesday, September 2, 2008
Recover database Issue after missing spfile or pfile
In order to discover pfile or spfile I have discussed in The different ways to discover initialization parameter (spfile or pfile)
Now you may have the scenario that you yet not discovered your spfile or pfile then the remaining option is to create a new pfile. Suppose that you create your spfile like this.
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
sga_target=400M
That is without control_files parameter inside it. It is ok if you don't have any controlfile and you want to create a new one. If you don't give any location inside pfile then oracle will create controlfile in the location of $ORACLE_HOME/dbs/cntrlarjudba.dbf.
However if you specify control_files parameter inside pfile then oracle will attempt to create controlfile in the specified location. Like if you create pfile as
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
sga_target=400M
then create controlfile statement will create controlfile in the location of /oradata2/arjudba/arjudba/control01.ctl, and /oradata2/arjudba/arjudba/control02.ctl
If you don't give any location of control_file parameter inside pfile then further startup will fails with ORA-00205
Like,
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 83889896 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
Now you may have the scenario that you yet not discovered your spfile or pfile then the remaining option is to create a new pfile. Suppose that you create your spfile like this.
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
sga_target=400M
That is without control_files parameter inside it. It is ok if you don't have any controlfile and you want to create a new one. If you don't give any location inside pfile then oracle will create controlfile in the location of $ORACLE_HOME/dbs/cntrlarjudba.dbf.
However if you specify control_files parameter inside pfile then oracle will attempt to create controlfile in the specified location. Like if you create pfile as
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
sga_target=400M
then create controlfile statement will create controlfile in the location of /oradata2/arjudba/arjudba/control01.ctl, and /oradata2/arjudba/arjudba/control02.ctl
If you don't give any location of control_file parameter inside pfile then further startup will fails with ORA-00205
Like,
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 83889896 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
Sunday, August 31, 2008
Recover database after missing online redo logs and all controlfiles.
This example is based on,
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.
1.
Let's start by deleting online redo log files and controlfile of my running database.
SQL> select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/redo02.log
/oradata2/arjudba/arjudba/redo01.log
/oradata2/arjudba/arjudba/redo03.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
SQL> !rm /oradata2/arjudba/arjudba/redo0*
3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.
6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=arjudba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel
9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means
1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.
This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.
Related Documents
Recover database after only lose of all controlfiles
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.
1.
Let's start by deleting online redo log files and controlfile of my running database.
SQL> select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/redo02.log
/oradata2/arjudba/arjudba/redo01.log
/oradata2/arjudba/arjudba/redo03.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
SQL> !rm /oradata2/arjudba/arjudba/redo0*
3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.
6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=arjudba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel
9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means
1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.
This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.
Related Documents
Recover database after only lose of all controlfiles
Recover database after only lose of all controlfiles
This example is based on,
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfile. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below. Note that you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfile. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below. Note that you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode
Wednesday, August 27, 2008
How to restore an Spfile from autobackup older than 7 days
Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example,
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.
However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.
For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;
In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.
However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.
For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;
In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.
Thursday, May 22, 2008
Performing Trial Recovery in Database
Whenever there is block corruption during recovery then you have to choice whether you will corrupt the block or will open the database with RESETLOGS options.
The general rule is if the block is unimportant and if the problem is isolated then corrupt the block. But if the problem is not isolated then open the database with RESETLOGS option.
Oracle database supports trial recovery in order to identify corrupt blocks. In case of trial recovery the database writes errors generated during trial recovery to alert files and marks the data block as corrupt in memory and thus allow recovery to proceed.
When trial recovery ends, the database removes all effects of the test run from the system—except the possible error messages in the alert files. If the instance fails during trial recovery, then the database removes all effects of trial recovery from the system because trial recovery never writes changes to disk.
You can perform trial recovery by, RECOVER ... TEST option.
Example:
--------------
RECOVER DATABASE TEST
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST
RECOVER TABLESPACE sysaux TEST
RECOVER DATABASE UNTIL CANCEL TEST
Related Documents:
-------------------------------
Block Media Recovery with RMAN
Extract data from corrupt block
Verify Physical data Structure
Allow recovery to corrupt blocks
The general rule is if the block is unimportant and if the problem is isolated then corrupt the block. But if the problem is not isolated then open the database with RESETLOGS option.
Oracle database supports trial recovery in order to identify corrupt blocks. In case of trial recovery the database writes errors generated during trial recovery to alert files and marks the data block as corrupt in memory and thus allow recovery to proceed.
When trial recovery ends, the database removes all effects of the test run from the system—except the possible error messages in the alert files. If the instance fails during trial recovery, then the database removes all effects of trial recovery from the system because trial recovery never writes changes to disk.
You can perform trial recovery by, RECOVER ... TEST option.
Example:
--------------
RECOVER DATABASE TEST
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST
RECOVER TABLESPACE sysaux TEST
RECOVER DATABASE UNTIL CANCEL TEST
Related Documents:
-------------------------------
Block Media Recovery with RMAN
Extract data from corrupt block
Verify Physical data Structure
Allow recovery to corrupt blocks
Tuesday, May 20, 2008
Recovering a Database in NOARCHIVELOG Mode
If a media failure damages datafiles in a NOARCHIVELOG database, then the only option for recovery is usually to restore a consistent whole database backup. As you are in noarchivelog mode so you have to understand that changes after taken backup is lost.
If you logical backup that is export file you can import that also.
In order to recover database in noarchivelog mode you have to follow the following procedure.
1)If the database is open shutdown it.
SQL>SHUTDOWN IMMEDIATE;
2)If possible, correct the media problem so that the backup database files can be restored to their original locations.
3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.
4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;
In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/new/1.ctl','/new/2.ctl' scope=spfile;
STARTUP FORCE MOUNT;
In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,
ALTER DATABASE RENAME FILE '/old/system01.dbf' TO '/new_disk/system01.dbf';
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
If you logical backup that is export file you can import that also.
In order to recover database in noarchivelog mode you have to follow the following procedure.
1)If the database is open shutdown it.
SQL>SHUTDOWN IMMEDIATE;
2)If possible, correct the media problem so that the backup database files can be restored to their original locations.
3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.
4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;
In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/new/1.ctl','/new/2.ctl' scope=spfile;
STARTUP FORCE MOUNT;
In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,
ALTER DATABASE RENAME FILE '/old/system01.dbf' TO '/new_disk/system01.dbf';
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Monday, May 19, 2008
Automatic Recovery During Applying logs
With the command RECOVER(which is SQL*Plus command) or ALTER DATABASE RECOVER you can perform media recovery. It is always good to use SQL*plus while recovery of database because with it you can perform automatic recovery. Whenever we use RECOVER command it displays a prompt in order to supply archived redo log. But if they logs are in a location in the disk then you can perform automatic recovery where it will not prompt to provide any location.
There are two ways to perform automatic recovery.
1)Issuing SET AUTORECOVERY ON before issuing the RECOVER command.
SQL>SET AUTORECOVERY ON;
SQL>RECOVER DATABASE;
2)Specifying the AUTOMATIC keyword as an option of the RECOVER command.
SQL>RECOVER AUTOMATIC DATABASE;
Recovering When Archived Logs Are in the Default Location
-------------------------------------------------------------------
Whenever you issue RECOVER command then it will prompt as,
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#
ORA-00289: Suggestion : logfile
ORA-00280: Change #### for thread # is in sequence #
Specify log: [ for suggested | AUTO | FROM logsource | CANCEL ]
If you have not changed your archived log destination and the format is in tact then if you specify AUTO it will automatically constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest
value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file.
Recovering When Archived Logs Are in a Nondefault Location
--------------------------------------------------------------------
When archived redo logs is not their default location then you have to following any options from these two.
1) Edit the LOG_ARCHIVE_DEST_n parameter that specifies the location of the archived redo logs, then recover as usual.
2) Use the SET statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE parameter of the RECOVER command.
1) Edit the LOG_ARCHIVE_DEST_n parameter
-----------------------------------
i)$ cp /backup/arch/* /tmp/
ii)LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'
iii)LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc
iv)STARTUP MOUNT
v)RECOVER DATABASE
2)SET statement in SQL*Plus(Override the arhived redo log source)
---------------------------------------
i)$ cp $ORACLE_HOME/oradata/trgt/arch/* /tmp
ii)SET LOGSOURCE "/tmp"
iii)RECOVER AUTOMATIC TABLESPACE users
you can avoid running SET LOGSOURCE and simply run:
RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"
Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.
There are two ways to perform automatic recovery.
1)Issuing SET AUTORECOVERY ON before issuing the RECOVER command.
SQL>SET AUTORECOVERY ON;
SQL>RECOVER DATABASE;
2)Specifying the AUTOMATIC keyword as an option of the RECOVER command.
SQL>RECOVER AUTOMATIC DATABASE;
Recovering When Archived Logs Are in the Default Location
-------------------------------------------------------------------
Whenever you issue RECOVER command then it will prompt as,
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#
ORA-00289: Suggestion : logfile
ORA-00280: Change #### for thread # is in sequence #
Specify log: [
If you have not changed your archived log destination and the format is in tact then if you specify AUTO it will automatically constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest
value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file.
Recovering When Archived Logs Are in a Nondefault Location
--------------------------------------------------------------------
When archived redo logs is not their default location then you have to following any options from these two.
1) Edit the LOG_ARCHIVE_DEST_n parameter that specifies the location of the archived redo logs, then recover as usual.
2) Use the SET statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE parameter of the RECOVER command.
1) Edit the LOG_ARCHIVE_DEST_n parameter
-----------------------------------
i)$ cp /backup/arch/* /tmp/
ii)LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'
iii)LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc
iv)STARTUP MOUNT
v)RECOVER DATABASE
2)SET statement in SQL*Plus(Override the arhived redo log source)
---------------------------------------
i)$ cp $ORACLE_HOME/oradata/trgt/arch/* /tmp
ii)SET LOGSOURCE "/tmp"
iii)RECOVER AUTOMATIC TABLESPACE users
you can avoid running SET LOGSOURCE and simply run:
RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"
Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.
User Managed Hot Backup and Recovery of lost of data file
Example of User Managed Hot Backup:
-----------------------------------------
1)Determine which data file you need to backup.
SQL> select file_name ,tablespace_name from dba_data_files
2 /
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata2/7.dbf USERS
/oradata2/data1/dbase/users01.dbf USERS
/oradata2/data1/dbase/sysaux01.dbf SYSAUX
/oradata2/data1/dbase/undotbs01.dbf UNDOTBS1
/oradata2/data1/dbase/system01.dbf SYSTEM
/oradata2/6.dbf USERS
6 rows selected.
2)Make the tablespace in backup mode one by one and copy the associated datafile to backup location.
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/users01.dbf /oradata2/arju
SQL> !cp /oradata2/7.dbf /oradata2/arju
SQL> ALTER TABLESPACE USERS END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju
SQL> ALTER TABLESPACE SYSAUX END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju
SQL> ALTER TABLESPACE UNDOTBS1 END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju
SQL> ALTER TABLESPACE SYSTEM END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/6.dbf /oradata2/arju
SQL> ALTER TABLESPACE USERS END BACKUP;
Tablespace altered.
3)Take a backup of your control file also.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/arju/control.ctl';
Database altered.
Now, I have taken backup.With this backup I will work for next recovery purpose. In this example I will show if someone has accidentally drop one datafile what will be my procedure.
Suppose someone accidentally two files.
SQL> !rm /oradata2/data1/dbase/users01.dbf
SQL> !rm /oradata2/6.dbf
Whenever you want to do any operation it will raise error like following,
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
User Managed Recovery of Datafile when your database is online (Archive Log Mode)
---------------------------------------------------------------------------
1)Make the affected tablespace offline.
SQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
Tablespace altered.
2)Copy the lost datafile from backup into the location of the datafile.
SQL> !cp /oradata2/arju/users01.dbf /oradata2/data1/dbase/
SQL> !cp /oradata2/arju/6.dbf /oradata2
3)Recover the tablespace.
SQL> RECOVER TABLESPACE USERS;
Media recovery complete.
4)Make the Tablespace online.
SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.
While database is offline and can't start it will show following message.
SQL> !rm /oradata2/data1/dbase/users01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'
1)In order to see which files need media recovery issue,
SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
; 2 3 4 5 6
DF# DF_NAME TBSP_NA STATUS ERROR CHANGE#
---- ----------------------------------- ------- ------- ---------- ---------
4 /oradata2/data1/dbase/users01.dbf USERS ONLINE FILE NOT 0
FOUND
2)Copy the lost file from backup to destination.
SQL> !cp /oradata2/arju/users01.dbf /oradata2/data1/dbase/users01.dbf
3)Recover the file and open the database.
SQL> RECOVER DATAFILE 4;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.
Note: If the location is damaged permamnent to make datafile in another location use,
SQL>ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' TO
'/disk2/users01.dbf';
If you do not have a backup of a specific datafile but you have archived log since the datafile creation then you can recover that datafile by creating an empty file by ALTER DATABASE CREATE DATAFILE and then simply perform recovery of the datafile.
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
-----------------------------------------
1)Determine which data file you need to backup.
SQL> select file_name ,tablespace_name from dba_data_files
2 /
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata2/7.dbf USERS
/oradata2/data1/dbase/users01.dbf USERS
/oradata2/data1/dbase/sysaux01.dbf SYSAUX
/oradata2/data1/dbase/undotbs01.dbf UNDOTBS1
/oradata2/data1/dbase/system01.dbf SYSTEM
/oradata2/6.dbf USERS
6 rows selected.
2)Make the tablespace in backup mode one by one and copy the associated datafile to backup location.
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/users01.dbf /oradata2/arju
SQL> !cp /oradata2/7.dbf /oradata2/arju
SQL> ALTER TABLESPACE USERS END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju
SQL> ALTER TABLESPACE SYSAUX END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju
SQL> ALTER TABLESPACE UNDOTBS1 END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju
SQL> ALTER TABLESPACE SYSTEM END BACKUP;
Tablespace altered.
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;
Tablespace altered.
SQL> !cp /oradata2/6.dbf /oradata2/arju
SQL> ALTER TABLESPACE USERS END BACKUP;
Tablespace altered.
3)Take a backup of your control file also.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/arju/control.ctl';
Database altered.
Now, I have taken backup.With this backup I will work for next recovery purpose. In this example I will show if someone has accidentally drop one datafile what will be my procedure.
Suppose someone accidentally two files.
SQL> !rm /oradata2/data1/dbase/users01.dbf
SQL> !rm /oradata2/6.dbf
Whenever you want to do any operation it will raise error like following,
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
User Managed Recovery of Datafile when your database is online (Archive Log Mode)
---------------------------------------------------------------------------
1)Make the affected tablespace offline.
SQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
Tablespace altered.
2)Copy the lost datafile from backup into the location of the datafile.
SQL> !cp /oradata2/arju/users01.dbf /oradata2/data1/dbase/
SQL> !cp /oradata2/arju/6.dbf /oradata2
3)Recover the tablespace.
SQL> RECOVER TABLESPACE USERS;
Media recovery complete.
4)Make the Tablespace online.
SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.
While database is offline and can't start it will show following message.
SQL> !rm /oradata2/data1/dbase/users01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'
1)In order to see which files need media recovery issue,
SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
; 2 3 4 5 6
DF# DF_NAME TBSP_NA STATUS ERROR CHANGE#
---- ----------------------------------- ------- ------- ---------- ---------
4 /oradata2/data1/dbase/users01.dbf USERS ONLINE FILE NOT 0
FOUND
2)Copy the lost file from backup to destination.
SQL> !cp /oradata2/arju/users01.dbf /oradata2/data1/dbase/users01.dbf
3)Recover the file and open the database.
SQL> RECOVER DATAFILE 4;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.
Note: If the location is damaged permamnent to make datafile in another location use,
SQL>ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' TO
'/disk2/users01.dbf';
If you do not have a backup of a specific datafile but you have archived log since the datafile creation then you can recover that datafile by creating an empty file by ALTER DATABASE CREATE DATAFILE and then simply perform recovery of the datafile.
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Saturday, May 17, 2008
Non-Critical Files in terms of Recovery
Non-critical losses are failure events that can be resolved without significantly impacting the operation of the database if performed properly. A good DBA should be able to resolve non-critical
losses quickly and leave the database in operational state throughout the recovery process. Though during the recovery process there may be performance perform but it should be operational state.
Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised. If recovery is performed properly, these files can be recovered or rebuilt in some cases with minimal impact to database operations.
Non-Critical Files in Oracle Database.
-------------------------------------------------
1)Temporary tablespaces: If temporary tablespace or its datafiles are deleted accidentally then they can be recreated easily within any database shut down.
In order to create new one issue,
SQL>create temporary tablespace temp2 tempfile '/oradata/temp01.dbf' size 100M;
Then explicitly assign users to this temporary tablespace, like
SQL>ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
In order to make database default temporary tablespace to temp2 use which will affect of all users whether they are explicitly set or not.,
An example:
i)create a Temporary Tablespace.
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 10m;
Tablespace created.
SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.
ii)Look at database default tablespace and temporary tablespace of user ARJU.
SQL> SELECT PROPERTY_VALUE,PROPERTY_NAME FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TABLESPACE%';
PROPERTY_VALUE PROPERTY_NAME
------------------------------ ------------------------------
TEMP DEFAULT_TEMP_TABLESPACE
USERS DEFAULT_PERMANENT_TABLESPACE
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
iii)Now change the database default temporary tablespace.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Database altered.
iv) You will see all schema users' tablespace is set to default.
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP2
v)However now you can change a user to point another tablespace.
SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
2)Non-current redo log files: If the status of redo log group is not current then dropping them will continue work in database.
In order to drop a redo log group query from V$LOG.
If the status is current then you can't drop it. Drop a non-current redo log group by,
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
To add new group with one memeber,
SQL> ALTER DATABASE ADD LOGFILE '/oradata2/data1/dbase/redo04.log' SIZE 5m;
To add a member in a group issue,
alter database add logfile member
'/oradata2/data1/dbase/redo01_01.log' to group 1;
To delete a member from a log group issue where status is no-current,
alter database drop logfile member
'/oradata2/data1/dbase/redo01_01.log';
3)Index tablespaces: If the tablespace containing index or index datafile is dropped then database performance might suffer. Users can continue work. However in order to recover them it is needed to rebuild the index.
4)Indexes: If any index is deleted then recover them by simply creating script and run them.
5)Read-only tablespaces: Read-only tablespaces are by nature non-critical database files. These tablespaces are static or do not have data modifications like normal tablespaces. This allows recovery to be a fairly straightforward process under most circumstances. No redo log information needs to be applied to recover read-only tablespaces if they stayed in read only mode since backup. Simply restore them from backup.
Like in RMAN,
RMAN>RESTORE TABLESPACE TBS_READ_ONLY;
or from OS,
$scp source_location location_in_control_file
losses quickly and leave the database in operational state throughout the recovery process. Though during the recovery process there may be performance perform but it should be operational state.
Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised. If recovery is performed properly, these files can be recovered or rebuilt in some cases with minimal impact to database operations.
Non-Critical Files in Oracle Database.
-------------------------------------------------
1)Temporary tablespaces: If temporary tablespace or its datafiles are deleted accidentally then they can be recreated easily within any database shut down.
In order to create new one issue,
SQL>create temporary tablespace temp2 tempfile '/oradata/temp01.dbf' size 100M;
Then explicitly assign users to this temporary tablespace, like
SQL>ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
In order to make database default temporary tablespace to temp2 use which will affect of all users whether they are explicitly set or not.,
An example:
i)create a Temporary Tablespace.
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 10m;
Tablespace created.
SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.
ii)Look at database default tablespace and temporary tablespace of user ARJU.
SQL> SELECT PROPERTY_VALUE,PROPERTY_NAME FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TABLESPACE%';
PROPERTY_VALUE PROPERTY_NAME
------------------------------ ------------------------------
TEMP DEFAULT_TEMP_TABLESPACE
USERS DEFAULT_PERMANENT_TABLESPACE
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
iii)Now change the database default temporary tablespace.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Database altered.
iv) You will see all schema users' tablespace is set to default.
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP2
v)However now you can change a user to point another tablespace.
SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
2)Non-current redo log files: If the status of redo log group is not current then dropping them will continue work in database.
In order to drop a redo log group query from V$LOG.
If the status is current then you can't drop it. Drop a non-current redo log group by,
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
To add new group with one memeber,
SQL> ALTER DATABASE ADD LOGFILE '/oradata2/data1/dbase/redo04.log' SIZE 5m;
To add a member in a group issue,
alter database add logfile member
'/oradata2/data1/dbase/redo01_01.log' to group 1;
To delete a member from a log group issue where status is no-current,
alter database drop logfile member
'/oradata2/data1/dbase/redo01_01.log';
3)Index tablespaces: If the tablespace containing index or index datafile is dropped then database performance might suffer. Users can continue work. However in order to recover them it is needed to rebuild the index.
4)Indexes: If any index is deleted then recover them by simply creating script and run them.
5)Read-only tablespaces: Read-only tablespaces are by nature non-critical database files. These tablespaces are static or do not have data modifications like normal tablespaces. This allows recovery to be a fairly straightforward process under most circumstances. No redo log information needs to be applied to recover read-only tablespaces if they stayed in read only mode since backup. Simply restore them from backup.
Like in RMAN,
RMAN>RESTORE TABLESPACE TBS_READ_ONLY;
or from OS,
$scp source_location location_in_control_file
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Problem Description:
---------------------------
Whenever you try to restore a datafile or restore database it failed with following message,
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 17 06:06:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
RMAN> RUN{
2> SET UNTIL SCN 1364008;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> }
.
.
.
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Cause of The problem:
---------------------------
If you try to Restore database then your target database must be in mounted stage but not open. If the database is in open state then whenever you give restore then restore operation is attempting to overwrite the currently active version of the datafile and hence the error comes. Alternatively , whenever a datafile is online and you attempt to restore that datafile the error will come.
Solution of The problem:
------------------------------
Check the status of database or the datafile which you want to restore.
In case of database,
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
--------
OPEN
In case of Datafile,
SQL> select FILE# , STATUS from V$DATAFILE;
If you want to restore database then mount the database like,
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
If you want to restore a datafile make the datafile offline Like,
SQL>ALTER DATABASE DATAFILE 4 OFFLINE;
And then perform
RMAN>RESTORE DATAFILE 4;
---------------------------
Whenever you try to restore a datafile or restore database it failed with following message,
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 17 06:06:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
RMAN> RUN{
2> SET UNTIL SCN 1364008;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> }
.
.
.
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Cause of The problem:
---------------------------
If you try to Restore database then your target database must be in mounted stage but not open. If the database is in open state then whenever you give restore then restore operation is attempting to overwrite the currently active version of the datafile and hence the error comes. Alternatively , whenever a datafile is online and you attempt to restore that datafile the error will come.
Solution of The problem:
------------------------------
Check the status of database or the datafile which you want to restore.
In case of database,
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
--------
OPEN
In case of Datafile,
SQL> select FILE# , STATUS from V$DATAFILE;
If you want to restore database then mount the database like,
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
If you want to restore a datafile make the datafile offline Like,
SQL>ALTER DATABASE DATAFILE 4 OFFLINE;
And then perform
RMAN>RESTORE DATAFILE 4;
Wednesday, May 14, 2008
How to perform Database Point in time Recovery DBPITR
About Database Point in time recovery
----------------------------------------------
Database point-in-time recovery is helpful whenever we want to back the whole database to an earlier time. With RMAN you can give a specified target time and RMAN restores the database from backups prior to that time, and then applies archived redo log or incremental backups to perform media recovery to recreate all changes between the time of the datafile backups and the target time.
Disadvantages of Database Point in time Recovery
------------------------------------------------------
1)Unlike TSPITR you can't get back a set of objects to their past state instead you have to back to an earlier with of the entire database.
2)The entire database will be unavailable during the operation.
3)It is time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles.
Requirements of Database Point in time Recovery
--------------------------------------------------------------
1)Your database must be in archivelog mode.
2)You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs or incremental backups for the period between the SCN of the backups and the target SCN.
Database Point-in-Time Recovery Within the Current Incarnation
----------------------------------------------------------------------
If you want to perform database point in time recovery within current incarnation then you don't have to perform extra work as RMAN by default search for backups within current incarnation. Only you need SET UNTIL clause and then RESTORE and RECOVER. However you can get back your database to an ancestor incarnation .In that case before performing operation set incarnation. Like RESET DATABASE INCARNATION TO 1. In order to do so as well as to know about incarnation please have a look About Database Incarnations.
In the following steps I demonstrate an example of how we can perform DBPITR.
1)Create a Table. Just an an extra work. I just created it and want to perform DBPITR before the time of table creation in order to show that this table would not found after DBPITR.
SQL> CONN ARJU/A
Connected.
SQL> CREATE TABLE BEFORE_PITR TABLESPACE USERS AS SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <100;
Table created.
2)Shutdown the database.
SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
3)Connect to rman and Perform DBPITR. Here I wanted to get back of database to 30 minutes ago from current date. So I used SYSDATE-1/24/60*30.
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 14 22:31:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> RUN{
2> RESTORE DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
3> RECOVER DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
4> }
Starting restore at 14-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
.
.
media recovery complete, elapsed time: 00:00:27
Finished recover at 14-MAY-08
4)Open the database with RESETLOGS option.
RMAN> SQL'ALTER DATABASE OPEN RESETLOGS';
using target database control file instead of recovery catalog
sql statement: ALTER DATABASE OPEN RESETLOGS
5)Check the objects under Arju Schema.
SQL> conn arju/a
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TesT
MY_TABLE
And see that BEFORE_PITR is lost.
In stead of giving 'SYSDATE-1/24/60*30' you can also use time expressions,SCN restore points,SCN or log sequence numbers with SET UNTIL clause.
Like,
RMAN>RUN{
#SET UNTIL TIME 'Nov 12 2007 06:00:00'; --Set NLS_DATE_FORMAT setting.
#SET UNTIL SEQUENCE 9923;
#SET UNTIL RESTORE POINT before_update; --The restore point you created early.
SET UNTIL SCN 123456;
RESTORE DATABASE;
RECOVER DATABASE;
}
----------------------------------------------
Database point-in-time recovery is helpful whenever we want to back the whole database to an earlier time. With RMAN you can give a specified target time and RMAN restores the database from backups prior to that time, and then applies archived redo log or incremental backups to perform media recovery to recreate all changes between the time of the datafile backups and the target time.
Disadvantages of Database Point in time Recovery
------------------------------------------------------
1)Unlike TSPITR you can't get back a set of objects to their past state instead you have to back to an earlier with of the entire database.
2)The entire database will be unavailable during the operation.
3)It is time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles.
Requirements of Database Point in time Recovery
--------------------------------------------------------------
1)Your database must be in archivelog mode.
2)You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs or incremental backups for the period between the SCN of the backups and the target SCN.
Database Point-in-Time Recovery Within the Current Incarnation
----------------------------------------------------------------------
If you want to perform database point in time recovery within current incarnation then you don't have to perform extra work as RMAN by default search for backups within current incarnation. Only you need SET UNTIL clause and then RESTORE and RECOVER. However you can get back your database to an ancestor incarnation .In that case before performing operation set incarnation. Like RESET DATABASE INCARNATION TO 1. In order to do so as well as to know about incarnation please have a look About Database Incarnations.
In the following steps I demonstrate an example of how we can perform DBPITR.
1)Create a Table. Just an an extra work. I just created it and want to perform DBPITR before the time of table creation in order to show that this table would not found after DBPITR.
SQL> CONN ARJU/A
Connected.
SQL> CREATE TABLE BEFORE_PITR TABLESPACE USERS AS SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <100;
Table created.
2)Shutdown the database.
SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
3)Connect to rman and Perform DBPITR. Here I wanted to get back of database to 30 minutes ago from current date. So I used SYSDATE-1/24/60*30.
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 14 22:31:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> RUN{
2> RESTORE DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
3> RECOVER DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
4> }
Starting restore at 14-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
.
.
media recovery complete, elapsed time: 00:00:27
Finished recover at 14-MAY-08
4)Open the database with RESETLOGS option.
RMAN> SQL'ALTER DATABASE OPEN RESETLOGS';
using target database control file instead of recovery catalog
sql statement: ALTER DATABASE OPEN RESETLOGS
5)Check the objects under Arju Schema.
SQL> conn arju/a
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TesT
MY_TABLE
And see that BEFORE_PITR is lost.
In stead of giving 'SYSDATE-1/24/60*30' you can also use time expressions,SCN restore points,SCN or log sequence numbers with SET UNTIL clause.
Like,
RMAN>RUN{
#SET UNTIL TIME 'Nov 12 2007 06:00:00'; --Set NLS_DATE_FORMAT setting.
#SET UNTIL SEQUENCE 9923;
#SET UNTIL RESTORE POINT before_update; --The restore point you created early.
SET UNTIL SCN 123456;
RESTORE DATABASE;
RECOVER DATABASE;
}
Monday, May 12, 2008
Creating a Duplicate Database on a Remote Host -Part1
In this scenario I will duplicate database on a remote host with the same directory structure as of original database. In this example original database is dbase and the hostname of the original database is neptune while the duplicate database name will be dupbase and the hostname of the duplicate database is saturn.
The following steps is needed to perform the operation.
A)Backup the original database. Here original database is dbase on host neptune.
i)SQL> host rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 13 02:50:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
ii)RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 13-MAY-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=21 stamp=654577550
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030550_42lhghq3_.bkp tag=TAG20080513T030550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08
Starting backup at 13-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/dbase/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase/sysaux01.dbf
input datafile fno=00008 name=/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase/users01.dbf
input datafile fno=00005 name=/oradata2/data.dbf
input datafile fno=00006 name=/oradata2/data1/data02.dbf
input datafile fno=00009 name=/oradata2/data_test.dbf
input datafile fno=00007 name=/oradata2/6.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_nnndf_TAG20080513T030552_42lhgk10_.bkp tag=TAG20080513T030552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_ncsnf_TAG20080513T030552_42lhjxkf_.bkp tag=TAG20080513T030552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08
Starting backup at 13-MAY-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=22 stamp=654577630
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030710_42lhjzro_.bkp tag=TAG20080513T030710 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08
B)Copy the backup set of datafile and archived redo log files to saturn as to the same path as it was in recorded in control file in terminus.
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030550_42lhghq3_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 25088 00:00
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_nnndf_TAG20080513T030552_42lhgk10_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 564 MB 01:03
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030710_42lhjzro_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 7680 00:00
-bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030409_42lhdrxt_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 90210 KB 00:10
-bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030409_42lhf17z_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 5331 KB 00:00
B)Prepare the auxiliary instance. Auxiliary instance will be created in saturn host and name of the auxiliary instance is dupbase.
i)Create pfile with parameter db_name=dupbase . If you want to rename control file set control_files parameter in pfile.
bash-3.00$ vi /oradata2/initdupbase.ora
db_name=dupbase
ii)Set the Oracle sid in saturn to dupbase.
bash-3.00$ export ORACLE_SID=dupbase
iii)Create spfile from pfile.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 03:31:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oradata2/initdupbase.ora';
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
SQL> CREATE SPFILE FROM PFILE='/oradata2/initdupbase.ora';
File created.
iv)Start the auxiliary instance with spfile.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
C)Connect to the saturn machine(auxiliary database) RMAN and issue following command.
bash-3.00$ rman target sys/a@neptune:1522/dbase AUXILIARY /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 13 04:23:45 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to auxiliary database: DUPBASE (not mounted)
RMAN> RUN{
2> SET UNTIL SEQUENCE 5 THREAD 1;
3> DUPLICATE TARGET DATABASE TO DUPBASE NOFILENAMECHECK;
4> }
.
.
.
database opened
Finished Duplicate Db at 13-MAY-08
D)now your database duplication is ok. Work and Test with that.
RMAN> exit
Recovery Manager complete.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 05:02:17 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
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> SELECT NAME FROM V$DATABASE;
NAME
---------
DUPBASE
Related Documents:
----------------------
Duplication fails with Missing log
The following steps is needed to perform the operation.
A)Backup the original database. Here original database is dbase on host neptune.
i)SQL> host rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 13 02:50:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
ii)RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 13-MAY-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=21 stamp=654577550
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030550_42lhghq3_.bkp tag=TAG20080513T030550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08
Starting backup at 13-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/dbase/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase/sysaux01.dbf
input datafile fno=00008 name=/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase/users01.dbf
input datafile fno=00005 name=/oradata2/data.dbf
input datafile fno=00006 name=/oradata2/data1/data02.dbf
input datafile fno=00009 name=/oradata2/data_test.dbf
input datafile fno=00007 name=/oradata2/6.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_nnndf_TAG20080513T030552_42lhgk10_.bkp tag=TAG20080513T030552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_ncsnf_TAG20080513T030552_42lhjxkf_.bkp tag=TAG20080513T030552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08
Starting backup at 13-MAY-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=22 stamp=654577630
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030710_42lhjzro_.bkp tag=TAG20080513T030710 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08
B)Copy the backup set of datafile and archived redo log files to saturn as to the same path as it was in recorded in control file in terminus.
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030550_42lhghq3_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 25088 00:00
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_nnndf_TAG20080513T030552_42lhgk10_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 564 MB 01:03
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030710_42lhjzro_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 7680 00:00
-bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030409_42lhdrxt_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 90210 KB 00:10
-bash-3.00$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030409_42lhf17z_.bkp oracle@saturn:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
Password:
o1_mf_annnn_TAG20080 100% |***********************************************| 5331 KB 00:00
B)Prepare the auxiliary instance. Auxiliary instance will be created in saturn host and name of the auxiliary instance is dupbase.
i)Create pfile with parameter db_name=dupbase . If you want to rename control file set control_files parameter in pfile.
bash-3.00$ vi /oradata2/initdupbase.ora
db_name=dupbase
ii)Set the Oracle sid in saturn to dupbase.
bash-3.00$ export ORACLE_SID=dupbase
iii)Create spfile from pfile.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 03:31:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oradata2/initdupbase.ora';
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
SQL> CREATE SPFILE FROM PFILE='/oradata2/initdupbase.ora';
File created.
iv)Start the auxiliary instance with spfile.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
C)Connect to the saturn machine(auxiliary database) RMAN and issue following command.
bash-3.00$ rman target sys/a@neptune:1522/dbase AUXILIARY /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 13 04:23:45 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to auxiliary database: DUPBASE (not mounted)
RMAN> RUN{
2> SET UNTIL SEQUENCE 5 THREAD 1;
3> DUPLICATE TARGET DATABASE TO DUPBASE NOFILENAMECHECK;
4> }
.
.
.
database opened
Finished Duplicate Db at 13-MAY-08
D)now your database duplication is ok. Work and Test with that.
RMAN> exit
Recovery Manager complete.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 05:02:17 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
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> SELECT NAME FROM V$DATABASE;
NAME
---------
DUPBASE
Related Documents:
----------------------
Duplication fails with Missing log
Purpose and Restriction of Recover Command in Oracle
Purpose of Recover Command:
----------------------------------------
1)Performing complete recovery of one or more restored datafiles or entire database.
2)Performing incomplete recovery of a database (DBPITR) or tablespace (TSPITR).
3)Applying incremental backups to a datafile image copy (not a restored datafile) to roll it forward in time.
Restriction of Recover Command:
-------------------------------------
•You cannot arbitrarily recover individual datafiles to different points in time. You can recover the whole database to a single point in time (DBPITR, in which case you should use SET UNTIL, followed by RESTORE DATABASE and RECOVER DATABASE) or recover wholly contained tablespaces to a point in time different from the rest of the database (TSPITR, in which case you must use RECOVER TABLESPACE... UNTIL...).
•For whole database recovery database must be in mounted stage but not open. For individual datafile or individual tablespace recovery database must be either mounted or open. If database is open state then the intended datafile or tablespace must be in offline state.
•The RECOVER DATABASE command does not recover any files that are offline normal or read-only at the point in time to which the files are being recovered. However if CHECK READONLY is specified, then RMAN checks each read-only file on disk to ensure that it is already current at the desired point in time.
•You must open the database with the RESETLOGS option after incomplete recovery or recovery with a backup control file.
•RMAN can recover through RESETLOGS operations transparently if the datafiles to be recovered are from a parent incarnation. If required, the RECOVER command can also restore and apply archived logs and incremental backups from prior incarnations.
•If, during recovery of a tablespace or database, the database encounters redo for adding a datafile, RMAN automatically creates a new datafile.
•You cannot manually allocate channels and then run RECOVER with the DEVICE TYPE option.
----------------------------------------
1)Performing complete recovery of one or more restored datafiles or entire database.
2)Performing incomplete recovery of a database (DBPITR) or tablespace (TSPITR).
3)Applying incremental backups to a datafile image copy (not a restored datafile) to roll it forward in time.
Restriction of Recover Command:
-------------------------------------
•You cannot arbitrarily recover individual datafiles to different points in time. You can recover the whole database to a single point in time (DBPITR, in which case you should use SET UNTIL, followed by RESTORE DATABASE and RECOVER DATABASE) or recover wholly contained tablespaces to a point in time different from the rest of the database (TSPITR, in which case you must use RECOVER TABLESPACE... UNTIL...).
•For whole database recovery database must be in mounted stage but not open. For individual datafile or individual tablespace recovery database must be either mounted or open. If database is open state then the intended datafile or tablespace must be in offline state.
•The RECOVER DATABASE command does not recover any files that are offline normal or read-only at the point in time to which the files are being recovered. However if CHECK READONLY is specified, then RMAN checks each read-only file on disk to ensure that it is already current at the desired point in time.
•You must open the database with the RESETLOGS option after incomplete recovery or recovery with a backup control file.
•RMAN can recover through RESETLOGS operations transparently if the datafiles to be recovered are from a parent incarnation. If required, the RECOVER command can also restore and apply archived logs and incremental backups from prior incarnations.
•If, during recovery of a tablespace or database, the database encounters redo for adding a datafile, RMAN automatically creates a new datafile.
•You cannot manually allocate channels and then run RECOVER with the DEVICE TYPE option.
Saturday, May 10, 2008
Causes and Solution of ORA-00376: ORA-01110:
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.
-----------------------
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.
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_2declaresqlstatement varchar2(512);offline_not_needed exception;pragma exception_init(offline_not_needed, -01539);beginsqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';krmicd.writeMsg(6162, sqlstatement);krmicd.execSql(sqlstatement);exceptionwhen offline_not_needed thennull;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_2declaresqlstatement varchar2(512);offline_not_needed exception;pragma exception_init(offline_not_needed, -01539);beginsqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';krmicd.writeMsg(6162, sqlstatement);krmicd.execSql(sqlstatement);exceptionwhen offline_not_needed thennull;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
Subscribe to:
Posts (Atom)