Directories let database code interact with the filesystem.

create or replace directory EXPORT_DIR as '/var/local/instance0/export';
create or replace directory IMPORT_DIR as '/var/local/instance0/import';
grant READ on directory IMPORT_DIR to APPUSER;
grant WRITE on directory EXPORT_DIR to APPUSER;

set linesize 200
col grantee format a7
col table_schema format a12
col table_name format a10
col privilege format a9
select grantee,table_schema,table_name,privilege
from all_tab_privs
where table_name = 'EXPORT_DIR' or table_name = 'IMPORT_DIR';
rman target /
crosscheck backup;
crosscheck copy;
list failure;
advise failure;
report schema;
restore database preview summary;
startup force mount;
restore database;
recover database;
alter database open;


recover database until cancel;
alter database open resetlogs;

If you need to rebuild your temp tablespace, eg the existing one is corrupt or the wrong size, the trick is to temporarily give oracle a new one to use as its default temp tablespace while you replace the real one, then switch back.

create temporary tablespace TEMP_TMP
 tempfile '/oracle/oradata/IFSS/temp02.dbf'
 size 1G reuse
 autoextend on next 100M maxsize unlimited;
alter database default temporary tablespace TEMP_TMP;
drop tablespace TEMP;
! rm /oracle/oradata/IFSS/temp01.dbf
create temporary tablespace TEMP
 tempfile '/oracle/oradata/IFSS/temp01.dbf'
 size 1G reuse
 autoextend on next 100M maxsize unlimited;
alter database default temporary tablespace TEMP;
drop tablespace TEMP_TMP;
! rm /oracle/oradata/IFSS/temp02.dbf
select * from dba_temp_files;
select * from dba_tablespaces;

Jowan points out below that “reuse” is better omitted.

This is very useful for copying say your Live instance to Staging or another tier. Doing a disconnected duplication means there is absolutely no chance of upsetting your Live instance. I also think it’s more intuitive. (It’s only available in 11g.)

To begin, you need a backup of your source instance, including control file snapshot and any archive logs you want to include, and transfer those to the destination server. (The copied backup files don’t need to be in a particular structure and don’t need to be in the same directory.) I rsync the files I need into /opt/oracle/tmp/{backupset,archivelog,autobackup}/2011_06_21/.... The gotcha I need to remember is that archivelogs need to be gunzipped before the first attempt at import.

If you’ve taken care of all the steps for creating the destination instance, you’re ready to kick off the import.

sqlplus / as sysdba
startup nomount
rman auxiliary /
duplicate database to DEST
until time "TO_DATE('2011-06-21 05:00:00', 'YYYY-MM-DD HH24:MI:SS')"
backup location '/path/to/tree/of/backup/files';

(where DEST is the name of the destination instance.)

sqlplus / as sysdba
alter database open

This often fails for one reason or another. Simply re-run the rman step and it should work second time. This will import everything under /path/to/tree/of/backup/files up to the specified cut-off time.
You may then want to disable archive logs if you don’t want them on the new instance.
Most likely you’ll also need to rebuild your TEMP tablespace(s).

The default settings for page/screen capabilities in oracle are pretty pessimistic. The result is your output is wrapped down the left-hand side of your screen, with column headings repeated too frequently.

Page dimensions

The defaults in sqlplus are 14 rows x 80-chars.
To change to, eg, 60 rows x 132-chars, use:

set PAGESIZE 60;
set LINESIZE 132;

Column widths

If your output has a column ‘Location’ that is too wide for your purposes, you can assign it a format via:

column Location format A20;

To list your current column formats:


To clear your current column formats:

clear columns;

To clear one specific column format:

column Location clear;

CSV files

One way to create comma-separated or tab-separated files is to spool them from within sqlplus.
First set up neutral formatting:



spool /tmp/output.csv
spool off

You’ve created a brand new instance or cloned Live into Staging; now you need to take it out of archivelog mode to boost performance.

create pfile from spfile;

then edit the pfile (.../dbs/initSID.ora) to include *.log_archive_start=FALSE, then take a backup, then continue with

shutdown immediate;
create spfile from pfile;
startup mount;
alter database noarchivelog;
alter database open;

then confirm the change with

archive log list;
shutdown immediate;

You then need to take a backup because all previous backups are now invalid.

In 11g you can/should omit the editing of the s/pfile since that is deprecated; in 11g the archivelog mode is stored in the control file(s).

You were trying to clone oracle instance ORA1 to oracle instance ORA2 using RMAN duplicate, but it failed and now when you connect RMAN it shows them both having name ORA1:

rman target / auxiliary SYS/pissword@ORA2
Recovery Manager: Release - Production on Thu May 26 07:23:06 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA1 (DBID=3447699504)
connected to auxiliary database: ORA1 (not mounted)

(the problem is the last line shows ‘ORA1’ instead of ‘ORA2’)
Before the RMAN duplicate failed, rman changed the name of the auxiliary instance to have the name of the original instance (as the first step of the cloning process). To change it back, edit it via its pfile:

create pfile from spfile

Then edit the pfile (eg initORA2.ora) to change the value of db_name from ORA1 to ORA2.
Then apply the change:

shutdown immediate;
create spfile from pfile;
startup nomount;

and re-test the connection via rman to check it now shows ‘ORA2’.

Next Page »