Showing posts with label Database Level. Show all posts
Showing posts with label Database Level. Show all posts
OPatch Apply In database Level
OPatch is a Database patch.
Please download latest OPatch Tool and extract the RDBMS Oracle home.
1. Check the OPatch already installed or not
cd $ORACLE_HOME/OPatch
$opatch -lsinventory
This is command will show existing patch
Then Export the following environmet file
$ export ORACLE_HOME=/UAT/app/oracle
$ export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
$ export OBJECT_MODE=32_64
$ cd patch/
Extract the Following OPatch.
p5246372_10203_LINUX.zip
p5965763_10203_LINUX.zip
5246372
5965763
Then Go to patch Directory and apply the Opatch.
$ cd 5965763/
$ opatch apply
Reference:
OPatch documentation list
Doc ID: 293369.1
Please download latest OPatch Tool and extract the RDBMS Oracle home.
1. Check the OPatch already installed or not
cd $ORACLE_HOME/OPatch
$opatch -lsinventory
This is command will show existing patch
Then Export the following environmet file
$ export ORACLE_HOME=/UAT/app/oracle
$ export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
$ export OBJECT_MODE=32_64
$ cd patch/
Extract the Following OPatch.
p5246372_10203_LINUX.zip
p5965763_10203_LINUX.zip
5246372
5965763
Then Go to patch Directory and apply the Opatch.
$ cd 5965763/
$ opatch apply
Reference:
OPatch documentation list
Doc ID: 293369.1
Create UNDO tablespace in Oracle
In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.
Drop Existing Rollback segments and create new UNDO tablespace
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
ROLL301 RBS3
SQL> select file_name,tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS3';
SQL> select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;
SEGMENT_NAME
============
alter rollback segment ROLL301 offline;
alter rollback segment ROLL302 offline;
alter rollback segment ROLL303 offline;
SQL> alter rollback segment ROLL301 offline;
Rollback segment altered.
SQL> alter rollback segment ROLL302 offline;
Rollback segment altered.
==============================
SQL> drop rollback segment;
SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM from dba_rollback_segs;
SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM dba_rollback_segs;
'DROPROLLBACKSEGMENT'SEGMENT_NAME';'
------------------------------------------------------
drop rollback segment ROLL301;
drop rollback segment ROLL302;
alter tablespace RBS3 offline;
SQL> alter tablespace RBS3 offline;
SQL> alter tablespace RBS2 offline;
drop tablespace RBS2;
Tablespace altered.
Enabling Automatic Undo Management
Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set:
UNDO_MANAGEMENT = AUTO # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION = 900 # The time undo is retained.
# Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin
# SQL statements are issued.
Create Undo Tablespace
SQL> create undo tablespace APPS_UNDOTS1 datafile '/db2/oradata/dev/data/undodbs01.dbf' size 3000M reuse extent management local;
Add Datafile
SQL> ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf' size 3000M;
Add a datafile
ALTER TABLESPACE undotbs_01
ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
Resize an undo datafile
SQL> ALTER DATABASE DATAFILE '/db2/oradata/dev/data/undodbs04.dbf' RESIZE 40000M;
SQL> create undo tablespace APPS_UNDOTS2 datafile '/db2/oradata/dev/data/undodbs04.dbf' size 3000M reuse extent management local;
SQL> ALTER TABLESPACE APPS_UNDOTS2 ADD DATAFILE '/db2/oradata/dev/data/undodbs05.dbf' size 2000M;
Dynamic Parameters.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800;
SQL> ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;
Static Parameters.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
Drop an undo tablespace.
DROP TABLESPACE undotbs_01;
Monitoring
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1
Drop Existing Rollback segments and create new UNDO tablespace
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
ROLL301 RBS3
SQL> select file_name,tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS3';
SQL> select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;
SEGMENT_NAME
============
alter rollback segment ROLL301 offline;
alter rollback segment ROLL302 offline;
alter rollback segment ROLL303 offline;
SQL> alter rollback segment ROLL301 offline;
Rollback segment altered.
SQL> alter rollback segment ROLL302 offline;
Rollback segment altered.
==============================
SQL> drop rollback segment
SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM from dba_rollback_segs;
SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM dba_rollback_segs;
'DROPROLLBACKSEGMENT'SEGMENT_NAME';'
------------------------------------------------------
drop rollback segment ROLL301;
drop rollback segment ROLL302;
alter tablespace RBS3 offline;
SQL> alter tablespace RBS3 offline;
SQL> alter tablespace RBS2 offline;
drop tablespace RBS2;
Tablespace altered.
Enabling Automatic Undo Management
Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set:
UNDO_MANAGEMENT = AUTO # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION = 900 # The time undo is retained.
# Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin
# SQL statements are issued.
Create Undo Tablespace
SQL> create undo tablespace APPS_UNDOTS1 datafile '/db2/oradata/dev/data/undodbs01.dbf' size 3000M reuse extent management local;
Add Datafile
SQL> ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf' size 3000M;
Add a datafile
ALTER TABLESPACE undotbs_01
ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
Resize an undo datafile
SQL> ALTER DATABASE DATAFILE '/db2/oradata/dev/data/undodbs04.dbf' RESIZE 40000M;
SQL> create undo tablespace APPS_UNDOTS2 datafile '/db2/oradata/dev/data/undodbs04.dbf' size 3000M reuse extent management local;
SQL> ALTER TABLESPACE APPS_UNDOTS2 ADD DATAFILE '/db2/oradata/dev/data/undodbs05.dbf' size 2000M;
Dynamic Parameters.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800;
SQL> ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;
Static Parameters.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
Drop an undo tablespace.
DROP TABLESPACE undotbs_01;
Monitoring
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1
How To Use Temporary Tablespaces
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.
Each database should have one temporary tablespace that is created when the database is created. You can create, drop and manage tablespaces with create temporary tablespace, drop temporary tablespace and alter temporary tablespace commands.
Allocate temporary tablespace to each user in the daabase, so we can avoid from sort space in the System tablespace.
SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;
You can remove a TEMPFILE from a database.
SQL> ALTER DATABASE TEMPFILE '/db2/oradata/dev/data/temp02.dbf' DROP INCLUDING DATAFILES;
If you remove all tempfiles from a temporary tablespace, you may encounter error:
ORA-25153: Temporary Tablespace is Empty. So add a TEMPFILE to a temporary tablespace:
SQL>ALTER TABLESPACE temp ADD TEMPFILE '/db2/oradata/dev/data/temp002.dbf' SIZE 200M;
SQL> ALTER TABLESPACE temp OFFLINE
SQL> DROP TABLESPACE temp;
HOW TO create Temporary Tablespaces?
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db2/oradata/dev/data/temp01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
Add Temp Datafiles
SQL> ALTER TABLESPACE temp
ADD TEMPFILE '/db2/oradata/dev/data/temp02.dbf' SIZE 2000M REUSE;
How to Set Default Temporary Tablespaces
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
Monitoring Temporary Tablespaces and Sorting
Ttempfiles are not listed in V$DATAFILE and DBA_DATA_FILES
Use V$TEMPFILE and DBA_TEMP_FILES.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP 328204288 1819279360
TEMP 332398592 1815085056
TEMP 317718528 1829765120
Each database should have one temporary tablespace that is created when the database is created. You can create, drop and manage tablespaces with create temporary tablespace, drop temporary tablespace and alter temporary tablespace commands.
Allocate temporary tablespace to each user in the daabase, so we can avoid from sort space in the System tablespace.
SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;
You can remove a TEMPFILE from a database.
SQL> ALTER DATABASE TEMPFILE '/db2/oradata/dev/data/temp02.dbf' DROP INCLUDING DATAFILES;
If you remove all tempfiles from a temporary tablespace, you may encounter error:
ORA-25153: Temporary Tablespace is Empty. So add a TEMPFILE to a temporary tablespace:
SQL>ALTER TABLESPACE temp ADD TEMPFILE '/db2/oradata/dev/data/temp002.dbf' SIZE 200M;
SQL> ALTER TABLESPACE temp OFFLINE
SQL> DROP TABLESPACE temp;
HOW TO create Temporary Tablespaces?
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db2/oradata/dev/data/temp01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
Add Temp Datafiles
SQL> ALTER TABLESPACE temp
ADD TEMPFILE '/db2/oradata/dev/data/temp02.dbf' SIZE 2000M REUSE;
How to Set Default Temporary Tablespaces
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
Monitoring Temporary Tablespaces and Sorting
Ttempfiles are not listed in V$DATAFILE and DBA_DATA_FILES
Use V$TEMPFILE and DBA_TEMP_FILES.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP 328204288 1819279360
TEMP 332398592 1815085056
TEMP 317718528 1829765120
Create a password file
Enable SYSDBA remote login.
Set: remote_login_passwordfile= EXCLUSIVE
If this fails, check the passwordfile as follows:
1. Ensure REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set in the init.ora for the database.
2. Create a password file:
Unix: $
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=first entries=5
Windows:
C:\> orapwd file=%ORACLE_HOME%\database\pwd%ORACLE_SID% password= fist entries=5
3. . To synchronize the password for sys for normal connections and connections as sysdba connect as a sysdba user and reset the sys password:
$ sqlplus "/ as sysdba" SQL> ALTER USER SYS IDENTIFIED BY change_on_install;
Set: remote_login_passwordfile= EXCLUSIVE
If this fails, check the passwordfile as follows:
1. Ensure REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set in the init.ora for the database.
2. Create a password file:
Unix: $
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=first entries=5
Windows:
C:\> orapwd file=%ORACLE_HOME%\database\pwd%ORACLE_SID% password= fist entries=5
3. . To synchronize the password for sys for normal connections and connections as sysdba connect as a sysdba user and reset the sys password:
$ sqlplus "/ as sysdba" SQL> ALTER USER SYS IDENTIFIED BY change_on_install;
Create StatsPack and How does one use it
SQL> connect sys as sysdba
CREATE TABLESPACE
CREATE TABLESPACE perfstat
DATAFILE ‘/oracle/app/oracle/visdata/perfstat01.dbf’ SIZE 500m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k;
Drop Existing statspack
sqlplus "/ as sysdba" @spdrop.sql
Create New Statspack
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
SQL> @spcreate.sql
TABLESPACE_NAME CONTENTS
------------------------------ ---------
ODM PERMANENT
OLAP PERMANENT
OWAPUB PERMANENT
PERFSTAT PERMANENT
PORTAL PERMANENT
SYNCSERVER PERMANENT
TEMP TEMPORARY
TEST PERMANENT
XYZ PERMANENT
20 rows selected.
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: PERFSTAT
Using PERFSTAT for the default tablespace
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: TEMP
Use Statspack:
[oracle@sys4 admin]$ sqlplus perfstat/perfstat
Take a performance snapshots
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
SNAP_ID SNAP_TIME
---------- ---------
1 04-JAN-08
2 04-JAN-08
Enter two snapshot id's for difference report
SQL> @spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
190608494 VIS 1 VIS
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
190608494 1 VIS VIS sys4.doyen.i
n
Using 190608494 for database Id
Using 1 for instance number
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
VIS VIS 1 04 Jan 2008 12:31 5
2 04 Jan 2008 12:35 5
Enter Snapshot ID
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: report_0408
Staspack output
-rw-r--r-- 1 oracle dba 77294 Jan 4 12:37 report_0408.lst
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
CREATE TABLESPACE
CREATE TABLESPACE perfstat
DATAFILE ‘/oracle/app/oracle/visdata/perfstat01.dbf’ SIZE 500m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500k;
Drop Existing statspack
sqlplus "/ as sysdba" @spdrop.sql
Create New Statspack
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
SQL> @spcreate.sql
TABLESPACE_NAME CONTENTS
------------------------------ ---------
ODM PERMANENT
OLAP PERMANENT
OWAPUB PERMANENT
PERFSTAT PERMANENT
PORTAL PERMANENT
SYNCSERVER PERMANENT
TEMP TEMPORARY
TEST PERMANENT
XYZ PERMANENT
20 rows selected.
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: PERFSTAT
Using PERFSTAT for the default tablespace
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: TEMP
Use Statspack:
[oracle@sys4 admin]$ sqlplus perfstat/perfstat
Take a performance snapshots
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
SNAP_ID SNAP_TIME
---------- ---------
1 04-JAN-08
2 04-JAN-08
Enter two snapshot id's for difference report
SQL> @spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
190608494 VIS 1 VIS
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
190608494 1 VIS VIS sys4.doyen.i
n
Using 190608494 for database Id
Using 1 for instance number
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
VIS VIS 1 04 Jan 2008 12:31 5
2 04 Jan 2008 12:35 5
Enter Snapshot ID
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press
Enter value for report_name: report_0408
Staspack output
-rw-r--r-- 1 oracle dba 77294 Jan 4 12:37 report_0408.lst
SQL> !pwd
/oracle/app/oracle/visdb/9.2.0/rdbms/admin
How to enable trace in Oracle
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
sql_trace = TRUE
to disable trace:
sql_trace = FALSE
- or -
to enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
to stop trace run:
SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;
2. Enable trace at session level
to start trace:
ALTER SESSION SET sql_trace = TRUE;
to stop trace:
ALTER SESSION SET sql_trace = FALSE;
- or -
EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);
- or -
EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;
3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
SELECT * FROM v$session WHERE osuser = OSUSER;
to start trace:
EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);
to stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);
- or -
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);
SQL> select sid, serial# from v$session where username = 'USER';
SQL> alter system kill session 'SID,SERIAL#';
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
sql_trace = TRUE
to disable trace:
sql_trace = FALSE
- or -
to enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
to stop trace run:
SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;
2. Enable trace at session level
to start trace:
ALTER SESSION SET sql_trace = TRUE;
to stop trace:
ALTER SESSION SET sql_trace = FALSE;
- or -
EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);
- or -
EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;
3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
SELECT * FROM v$session WHERE osuser = OSUSER;
to start trace:
EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);
to stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);
- or -
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);
SQL> select sid, serial# from v$session where username = 'USER';
SQL> alter system kill session 'SID,SERIAL#';
Backup & Recovery with RMAN
Agenda
• Types of failures and backups in Oracle
• RMAN Architecture
• Manual vs. RMAN backups
• On-tape backups with RMAN
• RMAN Configuration
• RMAN backup strategies
• RMAN backups Syntax
• Exemplary recovery scanarios
Types of failures
• Instance Failure
– Usually connected with an Oracle process failure
• Media Failure
– Disk failure, storage array controller failure etc.
• Human error
– In most cases accidentally deleted/updated data
– Database user or DBA
• Disaster
– Fire, flood, earthquake, plane crash etc.
Backup options in Oracle
• Physical backups
– Cold (off-line) backups
• Full database only
• Require downtime
– Hot (on-line) backups
• Different types of backups: full, incr. (cumulative, differential), archivelogs
• cumulative backup: which backs up all blocks changed after the most recent incremental backup at level 0
• differential backup: which backs up all blocks changed after the most recent incremental backup at level 1 or 0
• Different scopes: full database, tablespace(s) or datafile(s)
• Do not require database downtime
• Can be used to recover full database, single/multiple tablespace(s)/datafile(s) or a corrupted block
• Database can be recovered to any point in time within assumed backup retention period
RMAN architecture
Types of RMAN hot backups
• Copy or backupset
• Full database backup
• Incremental backups (in 10g 2 levels available: 0 and 1)
– Cumulative, differential
• Archivelog backups
• Tablespace(s), datafile(s) backups
Manual vs. RMAN backups
• RMAN advantages:
– Supports incremental backup strategies
– RMAN on-line backups are not so heavy for the system as manual on-line backups
– RMAN can detect corrupted blocks
– RMAN automatically track database structure changes
– Provides easy, automated backup, restore and recovery operations
– Keeps invenotory of taken backups
– Can seamlessly work with third party media managers
• Disadvantage: something new to learn
– RMAN concepts and command syntax sometimes are not intuitive
On-tape backups with RMAN
• RMAN allows to take on-disk backups out of the box
– Flash recovery area, if configured, further simplifies such backups
– On disk backups are interesting but usually not sufficient for a disaster recovery
• On-disk backups can be manually sent to tapes
– Recovery can be very troublesome
• RMAN can seamlessly work with third party Media Managers
– Media Manager Library (MML) is required
– Different configuration tasks for different MMLs
• Many vendors of Media Management software provide MMLs
• Most popular are:
– Tivoli Storage Manager
– Veritas NetBackup
RMAN Configuration
• RMAN can be preconfigured
– Configuration is stored in the control file and in the recovery catalog (if used)
– Can facilitate backup automation
• Most useful settings:
RMAN Configuration
• Example:
configure RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
configure DEFAULT DEVICE TYPE TO 'sbt';
configure DEVICE TYPE 'sbt' PARALLELISM 2;
configure CHANNEL DEVICE TYPE ‘sbt’ parms='ENV=
(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)';
configure DEVICE TYPE DISK PARALLELISM 2;
configure MAXSETSIZE TO 200 G;
configure archivelog backup copies for device type 'sbt' to 1;
configure controlfile autobackup on;
• The SHOW ALL command lists all RMAN configuration settings
• To clear a given settings append CLEAR at the end of the CONFIGURE command
RMAN backup strategies
• RMAN allows many types of backups
• It possible to build own backup strategy that suits given database best
• Both Oracle-recommended strategies implemented for all production systems
• Incremental backup strategy:
– Backups go to tapes
– Weekly or biweekly level 0 backups (depending on the DB size)
– A level 1 cumulative backup inbetween
– Daily incremental level 1 differential backups
– Archivelog backup every 30 minutes
• Incrementally updated DB copy strategy:
– daily incremental differential backups applied with 2 days of delay
– Copies, incremental backups and archived redo logs stored in the Flash Recovery Area
Backup operations
RMAN> RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK/SBT
FORMAT '/u01/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
}
Backup operations
Backup operations
RMAN> backup as copy database;
RMAN> backup copy of database;
RMAN> backup database;
RMAN> configure device type disk backup type to compressed backupset;
RMAN> backup as compressed backupset full database plus archivelog;
Complete database recovery
• Needed when:
– All datafiles are lost or the SYSTEM tablespace datafiles are lost
– At least one member of each redo log group survived
• Requires:
– Control file recovery (if it’s lost)
– Datafile restore from a backup
– Database recovery using incremental backups and/or archived redo logs and online redo logs
Database point-in-time recovery
• Needed when:
– all datafiles are lost
– All copies of the current control file are lost
– Or all online redo log group members are lost
• If done after a disaster it has to be preceded by:
– Hardware configuration
– OS and Oracle software installation
– Re-creation or restore from non-RMAN backup of listener.ora, tnsnames.ora and other important configuration files
– ASM instance and diskgroup configuration (if needed)
– MML installation and configuration
• Requires
– Spfile restore
– Controlfile restore
– Datafiles restore and recovery
Recovery
Tablespace point-in-time recovery
• Needed
– Mainly to address a human error
• Oracle makes efforts to automate it
– Can be done with few clicks in OEM
• Requires
– Point in time recovery of the whole database Export/import of selected tablespaces schemas or objects
Block media recovery
• Needed when:
– Database reports either single or multi block corruption
• Can be done with an open database
• Database corruptions can be discovered with RMAN backup validate database command
• Corrupted blocks can be found in V$DATABASE_BLOCK_CORRUPTION
Single/multiple datafile/tablespace recovery
• Needed when
– Single/multiple tablespaces or datafiles have been lost
– SYSTEM tablespace is intact
– Controlfiles and online redo logs are intact
• Requires
– To put offline datafiles and tablespaces being recovered
– The database can be open and available to users
v$views
• V$BACKUP_ARCHIVELOG_DETAILS
• V$BACKUP_ARCHIVELOG_SUMMARY
• V$BACKUP_CONTROLFILE_DETAILS
• V$BACKUP_CONTROLFILE_SUMMARY
• V$BACKUP_COPY_DETAILS
• V$BACKUP_COPY_SUMMARY
• V$BACKUP_PIECE_DETAILS
• V$RMAN_BACKUP_JOB_DETAILS
• V$RMAN_BACKUP_TYPE
• V$FLASH_RECOVERY_AREA_USAGE
v$views
DBA_HIST_INSTANCE_RECOVERY
DBA_RECOVERABLE_SCRIPT
DBA_RECOVERABLE_SCRIPT_BLOCKS
DBA_RECOVERABLE_SCRIPT_ERRORS
DBA_RECOVERABLE_SCRIPT_PARAMS
GV_$INSTANCE_RECOVERY
GV_$RECOVER_FILE
GV_$RECOVERY_FILE_STATUS
GV_$RECOVERY_LOG
GV_$RECOVERY_PROGRESS
GV_$RECOVERY_STATUS
V_$FLASH_RECOVERY_AREA_USAGE
V_$INSTANCE_RECOVERY
V_$RECOVER_FILE
V_$RECOVERY_FILE_DEST
V_$RECOVERY_FILE_STATUS
V_$RECOVERY_LOG
V_$RECOVERY_PROGRESS
V_$RECOVERY_STATUS
Complete Restore / Recover Syntax
ORACLE_SID=TARGBD
Export ORACLE_SID
rman target rman/rman
RMAN> set dbid=1138590899
Executing command: SET DBID
RMAN> startup nomount
Oracle instance started
RMAN> set controlfile autobackup format for device type disk to 'c:\backup\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;
RMAN> mount database;
database mounted
RMAN> restore database;
Starting restore at …..
RMAN> recover database;
Starting recover at ….
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
Block Media Recovery
Oracle introduced the ability to perform block level recovery in 9i. The following syntax can be used to perform block level recovery:
RMAN> blockrecover datafile 1 block 2;
OR
run {
allocate channel c1 device type disk|sbt;
blockrecover datafile 1 block 2;
}
Note these restrictions of block media recovery:
• You can only perform block media recovery with RMAN. No SQL*Plus recovery interface is available.
• You can only perform complete recovery of individual blocks. In other words, you cannot stop recovery before all redo has been applied to the block.
• You can only recover blocks marked media corrupt. The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were marked corrupt since the most recent BACKUP or BACKUP ... VALIDATE command was run against the file.
• You must have a full RMAN backup. Incremental backups are not allowed. Note that Block media recovery is able to restore blocks from parent incarnation backups and recover the corrupted blocks through a RESETLOGS.
• Blocks that are marked media corrupt are not accessible to users until recovery is complete. Any attempt to use a block undergoing media recovery results in an error message indicating that the block is media corrupt.
Export ORACLE_SID
rman target rman/rman
RMAN> set dbid=1138590899
Executing command: SET DBID
RMAN> startup nomount
Oracle instance started
RMAN> set controlfile autobackup format for device type disk to 'c:\backup\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;
RMAN> mount database;
database mounted
RMAN> restore database;
Starting restore at …..
RMAN> recover database;
Starting recover at ….
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
Block Media Recovery
Oracle introduced the ability to perform block level recovery in 9i. The following syntax can be used to perform block level recovery:
RMAN> blockrecover datafile 1 block 2;
OR
run {
allocate channel c1 device type disk|sbt;
blockrecover datafile 1 block 2;
}
Note these restrictions of block media recovery:
• You can only perform block media recovery with RMAN. No SQL*Plus recovery interface is available.
• You can only perform complete recovery of individual blocks. In other words, you cannot stop recovery before all redo has been applied to the block.
• You can only recover blocks marked media corrupt. The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were marked corrupt since the most recent BACKUP or BACKUP ... VALIDATE command was run against the file.
• You must have a full RMAN backup. Incremental backups are not allowed. Note that Block media recovery is able to restore blocks from parent incarnation backups and recover the corrupted blocks through a RESETLOGS.
• Blocks that are marked media corrupt are not accessible to users until recovery is complete. Any attempt to use a block undergoing media recovery results in an error message indicating that the block is media corrupt.
Oracle Data Pump in Oracle Database 10g
Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.
For the examples to work we must first unlock the SchemaUSER_NAME account and create a directory object it can access:
CONN sys/password@SID_NAME AS SYSDBA
ALTER USER U_NAME IDENTIFIED BY PASSWD ACCOUNT UNLOCK;
GRANT CREATE ANY DIRECTORY TO ;
CREATE OR REPLACE DIRECTORY DIR_NAME AS '/u05/oradata/';
GRANT READ, WRITE ON DIRECTORY DIR_NAME TO SCHEMA_NAME;
Table Exports/Imports
The TABLES parameter is used to identify the tables that are to be exported. The following is an example of the table export and import syntax:
expdp USER_NAME/PASSWD@SID_NAME tables=TABLE_NAME1, TABLE_NAME2, .. n directory=DIR_NAME dumpfile= DUMPFILE_NAME1.dmp logfile=LOG_FILENAME.log
impdp USER_NAME/PASSWD@SID_NAME tables=TABLE_NAME1, TABLE_NAME2, .. n directory=DIR_NAME dumpfile=DUMPFILE_NAME1.dmp logfile=LOG_FILENAME.log
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to identify the schemas to be exported. The following is an example of the schema export and import syntax:
expdp USER_NAME/PASSWD@SID_NAME schemas=SCHEMA_NAME directory=DIR_NAME dumpfile=INV.dmp logfile=EXPINV.log
impdp USER_NAME/PASSWD@SID_NAME schemas=SCHEMA_NAME directory=DIR_NAME dumpfile=INV.dmp logfile=IMPINV.log
Full Database Exports/Imports
The whole database export is mandatory. The following is an example of the full database export and import syntax:
expdp sys/password@SID_NAME full=Y directory=DIR_NAME dumpfile=FULLDB.dmp logfile=FULLDB10G.log
impdp sys/password@SID_NAME full=Y directory=DIR_NAME dumpfile=FULLDB.dmp logfile=FULLDB10G.log
The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:
SQL select * from dba_datapump_jobs;
expdp help=y
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
ADD_FILE=dumpfile-name
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
impdp help=y
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply (Y/N) to specific objects.
Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.
ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
For the examples to work we must first unlock the SchemaUSER_NAME account and create a directory object it can access:
CONN sys/password@SID_NAME AS SYSDBA
ALTER USER U_NAME IDENTIFIED BY PASSWD ACCOUNT UNLOCK;
GRANT CREATE ANY DIRECTORY TO ;
CREATE OR REPLACE DIRECTORY DIR_NAME AS '/u05/oradata/';
GRANT READ, WRITE ON DIRECTORY DIR_NAME TO SCHEMA_NAME;
Table Exports/Imports
The TABLES parameter is used to identify the tables that are to be exported. The following is an example of the table export and import syntax:
expdp USER_NAME/PASSWD@SID_NAME tables=TABLE_NAME1, TABLE_NAME2, .. n directory=DIR_NAME dumpfile= DUMPFILE_NAME1.dmp logfile=LOG_FILENAME.log
impdp USER_NAME/PASSWD@SID_NAME tables=TABLE_NAME1, TABLE_NAME2, .. n directory=DIR_NAME dumpfile=DUMPFILE_NAME1.dmp logfile=LOG_FILENAME.log
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to identify the schemas to be exported. The following is an example of the schema export and import syntax:
expdp USER_NAME/PASSWD@SID_NAME schemas=SCHEMA_NAME directory=DIR_NAME dumpfile=INV.dmp logfile=EXPINV.log
impdp USER_NAME/PASSWD@SID_NAME schemas=SCHEMA_NAME directory=DIR_NAME dumpfile=INV.dmp logfile=IMPINV.log
Full Database Exports/Imports
The whole database export is mandatory. The following is an example of the full database export and import syntax:
expdp sys/password@SID_NAME full=Y directory=DIR_NAME dumpfile=FULLDB.dmp logfile=FULLDB10G.log
impdp sys/password@SID_NAME full=Y directory=DIR_NAME dumpfile=FULLDB.dmp logfile=FULLDB10G.log
The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:
SQL select * from dba_datapump_jobs;
expdp help=y
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
ADD_FILE=dumpfile-name
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.
impdp help=y
Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply (Y/N) to specific objects.
Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.
ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
The following commands are valid while in interactive mode.
Note: abbreviations are allowed
Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Procedure to enable SQL trace for users on your database
What is tkprof
tkprof is one of the most helpful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.
Analyzing Results
· Compare the number of parses to number of executions.
· Search for SQL statements that do not use bind variables
· Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption.
1. Find the User Dump Directory
SQL> select value from v$parameter where name = 'user_dump_dest';
2. Get the SID and SERIAL# for the process you want to trace.
SQL> select sid, serial# from sys.v_$session
SID SERIAL#
---------- ----------
8 13607
3. Enable tracing for your selected process:
SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, true);
4. Ask user to run just the necessary to demonstrate his problem.
5. Disable tracing for your selected process:
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);
SQL> ALTER SYSTEM SET TIMED_STATISTICS = FALSE;
6. Look for trace file in USER_DUMP_DEST
$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc
7. Run TKPROF to analyse trace output
$ tkprof ora_9294.trc OUTPUT=ora_9294.lst EXPLAIN=SCHEMA_NAME/PASSWD
8. View/print output
tkprof is one of the most helpful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.
Analyzing Results
· Compare the number of parses to number of executions.
· Search for SQL statements that do not use bind variables
· Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption.
1. Find the User Dump Directory
SQL> select value from v$parameter where name = 'user_dump_dest';
2. Get the SID and SERIAL# for the process you want to trace.
SQL> select sid, serial# from sys.v_$session
SID SERIAL#
---------- ----------
8 13607
3. Enable tracing for your selected process:
SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, true);
4. Ask user to run just the necessary to demonstrate his problem.
5. Disable tracing for your selected process:
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);
SQL> ALTER SYSTEM SET TIMED_STATISTICS = FALSE;
6. Look for trace file in USER_DUMP_DEST
$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc
7. Run TKPROF to analyse trace output
$ tkprof ora_9294.trc OUTPUT=ora_9294.lst EXPLAIN=SCHEMA_NAME/PASSWD
8. View/print output
Oracle9i Release 2: Using the DBNEWID Utility
Only the DBID of a database
Only the DBNAME of a database
1. Make a whole database backup.
2. Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3. Issue the following query to determine the current DBID:
SELECT dbid, name FROM v$database;
4. Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:
SHUTDOWN IMMEDIATE OR NORMAL
5. Start the instance and mount the database, specifying the parameter file if you are not using a server parameter file or the text initialization parameter file is not in the default location:
STARTUP MOUNT
6. Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. The DBNEWID utility performs validations of the headers of the data files and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation unless you specify a log file, changes the DBID for each data file (including offline normal and read-only data files), and then exits. The database is left mounted but is not yet usable.
nid TARGET=SYS/secure@
i.e: nid TARGET=SYS/PASSWORD@TEST
7. After DBNEWID successfully changes the DBID, shut down the instance:
SHUTDOWN IMMEDIATE OR NORMAL
8. Create a new password file using the ORAPWD utility:
orapwd file=orapw
9. Start the instance and mount the database:
STARTUP MOUNT;
10. Open the database with the RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
11. Verify the change to the DBID by issuing the following query:
SELECT dbid, name FROM v$database;
Changing Your Database Name (DBNAME)
1. Make a whole database backup.
2. Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3. Issue the following query to determine the current DBID:
SELECT dbid, name FROM v$database;
4. Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:
SHUTDOWN IMMEDIATE OR NORMAL
5. Start the instance and mount the database, specifying the parameter file if you are not using a server parameter file or the text initialization parameter file is not in the default location:
STARTUP MOUNT
6. Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify the DBNAME parameter and supply your new database name. You must also specify the YES value for the SETNAME parameter to indicate that only the DBNAME is to be changed. DBNEWID performs validations of the headers of the control files, but not the data files, before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, and exits. After DBNEWID completes successfully, the database is left mounted but is not yet usable.
nid TARGET=
i.e: nid TARGET=SYS/PASSWORD@TEST DBNAME=DEVEL SETNAME=YES
7. After DBNEWID successfully changes the DBID, shut down the instance:
SHUTDOWN IMMEDIATE OR NORMAL
8. Create a new password file using the ORAPWD utility:
orapwd file=orapw
9. Change the DB_NAME initialization parameter to your new database name
10. Start the instance and mount the database:
STARTUP
11. Verify the change to the DBID by issuing the following query:
SELECT dbid, name FROM v$database;
Subscribe to:
Posts (Atom)
