Oracle Nexus

How can one see who is using a temporary segment?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 14:10:00 +0000

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
/

How do I find used/free space in a TEMPORARY tablespace?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 14:07:00 +0000

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;

How does one see the uptime for a database?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 14:04:00 +0000

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%';

How does one give developers access to trace files (required as input to tkprof)?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 13:41:00 +0000

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.

Can one resize tablespaces and data files?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 13:01:00 +0000

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;

Can one rename a tablespace?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 12:58:00 +0000

From Oracle 10g Release 1, users can rename tablespaces.
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?

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 12:48:00 +0000

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.

ORA-01652: unable to extend temp segment by 640 in tablespace TEMP

noreply@blogger.com (Nexus) — Fri, 27 Mar 2009 11:16:00 +0000

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

Where can one find the high water mark for a table?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:58:00 +0000

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.

How does one prevent tablespace fragmentation?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:56:00 +0000

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.

How does one coalesce free space?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:54:00 +0000

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.

How does one coalesce free space?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:54:00 +0000

What database block size should I use?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:51:00 +0000

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).

How does one rename a database?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:48:00 +0000

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?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:47:00 +0000

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;

My database is down and I cannot restore. What now ?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:45:00 +0000

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/.

I've lost an archived/online REDO LOG file, can I get my DB back?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:42:00 +0000

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:

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?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:40:00 +0000

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.

How does one backup a database using the export utility?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 14:38:00 +0000

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.

My database was terminated while in BACKUP MODE, do I need to recover?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 10:32:00 +0000

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.

How does one do on-line database backups?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 10:30:00 +0000

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.

How does one do off-line database backups?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 10:28:00 +0000

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.

Does Oracle write to data files in begin/hot backup mode?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 10:26:00 +0000

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.

Can one restore RMAN backups without a CONTROLFILE and RECOVERY CATALOG?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 10:16:00 +0000

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.

How does one integrate RMAN with third-party Media Managers?

noreply@blogger.com (Nexus) — Thu, 26 Mar 2009 10:12:00 +0000

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)';