DB Notes

Oracle Auto-Startup via Cron Use one of these two options to have both your primary and standby databases come up automatically on reboot. Paste the entire snippet into your Blogger HTML editor. Option 1: Listener + Data Guard Broker Only Prerequisites Broker Start Enabled In each database as SYSDBA: ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=SPFILE; Then restart so the Broker daemon comes up with the instance. Listener Configuration Make sure your listener.ora has the proper service entries for both primary and standby. Verify it can be started manually: lsnrctl status lsnrctl start Crontab Entry # As user oracle (`sudo crontab -u oracle -e`) @reboot /home/oracle/scripts/start_all.sh >> /home/oracle/scripts/start_all.log 2>&1 Startup Script /home/oracle/scripts/start_all.sh #!/bin/bash . /home/oracle/scripts/setEnv.sh # 1) Ensure listener is running lsnrctl start ...

Oracle 19c Data Guard – Standby Creation, Switchover & Failover Oracle 19c Physical Standby Creation & Management (RMAN Duplicate + Data Guard Broker) This article walks through building a physical standby using RMAN  DUPLICATE FROM ACTIVE DATABASE , then managing switchover / failover with the Data Guard Broker. The commands were tested on Oracle Linux 8 with Oracle Database 19c . Assumptions Two hosts ( oracle1.localdomain & oracle2.localdomain ) with Oracle 19c installed. Primary instance runs on oracle1 ; standby will be created on oracle2 . Listener port 1521 is reachable in both directions (check firewalls). Primary DB name MYDBPROD ; standby unique name MYDBDG . 1 – Primary Server Preparation 1.1 Switch to ARCHIVELOG & Force Logging -- Check mode SELECT log_mode FROM v$database; -- If NOARCHIVELOG: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCH...

Check the standby server: SQL> SELECT database_role FROM v$database; Stop the MRP process and switch to primary mode: SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH SID='*'; Restart the standby database and verify it is in primary mode: SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP; SQL> SELECT database_role FROM v$database; For setups with two or more standby servers: Check the data flow between standby servers. If an issue occurs with redirect_dml mode, restart the standby servers to resolve the problem. Note: Ensure the failover process is monitored carefully to avoid data inconsistencies.

Database Switchover Steps 1. Pre-operation Steps Check archive logs on both the primary and standby servers: Primary: SELECT thread#, MAX(sequence#) "Last Primary Seq Generated" FROM gv$archived_log val, gv$database vdb WHERE val.resetlogs_change# = vdb.resetlogs_change# GROUP BY thread# ORDER BY 1; Standby: SELECT thread#, MAX(sequence#) "Last Standby Seq Received" FROM gv$archived_log val, gv$database vdb WHERE val.resetlogs_change# = vdb.resetlogs_change# GROUP BY thread# ORDER BY 1; Verify the last applied log sequence: SELECT thread#, MAX(sequence#) "Last Standby Seq Applied" FROM gv$archived_log val, gv$database vdb WHERE val.resetlogs_change# = vdb.resetlogs_change# AND val.applied IN ('YES', 'IN-MEMORY') GROUP BY thread# ORDER BY 1; Verify initialization parameters: Ensure the following parameters are correctly configured: SELECT name, value FROM v$parameter WHERE UPPER(name) IN ('DB...

The following steps can be used to quickly reconfigure a corrupted standby database in Oracle Data Guard. These steps assume the configurations are already in place, and the commands below should be executed on the standby server. 1. Create a PFILE Create a PFILE from the current SPFILE: create pfile='/tmp/pfile.ora' from spfile; 2. Drop the Old Standby Database Use RMAN to drop the old standby database: RMAN> startup mount; RMAN> sql 'alter system enable restricted session'; RMAN> drop database including backups noprompt; 3. Create SPFILE from the PFILE After dropping the database, recreate the SPFILE from the previously created PFILE: sqlplus / as sysdba create spfile from pfile='/tmp/pfile.ora'; startup nomount; 4. Start Database Recovery Use RMAN to duplicate the database for standby: rman target sys@DWHPRI auxiliary sys@DWHDG duplicate target database for standby from active database dorecover nofilenamecheck; 5. Manage S...

Steps below can be used to recover disconnected & unrecoverable standby databases. The commands below should be run on standby server.   1) Create pfile: create pfile='/tmp/pfile.ora' from spfile;   2) Drop old database. RMAN> startup mount; RMAN> sql 'alter system enable restricted session'; RMAN> drop database including backups noprompt;   3) Create spfile from pfile. sqlplus / as sysdba create spfile from pfile='/tmp/pfile.ora'; startup nomount;   4) Start database recover operation. rman target sys@primarydb auxiliary sys@stbydb (names coming from tnsnames.ora) duplicate target database for standby from active database dorecover nofilenamecheck; 5) After recovery completed, run the command below: alter database recover managed standby database disconnect from session; Note: If you get an error, shutdown the database and re-mount it. (startup mount)    6) Run the select below, if GAP_STATUS column equals to "NO GAP"  then recover the da...