Generate DDL for all Database Links
Filed under: DBMS_METADATA, General DBA, PL/SQL, Scripts
Simple script to generate DDL for all database links in the database:
set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) || ‘/’ FROM dba_db_links a
/
Script to generate Index DDL from a table with DBMS_METADATA.GET_DEPENDENT_DDL
Filed under: DBMS_METADATA, General DBA, PL/SQL, Scripts
Here is a simple script to generate the DDL code for all indexes in a particular table.
set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL(‘INDEX’,UPPER(‘&table_name’),UPPER(‘&schema_name’))
from dual
/
set heading on;
set echo on;
How to create cold backup using RMAN?
Using the steps below one take cold backup using RMAN. As it’s a cold backup the database as the database is in mount stage the database doesn’t have to be archivelog so it can taken for a database which is in noarchivelog mode too.
Step 1) Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 2) Start database in mount stage
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 75497480 bytes
Database Buffers 88080384 bytes
Redo Buffers 2174976 bytes
Step 3) Run rman and connect to target database and run rman to backup database and connection to catalog if you are using one
$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Apr 23 02:33:38 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=1992878807, not open)
RMAN> backup database;
Starting backup at 23-APR-10
..
using target database control file instead of recovery catalog
…
How to find RMAN catalog version?
To find RMAN catalog version log-in to the catalog through SQL*Plus and query the table rcver will print version.
SQL> select * from rcver;
VERSION
————
10.02.00.00
SQL> select object_type from user_objects where object_name = ‘RCVER’;
OBJECT_TYPE
——————-
TABLE
SQL> desc rcver;
Name Null? Type
—————————————– ——– —————————-
VERSION NOT NULL VARCHAR2(12)
Steps to create rman catalog and register DB
1) Create tablespace for example name catalog_tbs
2) Create user for example user rman_catalog and assign catalog_tbs as default tablespace
3) Grant user rman_catalog the following roles: connect, resource, recovery_catalog_owner.
4) Connect to catalog through RMAN
5) Create recovery catalog
$ rman catalog=rman_catalog/password
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog tablespace catalog_tbs;
recovery catalog created
6) Next register database with the catalog
$ rman target=sys/syspassword catalog=rman_catlog/password
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Note: One can register multiple databases with different versions in the same catalog but the catalog version is the highest version of the databases registered so if you upgrade a database the catalog will need to be upgraded.
To upgrade the catalog, it is included in the steps of upgrade how to upgrade the catalog, below shows how to upgrade the catalog.
RMAN> upgrade catalog;
recovery catalog owner is RMAN_CAT
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 10.02.00.00
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00
How to change/clear retention policy for RMAN?
Using CLEAR one can set the retention policy to default and using NONE have no retention policy.
The example below shows the difference between the two.
——— Display retention policy and shows using the option CLEAR ———-
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> configure retention policy to redundancy 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
-- Clearing the retention value set the redundancy to 1
RMAN> configure retention policy clear;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN configuration parameters are successfully reset to default value
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
——— Display retention policy and shows using the option NONE ———-
RMAN> show retention policy;
RMAN configuration parameters are: Setting to NONE is not the same as clearing retention, it is disables it and commands like REPORT OBSOLETE or DELETE OBSOLETE reports error “RMAN-06525: RMAN retention policy is set to none”
RMAN> configure retention policy to none;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO NONE;
RMAN> configure retention policy clear;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO NONE;
RMAN configuration parameters are successfully reset to default value
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
Example of using Logminer
LogMiner can be used to recover data and audit database, it has 2 packages one of them named “DBMS_LOGMNR_D” to build dictonary and other setup to read from archive logs.
– Using DBMS_LOGMNR_D.BUILD – one creates a information on objects in the database, it is useful as when looking at contents of archive logs it will list the object names instead of object ids. One would need to set UTL_FILE_DIR if using the store_in_flat_file option.
SQL> exec DBMS_LOGMNR_D.BUILD ( ‘dict.ora’, ‘/tmp’, options => dbms_logmnr_d.store_in_flat_file );
PL/SQL procedure successfully completed.
Note: The Logmnr is applied at the session level so one can’t view the information of logminer contents if not set through another session.
– Add archive log, the first one is DBMS_LOGMNR.NEW
SQL> exec DBMS_LOGMNR.ADD_LOGFILE ( ‘/u01/oradata/TEST/arch/1_1205_694732375.dbf’, DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
– Add additional archive log
SQL> exec DBMS_LOGMNR.ADD_LOGFILE ( ‘/u01/oradata/TEST/arch/1_1204_694732375.dbf’, DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
– to get the timestamp of period one is looking at start
SQL> select first_change#, first_time from v$log_history where sequence# = 1204;
FIRST_CHANGE# FIRST_TIME
————- ——————–
6293397 16-APR-2010 02:34:15
– to get the timestamp of period one is looking at end
SQL> select first_change#, first_time, sequence# from V$log where status = ‘CURRENT’;
FIRST_CHANGE# FIRST_TIME SEQUENCE#
————- ——————– ———-
6303785 16-APR-2010 09:00:42 1206
– start logminer with the above time, if one specifies the start time less than the first archive log this procedure will return “ORA-01291: missing logfile”, if one specifies end time greater than the log file it will not report an error
SQL> exec DBMS_LOGMNR.START_LOGMNR (startTime => TO_DATE(’16-APR-2010 02:34:15′, ‘DD-MON-YYYY HH24:MI:SS’), endTime => TO_DATE(’16-APR-2010 09:00:42′, ‘DD-MON-YYYY HH24:MI:SS’), DictFileName => ‘/tmp/dict.ora’);
PL/SQL procedure successfully completed.
– get the period of logminer
SQL> select min(timestamp), max(timestamp) from V$logmnr_contents;
MIN(TIMESTAMP) MAX(TIMESTAMP)
——————– ——————–
16-APR-2010 02:34:15 16-APR-2010 09:00:42
– print the objects modified during the above period
SQL> SELECT distinct seg_name from v$logmnr_contents;
SEG_NAME
—————————–
WRH$_PROCESS_MEMORY_SUMMARY
..
– shows the operation performed on the object, if dictonary is not available the one can query using object_id show below
SQL> select operation, count(1) from v$logmnr_contents where seg_name = ‘WRH$_PROCESS_MEMORY_SUMMARY’ group by operation;
OPERATION COUNT(1)
——————————– ———-
INSERT 24
–
SQL> select object_id from dba_objects where object_name = ‘WRH$_PROCESS_MEMORY_SUMMARY’;
OBJECT_ID
———-
9003
SQL> select operation, count(1) from v$logmnr_contents where DATA_OBJ# = 9003 group by operation;
OPERATION COUNT(1)
——————————– ———-
INSERT 24
– end log miner
SQL> exec DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
Note:
– If start_logmnr not set, the procedure will return ORA-01306
SQL> select count(1) from v$logmnr_contents;
select count(1) from v$logmnr_contents
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents
If start being called but no archive logs defined, then dbms_logmnr will return ORA-01292
RROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner session
ORA-06512: at “SYS.DBMS_LOGMNR”, line 58
ORA-06512: at line 1
How to create backup of control file?
– backup control file in binary format to a file named /tmp/x.ctl, it will preserve the backup information of the database
SQL> alter database backup controlfile to ‘/tmp/x.ctl’;
Database altered.
– backup control file in readable format in /tmp/x.trc, the script created doesn’t contain information about backups so if you backup in the binary format it will keep the backup information
SQL> alter database backup controlfile to trace as ‘/tmp/x.trc’;
Database altered.
control_file_record_keep_time
Filed under: Backup & Recovery, Control file, General DBA, RMAN
control_file_record_keep_time – the value indicates number of days before reusable record is overwritten, if set to 0 reusable record is not expanded. The default value is 7. Reusable section is where archive log records and backup information are kept. This parameter can be modified without restart of the instance.
Additional
http://mrothouse.wordpress.com/2006/11/06/oracle9i-control_file_record_keep_time/
How to find what options are available/installed?
– shows what options are enabled
SQL> select parameter FROM v$option where value = 'TRUE';
PARAMETER
—————————————————————-
Partitioning
Objects
Advanced replication
Bit-mapped indexes
…
– shows what options are disabled/not instaled
SQL> select parameter FROM v$option where value = 'FALSE';
PARAMETER
—————————————————————-
Real Application Clusters
Oracle Label Security
Data Mining Scoring Engine
