Thinking Out Loud

October 22, 2021

Recover Dropped User Using Standby Database

Filed under: Dataguard — mdinh @ 12:18 am

A critical user was accidentally dropped around one week ago and will need be recovered.

Here are semi detailed steps used to flashback standby database, export user from standby, and import to primary.

============================================================
### Recover user that was deleted about week ago.
============================================================

Request Created: 14/Oct/2021 6:41 AM

DB version: 12.1.0.2.0 

==============================
### CURRENT FLASHBACK INFO:
==============================

SQL>

NAME  DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
----- -------------- --------- ----------------
ORC1  STANDBY1       MOUNTED   PHYSICAL STANDBY

 INST OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_MB EST_FLASHBACK_MB
----- -------------------- --------------------- ---------------- ------------ ----------------
    1 984681951010         04-OCT-2021 18:44:28  7200             1767000      895861

CURRENT_SCN
------------
985044762265

SQL>

==============================
### STOP STANDBY APPLY:
==============================

DGMGRL> edit database 'STANDBY1' set state='APPLY-OFF';
Succeeded.

DGMGRL>

========================================
### FLASHBACK USING TIMESTAMP FAILED:
========================================

SQL> shutdown abort;
SQL> startup mount restrict exclusive;

SQL> flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL>

========================================
### FLASHBACK USING SCN: 
to_timestamp can't convert to SCN properly
========================================

SQL> flashback database to scn 984681951011;

========================================
### FLASHBACK WILL EVENTUALLY FAIL:
========================================

SQL> flashback database to scn 984681951011;

flashback database to scn 984681951011
*
ERROR at line 1:
ORA-38861: flashback recovery stopped before reaching recovery target

SQL> 

========================================
### CHECK ALERT LOG:
========================================

Thu Oct 14 13:59:52 2021
Errors in file /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/ORC1_pr00_12838.trc:
ORA-00283: recovery session canceled due to errors
ORA-38861: flashback recovery stopped before reaching recovery target
ORA-16016: archived log for thread 1 sequence# 477183 unavailable
ORA-38861: signalled during: flashback database to scn 984681951011...

========================================
### RESTORE ARCHIVELOG from alert log:
========================================

RMAN> restore archivelog logseq 477183;

========================================
### CONTINUE FLASHBACK:
========================================

SQL> flashback database to scn 984681951011;

========================================
### DO NOT OPEN READ ONLY 
WITHOUT CANCELING REDO APPLY WITHOUT ADG.
========================================

Active Data Guard enables read-only access to a physical standby database while Redo Apply is active.

SQL> recover managed standby database cancel;
SQL> alter database open read only;

========================================
### CHECK FOR DROPPED USER:
========================================

SQL> select created, username from dba_users where username='XXX';

CREATED                    USERNAME
-------------------------- --------
2008-SEP-18 20:19:33       XXX
SQL>

========================================
### USING DATAPUMP DID NOT WORK:
========================================

How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (Doc ID 1356592.1)

--- For physical standby
We can execute exp in physical standby database when it is in read only
https://dbaminds.wordpress.com/2016/01/07/perform-export-expdp-from-physical-standby-and-logical-standby/

--- Use Snapshot Standby - did not test.
https://dohdatabase.com/2021/04/22/datapump-export-from-data-guard/

========================================
### EXP WORKED: PERFORMED BY CLIENT
========================================

### From STANDBY:
$ cat exp.par
file=exp.dmp
compress=n
grants=y
indexes=y
direct=y
log=exp.log
rows=y
consistent=y
owner=schema
triggers=y
constraints=y

exp userid=system parfile=exp.par
scp exp.dmp oracle@target:/home/oracle/

### From PRIMARY:
$ cat imppar
file=exp.dmp
grants=y
indexes=y
rows=y
log=imp.log
fromuser=schema
commit=n
constraints=y
compile=y

imp userid=system parfile=imp.par

========================================
### RECOVER STANDBY TO CURRENT TIME:
========================================

SQL> recover managed standby database using current logfile parallel 4 disconnect;
Media recovery complete.
SQL>

========================================
### RESTORE ARCHIVELOG TO FRA IS BEING DELETED:
========================================

Example:
Thu Oct 14 18:02:30 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477816.752.1085939417

Thu Oct 14 18:02:31 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477794.1120.1085939341

========================================
### RESTORE ARCHIVELOG TO NEW DESTINATION:
========================================

mkdir -p /ubb1/rman/ORC1/archivelog

========================================
### CHECK ALERT LOG FOR GAP SEQUENCE:
========================================

grep 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log

========================================
### RESOLVE ARCHIVE GAP:
========================================

Fetching gap sequence in thread 1, gap sequence 477604-477604

RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477604 until sequence 477604;}2> 3>

========================================
### FOR REFERENCE: 157 gap sequences
========================================

$ grep -c 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log
157

========================================
### RECOVER ALL ARCHIVELOG TO SAVE TIME:
========================================
 
SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
8723                         1        1 LGWR         RFS       RECEIVING       483532   119582      0
29047                        1        1 N/A          MRP0      APPLYING_LOG    477715  1545345      0

SQL>

========================================
### RESTORE ARCHIVELOG UNTIL SEQUENCE 483515:
========================================

RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;}2> 3>

ALTERNATIVE: 
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;

========================================
### ENABLE REDO APPLY USING DGMGRL:
========================================

SQL> recover managed standby database cancel;
Media recovery complete.
SQL>

DGMGRL> show configuration

Configuration - linkdg

  Protection Mode: MaxPerformance
  Members:
  PRIMARY - Primary database
    STANDBY1 - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the database

    STANDBY2 - Physical standby database 
    SBY3     - Physical standby database 
      Warning: ORA-16532: Oracle Data Guard broker configuration does not exist

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 28 seconds ago)

DGMGRL> show database STANDBY1

Database - STANDBY1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          1 minute 6 seconds (computed 0 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    ORC1

Database Status:
SUCCESS

DGMGRL> edit database 'STANDBY1' set state='APPLY-ON';
Succeeded.

DGMGRL> show database STANDBY1

Database - STANDBY1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 6.01 MByte/s
  Real Time Query:    OFF
  Instance(s):
    ORC1

Database Status:
SUCCESS

DGMGRL> validate database STANDBY1;

  Database Role:     Physical standby database
  Primary Database:  PRIMARY

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

DGMGRL> show configuration

Configuration - linkdg

  Protection Mode: MaxPerformance
  Members:
  PRIMARY - Primary database
    STANDBY1 - Physical standby database
    STANDBY2 - Physical standby database
    SBY3     - Physical standby database
      Warning: ORA-16532: Oracle Data Guard broker configuration does not exist

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 15 seconds ago)

DGMGRL>

Q.E.D.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: