Recover table on Stand by side using flash back

We had a task to flashback the stand by database  to  recover the dropped object/table. Please find the steps  carried out.

 

1)      Create a table on the primary database or use an existing table;

 

2)      Load table with some data;

 

3)      Accidently drop table or deleted certain records;

 

4)      Request table data to be restored;

 

5)      DBA team to flashback standby database, find missing data and reload into primary database – timed test.

 

 

Create a table  on Primary

create table FB_WO828610 (Name Varchar2(20));

 

Insert  Values to a table

Insert into FB_WO828610 values ('LCH1');

Insert into FB_WO828610 values ('LCH2');

Insert into FB_WO828610 values ('LCH3');

Insert into FB_WO828610 values ('LCH4');

Insert into FB_WO828610 values ('LCH5');

Insert into FB_WO828610 values ('LCH5');

Insert into FB_WO828610 values ('LCH7');

Insert into FB_WO828610 values ('LCH8');

Insert into FB_WO828610 values ('LCH9');

Insert into FB_WO828610 values ('LCH10');

Insert into FB_WO828610 values ('LCH11');

Insert into FB_WO828610 values ('LCH12');

 

Commit;

 

 

Switch logfiles;

alter system switch logfile;

 

Count the table data for a confirmation on Primary

Select count (*) from FB_WO828610;

 

Note the time stamp before you drop the table.

select systimestamp from dual;

 

SYSTIMESTAMP

---------------------------------------------------------------------------

23-FEB-18 15.30.30.866715 +00:00

 

 

On Standby side Database Open in Read only mode;

alter database open read only;

 

Count the table data for a confirmation on Stand by  

Select count (*) from FB_WO828610;

 

Then drop table on Primary

drop table FB_WO828610;

 

Check for existence of the table on both primary and standby.

select count (*) from FXC_FINANCIAL.FB_WO828610;

 

Bring Standby DB to mount stage

shutdown immediate;

startup mount;

alter database recover managed standby database cancel;

flashback database to timestamp to_date('23-FEB-2018 15.30.30','DD-MON-YYYY HH24:MI:SS');

 

Open Standby database on Read Only mode

alter database open read only;

 

Then export  and import it in to Primary side. Do not use impdp utility

 

exp  file=/nfs/oradump_dba/apex/expdp_FB_WO828610.dmp log=/nfs/oradump_dba/apex/expdp_FB_WO828610.log tables=FB_WO828610;

imp  file=/nfs/oradump_dba/apex/expdp_FB_WO828610.dmp log=/nfs/oradump_dba/apex/impdp_FB_WO828610.log tables=FB_WO828610;

Role Back to a Restore point

Following restore point is available on the RAC database. We wanted to role back to the below restore point.

 

SQL> SELECT DATABASE_INCARNATION#,SCN,NAME,TIME,STORAGE_SIZE/1024/1024 "SIZE(MB)",GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

 

DATABASE_INCARNATION#        SCN NAME                                     TIME                                                                          SIZE(MB) GUA

--------------------- ---------- ---------------------------------------- --------------------------------------------------------------------------- ---------- ---

                    2 5.5896E+12 DATAGUARDTEST                            22-AUG-17 06.45.23.000000000                                                128677.508 YES

When roll backing  to the restore point following error raised

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS

---------------- ------------

RRRM02A2         MOUNTED

SQL>

SQL> FLASHBACK DATABASE TO RESTORE POINT DATAGUARDTEST;

FLASHBACK DATABASE TO RESTORE POINT DATAGUARDTEST

*

ERROR at line 1:

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

ORA-38762: redo logs needed for SCN 5589595118347 to SCN 5589595118351

ORA-38761: redo log sequence 33 in thread 2, incarnation 2 could not be

accessed

 

We have carried Below Steps to resolve this.

srvctl status database -d RRRM02A

srvctl stop database -d RRRM02A

srvctl status database -d RRRM02A

srvctl start instance -d RRRM02A -i RRRM02A1 -o mount

SQL> select name, host_name, status from gv$instance;

SQL> select log_mode,flashback_on from v$database;

SQL> select name,time from v$restore_point;

 

SQL>alter system set cluster_database=false scope=spfile sid='*';

Connect RMAN Catalogue

rman target / catalog RMAN_RRRM02/******@PRMAN02.CORP.LCH.COM

 

RMAN>FLASHBACK DATABASE TO RESTORE POINT DATAGUARDTEST;

RMAN> sql 'alter database open resetlogs';

SQL> alter system set cluster_database=true scope=spfile sid='*';

srvctl status database -d RRRM02A

SQL> select open_mode,database_role from gv$database;

Creating a Read Only Role for EM Console

Create a New User  to access em console.

create user oem identified by oem1  default tablespace users temporary tablespace temp;

Then that user have to have oem_monitor role.

grant create session, oem_monitor to oem;

Useful RMAN Commands for DayToday Use

List backup;  --Listing backup Detail view
List backup summary;-- Listing backup Summary View
List backupset;--listing backupset
List backup of spfile; --Listing only SPFILE
List backup of controlfile; --Listing only Control file

backup spfile;
backup current controlfile;
backup database plus archivelog;
backup database plus archivelog  delete input;

Active Database Duplication

Active Database Duplication Method

I write this on Duplicating the primary database on a Different server with same file locations.

Pre-Requirement

Create a  source database on one server and then replicate it on another server.

Edit tnsnames.ora  on primary server & stand by server.You will have to create static listener.
DB11G is primary server(Source)
DB11G_STBY is the new server.(Target)



DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)
    )
  )

Now You want to create static listeners.

On Source Machine 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

 On Target  Machine

SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )



Then start the listeners on both servers.

then go to rman command.

os > rman target sys/oracle@DB11G AUXILIARY sys/oracle@DB11G_STBY


This will connect to source database from the target database.

DUPLICATE DATABASE TO DB11G
FROM ACTIVE DATABASE
  SPFILE
  NOFILENAMECHECK;

This will restore the database on target server.Opens with Read write mode

Gather Stats to rescue yourself after Migrating to 11.2.0.4

This  was a scenario which was  faced by our team at the customer site

Oracle Database migration was planned  with OS Up gradation.

Old Database was 10g and runnig on AIX 6.1

New Database is 11g running on AIX 7.1

After migrating to 11.2.0.4  there were performance degradation.

work around carried out :

optimizer_features_enabled to 10.2.0.1

Alter system set optimizer_features_enabled=10.2.0.1 scope=both;

After altering to this Some queries which were taken too long time were run in quick time.

Lets say if query take  10S in 10.2.0,4 

That query take 40S in 11.2.0.4 

after setting optimizer_features_enabled to 10.2.0.1 it takes only 15S.

This rescue me for 2 days the performance issue raised  again.

After that again statistics were gathered and created all indexes online.

exec dbms_stats.gather_system_stats();

exec dbms_stats.gather_dictionary_stats

exec dbms_stats.gather_fixed_objects_stats();

exec dbms_stats.gather stats();

Rebuild all the Indexes  

 select OWNER,count(OBJECT_TYPE) from dba_objects where  OBJECT_TYPE='INDEX' group by owner

select 'ALTER INDEX ' || owner||'.'||index_name || ' rebuild online nologging;' from dba_indexes

where owner ='&OWNER';

This will give the  Alter Statements for Rebuild online INDEX.

Now the performance looks good ..!! :)

You can check when statistics were gathered last.

SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS
LAST; --Tables.
SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS
LAST; -- Indexes.

Moving Tables to new TableSpace.

Task: There was a requirement to move some tables to SSD disk which is on ASM

So the tasks carried out was

Created ASM  DISK Group  INDEX ASM.

Created  a tablespace  called DATA_SSD using EM

Then moved required tables to newly created tablespace.


Alter table table_name move tablespace new_tablespace;

Alter table EMPLOYEE move tablespace DATA_SSD;

After Creating this   It's required  to Rebuild Indexes

Alter index index_name rebuild tablespace new_tablespace;

Alter index EMPNO_IDX rebuild tablespace DATA_SSD;