My Precious Databank

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# < SYSDATE - 180; 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$

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,              CURRENT_DATE - ((RANDOM() * 3650)::INT),  -- Random date within the last 10 years             (RANDOM() * 1000)::DECIMAL(10,2),              CASE WHEN RANDOM() < 0.5 THEN 'completed' ELSE 'pending

   The query below shows what tablespaces does user have files and what are the total sizes of their objects in there:   SELECT  o.owner,     s.tablespace_name, FLOOR(SUM(s.bytes) / (1024 * 1024)) AS size_mb  FROM      dba_objects o JOIN      dba_segments s ON      o.object_name = s.segment_name     AND o.owner = s.owner     group by  o.owner,     s.tablespace_name   order by owner;