Oracle Database Scripts

Saturday, April 11, 2026

Recovery option with only archivelogs in oracle db

1. Recreate the Datafile (No Backup Method)
This uses the control file to "re-initialize" the file.
Warning: This only works if you have all archive logs since the file was first added to the database.

ERROR at line 1:
ORA-01122: database file 27 failed verification check
ORA-01110: data file 27: '/pac/instance/data/cim_dat13.dbf'
ORA-01210: data file header is media corrupt

-- Step 1: Identify and offline the file
ALTER DATABASE DATAFILE 27 OFFLINE;

-- Step 2: Recreate the empty structure
ALTER DATABASE CREATE DATAFILE 27;
-- OR to a new path if the old disk is failing:
ALTER DATABASE CREATE DATAFILE 27 AS '/pac/instance/data/cim_dat13.dbf';

-- Step 3: Apply the transactions from logs
RECOVER DATAFILE 27;

-- Step 4: Bring it back to the database
ALTER DATABASE DATAFILE 27 ONLINE;

2.datafile using rman:

RMAN> RUN {
SET NEWNAME FOR DATAFILE 27 TO '/pac/instance/data/cim_dat13.dbf'; -- Optional: change path
RESTORE DATAFILE 27; -- RMAN creates the file if it doesn't exist
RECOVER DATAFILE 27;
}

3. Block Media Recovery (Repairing specific errors)
If the header is okay but specific data blocks are corrupted (e.g., ORA-01578),
you can repair just those blocks without taking the whole file offline.
RMAN will use the archivelogs to "re-build" the block.
-- Repair a specific block in a file
RMAN> RECOVER DATAFILE 27 BLOCK 1;

-- Repair all blocks listed in V$DATABASE_BLOCK_CORRUPTION
RMAN> RECOVER CORRUPTION LIST;

4. Point-in-Time Recovery (PITR)
While PITR usually requires a backup, if you are performing a RECOVER DATABASE and want to stop at
a specific point because you are missing later logs, you can use these variants:
-- Stop recovery at a specific time
SQL> RECOVER DATABASE UNTIL TIME '2023-10-27:14:00:00';

-- Stop recovery at a specific System Change Number (SCN)
SQL> RECOVER DATABASE UNTIL SCN 123456789;

-- Manually apply logs until you choose to stop
SQL> RECOVER DATABASE UNTIL CANCEL;

5. Tablespace-Level Recovery
If multiple files in a single tablespace are corrupted, you can recover them together.
SQL> ALTER TABLESPACE cim_data_ts OFFLINE;
SQL> RECOVER TABLESPACE cim_data_ts;
SQL> ALTER TABLESPACE cim_data_ts ONLINE;

6. Critical Constraints

    The "SYSTEM" Restriction: You generally cannot recreate the SYSTEM tablespace (Datafile 1)
    this way because the database needs it just to read the logs and control files.
    Gap in Logs: If you are missing even one archivelog in the sequence, the RECOVER command
    will fail with an ORA-00308 (archived log not found).

Thursday, April 9, 2026

Complete Standby Database Setup from Scratch

Environment Details (Example)

Primary Server  : primary_server   IP: 192.168.1.10
Standby Server  : standby_server   IP: 192.168.1.20
DB Name         : ORCL
Primary SID     : ORCL
Standby SID     : ORCLDG
Oracle Home     : /u01/app/oracle/product/19c/dbhome_1
Oracle Base     : /u01/app/oracle

PHASE 1 — PRIMARY SERVER PREPARATION

Step 1 — Check Primary Database Mode

-- Must be in ARCHIVELOG mode
SQL> SELECT LOG_MODE FROM V$DATABASE;

-- If NOARCHIVELOG, enable it
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-- Verify
SQL> SELECT LOG_MODE FROM V$DATABASE;
-- OUTPUT: ARCHIVELOG

Step 2 — Enable Force Logging on Primary

SQL> ALTER DATABASE FORCE LOGGING;

-- Verify
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
-- OUTPUT: YES

Step 3 — Enable Flashback (Recommended)

SQL> ALTER DATABASE FLASHBACK ON;

-- Verify
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

Step 4 — Create Standby Redo Logs on Primary

-- Formula: (primary redo log groups + 1) x threads
-- Example: Primary has 3 groups → create 4 SRL groups

-- Check existing redo log size first
SQL> SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG;

-- Create Standby Redo Logs (same size as online redo logs)
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
     '/u01/app/oracle/oradata/ORCL/stdby_redo04.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
     '/u01/app/oracle/oradata/ORCL/stdby_redo05.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
     '/u01/app/oracle/oradata/ORCL/stdby_redo06.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
     '/u01/app/oracle/oradata/ORCL/stdby_redo07.log' SIZE 200M;

-- Verify
SQL> SELECT GROUP#, MEMBER FROM V$STANDBY_LOG;

Step 5 — Configure Primary init.ora / spfile Parameters

-- Check current parameter file type
SQL> SHOW PARAMETER spfile;

-- Set Data Guard parameters on PRIMARY
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MODE=TRUE SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
     'LOCATION=/u01/app/oracle/archivelog/ORCL/
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
      DB_UNIQUE_NAME=ORCL' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'SERVICE=ORCLDG LGWR ASYNC
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=ORCLDG' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ORCL' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(ORCL,ORCLDG)' SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_SERVER='ORCLDG' SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_CLIENT='ORCL' SCOPE=BOTH;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
     '/oradata/ORCL/','/oradata/ORCLDG/' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
     '/oradata/ORCL/','/oradata/ORCLDG/' SCOPE=SPFILE;

Step 6 — Configure listener.ora on Primary

# Edit /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCL)
    )
  )

Step 7 — Configure tnsnames.ora on Primary

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDG)
    )
  )

# Reload listener
lsnrctl reload
lsnrctl status

Step 8 — Create Password File on Primary

# Check existing password file
ls $ORACLE_HOME/dbs/orapwORCL

# Create if not exists
orapwd file=$ORACLE_HOME/dbs/orapwORCL password=SysPassword123 entries=10

# Copy password file to standby (MUST be identical)
scp $ORACLE_HOME/dbs/orapwORCL \
    oracle@standby_server:$ORACLE_HOME/dbs/orapwORCLDG

PHASE 2 — STANDBY SERVER PREPARATION

Step 9 — Create Directory Structure on Standby

# Login to standby server as oracle user
ssh oracle@standby_server

# Create all required directories
mkdir -p /u01/app/oracle/oradata/ORCLDG
mkdir -p /u01/app/oracle/archivelog/ORCLDG
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDG
mkdir -p /u01/app/oracle/admin/ORCLDG/adump
mkdir -p /u01/app/oracle/admin/ORCLDG/bdump
mkdir -p /u01/app/oracle/admin/ORCLDG/cdump
mkdir -p /u01/app/oracle/admin/ORCLDG/udump
mkdir -p /u01/app/oracle/admin/ORCLDG/pfile
mkdir -p /backup/standby

# Set correct permissions
chown -R oracle:oinstall /u01/app/oracle/oradata/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/archivelog/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/admin/ORCLDG
chown -R oracle:oinstall /backup/standby

chmod -R 755 /u01/app/oracle/oradata/ORCLDG
chmod -R 755 /u01/app/oracle/archivelog/ORCLDG

# Verify directories
ls -lR /u01/app/oracle/oradata/ORCLDG
ls -lR /u01/app/oracle/admin/ORCLDG

Step 10 — Create standby init.ora on Standby

vi /u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora

# ── Database Identity ──────────────────────────────────
*.db_name='ORCL'                          # Same as primary
*.db_unique_name='ORCLDG'                 # Different from primary
*.db_domain=''

# ── Instance ───────────────────────────────────────────
*.instance_name='ORCLDG'

# ── Memory ─────────────────────────────────────────────
*.sga_target=2G
*.pga_aggregate_target=512M
*.memory_target=0

# ── Paths ──────────────────────────────────────────────
*.db_create_file_dest='/u01/app/oracle/oradata/ORCLDG'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCLDG'
*.db_recovery_file_dest_size=50G

*.audit_file_dest='/u01/app/oracle/admin/ORCLDG/adump'
*.core_dump_dest='/u01/app/oracle/admin/ORCLDG/cdump'
*.background_dump_dest='/u01/app/oracle/admin/ORCLDG/bdump'
*.user_dump_dest='/u01/app/oracle/admin/ORCLDG/udump'

# ── File Name Convert (Primary path → Standby path) ───
*.db_file_name_convert=
    '/u01/app/oracle/oradata/ORCL/',
    '/u01/app/oracle/oradata/ORCLDG/'

*.log_file_name_convert=
    '/u01/app/oracle/oradata/ORCL/',
    '/u01/app/oracle/oradata/ORCLDG/'

# ── Archivelog ─────────────────────────────────────────
*.log_archive_dest_1=
    'LOCATION=/u01/app/oracle/archivelog/ORCLDG/
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
     DB_UNIQUE_NAME=ORCLDG'

*.log_archive_dest_2=
    'SERVICE=ORCL LGWR ASYNC
     VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
     DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='DG_CONFIG=(ORCL,ORCLDG)'

# ── Data Guard ─────────────────────────────────────────
*.fal_server='ORCL'
*.fal_client='ORCLDG'
*.standby_file_management='AUTO'

# ── Redo Transport ─────────────────────────────────────
*.remote_login_passwordfile='EXCLUSIVE'

# ── Misc ───────────────────────────────────────────────
*.compatible='19.0.0'
*.open_cursors=300
*.processes=300
*.undo_tablespace='UNDOTBS1'
*.control_files='/u01/app/oracle/oradata/ORCLDG/control01.ctl',
                '/u01/app/oracle/oradata/ORCLDG/control02.ctl'

Step 11 — Configure listener.ora on Standby

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDG)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCLDG)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDG_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCLDG)
    )
  )

Step 12 — Configure tnsnames.ora on Standby

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDG)
    )
  )

# Start listener on standby
lsnrctl start
lsnrctl status

Step 13 — Set Oracle Environment on Standby

# Edit .bash_profile on standby
vi ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID=ORCLDG
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

# Apply
source ~/.bash_profile

# Verify
echo $ORACLE_SID

Step 14 — Test Connectivity Between Primary and Standby

# From primary — test connection to standby
tnsping ORCLDG

# From standby — test connection to primary
tnsping ORCL

# Test RMAN connection from standby
rman TARGET sys/password@ORCL
rman TARGET sys/password@ORCLDG

PHASE 3 — BUILD STANDBY USING RMAN DUPLICATE

Step 15 — Start Standby in NOMOUNT

# On standby server
export ORACLE_SID=ORCLDG

sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora';

Step 16 — Run RMAN DUPLICATE from Standby Server

# Connect RMAN to both primary (TARGET) and standby (AUXILIARY)
rman TARGET sys/SysPassword123@ORCL \
     AUXILIARY sys/SysPassword123@ORCLDG

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY
      FROM ACTIVE DATABASE
      DORECOVER
      SPFILE
        PARAMETER_VALUE_CONVERT
          '/u01/app/oracle/oradata/ORCL/',
          '/u01/app/oracle/oradata/ORCLDG/'
        SET db_unique_name='ORCLDG'
        SET db_file_name_convert=
            '/u01/app/oracle/oradata/ORCL/',
            '/u01/app/oracle/oradata/ORCLDG/'
        SET log_file_name_convert=
            '/u01/app/oracle/oradata/ORCL/',
            '/u01/app/oracle/oradata/ORCLDG/'
        SET control_files=
            '/u01/app/oracle/oradata/ORCLDG/control01.ctl',
            '/u01/app/oracle/oradata/ORCLDG/control02.ctl'
        SET log_archive_dest_1=
            'LOCATION=/u01/app/oracle/archivelog/ORCLDG/'
        SET fal_server='ORCL'
        SET fal_client='ORCLDG'
        SET standby_file_management='AUTO'
      NOFILENAMECHECK;

RMAN will now automatically copy all datafiles, controlfile, and apply archivelogs. This may take time depending on DB size.

PHASE 4 — POST DUPLICATE STEPS

Step 17 — Create spfile from pfile on Standby

SQL> CREATE SPFILE FROM PFILE=
     '/u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora';

-- Restart using spfile
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 18 — Start MRP Apply Process

-- Start Managed Recovery Process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     DISCONNECT FROM SESSION;

-- OR with real-time apply (preferred)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Step 19 — Verify Standby is Working

-- Check database role
SQL> SELECT DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE,
            PROTECTION_MODE FROM V$DATABASE;

-- Check MRP process
SQL> SELECT PROCESS, STATUS, SEQUENCE#,
            BLOCKS FROM V$MANAGED_STANDBY;

-- Check archive log apply status
SQL> SELECT SEQUENCE#, APPLIED, FIRST_TIME,
            NEXT_TIME FROM V$ARCHIVED_LOG
     ORDER BY SEQUENCE# DESC;

-- Check gap between primary and standby
SQL> SELECT * FROM V$ARCHIVE_GAP;

-- Compare sequences
-- On Primary:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

-- On Standby:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
     WHERE APPLIED = 'YES';

Complete Checklist

PRIMARY SIDE
─────────────────────────────────────────────────
[ ] Database in ARCHIVELOG mode
[ ] FORCE LOGGING enabled
[ ] Standby Redo Logs created
[ ] LOG_ARCHIVE_DEST_2 pointing to standby
[ ] LOG_ARCHIVE_CONFIG set
[ ] FAL_SERVER / FAL_CLIENT set
[ ] listener.ora updated with static entry
[ ] tnsnames.ora has both ORCL and ORCLDG entries
[ ] Password file created and copied to standby

STANDBY SIDE
─────────────────────────────────────────────────
[ ] All directories created with correct permissions
[ ] init_ORCLDG.ora created with standby parameters
[ ] db_file_name_convert set correctly
[ ] log_file_name_convert set correctly
[ ] listener.ora configured with static SID entry
[ ] tnsnames.ora has both entries
[ ] ORACLE_SID set to ORCLDG in .bash_profile
[ ] tnsping working both ways
[ ] STARTUP NOMOUNT successful
[ ] DUPLICATE completed without errors
[ ] spfile created from pfile
[ ] MRP process started
[ ] Sequence# in sync with primary
[ ] No gaps in V$ARCHIVE_GAP

Key Points to Remember

Item	Value
`db_name`	Same on primary and standby
`db_unique_name`	Different (ORCL vs ORCLDG)
Password file	Must be identical on both
Standby Redo Logs	Must exist on both servers
`NOFILENAMECHECK`	Use when directory paths are different
MRP	Must be running for real-time sync
After switchover	Always take fresh Level 0 backup

___________________________________***______________________________________

Minimal init.ora for Standby Database

Absolute Minimum Parameters Required

# /u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora

# ── Must Have ──────────────────────────────────────────

# Same as primary db_name

db_name='ORCL'

# Unique name for standby

db_unique_name='ORCLDG'

# Controlfile location on standby

control_files='/u01/app/oracle/oradata/ORCLDG/control01.ctl',

'/u01/app/oracle/oradata/ORCLDG/control02.ctl'

# Convert primary paths to standby paths

db_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/ORCLDG/'

log_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/ORCLDG/'

# Required for redo transport authentication

remote_login_passwordfile='EXCLUSIVE'

# Audit directory must exist

audit_file_dest='/u01/app/oracle/admin/ORCLDG/adump'

# Oracle version compatibility

compatible='19.0.0'

RMAN BACKUP RESTORE RECOVERY

Incremental backups:

Backuping up database based on changed blocks

stratergies:- incremental (differential(default) and cumulative )

incremental :- level 0 (full db) , level 1 ( backuping blocks since level 0 )

differential:- backuping db since last level 1 backup.

cumulative:- backuping db since last level 0 backup.

differential:- sun(level 0), mon (level 1) , tue(level 1), wed(level 1), thu(level 1), fri(level 1), sat(level 1)

cumulative:- sun(level 0), mon(sun+level 1), tue(sun+mon+level1), wed(sun+mon+tue+level1),

thu(sun+mon+tue+wed+level1), fri(sun+mon+tue+wed+thu+level1),sat(sun+mon+tue+wed+thu+fri+level1)

Recovery stepsfor both (differential & cumulative will be same rman auto-select backups):-

RMAN> STARTUP MOUNT;

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

Side-by-Side Comparison

Factor	Differential	Cumulative
Backup size (daily)	Smaller (only daily changes)	Larger (grows each day)
Backup time	Faster	Slower (more blocks each day)
Storage usage	Less	More
Recovery time	Slower (apply multiple L1s)	Faster (apply only 1 L1)
Backup sets to restore	L0 + all L1s since L0	L0 + latest L1 only
Complexity	Slightly complex recovery	Simple recovery
Best for	Limited storage environments	Fast recovery requirement (low RTO)

Point-in-time-recovery:-

1. Restore to specific time:-

RMAN> STARTUP MOUNT;

RMAN> RUN {
SET UNTIL TIME "TO_DATE('2024-04-09 14:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

2. Restore to specific scn:-

RMAN> STARTUP MOUNT;

RMAN> RUN {

SET UNTIL SCN 1234567;

RESTORE DATABASE;

RECOVER DATABASE;

}

RMAN> ALTER DATABASE OPEN RESETLOGS;

3. Restore to specific log sequence:-

RMAN> STARTUP MOUNT;

RMAN> RUN {

SET UNTIL SEQUENCE 500 THREAD 1;

RESTORE DATABASE;

RECOVER DATABASE;

}

RMAN> ALTER DATABASE OPEN RESETLOGS;

_________________________________________________________________

TABLESPACE RECOVERY:-

-- Take tablespace offline

SQL> ALTER TABLESPACE users OFFLINE IMMEDIATE;

-- Restore and recover tablespace

RMAN> RESTORE TABLESPACE users;

RMAN> RECOVER TABLESPACE users;

-- Bring tablespace online

SQL> ALTER TABLESPACE users ONLINE;

_________________________________________________________________

Datafile recovery:-

-- If datafile is missing/corrupted

RMAN> RESTORE DATAFILE 5;

RMAN> RECOVER DATAFILE 5;

-- Or by filename

RMAN> RESTORE DATAFILE '/oradata/users01.dbf';

RMAN> RECOVER DATAFILE '/oradata/users01.dbf';

__________________________________________________________________

Control file recovery:-

-- If controlfile is lost

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- OR from specific location

RMAN> RESTORE CONTROLFILE FROM '/backup/ctrl_backup';

RMAN> ALTER DATABASE MOUNT;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

__________________________________________________________________

Archivelog only recovery (no restore required)

-- If datafiles are intact but archivelogs needed

RMAN> RECOVER DATABASE; -- applies pending archivelogs

-- Recover using specific archivelog

RMAN> RECOVER DATABASE UNTIL SEQUENCE 450;

__________________________________________________________________

Sunday, September 7, 2025

Recover Standby Database Using Incremental Backup

##############################################

# STEP 1: On STANDBY - Check current SCN

##############################################

sqlplus / as sysdba <<EOF

SELECT CURRENT_SCN FROM V\$DATABASE;

EXIT;

EOF

##############################################

# STEP 2: On PRIMARY - Take incremental backup

# Replace <STANDBY_SCN> with value from above

##############################################

rman target / <<EOF

BACKUP INCREMENTAL FROM SCN <STANDBY_SCN> DATABASE FORMAT '/u01/backup/incr/incr_%U.bkp';

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/backup/incr/standby_ctl.bkp';

EXIT;

EOF

##############################################

# STEP 3: Copy backup files to STANDBY server

##############################################

scp /u01/backup/incr/* oracle@standby:/u01/backup/incr/

##############################################

# STEP 4: On STANDBY - Catalog the backup pieces

##############################################

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shut immediate;

startup nomount;

exit;

EOF

rman target / <<EOF

CATALOG START WITH '/u01/backup/incr/';

EXIT;

EOF

##############################################

# STEP 5: On STANDBY - Restore standby controlfile

##############################################

rman target / <<EOF

RESTORE STANDBY CONTROLFILE FROM '/u01/backup/incr/standby_ctl.bkp';

EXIT;

EOF

##############################################

# STEP 6: Mount the STANDBY database

##############################################

sqlplus / as sysdba <<EOF

ALTER DATABASE MOUNT;

EXIT;

EOF

##############################################

# STEP 7: Apply incremental backup to STANDBY

##############################################

rman target / <<EOF

RECOVER DATABASE NOREDO;

EXIT;

EOF

##############################################

# STEP 8: Start managed recovery on STANDBY

##############################################

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

EXIT;

EOF

##############################################

# STEP 9: Verify STANDBY synchronization

##############################################

sqlplus / as sysdba <<EOF

SELECT NAME, OPEN_MODE, DATABASE_ROLE, CURRENT_SCN FROM V\$DATABASE;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V\$MANAGED_STANDBY;

EXIT;

EOF

_________________________________________________________________________

Full Flow Using RMAN DUPLICATE (Easier Method)

Instead of manual steps, use DUPLICATE — RMAN handles everything automatically.

-- Connect to both primary and standby
RMAN> CONNECT TARGET sys/password@primary
RMAN> CONNECT AUXILIARY sys/password@standby

-- Duplicate to standby (using active database — no backup needed)

RMAN> DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='STANDBY'
SET log_archive_dest_1='LOCATION=/arch/standby/'
SET fal_server='PRIMARY'
SET standby_file_management='AUTO'
NOFILENAMECHECK;

OR using existing backups with duplicat command:-

RMAN> DUPLICATE TARGET DATABASE

FOR STANDBY

BACKUP LOCATION '/backup/standby/'

DORECOVER

NOFILENAMECHECK;

Thursday, August 7, 2025

DATE AND TIME

Oracle DB:

Function -------------------	Description ------------------------------------------------	Time Zone Used -------------------------------
`SYSDATE`	Server date/time (from OS)	❌ Not affected by session TZ
`SYSTIMESTAMP`	Server timestamp with time zone	❌ Not affected by session TZ
`CURRENT_DATE`	Session date/time (no TZ shown)	✅ Affected by session TZ
`CURRENT_TIMESTAMP`	Session timestamp with time zone	✅ Affected by session TZ
`LOCALTIMESTAMP`	Session timestamp (no TZ info, but adjusted)	✅ Affected by session TZ

----------------------------------------------------------------------------------------------------------------

Linux:

export TZ=<Time_Zone_Name>

[root@localhost ~]# export TZ=Asia/Kolkata

[root@localhost ~]# date

Thu Aug  7 05:32:26 PM IST 2025

Region -------------	Time Zone Name -----------------------	UTC Offset -------------------------------	Description / Area -------------------------
Africa	`Africa/Johannesburg`	UTC+02:00	South Africa
	`Africa/Cairo`	UTC+02:00	Egypt
	`Africa/Nairobi`	UTC+03:00	Kenya
	`Africa/Lagos`	UTC+01:00	Nigeria
America	`America/Toronto`	UTC-05:00 / -04:00 DST	Canada – Eastern Time
	`America/Vancouver`	UTC-08:00 / -07:00 DST	Canada – Pacific Time
	`America/Edmonton`	UTC-07:00 / -06:00 DST	Canada – Mountain Time
	`America/Winnipeg`	UTC-06:00 / -05:00 DST	Canada – Central Time
	`America/St_Johns`	UTC-03:30 / -02:30 DST	Canada – Newfoundland
	`America/New_York`	UTC-05:00 / -04:00 DST	USA – Eastern Time
	`America/Chicago`	UTC-06:00 / -05:00 DST	USA – Central Time
	`America/Denver`	UTC-07:00 / -06:00 DST	USA – Mountain Time
	`America/Los_Angeles`	UTC-08:00 / -07:00 DST	USA – Pacific Time
	`America/Bogota`	UTC-05:00	Colombia
Asia	`Asia/Kolkata`	UTC+05:30	India
	`Asia/Dubai`	UTC+04:00	UAE
	`Asia/Tokyo`	UTC+09:00	Japan
	`Asia/Singapore`	UTC+08:00	Singapore
	`Asia/Shanghai`	UTC+08:00	China
	`Asia/Karachi`	UTC+05:00	Pakistan
	`Asia/Tehran`	UTC+03:30	Iran
Europe	`Europe/London`	UTC+00:00 / +01:00 DST	UK
	`Europe/Berlin`	UTC+01:00 / +02:00 DST	Germany
	`Europe/Paris`	UTC+01:00 / +02:00 DST	France
	`Europe/Moscow`	UTC+03:00	Russia
	`Europe/Istanbul`	UTC+03:00	Turkey
Australia	`Australia/Sydney`	UTC+10:00 / +11:00 DST	New South Wales
	`Australia/Perth`	UTC+08:00	Western Australia
	`Australia/Melbourne`	UTC+10:00 / +11:00 DST	Victoria
UTC/GMT	`Etc/UTC`	UTC+00:00	Coordinated Universal Time
	`Etc/GMT+5`	UTC-05:00	Fixed offset (reverse sign!)
Canada TZ	`Canada/Eastern` (alias)	UTC-05:00 / -04:00 DST	Alias for America/Toronto
	`Canada/Pacific` (alias)	UTC-08:00 / -07:00 DST	Alias for America/Vancouver

---------------------------------------------------------------------------------------------------------------

Upgrade and Patching oracle

Upgrade:

Path -----------------------------------------------------------------	Description ----------------------------------------------------
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/`	Main directory for DBUA logs per DB SID
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/upgrade*.log`	Main upgrade logs
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/trace.log`	Detailed trace of DBUA steps
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/postupgrade/*.log`	Logs of post-upgrade steps (script output)
`$ORACLE_HOME/cfgtoollogs/dbua/`	May also contain general DBUA logs (older versions)

-----------------------------------------------------------------------------------------------------------------------------

OPatch:

Tool -------------	Log Location --------------------------------------------------------------------------------------------	Description ------------------------------------------
OPatch	`$ORACLE_HOME/cfgtoollogs/opatch/`	Main directory for `opatch` logs.
	`$ORACLE_HOME/cfgtoollogs/opatch/opatch<timestamp>.log`	Log file for each `opatch` execution.
OPatchAuto	`$ORACLE_HOME/cfgtoollogs/opatchauto/`	Root directory for all `opatchauto` sessions.
	`$ORACLE_HOME/cfgtoollogs/opatchauto/<timestamp>/`	Subfolder for a specific `opatchauto` run.
	`$ORACLE_HOME/cfgtoollogs/opatchauto/<timestamp>/opatchauto<timestamp>.log`	Main log for the auto-patching operation.
	Other files in the same folder	Logs for `root.sh`, database patching, rollback, etc.

-----------------------------------------------------------------------------------------------------------------------------

Locking Unlocking oraInventory

Action ---------------------	Command ---------------------------------------------	Description ----------------------------------------
Find oraInventory path	Check from `oraInst.loc` file:`cat /etc/oraInst.loc` Look for `inventory_loc=`	Tells you where the `oraInventory` directory is located (e.g., `/u01/app/oraInventory`).
Check lock status	`ls -l $ORACLE_INVENTORY/locks/inventory.lck`	Checks if the lock file already exists. Replace `$ORACLE_INVENTORY` with the actual path.
Manually lock oraInventory	`touch $ORACLE_INVENTORY/locks/inventory.lck`	Creates the lock file manually to prevent concurrent OUI or patch operations.
Manually unlock oraInventory	`rm -f $ORACLE_INVENTORY/locks/inventory.lck`	Removes the lock file to release the lock (⚠️ only if no install/patch is running).
Check if Oracle install/patch process is running	`ps -ef	grep -i oraInst`

---------------------------------------------------------------------------------------------------------------------------

Wednesday, August 6, 2025

Dataguard standby views

View / Table ------------------------------	Purpose / Usage -----------------------------------------------------------------------------------
`v$dataguard_stats`	Shows Data Guard apply/transport lag statistics.
`v$dataguard_status`	Displays Data Guard error/status messages.
`v$dataguard_config`	❌ Not a standard Oracle view (possibly custom).
`v$dataguard_process`	Shows active Data Guard background processes (RFS, MRP, etc.).
`v$bgprocess`	Lists all background processes running in the instance.
`v$active_instances`	Lists active instances in RAC environments.
`v$active_services`	Shows currently active services in the instance.
`v$active_session_history`	ASH data; samples active sessions for performance diagnostics.
`dba_activity_table`	❌ Not standard — likely custom. Use `DBA_AUDIT_TRAIL` for DML audit.
`dba_app_errors`	Lists compilation errors for PL/SQL programs.
`dba_applications`	Lists applications registered (e.g., Workspace Manager).
`dba_ddl_locks`	Shows schema-level locks (DDL operations).
`dba_dml_locks`	Displays row-level DML locks (e.g., UPDATE, INSERT).
`dba_db_links`	Lists all defined DB links and their properties.
`dba_db_link_sources`	Shows which users/objects use DB links.
`dba_dependencies`	Shows object dependencies (views, packages, tables).
`dba_directories`	Lists Oracle Directory objects (for UTL_FILE, Data Pump, etc.).
`dba_goldengate_privileges`	Lists users with Oracle GoldenGate replication privileges.
`v$dbfile`	Lists datafiles and status info for current instance.
`v$tempfile`	Lists tempfiles used by temporary tablespaces.
`v$logfile`	Lists redo log files and their group/size/status.
`dba_data_files`	Lists all permanent datafiles and their details.
`dba_temp_files`	Lists all tempfiles in temp tablespaces.
`v$rman_configuration`	Displays RMAN configuration parameters (retention, device type).
`v$result_cache_objects`	Lists cached results; check for cache flushes here.
`v$passwordfile_info`	Shows info about Oracle password file usage.
`v$pdbs`	Lists PDBs (Pluggable Databases) in a CDB environment.
`v$parameter_valid_values`	Lists valid values for init parameters (for validation).
`v$obsolete_backup_files`	Shows obsolete backups eligible for deletion by RMAN.
`v$obsolete_parameter`	Lists deprecated/obsolete init parameters.
`v$instance_ping`	RAC view; shows block ping stats between instances.
`v$session_connect_info`	Shows connection protocol/service per session.

---------------------------------------------------------------------------------------------------------------

Friday, July 25, 2025

RMAN BACKUP FOR PATCHING AND UPGRADES

make sure:

1) database in archive log mode:

select log_mode from v$database;

archive log list;

-- look for how to make db in archivelog mode

https://kayyumoracledba.blogspot.com/2023/05/enable-archive-log-mode-in-oracle.html

2) database in flashback_on mode:

select flashback_on from v$database;

--look for how to make db in flashback_on mode

https://kayyumoracledba.blogspot.com/2023/05/enable-archive-log-mode-in-oracle.html

-- RMAN BACKUP Before Upgrades or patching --

run {

backup incremental level 0 database tag='BKP_DB_B4UPGRADE';

backup archivelog all not backed up delete input tag='BKP_ARC_B4UPGRADE';

backup current controlfile tag='BKP_CONTROLFILE_B4UPGRADE';

backup spfile tag='BKP_SPFILE_B4UPGRADE';

}

--GRP before upgrades or patching:-

-- Run this as SYSDBA

create restore point GRP_B4UPGRADE guarantee flashback database;

COL name FORMAT A30;

COL guarantee_flashback_database FORMAT A10;

COL time FORMAT A30;

SELECT

name,

scn,

guarantee_flashback_database,

time

FROM

v$restore_point;

NAME	SCN	GUARANTEE_FLASHBACK_DATABASE	TIME
GRP_B4UPGRADE	123456789	YES	25-JUL-25 07.10.05.000000 PM

--to see in indian time zone

COLUMN name FORMAT A30

COLUMN guarantee_flashback_database FORMAT A10

COLUMN ist_time FORMAT A30

SELECT

name,

scn,

guarantee_flashback_database,

CAST(

FROM_TZ(CAST(time AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Kolkata'

AS TIMESTAMP

) AS ist_time

FROM

v$restore_point;

SELECT

object_name,

object_type,

CAST(

FROM_TZ(CAST(created AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata'

AS TIMESTAMP

) AS ist_created

FROM

dba_objects;

--To see if your session is using a different time zone than the DB:

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;

DBTIMEZONE SESSIONTIMEZONE

------------- ----------------

+00:00 Asia/Kolkata

SELECT

SYSTIMESTAMP AS original_time,

FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata' AS converted_to_ist,

FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE DBTIMEZONE AS back_to_db_tz

FROM dual;

ORIGINAL_TIME CONVERTED_TO_IST BACK_TO_DB_TZ

----------------------------------- ------------------------------- -------------------------------

25-JUL-25 14.45.00.000000 +00:00 25-JUL-25 20.15.00.000000 +05:30 25-JUL-25 14.45.00.000000 +00:00

-- For setting session for IST time zone

ALTER SESSION SET TIME_ZONE = 'Asia/Kolkata';

-- To convert startup_time to timestamp ist

SELECT

TO_CHAR(

FROM_TZ(CAST(startup_time AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata',

'dd-mon-yy hh:mi:ss am'

) AS startup_ist

FROM v$instance;

Column/Function	Respects Session Time Zone?	Type
`startup_time`	❌ No	`DATE`
`SYSTIMESTAMP`	✅ Yes	`TIMESTAMP WITH TIME ZONE`
`CURRENT_TIMESTAMP`	✅ Yes	`TIMESTAMP WITH TIME ZONE`
`LOCALTIMESTAMP`	✅ Yes	`TIMESTAMP`

To convert a DATE value to IST (Indian Standard Time) in Oracle, you need to:

Cast the DATE to TIMESTAMP (because DATE does not store time zone info).
Attach the database time zone using FROM_TZ.
Convert it to IST using AT TIME ZONE 'Asia/Kolkata'.

✅ General Syntax

sql
SELECT 
  FROM_TZ(CAST(your_date_column AS TIMESTAMP), DBTIMEZONE) 
  AT TIME ZONE 'Asia/Kolkata' AS converted_to_ist
FROM your_table;

Friday, July 4, 2025

Grants read-only access on all schemas tables in oracle

SQL> BEGIN
FOR x IN (select table_name from dba_tables where owner='SCHEMA_OWNER') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO <role_name>';
END LOOP;
END;
/

SQL> CREATE USER <username> IDENTIFIED BY "<password>"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE "DEFAULT"; 2    3    4

User created.

SQL> create role pune_ro;
role created.

SQL> show user;
USER is "SYS"
SQL> BEGIN
FOR x IN (select table_name from dba_tables where owner='PUNE') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO pune_ro';
END LOOP;
END;
/ 2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> alter user <username> default role all;

User altered.

SQL> grant app_user_dbpull to <username> ;

Grant succeeded.

SQL> revoke select on pune.alert from <username>;

Revoke succeeded.

SQL> grant pune_ro to <username>;

Grant succeeded.

---

1. create a user

1.1 alter user <username> default role all;

2. create a role

3. grant all tables select on access on schemas tables

4. grant the role to <username>

Saturday, April 11, 2026

Thursday, April 9, 2026

Complete Standby Database Setup from Scratch

Environment Details (Example)

PHASE 1 — PRIMARY SERVER PREPARATION

Step 1 — Check Primary Database Mode

Step 2 — Enable Force Logging on Primary

Step 3 — Enable Flashback (Recommended)

Step 4 — Create Standby Redo Logs on Primary

Step 5 — Configure Primary init.ora / spfile Parameters

Step 6 — Configure listener.ora on Primary

Step 7 — Configure tnsnames.ora on Primary

Step 8 — Create Password File on Primary

PHASE 2 — STANDBY SERVER PREPARATION

Step 9 — Create Directory Structure on Standby

Step 10 — Create standby init.ora on Standby

Step 11 — Configure listener.ora on Standby

Step 12 — Configure tnsnames.ora on Standby

Step 13 — Set Oracle Environment on Standby

Step 14 — Test Connectivity Between Primary and Standby

PHASE 3 — BUILD STANDBY USING RMAN DUPLICATE

Step 15 — Start Standby in NOMOUNT

Step 16 — Run RMAN DUPLICATE from Standby Server

PHASE 4 — POST DUPLICATE STEPS

Step 17 — Create spfile from pfile on Standby

Step 18 — Start MRP Apply Process

Step 19 — Verify Standby is Working

Complete Checklist

Key Points to Remember

Minimal init.ora for Standby Database

Absolute Minimum Parameters Required

Side-by-Side Comparison

Sunday, September 7, 2025

Full Flow Using RMAN DUPLICATE (Easier Method)

Thursday, August 7, 2025

Wednesday, August 6, 2025

Friday, July 25, 2025

✅ General Syntax

Friday, July 4, 2025

Saturday, May 24, 2025