DB refresh
Steps:
Pre Refresh Steps:
1)
Select username, default_tablespace, temporary_tablespace, password, created from dba_users;
Select tablespace_name, extent_management, allocation_type from dba_tablespaces;
Select tablespace_name, sum(bytes)/1048576 from dba_free_space group by tablespace_name;
SELECT seg.tablespace_name, seg.tablespace_size_mb, dfile.tablespace_total_size_mb, Round
(seg.tablespace_size_mb / dfile.tablespace_total_size_mb * 100, 2) pct_full FROM ( SELECT
DS.tablespace_name, Round(Sum(DS.bytes) / 1024 / 1024, 2) tablespace_size_mb FROM
DBA_SEGMENTS DS GROUP BY DS.tablespace_name) seg, ( SELECT VT.name tablespace_name,
Round(Sum(VF.bytes) / 1024 / 1024, 2) tablespace_total_size_mb FROM V$DATAFILE VF,
V$TABLESPACE VT WHERE VF.ts# = VT.ts# GROUP BY VT.name) dfile WHERE
seg.tablespace_name = dfile.tablespace_name ORDER BY seg.tablespace_name;
Select tablespace_name, file_id,rpad(file_name,55),sum(bytes)/1048576 from dba_data_files group by
tablespace_name,file_id,rpad(file_name,55);
select a.tablespace_name,rpad(a.file_name,55),sum(b.bytes)/1048576 from dba_data_files a,
dba_free_space b where a.file_id=b.file_id group by a.tablespace_name,rpad(a.file_name,55);
SELECT rpad(A.NAME,10),rpad(B.NAME,45),SUM(B.BYTES/1048567) FROM V$TABLESPACE A,
V$TEMPFILE B WHERE A.TS#=B.TS# GROUP BY A.NAME,B.NAME;
SELECT rpad(A.TABLESPACE_NAME,10),rpad(A.FILE_NAME,55),SUM(B.BYTES/1048567)
FROM DBA_TEMP_FILES A, DBA_FREE_SPACE B WHERE A.FILE_ID=B.FILE_ID GROUP BY
rpad(A.TABLESPACE_NAME,10),rpad(A.FILE_NAME,55);
2)
Collect the private and public dblinks----------------------important
---public DB Links
SELECT 'DROP PUBLIC DATABASE LINK ' || AO.db_link || ';' || Chr(13),
To_Char(DBMS_METADATA.Get_DDL('DB_LINK', AO.db_link, AO.owner)) || ';' || CHR(13) FROM
DBA_DB_LINKS AO WHERE owner = 'PUBLIC';
------------private dblinks
SELECT 'CONNECT ACN_DBA/<<DBA PASSWORD>>' || CHR(13), 'ALTER USER ' ||
DU.username || ' IDENTIFIED BY ' || DU.username || ';' || CHR(13), 'CONNECT ' || DU.username || '/' ||
DU.username || CHR(13), 'DROP DATABASE LINK ' || DDL.db_link || ';' || CHR(13),
To_Char(DBMS_METADATA.Get_DDL('DB_LINK', DDL.db_link, DDL.owner)) || ';' || CHR(13),
'ALTER USER ' || DU.username || ' IDENTIFIED BY VALUES ''' || DU.password || ''';' || CHR(13)
FROM DBA_DB_LINKS DDL, DBA_USERS DU WHERE DDL.owner <> 'PUBLIC' AND
DDL.owner = DU.username;
3) Temporary file information
SELECT 'ALTER TABLESPACE ' || VTS.NAME || ' ADD TEMPFILE ''' || VTF.NAME|| '''SIZE
' || VTF.create_bytes / (1024 * 1024) || 'MB AUTOEXTEND ON;' FROM V$TABLESPACE
VTS, V$TEMPFILE VTF WHERE VTS.ts# = VTF.ts#;
4)
Select rpad (file_name,60) filename, tablespace_name from dba_data_files;
5)
select rpad(file_name,60) filename, tablespace_name from dba_temp_files;
6)
select * from global_name;
7) List of scheduled Jobs from:
Show parameters job_
alter system set job_queue_processes=0;----------------This is important --- leave the value 0 after
refresh.
SELECT job_name, state FROM DBA_SCHEDULER_JOBS;
8) Check invalid objects --before restore/refresh:
select owner, object_name, object_type, status from dba_objects where status <>'VALID';
9) Collect parameter settings:
show parameter
( Record all the parametres )
Ensure any user is connected or not
Select instance_name, status, version from v$instance;
Select sid, serial#, program, status from v$session;
10) Create a RMAN script:
Connect RMAN catalog, target and auxiliary databases
rman catalog …. /......@... target sys/.....@... auxiliary / t race=log_file.log
Prepare a script
run { set until time "to_date('30-APR-2011 06:24','DD-MON-YYYY HH24:MI')";
allocate auxiliary channel t1 type 'SBT_TAPE' ;
allocate auxiliary channel t2 type 'SBT_TAPE' ;
SET NEWNAME FOR DATAFILE '/disk/d9/oradata /users02.dbf’ TO '/san/vol9/oradata
/users02.dbf';
duplicate target database to <db_name>
logfile '/san/vol1/oradata/devdimdv/redo01.rdo' size 100M
Post Refresh Steps:
11) Creating new TEMP TS:
11.1 After the refresh, set job_queue_processes parameter to zero
Alter system set job_queue_processes=0;
11.2 select distinct temporary_tablespace from dba_users;
11.3 CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'/san/vol4/oradata/ temp01a.dbf' SIZE 1024M REUSE,
'/san/vol7/oradata//temp01b.dbf' SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
11.4 Make it default TEMPORARY Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
11.5 select username, temporary_tablespace from dba_users;
11.6 Drop the previous TEMPORARY Tablespace
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
12) Drop and create db links, enable blk_change_tracking:
12.1 Drop and recreate public and private dblinks
Check all dblinks should have the present date
12.2 Select owner, rpad(db_link,40), created from dba_db_links;
12.3 check the invalid objects
Select OWNER, OBJECT_NAME, OBJECT_TYPE from all_objects where status='INVALID';
12.4 Check the block change tracking
Select rpad (filename,50), rpad(status,15), bytes from v$block_change_tracking;
12.5 alter DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/...
/rman_change_track.f' REUSE;
12.6 SHOW PARAMETER JOB_
12.7 show parameters db_recovery
12.8 show parameters log_archive_dest
12.9 show parameter spfile
12.10 select flashback_on from v$database;-------if not, then enable it
12.10.1 SHUTDOWN IMMEDIATE;
12.10.2 STARTUP MOUNT EXCLUSIVE;
12.10.3 ALTER DATABASE FLASHBACK ON;
12.10.4 ALTER DATABASE OPEN;
12.11 create pfile=’/…../’ from spfile;
12.12 show parameter
