expdp flashback_time tips

userid="sys/don as sysdba"
job_name=job1
# parallel=2
# Flashback_time at specific time
FLASHBACK_TIME="TO_TIMESTAMP('13-05-2010 13:20:00', 'DD-MM-YYYY HH24:MI:SS')"

# Flashback_time at current time
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"

# Flashback_time at current time
flashback_time=systimestamp

# Flashback_time at daily time example 11PM
flashback_time="to_timestamp(to_char(trunc(sysdate)+22/24,'YYYY-MM-DD'),'YYYY-MM-DD')"

directory=dpdump
filesize=20000000000
EXCLUDE=statistics
SCHEMAS=SCOTT
dumpfile=expdp_demo_scott_%U.dmp
logfile=expdp_demo_scott.log
COMPRESSION=ALL
CLUSTER=N
CONTENT=METADATA_ONLY

Moving Audit table AUD$ to its own tablespace

   select owner, tablespace_name from dba_tables where table_name='AUD$';
   
   CREATE SMALLFILE TABLESPACE SCOTT_AUDIT 
   DATAFILE SIZE 100M 
   AUTOEXTEND ON
   NEXT 100K 
   MAXSIZE UNLIMITED 
   LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
   SEGMENT SPACE MANAGEMENT AUTO  DEFAULT COMPRESS FOR OLTP;
   
   create table audx tablespace SCOTT_AUDIT
      storage (initial 50k next 50k pctincrease 0)
         as select * from aud$ where 1 = 2 ;
   
   rename AUD$ to AUD$_drop ;
   rename audx to aud$ ;
   create index i_aud1
     on aud$(sessionid, ses$tid)
       tablespace SCOTT_AUDIT storage(initial 50k next 50k pctincrease 0) ;
   
   drop table AUD$_drop ;
   purge dba_recyclebin;
   select owner, tablespace_name from dba_tables where table_name='AUD$';
   
   set audit_trail=NONE in init.ora and create spfile 
   
   -- production only
   sqlplus / as sysdba
   audit dba;
   
   select bytes from dba_segments where segment_name='AUD$'
   select bytes from dba_segments where segment_name='AUD$_drop';
   select count(1) from aud$;
   
   These views will show you what auditing is configured:
      Select * from dba_priv_audit_opts;
      Select * from dba_stmt_audit_opts;
      Select * from dba_obj_audit_opts;

Managing Aud$

show parameter audit

-- check tablespaces

SELECT table_name, tablespace_name

FROM   dba_tables

WHERE  table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY table_name;

-- when last deleted

SELECT * FROM dba_audit_mgmt_last_arch_ts;

-- total # of records

SELECT COUNT(*) FROM aud$;

SCOTT_AUDIT is tablespace name

-- change for AUD$

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

    audit_trail_location_value => 'SCOTT_AUDIT');

END;

/

-- change for FGA_LOG$

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,

    audit_trail_location_value => 'SCOTT_AUDIT');

END;

/

-- both in single step

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

    audit_trail_location_value => 'SCOTT_AUDIT');

END;

/

-- Controlling the Size and Age of the OS Audit Trail

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A20

COLUMN audit_trail FORMAT A20

SELECT * FROM   dba_audit_mgmt_config_params

-- Purging Audit Trail Records

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A20

COLUMN audit_trail FORMAT A20

SELECT * FROM dba_audit_mgmt_config_params;

BEGIN

  DBMS_AUDIT_MGMT.init_cleanup(

    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

    default_cleanup_interval => 12 /* hours */);

END;

/

SET SERVEROUTPUT ON

BEGIN

  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN

    DBMS_OUTPUT.put_line('YES');

  ELSE

    DBMS_OUTPUT.put_line('NO');

  END IF;

END;

/

-- how much data you wish to purge

COLUMN audit_trail FORMAT A20

COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;

-- to deleted manualy, must run DBMS_AUDIT_MGMT.init_cleanup before this

BEGIN

  DBMS_AUDIT_MGMT.clean_audit_trail(

   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

   use_last_arch_timestamp => FALSE);

END;

/

CATALOG missing ARCHIVE log file / RMAN

rman target /
RMAN> CATALOG ARCHIVELOG '/u01/scott/2_4553_621298904.dbf';
RMAN> list archivelog all;
exit;

Copy files from ASM to filesystem

sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY log_files AS '+FRA1/demo/archive_logs' ;
CREATE DIRECTORY DSK_FILES AS '/u01/scott';
exec DBMS_FILE_TRANSFER.COPY_FILE ( 'log_files' , '2_4553_621298904.dbf' , 'dsk_files' , '2_4553_621298904.dbf' );

Recovering Standby Database - Archive log missing from Standby

on PRIMARY:
mkdir /u01/scott

rman target /
RMAN> list archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
9833 2 4553 A 07-NOV-11 +FRA1/demo/archive_logs/2_4553_621298904.dbf
exit


sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY log_files AS '+FRA1/demo/archive_logs' ;
CREATE DIRECTORY DSK_FILES AS '/u01/scott';
exec DBMS_FILE_TRANSFER.COPY_FILE ( 'log_files' , '2_4553_621298904.dbf' , 'dsk_files' , '2_4553_621298904.dbf' );

on STANDBY:
mkdir /u01/scott
cd /u01/scott
scp PRIMARY_HOST_IP_ADDRESS:/u01/scott/2_4553_621298904.dbf .
rman target /
RMAN> CATALOG ARCHIVELOG '/u01/scott/2_4553_621298904.dbf';
RMAN> list archivelog all;
exit;