This is an old revision of the document!
Table of Contents
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.