Recovery senario when database is in BEGIN backup mode ( recovery started by recover database until cancel )

Recovery senario when database is in BEGIN backup mode ( recovery started by recover database until cancel )

Think Database was in begin backup mode , database crashed or brought down by DBA with out turn of tablespace to end backup.
During database startup database will go for recovery with out checking datafiles in backup mode DBA Will start recovery using below recovery command.


recover database until cancel;

After applying all archive logfiles and redolog files, Database will not open , Since recovery command used until cancel DBA need to open with open resetlogs. Opening database with resetlogs will make DG/DR need to rebuild and old backups will become nomore valid.

How to resolve this issues with out using alter database open resetlogs ?;Answer
========
This is one of the challenging status for DBA to test his skill set. Please follow below methode to fix issue.

Database is in mount state.

1. First turn off all datafiles to end backup using below command.

alter database datafile end backup;


2. alter database backup controlfile to trace;

3. Go to udump and open trace file generated by above command.

4. Just recreate control file using below entry changes in trace file create control file script.

NORESETLOGS
ARCHIVELOG

Example
========

CREATE CONTROLFILE REUSE DATABASE "RECOBASE" NORESETLOGS ARCHIVELOG

5. try to open database .

6. Some times database may ask for recovery ( because datafile header seqence will be overwitten by last sequence ) and just issue.

7. if database goes for recovery

recover database ;

8 alter database open ;

this methode will save both DG and old backup.

Lesson lernt.

Below recovery is used only for incomplete recovery only in critical situation like hot backup restoration will be issued below commands.

recover database until cancel ;

recover database until cancel using by backup control file;

Recovery Senario using datafiles of standby

Recovery senario
=====================
Think Database is having having backup copy which is not valid and DG/DR is there which is not in sync with production database.

Around 10 to 15 datafiles located in perticular vg/mount point is corrupted due to hardware error.

How to handle this senario ?


Solution
===========

1. If Database is not having any nologging objects try to sync up DG by applying all archive logfiles and carryout switch over process and start using DG as production database.

2. IF Database is having nologging objects i.e index try to sync up DG by applying all archive logfiles and carryout switch over process and start using DG as production database. After switch over recreate all indexes will fix issue.

3. Else copy Datafiles from DG/DR to production database and start recovery will also help to recover database. In this methode if objects are loaded with nologging option will loose data.

Hence before building DG enable forced login in all critical database which will helps all recovery senarios.

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.