dbawiki

When upgrading to 18c, passwords that only use version 10g will have to be changed. This is done by expiring the passwords for those users.
Find the relevant usernames

SELECT USERNAME FROM DBA_USERS 
WHERE ( PASSWORD_VERSIONS = '10G '
OR PASSWORD_VERSIONS = '10G HTTP ')
AND USERNAME <> 'ANONYMOUS';

Set database access to be less restrictive than the default. Put this in sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Expire the passwords

ALTER USER username PASSWORD EXPIRE;

Using straight data pump to export a sample does not take referential constraints into account.
Using this method will fix that oversight.
http://www.dba-oracle.com/t_data_pump_export_rows_subset.htm

set lines 1000
col name for a30
col value for a100
col con_id nopri
select * from v$rman_configuration;

Either use rman command line and specify:

configure controlfile autobackup on;

or use SQL*Plus

var a number
exec :a := dbms_backup_restore.setconfig('CONTROLFILE AUTOBACKUP','ON');
 
PL/SQL procedure successfully completed.
 
print :a
 
         A
----------
         1

Verify by either 'show all' in rman or as above selecting from v$rman_configuration.

     CONF# NAME                           VALUE
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
         1 CONTROLFILE AUTOBACKUP         ON
         2 RETENTION POLICY               TO RECOVERY WINDOW OF 28 DAYS

Other options are dbms_backup_restore.deleteconfig(<config number>) to restore one item to its default value

SQL> exec dbms_backup_restore.deleteconfig(1);

PL/SQL procedure successfully completed.

SQL> print a;

         A
----------


SQL> select * from v$rman_configuration;

     CONF# NAME                           VALUE
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
         2 RETENTION POLICY               TO RECOVERY WINDOW OF 28 DAYS

or dbms_backup_restore.resetconfig to remove all customisations.

SQL> exec dbms_backup_restore.resetconfig;
 
PL/SQL procedure successfully completed.
 
SQL> select * from v$rman_configuration;
 
no rows selected
 
SQL>

Show how far back the RMAN catalog goes for each instance

select db_name         instance
,      min(start_time) earliest_date
,      input_type      backup_type
from   rc_rman_backup_job_details
group  by db_name,input_type
order  by 2;

produces something like

DB_NAME  MIN(START_TIME)             INPUT_TYPE
-------- --------------------------- -------------
EBSM     11-MAY-16 16:34:55          ARCHIVELOG
CRELSASP 25-JUN-16 18:29:14          ARCHIVELOG
CRELSASP 29-JUN-16 18:59:48          DB FULL
DOCT     01-JUL-16 19:31:58          DB FULL
CRELANP2 08-JUL-16 12:25:20          ARCHIVELOG
BILP2    08-JUL-16 12:25:20          ARCHIVELOG
CRELSASD 08-JUL-16 16:30:22          ARCHIVELOG
CRELANP2 11-JUL-16 19:04:54          DB FULL
CRELSASD 13-JUL-16 18:59:03          DB FULL
BILP2    20-JUL-16 18:59:43          DB FULL
BILF     16-AUG-16 22:33:12          ARCHIVELOG
FORMN    18-AUG-16 14:31:49          ARCHIVELOG
CRELANF  19-AUG-16 14:32:44          ARCHIVELOG
LOANF    19-AUG-16 14:32:48          ARCHIVELOG
FORMN    19-AUG-16 15:33:30          CONTROLFILE
LOANF    19-AUG-16 19:09:08          DB FULL
BILF     22-AUG-16 19:00:47          DB FULL
FORMN    23-AUG-16 19:00:34          DB FULL
CRELANF  25-AUG-16 19:00:29          DB FULL
ADSNA    27-AUG-16 12:31:47          ARCHIVELOG
...

If these dates go back further than the retention period in the RMAN configuration then these entries need clearing out. This script will do it if the controlfile_record_keep_time is set correctly on all databases.

RMANUSR="rman"
RMANPWD="rman"
RMANCAT=${1:-rman12d}

# =======================================
# thats it, nothing to change after here!
# =======================================
export PATH="/usr/local/bin/oraenv:${PATH}"
ORAENV_ASK=NO
SIDLIST=$(ps -ef | grep [o]ra_pmon | awk '{print $NF}' | sed -e 's/ora_pmon_//' | egrep -v 'grep|///|sed|awk|ASM|^$')
for i in ${SIDLIST}
do
    export ORACLE_SID="${i}"
    unset ORACLE_HOME
    . oraenv
    if [[ "${ORACLE_HOME}" == "" ]]; then
        echo "WARNING: Unable to set environment for ${i}, skipping."
        continue
    fi

    # find out if the database is in archive log mode
    LOG_TEXT=$(echo 'archive log list' | "${ORACLE_HOME}/bin/sqlplus" -s / as sysdba)
    LOG_MODE=$(echo $LOG_TEXT | perl -ne 'print "$2" if /Database log mode (.+) Automatic archival (.+) Archive destination (.+?) /')
    if [[ "${LOG_MODE}" == "Disabled" ]]; then
        echo "INFO: Instance ${i} is not archiving so will not be in catalog ${RMANCAT}, skipping."
        continue
    fi

    # test we can see the database from the rman instance
    # if this errors out, the instance probably is not in this catalog
    RMANTEST=$(echo "exit;" | "${ORACLE_HOME}/bin/rman" target / catalog ${RMANUSR}/${RMANPWD}@${RMANCAT})
    echo "${RMANTEST}" | egrep "(not started|RMAN-04004|RMAN-06004)" >/dev/null 2>&1
    if [[ $? -eq 0 ]]; then
        echo "INFO: Instance ${i} is not accessible from or not in catalog ${RMANCAT}, skipping."
        continue
    fi
    echo
    echo "Processing ${i} in catalog ${RMANCAT}"
    "${ORACLE_HOME}/bin/rman" <<EORMAN
connect target /
connect catalog ${RMANUSR}/${RMANPWD}@${RMANCAT}
unregister database noprompt;
register database;
EORMAN
done

The UNREGISTER verb does not exist in 9i so have to use PL/SQL method.

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: PVCS (DBID=2561628519)
connected to recovery catalog database

RMAN> unregister database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, spool, startup, shutdown, send, show, test, upgrade, validate"
RMAN-01008: the bad identifier was: unregister
RMAN-01007: at line 1 column 1 file: standard input

From SQL*Plus, get the db_key and dbid from the catalog

select db_key, dbid, name from rc_database where name = 'PVCS';

    DB_KEY       DBID NAME
---------- ---------- --------
   5102549 2561628519 PVCS

Use these values as parameters to the packaged procedure

SQL> execute dbms_rcvcat.unregisterdatabase(5102549, 2561628519);

PL/SQL procedure successfully completed.