Oracle DBA (12c/19c) Articles and Scripts

  Start Redo Apply SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; OR SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT; ** Remember that in Oracle 12c, Real-time Redo Apply is enabled by Default. So, the first command will enable the real-time apply (This behavior is different than 11g version). This means, if you have specified "DELAY" option in your Redo Transport parameter (log_archive_dest_n) on the primary database and you enable the redo apply using the first command on Standby Database in 12c version, then the database will ignore the DELAY option and apply the redo in real-time. (You will see a message similar to below in the alert.log file informing you that the DELAY option is ignored). WARNING: Managed Standby Recovery started with REAL TIME APPLY   DELAY 30 minutes specified at primary ignored ** If you want to use DELAY option in Oracle 12c, you must enable redo apply using the 2nd commnd. Stop Redo App

  Active Data Guard If you want to run the Redo Apply Process and at the same time, open the standby database in read-only mode, you will have to go for Active Data Guard option. This is not possible in a normal Data Guard database (Physical Standby). Remember that, Active Data Guard needs additional licenses and additional cost. So, please check with your Oracle representative before going for this option. Steps to Enable Active Data Guard Option 1. Cancel The Redo Apply Process SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 2. Shutdown and start the database in Mount Mode SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT 3. Open the database in Read-Only Mode (** If you do not want use the Active Data Guard option, then you do not have to execute the next Step. However, remember that, the redo apply will be disabled and real-time query can not be used) SQL> ALTER DATABASE OPEN; 4. Start the Redo Apply. (Executing this Step will enable the Active Data Guard Opti

  New DataPump feature disables redo logging when loading data into tables and when creating indexes. For my testing of the new feature I will be importing the SOE schema from the SwingBench tool. The SOE schema is about 2G and size. Test 1 I will be to import the normal way. $impdp parfile=impdp.par impdp.par directory=DATA_PUMP_DIR2 dumpfile=exp_schema_soe%U.dmp logfile=imp_schema_soe.log parallel=2 Before the import there were zero archive logs In the first test we should see redolog and archive logs being generated during the import of the SOE schema. Import During the import we can see redologs and archivelogs are being generated during the import After the import 25 archive logs were generated The import had an elapsed time of 14 minutes with 41 seconds Test 2  I will import using the new feature DISABLE_ARCHIVE_LOGGING SQL> drop user SOE cascade; $impdp parfile=impdp_noarchive.par impdp_noarchive.par directory=DATA_PUMP_DIR2 dumpfile=exp_schema_soe%U.dmp logfile=imp_schema_so

In this article, I’m demonstrating on a scenario where the one of the datafile of the primary database is corrupted and how it can be restored back from it’s standby  database. The following steps can also be followed for scenarios where the datafile of the primary database is accidentally deleted from the File system or ASM  diskgroup. Primary database: srpstb Standby database: srprim Primary database server: ora1-2 Standby database server: ora1-1 Primary database: From the below outcome, it can be noted that datafile 9 of tablespace MYTS is corruped on the primary database. SQL> select status,instance_name,database_role,open_mode from v$database,v$instance ; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------ ------------- ------------- ----------- OPEN   srpstb        PRIMARY       READ WRITE SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ----- ------ ------ ------------------ ---------- 9     128    1      0                  C

  Snapshot standby database can be used to perform both  READ and WRITE   activities. Most importantly, a   snapshot standby database keeps receiving the redo data from the primary database but does not apply them.  These redo data received from the primary database would be applied only when the snapshot standby database is converted back to the Physical standby mode. There by the snapshot standby database provides data protection on primary database. A snapshot standby database will allow you to make use of the data available on the physical standby database (which is a mirrored copy of the primary database).  This allows the users to test the application on a standby database which has the primary data before implementing it in the Real production environment. When a physical standby database is converted to a snapshot standby database, a  guaranteed restore point is automatically created .flash back is automaticlly created a Once when the updateable transactions are completed for t