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.October 22, 2021
Recover Dropped User Using Standby Database
October 12, 2021
How To Change RMAN Config For Standby DB
Here is the typical error when changing RMAN configuration for standby database.
[oracle@ol7-112-dg2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:02:07 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3331620895, not open)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN> configure retention policy to recovery window of 7 days;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/12/2021 21:03:28
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$There are suggestions found from MOS. Yuck!
Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)
Thanks to teammate for the easy method.
[oracle@ol7-112-dg2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 21:04:04 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set pages 200
SQL> col value format a50
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
HAWK PHYSICAL STANDBY
SQL> select * from v$rman_configuration;
no rows selected
SQL> set serveroutput on
SQL> !vi t.sql
SQL> @t.sql
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := dbms_backup_restore.setconfig('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
5 dbms_output.put_line('setconfig returned ' || x);
6 END;
7 /
setconfig returned 1
PL/SQL procedure successfully completed.
SQL> select * from v$rman_configuration;
CONF# NAME
---------- -----------------------------------------------------------------
VALUE CON_ID
-------------------------------------------------- ----------
1 RETENTION POLICY
TO RECOVERY WINDOW OF 7 DAYS 0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg2 ~]$
[oracle@ol7-112-dg2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:08:47 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3331620895, not open)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$IMPORTANT: Delete existing configuration before updating with another.
Here is a demonstration of what happens when configuration is not deleted.
SQL> select * from v$rman_configuration;
CONF# NAME VALUE CON_ID
---------- -------------------- -------------------------------------------------- ----------
1 RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS 0
SQL> @t.sql
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := dbms_backup_restore.setconfig('RETENTION POLICY','TO REDUNDANCY 1');
5 dbms_output.put_line('setconfig returned ' || x);
6 END;
7 /
setconfig returned 2
PL/SQL procedure successfully completed.
SQL> select * from v$rman_configuration;
CONF# NAME VALUE CON_ID
---------- -------------------- -------------------------------------------------- ----------
1 RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS 0
2 RETENTION POLICY TO REDUNDANCY 1 0
SQL>
--------------------------------------------------
RMAN> show all;
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN>
--------------------------------------------------
SQL> exec DBMS_BACKUP_RESTORE.DELETECONFIG(1);
PL/SQL procedure successfully completed.
SQL> select * from v$rman_configuration;
CONF# NAME VALUE CON_ID
---------- -------------------- -------------------------------------------------- ----------
2 RETENTION POLICY TO REDUNDANCY 1 0
SQL>
--------------------------------------------------
RMAN> show all;
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN>
Thinking Out Loud 