clean up a dropped database from RMAN catalog

RMAN> list db_unique_name all;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
68262   DGLOGI   1084485603       PRIMARY          DGLOGI
68262   DGLOGI   1084485603       STANDBY          DG
68262   DGLOGI   1084485603       STANDBY          DGS
1       ORCL     1330909046       PRIMARY          ORCL
261     DG       1716975309       PRIMARY          DG
261     DG       1716975309       STANDBY          DGS
192555  TTS      1769654909       PRIMARY          TTS


#DGLOGI  database was dropped

orarac2poc:DGS:/home/oracle> rcat
orarac2poc:RCAT:/home/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 30 09:54:18 2013

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,
Data Mining and Real Application Testing options

SYS@RCAT> desc dbms_rcvcat
ERROR:
ORA-04043: object dbms_rcvcat does not exist

# SYS can't access to the package , and it is mentioned in oracle document of PL/SQL package reference.

SYS@RCAT> conn rman
Enter password:
Connected.
RMAN@RCAT> select db_key, dbinc_key, dbid, name from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME
---------- ---------- ---------- --------
    192555     220486 1769654909 TTS
       261       8447 1716975309 DG
     68262      68263 1084485603 DGLOGI
         1          2 1330909046 ORCL

RMAN@RCAT> desc dbms_rcvcat
PROCEDURE ADDDBIDTOIMPORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FIRST                          BINARY_INTEGER          IN
 IDB                            VARCHAR2                IN
 IDBINC                         VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT
 DBNAME                         VARCHAR2                IN     DEFAULT
FUNCTION BEGINARCHIVEDLOGRESYNC RETURNS NUMBER
FUNCTION BEGINBACKUPCORRUPTIONRESYNC RETURNS NUMBER
FUNCTION BEGINBACKUPDATAFILERESYNC RETURNS NUMBER
FUNCTION BEGINBACKUPPIECERESYNC RETURNS NUMBER
FUNCTION BEGINBACKUPREDOLOGRESYNC RETURNS NUMBER
FUNCTION BEGINBACKUPSETRESYNC RETURNS NUMBER
FUNCTION BEGINBACKUPSPFILERESYNC RETURNS NUMBER
FUNCTION BEGINBLOCKCORRUPTIONRESYNC RETURNS NUMBER
...

#but catalog owner can "see" it.

@> conn rman
Connected.
RMAN@RCAT> select db_key, dbid, name from rc_database;

    DB_KEY       DBID NAME
---------- ---------- --------
    192555 1769654909 TTS
       261 1716975309 DG
     68262 1084485603 DGLOGI
         1 1330909046 ORCL

RMAN@RCAT> !ps -ef |grep pmon
grid      2247     1  0 Aug01 ?        00:03:01 asm_pmon_+ASM2
oracle    2798     1  0 Aug01 ?        00:04:51 ora_pmon_ORCL2
oracle    2802     1  0 Aug01 ?        00:04:15 ora_pmon_RCAT
oracle    8553     1  0 Aug21 ?        00:00:38 ora_pmon_DGS
oracle    8974     1  0 Jul30 ?        00:02:14 ora_pmon_TTS
oracle   11882 20904  0 14:58 pts/2    00:00:00 /bin/bash -c ps -ef |grep pmon
oracle   11884 11882  0 14:58 pts/2    00:00:00 grep pmon



RMAN@RCAT> exec dbms_rcvcat.unregisterdatabase(68262,1084485603);

PL/SQL procedure successfully completed.

RMAN@RCAT> select db_key, dbid, name from rc_database;

    DB_KEY       DBID NAME
---------- ---------- --------
    192555 1769654909 TTS
       261 1716975309 DG
         1 1330909046 ORCL

change default tablespace of db setting

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';


PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   SYSTEM                         Name of default permanent tablespace



SYS@cust1> create user liqy identified by liqyliqy;

User created.

SYS@cust1> select * from dba_users where username='LIQY';

USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE         DEFAULT_TABLESPACE
------------------------------ ---------- ------------------------------ -------------------------------- ------------------- ------------------- ------------------------------
TEMPORARY_TABLESPACE           CREATED             PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------- ------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PASSWORD E AUTHENTI
-------- - --------
LIQY                                   58                                OPEN                                                                     SYSTEM
TEMP                           2013-08-26 14:43:47 DEFAULT                        DEFAULT_CONSUMER_GROUP

10G 11G  N PASSWORD



SYS@rcat> desc database_properties
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROPERTY_NAME                             NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                     VARCHAR2(4000)
 DESCRIPTION                                        VARCHAR2(4000)

SYS@rcat> select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE
SYSTEM
Name of default permanent tablespace

DEFAULT_EDITION
ORA$BASE
Name of the database default edition

DEFAULT_TBS_TYPE
SMALLFILE
Default tablespace type


SYS@rcat> ALTER database default tablespace users;

Database altered.

SYS@rcat> select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP
Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE
USERS
Name of default permanent tablespace

DEFAULT_EDITION
ORA$BASE
Name of the database default edition

DEFAULT_TBS_TYPE
SMALLFILE
Default tablespace type

what decides number of records in v$log_history

V$log_history retention is defined by MAXLOGHISOTRY when you create the database (or recreate the controlfile)

MAXLOGHISTORY Clause
This parameter is useful only if you are using Oracle Database in archivelog mode with Real Application Clusters. Specify the maximum number of archived redo log files for automatic media recovery of Real Application Clusters. The database uses this value to determine how much space to allocate in the control file for the names of archived redo log files. The minimum value is 0. The default value is a multiple of the MAXINSTANCES value and depends on your operating system. The maximum value is limited only by the maximum size of the control file.

SQL> SELECT min(first_time), max(first_time) from v$log_history;

MIN(FIRST MAX(FIRST
--------- ---------
08-AUG-13 26-AUG-13

SQL> SELECT count(*) from v$log_history;                       

  COUNT(*)
----------
      5445
SQL> select * from V$CONTROLFILE_RECORD_SECTION where type='LOG HISTORY';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
LOG HISTORY                           56          5445         5445        3462       3461     580177