ORACLE TRACE

Remember that trace files are in the user_dump_destination, but for jobs and for shared server configurations they are in background_dump_destination.

Oracle 9i

turn on trace in another session:

EXECUTE dbms_system.set_ev ([sid],[serial#],10046,[level],'');
turn off
EXECUTE dbms_system.set_ev ([sid],[serial#],10046,0,'');

---

Oracle 9i/10g

turn on trace for current session:

ALTER SESSION SET EVENTS '10046 trace name context forever, level [level#]'

• level 0 #off
• level 1 #default
• level 4 #default + bind variable values
• level 8 #default + wait event information
• level 12 #level 4 + level 8

turn off
ALTER SESSION SET EVENTS '10046 trace name context off'

---

Oracle 9i/10g

turn on trace for os process:

ORADEBUG SETOSPID [os process from v$process];
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL [level#];
ORADEBUG TRACEFILE_NAME; --display current tracefile
turn off
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

---

Oracle 9i/10g

turn on trace for current session:

dbms_support package in $ORACLE_HOME/rdbms/admin/dbmssupp.sql
EXEC dbms_support.start_trace(waits=>TRUE, binds=>TRUE);
turn off
EXEC dbms_support.stop_trace;

turn on trace for the other session:

EXEC dbms_support.start_trace_in_session(sid=>[sid], serial=>[serial#], waits=>TRUE, binds=>TRUE);
turn off
EXEC dbms_support.stop_trace_in_session(sid=>[sid], serial=>[serial#]);

---

Oracle 10g

turn on trace for current session:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => [sid],serial_num => [serial#], waits => TRUE,binds => TRUE);
turn off
exec DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id=> [sid],serial_num=> [serial#]);
check:
SELECT sql_trace,sql_trace_waits,sql_trace_binds FROM v$session;

turn on trace for client:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => '[client_name]',waits => TRUE, binds => TRUE);
turn off
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => '[client_name]');

turn on trace at database level:

exec DBMS_MONITOR.DATABASE_TRACE_ENABLE (waits => TRUE,binds => TRUE,instance_name > NULL);
turn off
exec DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name > NULL);
check:
SELECT * FROM dba_enabled_traces;

---

Oracle 11g

turn on trace at component level

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => '[serv_name]', module_name => '[module]',action_name => '[action]',waits => TRUE,binds => FALSE, instance_name => NULL);
turn off
exec DBMS_MONITOR.SERV_MOD_TRACE_DISABLE(service_name => 'serv_name',module_name => 'module',action_name => '[action]',instance_name => NULL);
check: SELECT * FROM dba_enabled_traces;

ORACLE AUDIT

Table of Contents

• 1 Init params
  • 1.1 AUDIT_TRAIL
• 2 Turn on
• 3 VIEWS for SYS.AUD$
• 4 Options
  • 4.1 default audit options
  • 4.2 ALL
  • 4.3 ADDITIONAL
  • 4.4 Objects available to audit
• 5 Truncate audit table
  • 5.1 DBMS_AUDIT_MGMT

1 Init params

initoption	values	desc
AUDIT_TRAIL	NONE/DB/OS/DB,EXTENDED/XML/XML,EXTENDED	turn on audit and set type
AUDIT_FILE_DEST		set directory for audit files when AUDIT_TRAIL=OS, default: $ORACLE_HOME/rdbms/audit
AUDIT_SYS_OPERATIONS	TRUE/FALSE	for sys operations

1.1 AUDIT_TRAIL

• in 11g audyt is set default on DB value

none or false	Auditing is disabled.
db or true	Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended	As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml	Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended	As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os	Auditing is enabled, with all audit records directed to the operating system's audit trail.

2 Turn on

• set param audit_trail

Commands:

AUDIT	turn on audit
NOAUDIT ALL	turn off all audit operations for current user
NOAUDIT ALL BY [username]
NOAUDIT SELECT TABLE BY [username]
AUDIT ALL BY [username] BY ACCESS/SESSION	ACCESS - log everytime the event heppen, SESSION - log only at first time
NOAUDIT TABLE BY [username]
AUDIT select table, insert table, delete table, update table BY [username] BY ACCESS

Views:

STMT_AUDIT_OPTION_MAP	Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
AUDIT_ACTIONS	Contains descriptions for audit trail action type codes
ALL_DEF_AUDIT_OPTS	Contains default object-auditing options that will be applied when objects are created

3 VIEWS for SYS.AUD$

DBA_STMT_AUDIT_OPTS	show running audits for user
DBA_PRIV_AUDIT_OPTS	Describes current system privileges being audited across the system and by user
DBA_OBJ_AUDIT_OPTS	Describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL	Lists all audit trail entries USER view shows audit trail entries relating to current user.
DBA_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
DBA_AUDIT_EXISTS	Lists audit trail entries produced BY AUDIT NOT EXISTS
DBA_AUDIT_SESSION	Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
DBA_AUDIT_OBJECT	Contains audit trail records for all objects in the database. USER view lists audit trail records for statements concerning objects that are accessible to the current user.

• DDL (CREATE, ALTER & DROP of objects)
• DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
• SYSTEM EVENTS (LOGON, LOGOFF etc.)
• SELECT * FROM dba_stmt_audit_opts ORDER BY 1,3;

col obj_name format a30
col owner format a15
col username format a15
SELECT owner,username,obj_name,action_name,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') FROM dba_audit_trail 
  WHERE timestamp >= trunc(sysdate-1) and username='CCI' order by timestamp;
prompt ###zajetosc_tabeli_audytu
SELECT sum(bytes)/1024/1024 as MB FROM dba_segments WHERE segment_name='AUD$';

4 Options

4.1 default audit options

• rdbms/admin/secconf.sql
• rdbms/admin/undoaud.sql #wylaczenie

4.2 ALL

Object	SQL Statements and Operations Audited
ALTER SYSTEM	ALTER SYSTEM
CLUSTER	CREATE, ALTER, DROP, TRUNCATE
CONTEXT	CREATE, DROP
DATABASE LINK	CREATE, ALTER, ALTER PUBLIC DATABASE LINK, DROP DATABASE LINK
DIMENSION	CREATE, ALTER, DROP
DIRECTORY	CREATE, DROP
INDEX	CREATE INDEX, ALTER, ANALYZE INDEX, DROP
MATERIALIZED VIEW	CREATE, ALTER, DROP
NOT EXISTS	All SQL statements that fail because a specified object does not exist.
OUTLINE	CREATE, ALTER, DROP
PROCEDURE	CREATE FUNCTION, CREATE LIBRARY, CREATE PACKAGE, CREATE PACKAGE BODY
	CREATE PROCEDURE, DROP FUNCTION, DROP LIBRARY, DROP PACKAGE, DROP PROCEDURE
PROFILE	CREATE, ALTER, DROP
PUBLIC DATABASE LINK	CREATE, DROP
PUBLIC SYNONYM	CREATE, DROP
ROLE	CREATE, ALTER, DROP, SET
ROLLBACK SEGMENT	CREATE, ALTER, DROP
SEQUENCE	CREATE, DROP
SESSION	Logons
SYNONYM	CREATE, DROP
SYSTEM AUDIT	AUDIT sql_statements, NOAUDIT sql_statements
SYSTEM GRANT	GRANT system_privileges_and_roles, REVOKE system_privileges_and_roles
TABLE	CREATE, DROP,TRUNCATE TABLE
TABLESPACE	CREATE, TABLESPACE, ALTER, DROP
TRIGGER	CREATE, ALTER with ENABLE and DISABLE clauses, DROP, ALTER TABLE with ENABLE ALL TRIGGERS clause and DISABLE ALL TRIGGERS clause
TYPE	CREATE, CREATE TYPE BODY,ALTER,DROP,DROP TYPE BODY
USER	CREATE, ALTER, DROP
VIEW	CREATE, DROP

Notes:

• AUDIT USER #audits three SQL statements: CREATE, ALTER, DROP Use AUDIT ALTER USER to audit statements that require the ALTER USER system privilege. An AUDIT ALTER USER statement does not audit a user changing his or her own password, as this activity does not require the ALTER USER system privilege.

4.3 ADDITIONAL

ALTER SEQUENCE	ALTER SEQUENCE
ALTER TABLE	ALTER TABLE
COMMENT TABLE	COMMENT ON TABLE table, view, materialized view,COMMENT ON COLUMN table.column, view.column, materialized view.column
DELETE TABLE	DELETE FROM table, view
EXECUTE PROCEDURE	CALL
	Execution of any procedure or function or access to any variable, library, or cursor inside a package.
GRANT DIRECTORY	GRANT privilege ON directory,REVOKE privilege ON directory
GRANT PROCEDURE	GRANT privilege ON procedure, function, package,REVOKE privilege ON procedure, function, package
GRANT SEQUENCE	GRANT privilege ON sequence,REVOKE privilege ON sequence
GRANT TABLE	GRANT privilege ON table, view, materialized view,REVOKE privilege ON table, view, materialized view
GRANT TYPE	GRANT privilege ON TYPE,REVOKE privilege ON TYPE
INSERT TABLE	INSERT INTO table, view
LOCK TABLE	LOCK TABLE table, view
SELECT SEQUENCE	Any statement containing sequence.CURRVAL or sequence.NEXTVAL
SELECT TABLE	SELECT FROM table, view, materialized view
UPDATE TABLE	UPDATE table, view

4.4 Objects available to audit

Object	SQL Operations
Table	ALTER, AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE
View	AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INSERT, LOCK, RENAME, SELECT, UPDATE
Sequence	ALTER, AUDIT, GRANT, SELECT
Procedure, Function, Package	AUDIT, EXECUTE,GRANT
Materialized View	ALTER, AUDIT, COMMENT, DELETE, INDEX, INSERT, LOCK, SELECT, UPDATE
Mining Model	AUDIT, COMMENT, GRANT, RENAME, SELECT
Directory	AUDIT, GRANT, READ
Library	EXECUTE, GRANT
Object Type	ALTER, AUDIT, GRANT

5 Truncate audit table

• truncate table SYS.AUD$;

5.1 DBMS_AUDIT_MGMT

• DBA_AUDIT_MGMT_CONFIG_PARAMS;

ORACLE SQLNET

sqlnet.ora
trace_level_server=16 #turn on logging for server (4-USER,10-ADMIN,16-SUPPORT)
trace_level_client=16 #turn on logging for client
trace_directory_server=/tmp/oratrace #logfile dir
trace_directory_client=/tmp/oratrace
trace_file_client=cli #logfile name
trace_file_server=srv
trace_unique_client=true

ORACLE AWR

Table of Contents

• 1 EM
• 2 DescAWR (Automatic Workload Repository)
• 3 Prerequisities
• 4 Views
  • 4.1 Memory:
  • 4.2 Sql:
  • 4.3 Other:
• 5 Snapshots
  • 5.1 Check snapshots time collection
  • 5.2 Set snapshot retention
  • 5.3 Run manual snapshot:
  • 5.4 Drop snapshot:
• 6 Reports
  • 6.1 Generate AWR report:

1 EM

• Oracle Enterprise Manager => Advisor Central

2 DescAWR (Automatic Workload Repository)

• Stats which are stored in tables WRH$ in SYSAUX tablespace

3 Prerequisities

• STATISTICS_LEVEL = TYPICAL | ALL

4 Views

4.1 Memory:

• DBA_HIST_SGA
• DBA_HIST_PGASTAT
• DBA_HIST_PGA_TARGET_DEVICE
• DBA_HIST_SGASTAT
• DBA_HIST_BUFFER_POOL_STAT
• DBA_HIST_DB_CACHE_ADVICE
• DBA_HIST_SHARED_POOL_ADVICE

4.2 Sql:

• DBA_HIST_SQL_SUMMARY
• DBA_HIST_SQLSTAT
• DBA_HIST_SQL_PLAN

4.3 Other:

• DBA_HIST_SYSSTAT
• DBA_HIST_FILESTATXS
• DBA_HIST_WR_CONTROL
• DBA_HIST_SNAPSHOT
• DBA_HIST_DATABASE_INSTANCE
• DBA_HIST_ACTIVE_SESS_HISTORY

5 Snapshots

5.1 Check snapshots time collection

• SELECT * FROM DBA_HIST_WR_CONTROL

5.2 Set snapshot retention

• DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ([retention - min],[interwal - min])

5.3 Run manual snapshot:

• DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

5.4 Drop snapshot:

• DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE([low_id],[end_id]);

6 Reports

6.1 Generate AWR report:

• $oracle_home/rdbms/admin/awrrpt.sql
• $oracle_home/rdbms/admin/awrrpti.sql # with instance
• ADDM reports (Automatic Database Diagnostic Monitor:
• $oracle_home/rdbms/admin/addmrpt.sql

ORACLE RMAN CLONE

how to clone database by RMAN:
it depends on recovering files from previous backup

1. create PFILE for new database based on PFILE from source database
   init[db_new].ora
   main changes:
   
   • db_name
     
   • control_files
     
   • audit_file_dest
     
   • background_dump_dest
     
   • user_dump_dest
     
   • core_dump_dest
     
2. create password file for new database:
   orapwd file=orapw[db_new] password=[pass]
   
   
3. add new database to listener.ora and tnsnames.ora,
   
   
4. create dirs for new database file,
   
   
5. create directories for trace logs as admin with subdirectories: adump, udump, cdump, bdump,
   
   
6. startup new auxiliary database in nomout mode(! then exit from your session because it will be hang on)
   
   
7. start rman with ORACLE_SID = auxiliary database:
   
   
8. at the auxiliary server run command:
   rman auxiliary / target sys/[sys_pass]@[targetdb_tnsalias] catalog rman/[rman_pass]@[catalogdb_tnsalias]
   you should get an output:
   connected to target database: [db_name] (DBID=[db_id])
   connected to recovery catalog database
   connected to auxiliary database: [db_name] (not mounted)




9. run script:
   
   run {
   set newname for datafile 4 to '/oracle/oradata/[db_new]/users01.dbf';
   set newname for datafile 3 to '/oracle/oradata/[db_new]/sysaux01.dbf';
   set newname for datafile 2 to '/oracle/oradata/[db_new]/undotbs01.dbf';
   set newname for datafile 1 to '/oracle/oradata/[db_new]/system01.dbf';
   set newname for datafile 5 to '/oracle/oradata/[db_new]/example01.dbf';
   set newname for tempfile 1 to '/oracle/oradata/[db_new]/temp.dbf';
   duplicate target database to [db_new]
   pfile=/oracle/product/10.2.0/db_1/dbs/init[db_new]aux.ora
   logfile
   '/oracle/oradata/[db_new]/redo01.log' size 50m,
   '/oracle/oradata/[db_new]/redo02.log' size 50m,
   '/oracle/oradata/[db_new]/redo03.log' size 50m;}
   
   to generate script above use sql at source db:
   spool clonescript.rcv
   select 'set newname for datafile '||file_id||' to '''||file_name||''';' from dba_data_files order by file_id;
   select 'set newname for tempfile '||file_id||' to '''||file_name||''';' from dba_temp_files order by file_id;
   spool off;
   and next modify output in vim:
   :% s/ora6\/oradata\/DBSRC/u01\/oradata\/DBAUX\/datafile
   or another script:
   
   run {
   set until time "to_date('2010-07-02:01:00','yyyy-mm-dd:hh24:mi')";
   configure default device type to sbt;
   configure device type sbt parallelism 2;
   configure auxiliary channel 1 device type sbt parms 'ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)';
   configure auxiliary channel 2 device type sbt parms 'ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)';
   duplicate target database to [db_new]
   DB_FILE_NAME_CONVERT=('[target_path1]/','[aux_path]',
   '[target_path2]/','[aux_path]')
   pfile=[pfile_aux_path]/[db_new].ora
   logfile
   '[logfile_aux_path]/redo01.log' size 100m,
   '[logfile_aux_path]/redo02.log' size 100m,
   '[logfile_aux_path]/redo03.log' size 100m;
   }
   

ERRORS:

if you get an output error: "...segmentation fault..." you have to check the alertlog file, and if you get "...database must be open in upgrade mode...", it means that you have tried clone between diferent versions of databases, so you shoud: alter databse recover; alter database open upgrade; @$ORACLE_HOME/rdbms/admin/catupgrd.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql for more read documentation

ORACLE JOBS

Table of Contents

• 1 Views
• 2 Create new job
• 3 Starting job by hand
• 4 Turn off
• 5 Turn on
• 6 Changing job params
• 7 Removing job:
• 8 dbms_ijob
• 9 How to set time:
• 10 ERRORs
  • 10.1 ORA-23421
  • 10.2 ORA-12012

1 Views

• SELECT * FROM dba_jobs
• SELECT * FROM dba_jobs_running
• column TOTAL_TIME determine the time which system spend run this job from first initialization, it is not time of present
• column THIS_DATE is filled when job is still executing and show time present execution time

2 Create new job

declare
  jobnr number;
begin
  dbms_job.submit(
    job=>jobnr,
    what=>'dbms_output.put_line(''ble'');',
    next_date=>to_date('10:00 09/09/2009','HH24:MI DD/MM/YYYY'),
    interval=>'SYSDATE+1');
end;
/

3 Starting job by hand

• must be run by job owner, otherwise ORA-23421, even for user sys, beware on change next_date which will by calculated from now
• sql> exec DBMS_JOB.RUN(JOB => [nr]);

4 Turn off

• set state BROKEN
• sql> exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => TRUE);

5 Turn on

• unset state BROKEN
• sql> exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => FALSE, NEXT_DATE => TO_DATE(’10:00 01/01/09′, ‘HH24:MI MM/DD/YY’));

6 Changing job params

• must be set all arguments, if any is unchange we set NULL:
• sql> exec DBMS_JOB.CHANGE(JOB => [nr], NEXT_DATE => to_date('2011-01-01 10:00','YYYY-MM-DD HH24:MI'), WHAT => null, INTERVAL => ‘SYSDATE + 3′);

7 Removing job:

• exec DBMS_JOB.REMOVE(JOB => [nr]);

8 dbms_ijob

• DBMS_IJOB is a package included with Oracle that allows a sys or system user to manipulate other user's jobs.

9 How to set time:

sysdate+1	from present time + 1day
trunc(sysdate)+1	from 12.00 AM + 1day
trunc(sysdate)+17/24	17:00 PM today<br />
trunc(sysdate)+60/1440	1:00 AM (60min/60min*24=1440)

• sql> SELECT to_char(trunc(sysdate+1) + 90/1440, 'MM/DD/YYYY HH:MI AM') FROM dual;

10 ERRORs

10.1 ORA-23421

• procedure dbms_job must by run by owner even sys user cant do that check dbms_ijob

10.2 ORA-12012

• search above string ORA-12012 in alertlogs due to problem with previous jobs

ORACLE WAITS

TIME_STATISTICS = true

v$event_name #describe events with parameters P and share to 12 wait_class

v$system_event #based on events which was collected when instance was started for all sessions (please check TIME_WAITED regard to  STARTUP_TIME.v$instance)

• TOTAL_WAITS #how many times session was waiting on this event
• TOTAL_TIMEOUTS #how often default time was reached, default time is set for each event
  
• TIME_WAITED # time used by event (1/100 sek)
• TIME_WAITED_MICRO #as above (/1000000 sek)
  
• AVERAGE_WAIT #average time TIME_WAITED/TOTAL_WAITS (1/100 sek)

v$session_event #the same as v$system_event but for session, added SID column

v$session_wait #present events which was expected by session

• SEQ #internal seqence nr for any event, it is increased any time when session wait at event
  
• STATE #value WAITING and WAITED UNKNOWN TIME show that TIMES_STATISTICS is FALSE
• SECONDS_IN_WAIT #time spending by session
  

from 10g
v$session_wait_history #the same as v$session_wait but there is last 10 wait events for any session but col SEQ# means something diferent
v$active_session_history #last 30 min probes which was collected every 1 sec., more probes than at v$session_wait_history

v$system_wait_class #waits grouped by class category
v$session_wait_class #waits grouped by class and session category

v$event_histogram #share between events which spend less than 1,2,4,8,16 ms

ORACLE FLASHBACK

Table of Contents

• 1 Prerequisites
• 2 Configuration steps
• 3 Estimating log store space
• 4 Recover from flashback logs
• 5 Views
• 6 Recyclebin
  • 6.1 Show recyclebin
  • 6.2 Recover table from recyclebin
  • 6.3 Droping table without recyclebin
  • 6.4 Purging recyclebin
• 7 ERRORs

1 Prerequisites

• database ARCHIVELOG mode
• UNDO_MANAGEMENT=auto

2 Configuration steps

1. setting param DB_RECOVERY_FILE_DEST which point to flashback logs location
2. setting param DB_RECOVERY_FILE_DEST_SIZE which specify summary size of all logs
3. setting param DB_FLASHBACK_RETENTION_TARGET which specify logs store time in miutes
4. startup database in mount mode
5. turn on flashback
   • sql> ALTER DATABASE FLASHBACK ON;
6. check:
   • sql> SELECT log_mode,flashback_on FROM v$database;
7. open database

3 Estimating log store space

1. setting DB_FLASHBACK_RETENTION_TARGET
2. sql> SELECT estimated_flashback_size FROM v$flashback_database_log;

4 Recover from flashback logs

1. startup database in mount mode
2. sql> FLASHBACK DATABASE TO…
   • TIMESTAMP SYSDATE-1]
   • TO TIMESTAMP to_timestamp('2009-09-28 11:00','yyyy-mm-dd hh24:mi')
   • TO SCN [scn#];
3. sql> ALTER DATABASE OPEN READ ONLY;
   • at READ-ONLY mode it is possible to verify data
   • if data are not correct, so repeat steps from 1 to 3
4. turn off database
5. startup database in mount mode
6. sql> ALTER DATABASE OPEN RESETLOGS;

5 Views

• sql> SELECT oldest_flashback_scn,oldest_flashback_time,flashback_size FROM v$flashback_database_log;

  oldest_flashback_scn, oldest_flashback_time	oldest timestamp when data can be recovered
  	(To verify this operation, try delete oldest flashback log then put above SELECT command and oldest point was changed)
  estimated_flashback_size	the neccessary space to ensure DB_FLASHBACK_RETENTION_TARGET
  flashback_size

• sql> SELECT * FROM v$flash_recovery_area_usage;
• sql> SELECT * FROM v$recovery_file_dest;
• sql> SELECT SCN_TO_TIMESTAMP(10213123) FROM DUAL;

6 Recyclebin

6.1 Show recyclebin

• sql> SHOW RECYCLEBIN

6.2 Recover table from recyclebin

• sql> DROP TABLE [table_name]
• sql> FLASHBACK TABLE [table_name] TO BEFORE DROP;

6.3 Droping table without recyclebin

• sql> DROP TABLE [table_name] PURGE;

6.4 Purging recyclebin

• sql> PURGE TABLE [table_name];
• sql> PURGE TABLESPACE [ts_name];
• sql> PURGE TABLESPACE [ts_name] USER [user];
• sql> PURGE RECYCLEBIN
• sql> PURGE DBA_RECYCLEBIN;

7 ERRORs

[ symptom ]

• ORA-38729: Not enough flashback database log data to do FLASHBACK

[ solution ]

• timestamp older then oldest_flashback_time

ORACLE DPUMP

Table of Contents

• 1 Database directory
• 2 Dpump Order
• 3 Views
• 4 expdp:
• 5 impdp:
• 6 impdp parameter file
• 7 Parameters
  • 7.1 include/exclude param
  • 7.2 parallel param
    • 7.2.1 remarks
    • 7.2.2 expdp
  • 7.3 dumpfile param
  • 7.4 version param
• 8 Checking current state of dpump process
• 9 Estimating dump size:
• 10 Importing tables:
• 11 Dpump by NETWORK_LINK:
• 12 Console:
• 13 ERROR
  • 13.1 ORA-39087
  • 13.2 ORA-29283
  • 13.3 ORA-31633

1 Database directory

• CREATE OR REPLACE DIRECTORY [oracle_dir] as '[path_to_directory_on_disk]';
• DROP DIRECTORY [oracle_dir]
• SELECT directory_name, directory_path FROM dba_directories;
• GRANT READ, WRITE ON DIRECTORY [oracle_dir] TO [user]

2 Dpump Order

1. type definitions
2. table definitions
3. table data
4. table indexes
5. integrity constraints, views, procedures, triggers
6. bitmap, function-based, domain indexes

3 Views

• SELECT job_name,owner_name,state FROM dba_datapump_jobs;

4 expdp:

• EXPDP [user]/[pass] SCHEMAS=[schema] LOGFILE=[log_file] DIRECTORY=[directory] DUMPFILE=[dump_file] PARALLEL=[parallel write to many files - use witch option %U as sufix in file name]
• expdp [user]/[pass] directory=[dir] dumpfile=[file]_%U.expdp logfile=[logfile] filesize=4G \ job_name=[name] cluster=N parfile=[file].par

5 impdp:

• IMPDP [user]/[pass] DUMPFILE=[dump_file] PARFILE=[parameter_file]

6 impdp parameter file

REMAP_SCHEMA=[old_user]:[new_user>]
REMAP_TABLESPACE=[old_ts]:[new_ts]
REMAP_TABLESPACE=[old_ts]:[new_ts]
DIRECTORY=[oracle directory]
LOGFILE=[logfile]
PARALLEL=[a number of processes]
STATUS=[time in sek]
JOB_NAME=[job name]

schemas=[schema]
include=TABLE:"LIKE '[table_prefix]%'"
include=VIEW:"LIKE '[view_prefix]%'"
include=SYNONYM:"LIKE '[synonym_prefix]%'"
include=PACKAGE:"LIKE '[package_prefix]%'"
include=INDEX:"LIKE '[index_prefix]%'"
include=TRIGGER:"LIKE '[trigger_prefix]%'"
include=SEQUENCE:"LIKE '[sequence_prefix]%'"

7 Parameters

7.1 include/exclude param

• include #only object with iclude will be exported/imported
• exclude #all objects exclude few followed by exclude parameter will be exported/imported

7.2 parallel param

• oracle white paper - Parallel Capabilities of Oracle Data Pump
• only for enterprise edition
• significantly reduce the elapsed time for large indexes
• split job between more worker processes
• MCP(Master Control Process) controlling the pool of worker processes

7.2.1 remarks

• set the degree of parallelism to two times the nr of CPU
• for expdp parallel <= nr of dump files
• for impdp parallel <= nr of files in the dump file set

7.2.2 expdp

• in typical export there are both data and metadata, the first worker process will unload the metadata: ts, schemas, grants, roles, tables, indexes etc. all the rest unload the data, if the metadata worker finishes and there are still data objects to unload it will start unloading the data too

7.3 dumpfile param

• PX process ( Parallel Execution Processes)
• parallel param <= dumpfile param
• during expdp each woker or parallel execution Process requires exclusice access to the dump file, so having fewer dump files than the degree of parallelism will mean that some workers of PX processes will be unable to write the information they are exporting
• during impdp the workers and PX processes can all read from the same files, but parallel parameter should be significantly larger than the number of files in the dump file set

7.4 version param

• expdp version=10.2

8 Checking current state of dpump process

• SELECT job_name,state FROM v$datapump_job
• SELECT * FROM v$datapump_session

9 Estimating dump size:

• EXPDP [user]/[pass] FULL=y ESTIMATE_ONLY=y DIRECTORY=[oracle_dir] logfile=[logfile]
• !warning
  during estimation, dump size will be lower than original db size, because for indexes during dump only their definition is written, and finally indexes are rebuid, you must notice that index is based on ROWID which changing during dump

10 Importing tables:

• IMPDP [user]/[pass] TABLES=[tabela] REMAP_SCHEMA=[src_schema]:[dst_schema] DIRECTORY=[directory] PARALLEL=[nr] DUMPFILE=[dumpfile]

11 Dpump by NETWORK_LINK:

source db activity:

• CREATE DATABASE LINK [link_name] CONNECT TO [src_user] IDENTIFIED BY [src_password] USING [src_tnsname]
• choose mode IMPDP or EXPDP (the difference is that IMPDP connect with source db, get data and write them
  directly to destination db, to specified schema, in the other side EXPDP write data only to file)
• IMPDP [dst_user]/[dst_password] NETWORK_LINK = [link_name] DIRECTORY = [dir_name] SCHEMAS = [schema_name] LOGFILE = [logfile_name]
• EXPDP [dst_user]/[dst_password] NETWORK_LINK = [link_name] DIRECTORY = [dir_name] SCHEMAS = [schema_name] LOGFILE = [logfile_name] DUMPFILE = [file_name]

12 Console:

• EXPDP/IMPDP [user]/[pass] ATTACH=[job_name]
• commands:

  COMMAND	DESC
  help
  C-c	log mode => console mode
  exit_client	console mode => system
  continue_client	console mode => log mode
  add_file=plik1,plik2	add files
  kill_job	stop job end exit
  pararell=[nr]	change numbers of process
  start_job	start job which was stoped
  status
  stop_job[=IMMEDIATE]	stop job for a while

13 ERROR

13.1 ORA-39087

• failed directory

13.2 ORA-29283

• failed grants for directory

13.3 ORA-31633

[ symptom ]

• unable to create master table "SYSTEM.EXPDP_MANUAL"

[ solution ]

• drop table system.expdp_manual

ORACLE REDOLOGS

add new group:
ALTER DATABASE ADD LOGFILE GROUP [nr] ('[file]','[file]') size [size]M;
ALTER DATABASE ADD LOGFILE ('[file]') size [size]M;
remove group (group must by inactive - check v$log):
ALTER DATABASE DROP LOGFILE GROUP [group_nr];
add file to group:
ALTER DATABASE ADD LOGFILE MEMBER '[file]' TO GROUP [goup_nr];
remove file from group:
ALTER DATABASE DROP LOGFILE MEMBER '[file]';
turn on/off archivelog (mount mode):
ALTER DATABASE ARCHIVELOG|NOARCHIVELOG;
force archivie and switch redolog:
ALTER SYSTEM ARCHIVE LOG CURRENT;
(mount mode)
ALTER SYSTEM ARCHIVE LOG GROUP [group_nr];
archivelog info:
ARCHIVE LOG LIST;
switch redolog:
ALTER SYSTEM SWITCH LOGFILE;
 
force writing DIRTY BLOCKS from buffers (buffer cache) to datafile:
ALTER SYSTEM CHECKPOINT;
also for:
- change redologs(?)
- achieve LOG_CHECKPOINT_INTERVAL
- finish LOG_CHECKPOINT_TIMEOUT
- set ts of database in BACKUP mode
(SCN will be change only in header file of backup tablespace. During backup, in the other header files, SCN will be change normally without feedback with SCN in backup tablespace.
Tablespace after BACKUP mode adjust SCN to other files)
natomiast o ile zmiana redologów powoduje checkpoint to brak takiej zależności w drugą stronę

checkpoint (write blocks from cache to files):
ALTER SYSTEM CHECKPOINT;
1a. SCN in logfiles not change
1b. SCN in header datafiles change
 
check files checkpoint and SCN:
SELECT file#,checkpoint_change;checkpoint_time FROM v$datafile_header;
to show SCN must be set SQLPLUS param:
set numwidth 18
check current SCN (change every 3 sec.):
SELECT current_scn FROM v$database;
SELECT dbms_flashback.get_system_change_number FROM dual;
set rolling grop of redologs:
ARCHIVE_LAG_TARGET=1800 (default 0)

redolog is writing again only when data from it war written to datafiles and to archivelogs

switch redolog process:
ALTER SYSTEM SWITCH LOGFILE;
1a. new redolog group get status CURRENT witch current SCN,
1b. present redolog group get status ACTIVE it means that it is necessary to recover,
2a. oracle wait for finish writing data to datafile
2b. SCN in header files is changed(SELECT checkpoint_change# FROM v$datafile_header;) is equal SCN of current redolog group (SELECT first_change# FROM v$log;)
2c. present redolog group change status from ACTIVE to INACTIVE (SELECT * FROM v$log;)
check redolog load:
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM
x$kcccp cp,
x$kccle le
WHERE
le.leseq =cp.cpodr_seq
and bitand(le.leflg,24)=8;

x$kcccp - kernel cache, controlfile checkpoint progress
x$kccle - kernel cache, controlfile logfile entry

VIEWS
V$LOG
V$LOGFILE
V$LOG_HISTORY
V$ARCHIVE_DEST
V$ARCHIVED_LOG