UNDO tablespace in DG/DR/Standby database got Corrupted.

UNDO tablespace in DG/DR/Standby database got corrupted.
========================================================

suppose we found block corruption issue in DG/DR we can fix with 2 ways. if it is other than UNDO tablespace ,


Restore datafiles and archive log files from backup servers and restart recovery, recovery will go fine.

To reduce recovery time , we can place corresponding tablespace in Begin backup and copy the corresponding datafile to DG/DR location , once copy is completed place tablespace in end backup and restart recovery.

But if corrupted block is on undo tablespace above activity don't try , Because Undo tablespace is used for both rollback and rollforward activity.

copying latest undo tablespace related datafile will not help to fix the issue.


in such cases better to rebuild DG/DR.

SYSAUX tablespace Deleted and No backup not available for Database.

Suppose SYSAUX tablespace related datafiles are deleted or corrupted and no backup is available.
=====================================================================================

As we know sysaux tablespsace is newly introduced in 10g to reduce load on system tablespace. SYSAUX tablespace is contine information like AWR repositrory , streams long/lob data, RMAN repositry, OLAP , Datamining etc.

Moment sysaux tablespace is offline or datafile not accessible , Above modules will not work for database but database will be availble for users.

If database is having valid backup , restore backup then recover database to fix the issue.

If Database is not having any valid backup then , try to take export of user defined schemas once export is completed successfully , recreate database
and import. Even expdp/impdp will also fix the issue.

Moving LOB column tables in oracle to new tablespace.

If table is having LOB objects below prodedure need to be followed.
======================================================================

Whenever we create a table with a CLOB column, Oracle implicitly creates a LOB segment and a LOB index for the CLOB column.
By default, LOB segment and index are created in the same tablespace as of the table. During Creation of table,
we have an option to specify storage for LOB column. We can also specify whether we want to store it within the table or outside the table.
By default storage is outside the table. We can also give specific name to segment and index instead of name being generated by Oracle.
If segment name or index name is not defined, normally segments start with SYS_LOBxxxx and indexes start with SYS_ILxxxx.



col COLUMN_NAME for a30
set linesize 200
set pagesize 30000

select TABLE_NAME , COLUMN_NAME, SEGMENT_NAME , TABLESPACE_NAME , INDEX_NAME from dba_lobs where owner='TEST' ;



TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------------------------ ------------------------- ------------------------------ ------------------------------ ------------------------------
TEST_TABLE_LOB TEST_LOB1 SYS_LOB0000055439C00011$$ USERS SYS_IL0000055439C00011$$
TEST_TABLE_LOB TEST_LOB2 SYS_LOB0000055439C00009$$ USERS SYS_IL0000055439C00009$$
TEST_TABLE_LOB TEST_LOB3 SYS_LOB0000055439C00010$$ USERS SYS_IL0000055439C00010$$


After LOB movement to new tablespace.
=====================================

alter table TEST.TEST_TABLE_LOB MOVE LOB(TEST_LOB1,TEST_LOB2,TEST_LOB2) STORE AS (tablespace NEW_TABLESPACE );



TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------------------------ ------------------------- ------------------------------ ------------------------------ ------------------------------
TEST_TABLE_LOB TEST_LOB1 SYS_LOB0000055439C00011$$ NEW_TABLESPACE SYS_IL0000055439C00011$$
TEST_TABLE_LOB TEST_LOB2 SYS_LOB0000055439C00009$$ NEW_TABLESPACE SYS_IL0000055439C00009$$
TEST_TABLE_LOB TEST_LOB3 SYS_LOB0000055439C00010$$ NEW_TABLESPACE SYS_IL0000055439C00010$$

Moving table from one Tablespace to other tablespace in oracle.

When objects are created in diffrent tablespace or to remove fragmentation in tablespace. DBA plan for moving one T/S to other tablespace.

Best way to Full schema movement to other tablespace to other tablespace carry out exp/imp or expdp/impdp or alter table move.

exp/imp
===============

select userenv('language') from dual ;
AMERICAN_AMERICA.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

exp file=meta_data.dmp log=meta_data_exp.log owner=TEST buffer=500000

imp file=meta_data.dmp log=meta_data_imp.log indexfile=meta_data_required.sql ignore=y commit=y fromuser=TEST touser=TEST buffer=50000 rows=n

In meta_data_required.sql file all metadata of objects is captured , Edit file change tablespace name to other tablespace name and remove REM .

drop user TEST ;

create user TEST and provide required privilege and quota on new tablespace.


connect test/test
sql > @meta_data_required.sql

Above script will create all the objects in new tablespace.

verify tablespace used.

select distinct(tablespace_name) from dba_segments where owner='TEST';

once object creation is completed start import.

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

imp file=meta_data.dmp log=meta_data_imp_final.log ignore=y commit=y fromuser=TEST touser=TEST buffer=500000


In case of issue or to reduce down time.
===========================================

DBA team can plan table by table movement by using

alter table move tablespace ;

alter table move tablespace parallel 8;


Above alter table movment having below disadvantages.

1. After movemement of tables depending indexes will become unusable , need to rebuild. this operation is additional overhead and
generates high archive log generation.
2. LONG objects related tables movement not possible.

to speed up table movement use parallel hint as below.


alter table move tablespace parallel 8;

once movement is completed change degree of table to 1.

alter table degree 1;


Rebuild all related indexes using

select index_name , index_type , status, degree from dba_indexes where table_name='TEST_TABLE' and owner='TEST';

alter index index_name rebuild ;

else

alter index index_name rebuild parallel 8;

once index rebuild is done

select index_name , index_type , status, degree from dba_indexes where table_name='TEST_TABLE' and owner='TEST';

alter index index_name noparallel;

metadata extraction methode in Oracle Objects.

How to Extract Metadata of Objects in Oracle database.
=========================================================

set heading off
set echo off
set flush off
set pagesize 9999
set linesize 9999
set long 9999

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME ','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','TEST_MV','TEST') from dual;

select dbms_metadata.get_ddl('SYNONYM','TEST_V','TEST') from dual;
select dbms_metadata.get_ddl('VIEW','TEST_VIEW','TEST') from dual;
select dbms_metadata.get_ddl('SYNONYM','TEST_SYN','TEST') from dual;
select dbms_metadata.get_ddl('TABLE','TEST_TABLE','TEST') from dual;
select dbms_metadata.get_ddl('INDEX','TEST_INDX','TEST') from dual;

In case of date issue.
=========================
alter session set nls_date_format = 'dd-mm-rr';

and try to Extract metadata.