Oracle RAT Step by Step -Part 3

Replay workload on target database – Part 1

Restore Target database

 

RMAN or export/import can be used to stage target/replay database. To restore database using RMAN please refer to Oracle RMAN documentation.

Drop Db_links

 

Drop db_links that may be pointing to external systems or re-point db_links to test systems.

set lines 150

col db_link for a40

select OWNER,DB_LINK,USERNAME,to_char(CREATED, ‘MM/DD/YYYY HH24:MI:SS’) as CREATED from dba_db_links;

 

–generate drop statement for public database links

sqlplus -s / as sysdba <<EOF

set feedback 0 pages 0 lines 132 echo on timing off

set echo off

set term off

spool /tmp/drop_db_links.sql

 

select ‘DROP ‘||OWNER||’ DATABASE LINK ‘||DB_LINK||’ ; ‘ from dba_db_links where OWNER=’PUBLIC’;

 

spool off

EOF

 

Note: If db_links can’t be dropped using “drop database link” use below.

 

Check sys db_link tables –

select * from sys.link$;

select db_link, username, host from user_db_links;

 

Delete records manually –

delete from sys.link$;

commit;

 

Gather Schema Stats

 

BEGIN

DBMS_STATS.gather_schema_stats (ownname => ‘SCHEMA1’ ,

cascade =>true,

estimate_percent => dbms_stats.auto_sample_size,

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,

degree => 8);

END;

/

 

 

BEGIN

DBMS_STATS.gather_schema_stats (ownname => ‘SCHEMA2’ ,

cascade =>true,

estimate_percent => dbms_stats.auto_sample_size,

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,

degree => 8);

END;

/

 

 

 

 

BEGIN

DBMS_STATS.gather_schema_stats (ownname => ‘SCHEMA3’,

cascade =>true,

estimate_percent => dbms_stats.auto_sample_size,

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,

degree => 8);

END;

/

 

 

Create Flashback Restore Point

 

To replay capture data multiple times, Oracle database flashback will allow us to restore database to SCN # without having to use RMAN restore.

—Check for existing restore point

 

set linesize 121

col name format a15

col time format a32

 

SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size

FROM gv$restore_point;

–CREATE GUARANTEED RESTORE POINT

 

 

CREATE RESTORE POINT <RESTORE POINT NAME> GUARANTEE FLASHBACK DATABASE;

 

Example:

 

CREATE RESTORE POINT PRE_REPLAY_RAT GUARANTEE FLASHBACK DATABASE;

 

 

 

 

 

 

 

Create Directory

 

–Check the Directory

 

set lines 1024;

col DIRECTORY_PATH for a90

 

select * from dba_directories;

 

—CREATE RAT REPLAY DIRECTORY

 

 

create or replace directory <DIRECTORY NAME> as ‘<DIRECTORY LOCATION>’;

 

example : create or replace directory REPLAY_TGT_1 as ‘/export/home/oracle/rat/traget_db’;

 

 

Advertisements

Author: Abdul H Khan

Trying to be cloudy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s