Category: Uncategorized

 
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF bytes space ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF bytes space ON report
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(LPAD(db_files.type, 18), '') type
, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path
, volume_files.bytes
, volume_files.space
, NVL(LPAD(volume_files.type, 18), '') type
, volume_files.creation_date
, volume_files.disk_group_name
, null
FROM
( SELECT
g.name disk_group_name
, v.volume_name volume_name
, v.volume_device volume_device
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM v$asm_file f RIGHT OUTER JOIN v$asm_volume v USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) volume_files
WHERE volume_files.type IS NOT NULL ; 

 
SET ECHO off
SET FEEDBACK 8
SET HEADING on
SET LINESIZE 230
SET PAGESIZE 300
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a30 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY a.name ;

 

 
SET LINESIZE 230
 SET PAGESIZE 500
 SET VERIFY off
 COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
 COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
 COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
 COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
 COLUMN state FORMAT a11 HEAD 'State'
 COLUMN type FORMAT a6 HEAD 'Type'
 COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
 COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
 COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
 break on report on disk_group_name skip 1
 compute sum label "Grand Total: " of total_mb used_mb on report

 SELECT
 name group_name
 , sector_size sector_size
 , block_size block_size
 , allocation_unit_size allocation_unit_size
 , state state
 , type type
 , total_mb total_mb
 , (total_mb - free_mb) used_mb
 , ROUND((1- (free_mb / total_mb))*100, 2) pct_used
 FROM v$asm_diskgroup ORDER BY name ; 
 

 
SET LINESIZE 230
SET PAGESIZE 2300
COLUMN username FORMAT A25
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A25
SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser ; 

Sample Output:

Script will list below information with comments

Dictionary Cache Hit Ratio
Library Cache Hit Ratio
DB Block Buffer Cache Hit Ratio
Latch Hit Ratio
Disk Sort Ratio
Rollback Segment Waits
Dispatcher Workload

 
SET SERVEROUTPUT ON
SET LINESIZE 280
SET FEEDBACK OFF
SELECT *
FROM v$database;
PROMPT
DECLARE
v_value NUMBER;
FUNCTION Format(p_value IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';
END;
BEGIN
-- --------------------------
-- Dictionary Cache Hit Ratio
-- --------------------------
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
INTO v_value
FROM v$rowcache;
DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));
IF v_value < 90 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -----------------------
-- Library Cache Hit Ratio
-- -----------------------
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
INTO v_value
FROM v$librarycache;
DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));
IF v_value < 99 THEN
DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------------------
-- DB Block Buffer Cache Hit Ratio
-- -------------------------------
SELECT (1 - (phys.value / (db.value + cons.value))) * 100
INTO v_value
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';
DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));
IF v_value < 89 THEN
DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ---------------
-- Latch Hit Ratio
-- ---------------
SELECT (1 - (Sum(misses) / Sum(gets))) * 100
INTO v_value
FROM v$latch;
DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));
IF v_value < 98 THEN DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%'); ELSE DBMS_Output.Put_Line('Value Acceptable.'); END IF; -- ----------------------- -- Disk Sort Ratio -- ----------------------- SELECT (disk.value/mem.value) * 100 INTO v_value FROM v$sysstat disk, v$sysstat mem WHERE disk.name = 'sorts (disk)' AND mem.name = 'sorts (memory)'; DBMS_Output.Put('Disk Sort Ratio : ' || Format(v_value)); IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- ----------------------
-- Rollback Segment Waits
-- ----------------------
SELECT (Sum(waits) / Sum(gets)) * 100
INTO v_value
FROM v$rollstat;
DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));
IF v_value > 5 THEN
DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
-- -------------------
-- Dispatcher Workload
-- -------------------
SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
INTO v_value
FROM v$dispatcher;
DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));
IF v_value > 50 THEN
DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
ELSE
DBMS_Output.Put_Line('Value Acceptable.');
END IF;
END;
/ 

Different options for expdp/impdp.

As you know expdp is a logical backup not the physical one. It works in same version also you can expdp/impdp from lower to higher version. Oracle is very restricted expdp/impdp from higher to lower version.

There are few important things you should keep in mind.

1. Create directory, if already not, and ensure relevant user got grant to read, write the directory. This directory will be used to store the dump and log file.

CREATE OR REPLACE DIRECTORY DIR_NAME AS ‘/ora01/db/oracle/’;
GRANT READ, WRITE ON DIRECTORY DIR_NAME TO scott;

2. Whenever you need to export online database/schema ensure you will use CONSISTENT=Y (in 11.2). If need you can use SCN or time using option FLASHBACK_TIME.

Check SCN/TIMESTAMP:

OR use below syntax. It will ensure your expdp will be consistent.
Note: It has been used many time successfully.

 
expdp \"/ as sysdba\" FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" 

1. Estimate the dump size without real export.

 
expdp \'/ as sysdba\' directory=DATAPUMP_DIR full=y ESTIMATE_ONLY=y 

2. Export/Import without password.

Note: With Flashback option.


 
expdp \"/ as sysdba\" FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" directory=DIR_NAME full=y dumpfile=full_DB.dmp logfile=full_db.log compression=all 

Note: Without flashback option

 
expdp \'/ as sysdba\' directory=DATAPUMP_DIR full=y 

3. Database full export and import.

 
expdp user/pwd DIRECTORY=DIR_NAME DUMPFILE=full_db.dmp logfile=full_db.log FULL=y 

4. Schema/s export and import.

 
expdp user/pwd DIRECTORY=DIR_NAME DUMPFILE=full_db.dmp logfile=full_db.log schemas=SCHEMA1,SCHEMA2 

5. Check the progress of export/import.

 
column owner_name format a10
select * from dba_datapump_jobs; 

Sample Output:

 
set linesize 320
column username format a10
select t.username,
t.START_TIME, t.LAST_UPDATE_TIME 'YYYY-MM-DD HH:MM:SS', t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.sofar,t.totalwork from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT' ; 

Sample Output:

6. Transportable tablespace export and import.

Notes:
1. You must make the tablespace/s read-only before export.
2. You should ‘TRANSPORT_FULL_CHECK=Y’ to ensure tablespaces are self-contained. Means there is no other tablespace holding dependant object/s.

 
ALTER TABLESPACE tblspace1 READ ONLY ;
ALTER TABLESPACE tblspace2 READ ONLY ;
EXPDP system/password DIRECTORY =DIR_NAME DUMPFILE=trns_tbl.dmp LOGFILE=trns_tbl.log TRANSPORT_TABLESPACES=tblspace1,tblspace2 TRANSPORT_FULL_CHECK=Y
ALTER TABLESPACE tblspace1 READ WRITE ;
ALTER TABLESPACE tblspace2 READ WRITE ; 

7. Table export and import.

Export:

 
expdp scott/tiger tables=EMP,DEPT directory=DIR_NAME dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log 

Import:

 
impdp scott/tiger tables=EMP,DEPT directory=DIR_NAME dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log 

8. Parallel export and import to improve the performance.

Export:

 
expdp scott/tiger directory=DIR_NAME parallel=4 dumpfile=fulldb_%U.dmp logfile=exfulldb.log full=y 

Import:

 
impdp scott/tiger directory=DIR_NAME parallel=4 dumpfile=fulldb_%U.dmp logfile=imfulldb.log 

9. EXCLUDE tables and schemas from a full database export/import.

Notes:
1. You need not prefix the owner name. If you put the OWNER.TABLE_NAME it would not work.
2. It will EXCLUDE all tables mentioned in the list even if more than one owner has the same object name.
For example: If TABLE_NAME1 table is owned by USER1 and USER2 its table will be EXCLUDED from both.
3. Below command will work only in the parameter file, not in the command line.
expdp_exclude_tables_n_users.par


 
DIRECTORY=DIR_NAME
DUMPFILE=dump_name.dmp
LOGFILE=log_file_name.log
FULL=Y
EXCLUDE=STATISTICS
EXCLUDE=TABLE:"IN ('TABLE_NAME1','TABLE_NAME3','TABLE_NAME3')"
EXCLUDE=SCHEMA:"IN ('SCHEMA1', 'SCHEMA2')"
-- END expdp_exclude_tables_n_users.par 

Command line syntax of above par


 
expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp FULL=Y
EXCLUDE=TABLE:\"IN \(\'TABLE_NAME1\', \'TABLE_NAME2\' , \'TABLE_NAME3\')\"
EXCLUDE=SCHEMA:\"IN \(\'SCHEMA1\', \'SCHEMA2\'\)\" 

expdp
Note: It will create a consistent dumpfile, which can be used to import full database, schema etc.

 
expdp \"/ as sysdba\" FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" directory=DIR_NAME full=y dumpfile=full_DB.dmp logfile=full_db.log compression=all 

impdp
Note: While importing you need not to mention the flashback option.

 
impdp \'/ as sysdba\' directory=DATAPUMP_DIR dumpfile=full_DB.dmp logfile=impfull_db.log full=y 

You must ensure there is enough space to store the dump under the mount point having directory location.

expdp

 
expdp user/pwd DIRECTORY=DIR_NAME DUMPFILE=full_db.dmp logfile=full_db.log FULL=y 

impdp

 
expdp user/pwd DIRECTORY=DIR_NAME DUMPFILE=full_db.dmp logfile=imp_full_db.log FULL=y 

Expdp/impdp estimate the dump size without real export.

 
expdp \'/ as sysdba\' directory=DATAPUMP_DIR full=y ESTIMATE_ONLY=y 

Expdp/impdp directory, SCN, timestamp and consistent

As you know expdp is a logical backup not the physical one. It works in same version also you can expdp/impdp from lower to higher version. Oracle is very restricted expdp/impdp from higher to lower version.

There are few important things you should keep in mind.

1. Create directory, if already not, and ensure relevant user got grant to read, write the directory. This directory will be used to store the dump and log file.

 
CREATE OR REPLACE DIRECTORY DIR_NAME AS ‘/ora01/db/oracle/’;
GRANT READ, WRITE,EXECUTE ON DIRECTORY DIR_NAME TO scott; 

2. Whenever you need to export online database/schema ensure you will use CONSISTENT=Y (in 11.2). If need you can use SCN or time using option FLASHBACK_TIME.

Check SCN/TIMESTAMP:

OR use below syntax. It will ensure your expdp will be consistent.
Note: It has been used many time successfully.

 
expdp \"/ as sysdba\" FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\"