My Precious Databank

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...

1. Identify Large Segments in the SYSTEM Tablespace Before deleting records, you may want to identify which segments occupy the most space in the SYSTEM tablespace. Run the following SQL command: SET PAGES 999 SET LINES 300 COL OWNER FOR A10 COL SEGMENT_NAME FOR A20 COL SEGMENT_TYPE FOR A15 COL MB FOR 9999999 SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, ROUND(BYTES/1024/1024) MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSTEM' ORDER BY BYTES DESC FETCH FIRST 5 ROWS ONLY; 2. Count Audit Records Older Than 180 Days To estimate the number of audit records that are older than 180 days, use this query: SELECT COUNT(*) FROM SYS.AUD$ WHERE ntimestamp# 3. Delete Audit Records in Batches To prevent undo tablespace issues during deletion, remove records in smaller batches. Use the following PL/SQL block: DECLARE v_limit NUMBER := 10000; -- Define batch size BEGIN LOOP DELETE FROM SYS.AUD$ WHERE ntimestamp# ...

1. Check the Current Configuration Before making any changes, verify if memory_target is set: SHOW PARAMETER memory_target; If memory_target is not 0, follow these steps: 2. Check Current SGA and PGA Values Run the following commands to view the current configurations: SHOW PARAMETER sga_target; SHOW PARAMETER sga_max_size; SHOW PARAMETER pga_aggregate_target; 3. Calculate and Set New Targets To adjust the SGA and PGA sizes, use the following commands. Replace the values with your desired configuration if needed: ALTER SYSTEM SET SGA_TARGET = 32G SCOPE = SPFILE; ALTER SYSTEM SET SGA_MAX_SIZE = 32G SCOPE = SPFILE; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 24G SCOPE = SPFILE; Note: If Automatic Memory Management (AMM) is enabled ( memory_target is not 0), adjust only MEMORY_TARGET instead of SGA_TARGET and PGA_AGGREGATE_TARGET . 4. Restart the Database To apply the changes, restart the database: SHUTDOWN IMMEDIATE; STARTUP; 5. Verify the New Configurati...

Let's create a table with 20 millions of random data. Be sure to index data column for comparing performance results.  CREATE TABLE not_partitioned_table (     id SERIAL PRIMARY KEY,     user_id INTEGER NOT NULL,     transaction_date DATE NOT NULL,     amount DECIMAL(10, 2),     status VARCHAR(20),     description TEXT,     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create an index on the transaction_date column CREATE INDEX idx_transaction_date ON not_partitioned_table(transaction_date); DO $$  DECLARE      i INT; BEGIN     FOR i IN 1..20000000 LOOP         INSERT INTO partitioned_table (user_id, transaction_date, amount, status, description)         VALUES (             (RANDOM() * 100000)::INT,             ...