How to deregister a database from a Recovery Catalog

Log on to the target database:

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:00:50 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
 305453026 PRODDB01

connect to the recovery catalog through sqlplus:

sqlplus uid/pwd@RMANCAT

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:01:33 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> select db_key,dbid,name from rc_database
  2  where dbid=305453026;

    DB_KEY       DBID NAME
---------- ---------- --------
         1  305453026 PRODDB01

Finally, execute the procedure dbms_rcvcat.unregisterdatabase, passing in the parameters db_key and dbid:

SQL> execute dbms_rcvcat.unregisterdatabase(1,305453026);

PL/SQL procedure successfully completed.

How to set a column to UNUSED

Setting a column to UNUSED can be a solution when you cannot or should not attempt to drop a column due to performance reasons.

Syntax:

ALTER TABLE [table name] SET UNUSED (col1, col2, ... col n);

or

ALTER TABLE [table name] SET UNUSED COLUMN (col1);

Example:

ALTER TABLE MYTABLE
SET UNUSED COLUMN MODIFIED_DATE;

Verify that the columns were set to UNUSED:

SELECT * 
FROM USER_UNUSED_COL_TABS;

Result:

TABLE_NAME	COUNT
MYTABLE	1
INVOICES_TMP	1

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS;

The column is now gone:

SELECT * 
FROM USER_UNUSED_COL_TABS;

TABLE_NAME	COUNT
INVOICES_TMP	1

You can also use the keyword CHECKPOINT when dropping the unused columns:

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS CHECKPOINT 1000;

Oracle explains about the CHECKPOINT clause:

"This clause causes a checkpoint to be applied after processing the specified number of rows"
and
"Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo Space."

Note that:
Setting a column to UNUSED will drop constraints and indexes on the column
An UNUSED column cannot be accessed, nor recovered.
You can however, add a new column with the same name as any UNUSED column for the table.

Sources:
Oracle Documentation: Marking columns unused
Oracle Documentation: ALTER TABLE

Important files and directories in a Netbackup setup

Important netbackup files and directories on a typcial unix server:

/usr/openv/netbackup/version --> displays the netbackup version
/usr/openv/netbackup/bp.conf --> displays important settings such as SERVER, MEDIA_SERVER, CLIENT_NAME
/usr/openv/netbackup/logs/user_ops/dbext/logs --> log files for rman backups

Example of bp.conf

SERVER = backupserver1.mydomain.com
MEDIA_SERVER = server1-bkp.mydomain.com
CLIENT_NAME = server1-bkp.mydomain.com
REQUIRED_INTERFACE = server1-bkp.mydomain.com

Note that the CLIENT_NAME used above translates into the NB_ORA_CLIENT typically used in when sending backups to tape using Netbackup:

NB_ORA_CLIENT=server1-bkp.mydomain.com

How to save a value in a bind variable

Useful for tracing or explaining queries that use bind variables:

connect scott/tiger
var leom char(10);
var eod char(10);

exec :leom := to_date('30.09.2013','DD.MM.YYYY');
exec  :eod := to_date('31.10.2013','DD.MM.YYYY');

SELECT col1, col2 ....col n
FROM   TABLE1
WHERE  START_DATE <= :eod
AND    END_DATE   <= :leom;

How to shrink a datafile in a temporary tablespace

alter tablespace TEMP shrink tempfile '/data/oracle/PRODDB01/datafiles/temp_20.dbf' KEEP 8G;

This feature was introduced in Oracle 11g.

How to create a temporary tablespace and assign it as the database default

CREATE TEMPORARY TABLESPACE TMP TEMPFILE
  '/data/oracle/u01/PRODDB01/datafile/tmp_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

How to restore a sequence of archivelogs back to their original location

connect catalog catowner/*********@RMANCAT
connect target
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=ora_dbserver1_00_netwzone1,NB_ORA_CLIENT=dbserver1-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
restore archivelog from logseq 412594 until logseq 412656;
release channel t1;
release channel t2;
}