Space release plan with downtime from datafiles in oracle
Step 1:
========
Shutdown the database.
Step 2:
========
startup restrict
Note : start the database in restrict mode. ( so no body should be able to do activity during maintance)
Verified:
============
SYS@QA02 SQL>SELECT logins from v$instance;
LOGINS
----------
RESTRICTED
Step3:
===========
Created one tablespace on DATABASE for datafile
Already created : <NEW TABLESPACE NAME>
Check ths size of datafiles or add more datafiles if need.
Step 4:
===========
Open new session and monitor the alert.log file in new session during this activity.
Step 5:
============
Find the list of invalid object in the database QA02
===========================================================
select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;
Step 6:
=========
Check the count of objects and take export backup
exp system full=No direct=y
Consistent=y rows=y
file=exp_QA02_tables.dmp
log=exp_QA02_tables.log
tables=<table name>, < table name>
select count(*) from <table name>
select count(*) from <table name>
Step 7: (for all objects)
================
Find the object list present in DATABASE datafiles:
select b.owner,b.SEGMENT_NAME,b.SEGMENT_TYPE,sum(b.bytes)/1024/1024 from dba_segments b,dba_extents a where
a.file_id in (138,139,140,141,142,143,151,152,153,154,155,156,157,158,160,161,162,165,168,169,171,172,178,179,180) and
a.segment_name = b.segment_name and a.owner = b.owner
group by b.owner,b.SEGMENT_NAME,b.SEGMENT_TYPE
order by 4 desc
Step 8: (For tables)
======================
Created a dynamic script for objects in datafiles:
select distinct 'Alter table '||b.owner||'.'||b.SEGMENT_NAME||' move tablespace APPS_TS_ARCHIVE_IDX;' from dba_segments b,dba_extents a where
a.file_id in (138,139,140,141,142,143,151,152,153,154,155,156,157,158,160,161,162,165,168,169,171,172,178,179,180) and
a.segment_name = b.segment_name and a.owner = b.owner and b.segment_type = 'TABLE'
'ALTERTABLE'||B.OWNER||'.'||B.SEGMENT_NAME||'MOVETABLESPACEAPPS_TS_ARCHIVE_IDX;'
------------------------------------------------------------------------------------------------------------------------------------------------------
Alter table APPLSYS.FND_DOC_SEQUENCE_AUDIT move tablespace APPS_TS_ARCHIVE_IDX;
Alter table CE.CE_ARCH_INTERFACE_LINES move tablespace APPS_TS_ARCHIVE_IDX;
Alter table ONT.OE_ORDER_LINES_HISTORY move tablespace APPS_TS_ARCHIVE_IDX;
Alter table AR.HZ_CUST_SITE_USES_ALL_M move tablespace APPS_TS_ARCHIVE_IDX;
Alter table AR.HZ_CUST_PROFILE_AMTS_M move tablespace APPS_TS_ARCHIVE_IDX;
Alter table CZ.CZ_PRICING_STRUCTURES move tablespace APPS_TS_ARCHIVE_IDX;
Alter table APPLSYS.FND_STATS_HIST move tablespace APPS_TS_ARCHIVE_IDX;
Step 9: For indexes :
=====================
select 'Alter INDEX '||b.owner||'.'||b.SEGMENT_NAME||' REBUILD tablespace APPS_TS_ARCHIVE_IDX;' from dba_segments b,dba_extents a where
a.file_id in (138,139,140,141,142,143,151,152,153,154,155,156,157,158,160,161,162,165,168,169,171,172,178,179,180) and
a.segment_name = b.segment_name and a.owner = b.owner and b.segment_type = 'INDEX'
No index present
Step 10 : ( for lob objects)
==============================
Checked for lob objects :
SELECT 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as '||segment_name||' (tablespace APPS_TS_ARCHIVE_IDX);'
from all_lobs where owner='XETA';
No lob objects found in the list of tables. So pass step to 9.
Step 11:
=================
Start movement of objects to new tablespace from the following command.
In QA02 following tables are present:
Alter table APPLSYS.FND_DOC_SEQUENCE_AUDIT move tablespace APPS_TS_ARCHIVE_IDX;
Alter table CE.CE_ARCH_INTERFACE_LINES move tablespace APPS_TS_ARCHIVE_IDX;
Alter table ONT.OE_ORDER_LINES_HISTORY move tablespace APPS_TS_ARCHIVE_IDX;
Alter table AR.HZ_CUST_SITE_USES_ALL_M move tablespace APPS_TS_ARCHIVE_IDX;
Alter table AR.HZ_CUST_PROFILE_AMTS_M move tablespace APPS_TS_ARCHIVE_IDX;
Alter table CZ.CZ_PRICING_STRUCTURES move tablespace APPS_TS_ARCHIVE_IDX;
Alter table APPLSYS.FND_STATS_HIST move tablespace APPS_TS_ARCHIVE_IDX;
Step 12:
===============
After movement of table one by one check the invalid objects:
Note: for parallel run please choose the table of different user/schema
Step 13:
===============
check invalid object:
select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;
Run the ?/rdbms/admin/utlrp.sql
Step 14:
================
Check the size of datafiles;
select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) and a.file_id in (138,139,140,141,142,143,151,152,153,154,155,156,157,158,160,161,162,165,168,169,171,172,178,179,180);
Step 15:
================
Resize the datafile :
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id= b.file_id and a.file_id in (138,139,140,141,142,143,151,152,153,154,155,156,157,158,160,161,162,165,168,169,171,172,178,179,180)
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);
Step 16:
===================
Check the status of the following objects;
===============
SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';
Check 17:
================
checked the objects are accessable
by running the
select count(*) from <table name>
select count(*) from <table name>
check 18:
==============
Disable the restricted session:
SQL> alter system disable restricted session;
Verified:
==========
SELECT logins from v$instance;
SYS@QA02 SQL> SELECT logins from v$instance;
LOGINS
----------
ALLOWED
Note:
===================
If need to compile manaually:
Compile the object manually if needed:
==============================================
alter MATERIALIZED VIEW APPS.GET_REGION_DISTRICT_MV compile;
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Last:
============================
Update the stats of table
No comments:
Post a Comment