hostname high cpu usage investigation:
oracle@PRD:hostname:hostname:(54) /home/oracle
$ ps -eo pcpu,pid,user,args --sort=-pcpu | head -n 11
%CPU PID USER COMMAND
62.7 131071 oracle ora_j001_hostname
11.9 192558 oracle oraclehostname (LOCAL=NO)
10.0 193835 oracle oraclehostname (LOCAL=NO)
8.0 245093 root s1-agent
5.8 184915 oracle oraclehostname (LOCAL=NO)
5.6 109801 oracle oraclehostname (LOCAL=NO)
5.1 184808 oracle oraclehostname (LOCAL=NO)
4.9 133171 oracle oraclehostname (LOCAL=NO)
4.8 133165 oracle oraclehostname (LOCAL=NO)
4.7 130391 oracle oraclehostname (LOCAL=NO)
SELECT s.sid, s.serial#, s.username, s.program, s.module, s.action
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid = '131071';
SELECT * FROM v$scheduler_running_jobs WHERE session_id = '2503';
SELECT sql_text
FROM v$sqltext
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid ='2503')
ORDER BY piece;
Saturday, April 11, 2026
hostname high cpu usage investigation:
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
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
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):-
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) |
1. Restore to specific time:-
RMAN> STARTUP MOUNT;
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;
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.
RMAN> CONNECT TARGET sys/password@primary
RMAN> CONNECT AUXILIARY sys/password@standby
-- Duplicate to standby (using active database — no backup needed)
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;
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 |
| 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) |
| 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
| NAME | SCN | GUARANTEE_FLASHBACK_DATABASE | TIME |
|---|---|---|---|
| GRP_B4UPGRADE | 123456789 | YES | 25-JUL-25 07.10.05.000000 PM |
| 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
DATEdoes 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
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>