isitdevops.com/databases

=====
===== SET UP FLASHBACK
=====
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =10G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘+DISKN’;
SQL> SELECT * FROM V$FLASHBACK_DATABASE_LOG;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> SHOW PARAMNETER DB_RECOVERY_FILE_DEST_SIZE;
SQL> SHOW PARAMNETER DB_RECOVERY_FILE_DEST;
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE= ???;

=====
===== GET FLASHBACK INFORMATION
=====
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
SQL> SHOW PARAMETER FLASH
SQL> SHOW PARAMETER DB_RECOVERY
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

SQL> SELECT (100-SUM(PERCENT_SPACE_USED)) SPACE_REMAINING_PERCENTAGE FROM V$FLASH_RECOVERY_AREA_USAGE;

SQL> SELECT ((P.VALUE/(1048576*1024))*F.SPACE_USED_PERCENTAGE) GB_USED FROM
(SELECT (SUM(PERCENT_SPACE_USED)/100) SPACE_USED_PERCENTAGE FROM V$FLASH_RECOVERY_AREA_USAGE) F,
V$PARAMETER P WHERE P.NAME=’DB_RECOVERY_FILE_DEST_SIZE’;

SQL> SELECT ((P.VALUE/(1048576*1024))*F.SPACE_REMAINING_PERCENTAGE) GB_REMAINING FROM
(SELECT 1-(SUM((PERCENT_SPACE_USED))/100) SPACE_REMAINING_PERCENTAGE FROM V$FLASH_RECOVERY_AREA_USAGE) F,
V$PARAMETER P WHERE P.NAME=’DB_RECOVERY_FILE_DEST_SIZE’;

SQL> SELECT ((P.VALUE/(1048576*1024))*F.SPACE_RECLAIMABLE_PERCENTAGE) GB_RECLAIMABLE FROM
(SELECT (SUM((PERCENT_SPACE_RECLAIMABLE))/100) SPACE_RECLAIMABLE_PERCENTAGE FROM V$FLASH_RECOVERY_AREA_USAGE) F,
V$PARAMETER P WHERE P.NAME=’DB_RECOVERY_FILE_DEST_SIZE’;

SQL> SELECT  OLDEST_FLASHBACK_SCN SCN, TO_CHAR(OLDEST_FLASHBACK_TIME, ‘HH24:MI:SS DD/MM/YYYY’) OLDEST_TIME FROM V$FLASHBACK_DATABASE_LOG;

=====
===== SQL TO SHOW ARCHIVE AND REDO LOGS (ORACLE 10G 11G)         
=====
SQL> COL GRP FORM 99
COL SEQU FORM 99999
COL THRD FORM 999
COL SIZ_K FORM 999,999
COL FILE_NAME FORM A30 WRAP
SELECT LOG.GROUP#         GRP, 
       LOG.THREAD#              THRD,
       LOG.SEQUENCE#            SEQU,
       LOG.BYTES/1024           SIZ_K,
       TO_CHAR(LOG.FIRST_TIME,’DD-MM-HH:MI.SS’) FIRST_TIM,
       SUBSTR(LOG.ARCHIVED,1,1) AR,
       SUBSTR(LOG.STATUS,1,4)   STS,
       LOGF.MEMBER              FILE_NAME
   FROM       V$LOG LOG, V$LOGFILE LOGF
   WHERE      LOG.GROUP# = LOGF.GROUP#
   ORDER BY   LOG.GROUP#, FILE_NAME
/
CLEAR COL

 
=====
===== CONVERT A STANDBY TO READ/WRITE AND THEN FLASHBACK TO STANDBY
=====
  On primary
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
  # on standby enable flashback and disable recovery
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’/ORAARCH1′;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT BEFORE_START GUARANTEE FLASHBACK DATABASE;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> ALTER DATABASE OPEN;
  # RUN READ/WRITE TEST ON OPENED STANDBY DATABASE.
  # FLASHBACK AND SWITCH BACK TO PHYSICAL STANDBY
SQL> STARTUP MOUNT FORCE;
SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_START;
SQL> DROP RESTORE POINT BEFORE_START;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
  # ON THE PRIMARY DATABASE ENABLE LOG SHIPPING
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

=====
===== USING FLASHBACK DATABASE AFTER ISSUING AN OPEN RESETLOGS (ORACLE 11G)
=====
  #STEP 1 DETERMINE THE SCN BEFORE THE RESETLOGS OPERATION OCCURRED.
  #ON THE PRIMARY DATABASE OBTAIN THE VALUE OF THE SCN THAT IS 2 SCNS
  #BEFORE THE RESETLOGS OPERATION OCCURRED ON THE PRIMARY DATABASE:
SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# – 2) FROM V$DATABASE;

  #STEP 2 – ON THE STANDBY DATABASE OBTAIN THE CURRENT SCN.
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

  #STEP 3 DETERMINE IF IT IS NECESSARY TO FLASH BACK THE DATABASE.
  #IF THE VALUE OF CURRENT_SCN IS LARGER THAN THE VALUE OF RESETLOGS_CHANGE# – 2,
  #ISSUE THE FOLLOWING STATEMENT TO FLASH BACK THE STANDBY DATABASE.
 #PHYSICAL::::
SQL> FLASHBACK STANDBY DATABASE TO SCN RESETLOGS_CHANGE# -2;
  #IF THE VALUE OF CURRENT_SCN IS LESS THAN THE VALUE OF THE RESETLOGS_CHANGE# – 2, SKIP TO STEP 4.
  #IF THE STANDBY DATABASE’S SCN IS FAR ENOUGH BEHIND THE PRIMARY DATABASE’S SCN,
  #LOG APPLY SERVICES WILL BE ABLE TO CONTINUE THROUGH THE OPEN RESETLOGS STATEMENT WITHOUT STOPPING.
  #IN THIS CASE, FLASHING BACK THE DATABASE IS UNNECESSARY BECAUSE LOG APPLY SERVICES DO NOT STOP
  #UPON REACHING THE OPEN RESETLOGS STATEMENT IN THE REDO DATA.

  #STEP 4 RESTART REDO APPLY ON THE PHYSICAL STANDBY DATABASE.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
#LOGICAL::::
  #On primary determine an SCN at least 2 SCN’s prior to the OPEN RESETLOGS
SQL> SELECT TO_CHAR(resetlogs_change# – 2) FROM v$DATABASE;
  #On standby, obtain the current SCN
SQL> SELECT TO_CHAR(current_scn) FROM V$DATABASE;
  #On standby flashback db
SQL> FLASHBACK STANDBY DATABASE TO SCN
  #On standby restart recovery
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
  #THE STANDBY DATABASE IS NOW READY TO RECEIVE AND APPLY REDO FROM THE PRIMARY DATABASE.

=====
===== FLASH BACK A FAILED PRIMARY DATABASE TO A PHYSICAL STANDBY DATABASE (ORACLE 11G)         
=====
  #ON THE NEW PRIMARY DATABASE DETERMINE THE SCN AT WHICH THE OLD
  #STANDBY DATABASE BECAME THE NEW PRIMARY DATABASE.
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

  #STEP 2 FLASH BACK THE FAILED PRIMARY DATABASE.
  #SHUT DOWN THE OLD PRIMARY DATABASE, MOUNT IT, AND FLASH IT BACK TO THE VALUE
  #FOR STANDBY_BECAME_PRIMARY_SCN THAT WAS DETERMINED IN STEP 1:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN STANDBY_BECAME_PRIMARY_SCN;

  #STEP 3 CONVERT THE DATABASE TO A PHYSICAL STANDBY DATABASE.
  #PERFORM THE FOLLOWING STEPS ON THE OLD PRIMARY DATABASE:
  #ISSUE THE FOLLOWING STATEMENT ON THE OLD PRIMARY DATABASE:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
  #THIS STATEMENT WILL DISMOUNT THE DATABASE AFTER SUCCESSFULLY CONVERTING
  #THE CONTROL FILE TO A STANDBY CONTROL FILE.
  #SHUT DOWN AND RESTART THE DATABASE:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;

  #STEP 4 RESTART TRANSPORTING REDO TO THE NEW PHYSICAL STANDBY DATABASE.
  #STEP 5 START REDO APPLY.
  #START REDO APPLY OR REAL-TIME APPLY ON THE NEW PHYSICAL STANDBY DATABASE:
  #TO START REDO APPLY:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 

=====
===== FLASH BACK A FAILED PRIMARY DATABASE TO A LOGICAL STANDBY DATABASE (ORACLE 11G)         
=====
  #STEP 1 DETERMINE THE SCN TO WHICH TO FLASH BACK THE FAILED PRIMARY DATABASE.
  #ON THE NEW PRIMARY DATABASE, ISSUE THE FOLLOWING QUERY TO DETERMINE THE SCN TO WHICH YOU WANT TO FLASH BACK THE FAILED PRIMARY DATABASE:
SQL> SELECT APPLIED_SCN AS FLASHBACK_SCN FROM V$LOGSTDBY_PROGRESS;

  #STEP 2 DETERMINE THE LOG FILES THAT MUST BE COPIED TO THE FAILED PRIMARY DATABASE FOR FLASHBACK DATABASE.
  #ON THE NEW PRIMARY DATABASE, ISSUE THE FOLLOWING QUERY TO DETERMINE THE LOG FILES THAT MUST BE COPIED TO THE FAILED PRIMARY DATABASE FOR FLASHBACK DATABASE TO REACH A CONSISTENT STATE
SQL> SELECT NAME FROM DBA_LOGSDTBY_LOG  2>   WHERE NEXT_CHANGE# >   3>           (SELECT VALUE FROM DBA_LOGSTDBY_PARAMETERS  4>   WHERE NAME = ‘STANDBY_BECAME_PRIMARY_SCN’) 5>           AND FIRST_CHANGE   #TO CREATE A NEW LOGICAL STANDBY DATABASE, SHUT DOWN THE DATABASE (IF NECESSARY), MOUNT THE FAILED PRIMARY DATABASE, FLASH IT BACK TO THE FLASHBACK_SCN DETERMINED IN STEP 1, AND ENABLE THE DATABASE GUARD.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> FLASHBACK DATABASE TO SCN BECAME_PRIMARY_SCN; SQL> ALTER DATABASE GUARD ALL;

  #STEP 4 OPEN THE DATABASE WITH THE RESETLOGS OPTION.
SQL> ALTER DATABASE OPEN RESETLOGS;

  #STEP 5 CREATE A DATABASE LINK TO THE NEW PRIMARY DATABASE AND START SQL APPLY.
SQL> CREATE PUBLIC DATABASE LINK MYLINK
  2> CONNECT TO SYSTEM IDENTIFIED BY PASSWORD   
  3> USING ‘SERVICE_NAME_OF_NEW_PRIMARY_DATABASE’; 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY MYLINK;
  #THE ROLE REVERSAL IS NOW COMPLETE.