This is an old revision of the document!
Table of Contents
Finding and Resetting User Passwords That Use the 10G Password Version
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;
Export a subset (sample) of database rows using a package and datapump
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
Show non default configuration settings in RMAN
set lines 1000 col name for a30 col value for a100 col con_id nopri select * from v$rman_configuration;
How to set RMAN configure options using package DBMS_BACKUP_RESTORE
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>
Clean up RMAN catalog
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.