For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.
If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.saddr = u.session_addr
/
select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value
/
Friday 27 March 2009
How do I find used/free space in a TEMPORARY tablespace?
Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
How does one see the uptime for a database?
Look at the following SQL query:
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
or
SELECT to_char(logon_time,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
or
Check on operating system level when the PMON process was stated (UNIX/ LINUX only):
ps -ef grep pmon
Users still running on Oracle 7 can try one of the following queries:
column STARTED format a18 head 'STARTUP TIME'
select C.INSTANCE,
to_date(JUL.VALUE, 'J')
to_char(floor(SEC.VALUE/3600), '09' )
':'
-- substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
substr (to_char(floor(mod(SEC.VALUE/60, 60)), '09'), 2, 2)
'.'
substr (to_char(mod(SEC.VALUE, 60), '09'), 2, 2) STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC,
SYS.V_$THREAD C
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_date(JUL.VALUE, 'J')
to_char(to_date(SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_char(to_date(JUL.VALUE, 'J') + (SEC.VALUE/86400), -- Return a DATE
'DD-MON-YY HH24:MI:SS') STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
or
SELECT to_char(logon_time,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
or
Check on operating system level when the PMON process was stated (UNIX/ LINUX only):
ps -ef grep pmon
Users still running on Oracle 7 can try one of the following queries:
column STARTED format a18 head 'STARTUP TIME'
select C.INSTANCE,
to_date(JUL.VALUE, 'J')
to_char(floor(SEC.VALUE/3600), '09' )
':'
-- substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
substr (to_char(floor(mod(SEC.VALUE/60, 60)), '09'), 2, 2)
'.'
substr (to_char(mod(SEC.VALUE, 60), '09'), 2, 2) STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC,
SYS.V_$THREAD C
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_date(JUL.VALUE, 'J')
to_char(to_date(SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_char(to_date(JUL.VALUE, 'J') + (SEC.VALUE/86400), -- Return a DATE
'DD-MON-YY HH24:MI:SS') STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
How does one give developers access to trace files (required as input to tkprof)?
The "alter session set sql_trace=true" command generates trace files in USER_DUMP_DEST that can be used by developers as input to tkprof. On Unix the default file mask for these files are "rwx r-- ---".
There is an undocumented INIT.ORA parameter that will allow everyone to read (rwx r-- r--) these trace files:
_trace_files_public = true
Include this in your INIT.ORA file and bounce your database for it to take effect.
There is an undocumented INIT.ORA parameter that will allow everyone to read (rwx r-- r--) these trace files:
_trace_files_public = true
Include this in your INIT.ORA file and bounce your database for it to take effect.
Can one resize tablespaces and data files?
Add more files to tablespaces
To add more space to a tablespace, one can simply add another file to it.
Example:
ALTER TABLESPACE USERS ADD DATAFILE '/oradata/orcl/users1.dbf' SIZE 100M;
Resize datafiles
One can manually increase or decrease the size of a datafile from Oracle 7.2 using the following command:
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Extend datafiles
Also, datafiles can be allowed to automatically extend if more space is required. Look at the following commands:
CREATE TABLESPACE pcs_data_ts DATAFILE 'c:ora_appspcspcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;
ALTER DATABASE DATAFILE 1 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
To add more space to a tablespace, one can simply add another file to it.
Example:
ALTER TABLESPACE USERS ADD DATAFILE '/oradata/orcl/users1.dbf' SIZE 100M;
Resize datafiles
One can manually increase or decrease the size of a datafile from Oracle 7.2 using the following command:
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Extend datafiles
Also, datafiles can be allowed to automatically extend if more space is required. Look at the following commands:
CREATE TABLESPACE pcs_data_ts DATAFILE 'c:ora_appspcspcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;
ALTER DATABASE DATAFILE 1 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
Can one rename a tablespace?
From Oracle 10g Release 1, users can rename tablespaces.
Example:
ALTER TABLESPACE ts1 RENAME TO ts2;
However, you must adhere to the following restrictions:
Example:
ALTER TABLESPACE ts1 RENAME TO ts2;
However, you must adhere to the following restrictions:
- COMPATIBILITY must be set to at least 10.0.1
- Cannot rename SYSTEM or SYSAUX
- Cannot rename an offline tablespace
- Cannot rename a tablespace that contains offline datafiles
For older releases, use the following workaround:
- Export all of the objects from the tablespace
- Drop the tablespace including contents
- Recreate the tablespace
- Import the objects
How are extents allocated to a segment?
Oracle8 and above rounds off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.
Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. Clearly Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.
Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. Clearly Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed tablespaces. If a tablespace is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K block size. Oracle doesn't round it up to the multiple of 5 when a tablespace is locally managed.
ORA-01652: unable to extend temp segment by 640 in tablespace TEMP
First check Alert.log file of database; Error was related to RMAN backup.
Step 1: Run below command on problem target database if SQL statement is hang or come out with Temporary tablespace error then Check the Temporaray tablespace usage.
SQL> select count(*) from v$rman_status ;select count(*) from v$rman_status;
ERROR at line 1:ORA-01652: unable to extend temp segment by 640 in tablespace TEMP
Step 2: Find out which User/Users are taking the space on temporaray tablespace and which is the SQL satement.
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, (b.blocks*8*1024/(1024*1024)) as MB, c.sql_textFROM v$session a, v$tempseg_usage b, v$sqlarea cWHERE a.saddr = b.session_addrAND c.address= a.sql_addressAND c.hash_value = a.sql_hash_valueORDER BY b.tablespace, b.blocks
/
select tablespace, username, segtype, sum(blocks*8*1024/(1024*1024)) as mb, count(*) as count from v$tempseg_usage group by tablespace, username, segtype
/
Step3: If it is SYS user account taking too much of temporaray tablespace space then. Please follow the follwing Metalink notes:
To implement the solution on Target database., please execute the following steps:
SQL> exec dbms_stats.gather_fixed_objects_stats;
Oracle Document;
Uses a lot Of Temporary Segments ORA-1652: Unable To Extend Temp Segment
Doc ID: Note:357765.1
Type: PROBLEM
Last Revision Date: 02-APR-2007
Status: MODERATED
Step 1: Run below command on problem target database if SQL statement is hang or come out with Temporary tablespace error then Check the Temporaray tablespace usage.
SQL> select count(*) from v$rman_status ;select count(*) from v$rman_status;
ERROR at line 1:ORA-01652: unable to extend temp segment by 640 in tablespace TEMP
Step 2: Find out which User/Users are taking the space on temporaray tablespace and which is the SQL satement.
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, (b.blocks*8*1024/(1024*1024)) as MB, c.sql_textFROM v$session a, v$tempseg_usage b, v$sqlarea cWHERE a.saddr = b.session_addrAND c.address= a.sql_addressAND c.hash_value = a.sql_hash_valueORDER BY b.tablespace, b.blocks
/
select tablespace, username, segtype, sum(blocks*8*1024/(1024*1024)) as mb, count(*) as count from v$tempseg_usage group by tablespace, username, segtype
/
Step3: If it is SYS user account taking too much of temporaray tablespace space then. Please follow the follwing Metalink notes:
To implement the solution on Target database., please execute the following steps:
SQL> exec dbms_stats.gather_fixed_objects_stats;
Oracle Document;
Uses a lot Of Temporary Segments ORA-1652: Unable To Extend Temp Segment
Doc ID: Note:357765.1
Type: PROBLEM
Last Revision Date: 02-APR-2007
Status: MODERATED
Thursday 26 March 2009
Where can one find the high water mark for a table?
There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result 2) - 1
NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result 2) - 1
NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
How does one prevent tablespace fragmentation?
Always set PCTINCREASE to 0 or 100. Bizarre values for PCTINCREASE will contribute to fragmentation. For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes:
100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.
As per my Openion :
Use the same extent size for all the segments in a given tablespace. Locally Managed tablespaces (available from 8i onwards) with uniform extent sizes virtually eliminates any tablespace fragmentation. Note that the number of extents per segment does not cause any performance issue anymore, unless they run into thousands and thousands where additional I/O may be required to fetch the additional blocks where extent maps of the segment are stored.
100K, 100K, 101K, 102K, etc. Such extents of bizarre size are rarely re-used in their entirety. PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc.
As per my Openion :
Use the same extent size for all the segments in a given tablespace. Locally Managed tablespaces (available from 8i onwards) with uniform extent sizes virtually eliminates any tablespace fragmentation. Note that the number of extents per segment does not cause any performance issue anymore, unless they run into thousands and thousands where additional I/O may be required to fetch the additional blocks where extent maps of the segment are stored.
How does one coalesce free space?
SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the command:
ALTER TABLESPACE ... COALESCE;
For releases older than 7.3, use:
SQL> alter session set events 'immediate trace name coalesce level n';
where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the command:
ALTER TABLESPACE ... COALESCE;
For releases older than 7.3, use:
SQL> alter session set events 'immediate trace name coalesce level n';
where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
How does one coalesce free space?
SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the command:
ALTER TABLESPACE ... COALESCE;
For releases older than 7.3, use:
SQL> alter session set events 'immediate trace name coalesce level n';
where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a tablespace using the command:
ALTER TABLESPACE ... COALESCE;
For releases older than 7.3, use:
SQL> alter session set events 'immediate trace name coalesce level n';
where 'n' is the tablespace number you get from SELECT TS#, NAME FROM SYS.TS$;
You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.
What database block size should I use?
Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant.
Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application,use a larger block size.
If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).
Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application,use a larger block size.
If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).
How does one rename a database?
Follow these steps to rename a database:
- Start by making a full database backup of your database (in case you need to restore if this procedure is not working).
- Execute this command from sqlplus while connected to 'SYS AS SYSDBA':
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS; - Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql.
- Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change
"CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...". - Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run
dbrename.sql. - Rename the database's global name:
ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;
How does one create a new database?
One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software to create the starter database.
One can also create databases manually using scripts. This option, however, is falling out of fashion as it is quite involved and error prone. Look at this example for creating and Oracle 9i or higer database:
CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;
One can also create databases manually using scripts. This option, however, is falling out of fashion as it is quite involved and error prone. Look at this example for creating and Oracle 9i or higer database:
CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;
My database is down and I cannot restore. What now ?
This is probably not the appropriate time to be sarcastic, but, recovery without backups are not supported. You know that you should have tested your recovery strategy, and that you should always backup a corrupted database before attempting to restore/recover it.
Nevertheless, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad - Life is DUL without it!). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. Hopefully you'll then be able to load the data into a working database.
Note that DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!
DUDE (Database Unloading by Data Extraction) is another non-Oracle utility that can be used to extract data from a dead database. More info about DUDE is available at http://www.ora600.nl/.
Nevertheless, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad - Life is DUL without it!). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. Hopefully you'll then be able to load the data into a working database.
Note that DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!
DUDE (Database Unloading by Data Extraction) is another non-Oracle utility that can be used to extract data from a dead database. More info about DUDE is available at http://www.ora600.nl/.
I've lost an archived/online REDO LOG file, can I get my DB back?
The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It is also handy if you do database recovery and one of the archived log files are missing and cannot be restored.
_allow_resetlogs_corruption = true
STEPS:
_allow_resetlogs_corruption = true
STEPS:
- Do a "SHUTDOWN NORMAL" of the database
- Set the above parameter
- Do a "STARTUP MOUNT" and "ALTER DATATBASE OPEN RESETLOGS;"
- If the database asks for recovery, use a UNTIL CANCEL type recovery and apply all available archive and online redo logs, then issue CANCEL and reissue the "ALTER DATATBASE OPEN RESETLOGS;" command.
- Wait a couple of minutes for Oracle to sort itself out
- Do a "SHUTDOWN NORMAL"
- Remove the above parameter!
- Do a database "STARTUP" and check your ALERT.LOG file for errors.
NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.
How does one put a database into ARCHIVELOG mode?
The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.
Issue the following commands to put a database into ARCHVELOG mode:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSEVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
Alternatively, add the above commands into your database's startup command script, and bounce the database. The following parameters needs to be set for databases in ARCHIVELOG mode:
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc
NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE 2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery.Archiving can be used in combination with on-line and off-line database backups.
NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode:
log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...
NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE 5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.
Issue the following commands to put a database into ARCHVELOG mode:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSEVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
Alternatively, add the above commands into your database's startup command script, and bounce the database. The following parameters needs to be set for databases in ARCHIVELOG mode:
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc
NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE 2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery.Archiving can be used in combination with on-line and off-line database backups.
NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode:
log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...
NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE 5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.
How does one backup a database using the export utility?
Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables, however [/b]one cannot roll-forward from an restored export file. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports.
One of the advantages of exports is that one can selectively re-import tables, however [/b]one cannot roll-forward from an restored export file. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports.
My database was terminated while in BACKUP MODE, do I need to recover?
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle 7.2, one can simply take the individual datafiles out of backup mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time.
See script end_backup2.sql in the script section of Blog.
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:
ALTER DATABASE END BACKUP;
This command must be issued when the database is mounted, but not yet opened.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time.
See script end_backup2.sql in the script section of Blog.
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:
ALTER DATABASE END BACKUP;
This command must be issued when the database is mounted, but not yet opened.
How does one do on-line database backups?
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes).
Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files.
Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files.
Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
How does one do off-line database backups?
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an offline backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an offline backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
Does Oracle write to data files in begin/hot backup mode?
Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.
In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
Can one restore RMAN backups without a CONTROLFILE and RECOVERY CATALOG?
Details of RMAN backups are stored in the database control files and optionally a Recovery Catalog. If both these are gone, RMAN cannot restore the database.
In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken.
Let's look at an example:
Let's take a backup (partial in our case for ilustrative purposes):
$ rman target / nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1046662649)
using target database controlfile instead of recovery catalog
RMAN> backup datafile 1;
Starting backup at 20-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle= /flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 20-AUG-04
Now, let's destroy one of the control files:
SQL> show parameters CONTROL_FILES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata/orcl/control01.ctl,
/oradata/orcl/control02.ctl,
/oradata/orcl/control03.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! mv /oradata/orcl/control01.ctl /tmp/control01.ctl
Now, let's see if we can restore it. First we need to start the databaase in NOMOUNT mode:
SQL> startup NOMOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1301536 bytes
Variable Size 262677472 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Now, from SQL*Plus, run the following PL/SQL block to restore the file:
DECLARE
v_devtype VARCHAR2(100);
v_done BOOLEAN;
v_maxPieces NUMBER;
TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
v_pieceName t_pieceName;
BEGIN
-- Define the backup pieces... (names from the RMAN Log file)
v_pieceName(1) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
v_pieceName(2) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
v_maxPieces := 2;
-- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');
-- Restore the first Control File...
DBMS_BACKUP_RESTORE.restoreSetDataFile;
-- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');
dbms_output.put_line('Start restoring 'v_maxPieces' pieces.');
FOR i IN 1..v_maxPieces LOOP
dbms_output.put_line('Restoring from piece 'v_pieceName(i));
DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done,
params=>null);
exit when v_done;
END LOOP;
-- Deallocate the channel...
DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
EXCEPTION
WHEN OTHERS THEN
DBMS_BACKUP_RESTORE.deviceDeAllocate;
RAISE;
END;
/
Let's see if the controlfile was restored:
SQL> ! ls -l /oradata/orcl/control01.ctl
-rw-r----- 1 oracle dba 3096576 Aug 20 16:45 /oradata/orcl/control01.ctl
We should now be able to MOUNT the database and continue recovery...
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control03.ctl
SQL> alter database mount;
SQL> recover database using backup controlfile;
ORA-00279: change 7917452 generated at 08/20/2004 16:40:59 needed for thread 1
ORA-00289: suggestion :
/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arc
ORA-00280: change 7917452 for thread 1 is in sequence #671
Specify log: {=suggested filename AUTO CANCEL}
/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
Database altered.
SQL> alter database open resetlogs;
Database altered.
In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken.
Let's look at an example:
Let's take a backup (partial in our case for ilustrative purposes):
$ rman target / nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1046662649)
using target database controlfile instead of recovery catalog
RMAN> backup datafile 1;
Starting backup at 20-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle= /flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 20-AUG-04
Now, let's destroy one of the control files:
SQL> show parameters CONTROL_FILES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata/orcl/control01.ctl,
/oradata/orcl/control02.ctl,
/oradata/orcl/control03.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! mv /oradata/orcl/control01.ctl /tmp/control01.ctl
Now, let's see if we can restore it. First we need to start the databaase in NOMOUNT mode:
SQL> startup NOMOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1301536 bytes
Variable Size 262677472 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Now, from SQL*Plus, run the following PL/SQL block to restore the file:
DECLARE
v_devtype VARCHAR2(100);
v_done BOOLEAN;
v_maxPieces NUMBER;
TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
v_pieceName t_pieceName;
BEGIN
-- Define the backup pieces... (names from the RMAN Log file)
v_pieceName(1) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
v_pieceName(2) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
v_maxPieces := 2;
-- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');
-- Restore the first Control File...
DBMS_BACKUP_RESTORE.restoreSetDataFile;
-- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');
dbms_output.put_line('Start restoring 'v_maxPieces' pieces.');
FOR i IN 1..v_maxPieces LOOP
dbms_output.put_line('Restoring from piece 'v_pieceName(i));
DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done,
params=>null);
exit when v_done;
END LOOP;
-- Deallocate the channel...
DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
EXCEPTION
WHEN OTHERS THEN
DBMS_BACKUP_RESTORE.deviceDeAllocate;
RAISE;
END;
/
Let's see if the controlfile was restored:
SQL> ! ls -l /oradata/orcl/control01.ctl
-rw-r----- 1 oracle dba 3096576 Aug 20 16:45 /oradata/orcl/control01.ctl
We should now be able to MOUNT the database and continue recovery...
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control03.ctl
SQL> alter database mount;
SQL> recover database using backup controlfile;
ORA-00279: change 7917452 generated at 08/20/2004 16:40:59 needed for thread 1
ORA-00289: suggestion :
/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arc
ORA-00280: change 7917452 for thread 1 is in sequence #671
Specify log: {
/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
Database altered.
SQL> alter database open resetlogs;
Database altered.
How does one integrate RMAN with third-party Media Managers?
The following Media Management Software Vendors have integrated their media management software with RMAN(Oracle Recovery Manager):
- Veritas NetBackup - http://www.veritas.com/
- EMC Data Manager (EDM) - http://www.emc.com/
- HP OMNIBack/ DataProtector - http://www.hp.com/
- IBM's Tivoli Storage Manager (formerly ADSM) - http://www.tivoli.com/storage/
- Legato Networker - http://www.legato.com/
- ManageIT Backup and Recovery - http://www.ca.com/manageit/
- Sterling Software's SAMS:Alexandria (formerly from Spectralogic) - http://www.sterling.com/sams/
- SUN's Solstice Backup - http://www.sun.com/storage/software/backup.html
- CommVault Galaxy - http://www.commvault.com/
etc...
The above Media Management Vendors will provide first line technical support (and installation guides) for their respective products.
A complete list of supported Media Management Vendors can be found at: http://www.oracle.com/technology/deploy/availability/htdocs/bsp.htm
When allocating channels one can specify Media Management spesific parameters. Here are some examples:
Netbackup on Solaris:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';
Netbackup on Windows:
allocate channel t1 type 'SBT_TAPE' send "NB_ORA_CLIENT=client_machine_name";
Omniback DataProtector on HP-UX:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY= /opt/omni/lib/libob2oracle8_64bit.sl';
Omniback DataProtector on HP-UX:
allocate channel 'dev_1' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)';
How does one create a RMAN recovery catalog?
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role.
Look at this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
One can also use the "upgrade catalog;" command to upgrade to a new RMAN release, or the "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation.
Look at this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
One can also use the "upgrade catalog;" command to upgrade to a new RMAN release, or the "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation.
How do I find out from the RMAN catalog if a particular archive log has been backed-up?
To list all archivelog backups for the past 24 hours:
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
How does one clone/duplicate a database with RMAN?
The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to.
Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.
Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.
Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":
connect target sys/secure@origdb
connect catalog rman/rman@catdb
connect auxiliary /
run {
set newname for datafile 1 to '/ORADATA/u01/system01.dbf';
set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';
set newname for datafile 3 to '/ORADATA/u03/users01.dbf';
set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';
set newname for datafile 5 to '/ORADATA/u02/example01.dbf';
allocate auxiliary channel dupdb1 type disk;
set until sequence 2 thread 1;
duplicate target database to dupdb
logfile
GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,
GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;
}
The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.
Notes: the "set newname" commands are only required if your datafile names will different from the target database. The newly cloned DB will have its own unique DBID.
Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.
Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.
Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":
connect target sys/secure@origdb
connect catalog rman/rman@catdb
connect auxiliary /
run {
set newname for datafile 1 to '/ORADATA/u01/system01.dbf';
set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';
set newname for datafile 3 to '/ORADATA/u03/users01.dbf';
set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';
set newname for datafile 5 to '/ORADATA/u02/example01.dbf';
allocate auxiliary channel dupdb1 type disk;
set until sequence 2 thread 1;
duplicate target database to dupdb
logfile
GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,
GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;
}
The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.
Notes: the "set newname" commands are only required if your datafile names will different from the target database. The newly cloned DB will have its own unique DBID.
Wednesday 25 March 2009
How does one backup and restore archived log files?
One can backup archived log files using RMAN or any operating system backup utility.
Remember to delete files after backing them up to prevent the archive log directory from filling up.
If the archive log directory becomes full,your database will hang! Look at this simple RMAN backup script:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/archback/log_%t_%sp%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }
The "delete input" clause will delete the archived logs as they as backed-up.
Here is a restore example:
RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }
Remember to delete files after backing them up to prevent the archive log directory from filling up.
If the archive log directory becomes full,your database will hang! Look at this simple RMAN backup script:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/archback/log_%t_%sp%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }
The "delete input" clause will delete the archived logs as they as backed-up.
Here is a restore example:
RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }
How does one backup and restore a database using RMAN?
The biggest advantage of RMAN is that it only backup used space in the database.
RMAN doesn't put tablespaces in backup mode, saving on redo generation overhead.
RMAN will re-read database blocks until it gets a consistent image of it.
Backup Example :rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/backup/%d_t%t_s%s_p%p'
(database);
release channel t1;
}
RMAN RESTORE Example:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
Subscribe to:
Posts (Atom)
