ADVERTISEMENT

Full DB Refresh

Pre Stuff on Source Database

This phase is executed on the target database — the environment that is going to be refreshed (typically from production). These pre-checks and backups are performed as a customer-specific request to preserve existing configurations, users, and metadata in case something goes wrong or if you need to revert after the RMAN duplicate.

✅ 1. Check Database Status

set lines 200 pages 200
col name for a15
col db_unique_name for a15
col open_mode for a15
col log_mode for a15
col logins for a15
col instance_name for a15
col HOST_NAME for a15
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select name,db_unique_name,created,open_mode,log_mode,logins,instance_name,database_role,host_name,startup_time from v$instance,v$database;

📌 Make sure the database is OPEN and in the correct role (PRIMARY).
📎 Refer: Check Database Status

✅ 2. Connect to RMAN and Take Controlfile/Spfile Backup

rman target /
SHOW ALL;

BACKUP CURRENT CONTROLFILE;
BACKUP SPFILE;

📌 SPFILE and CONTROLFILE backups are essential for recovery and duplication.

✅ 3. Create Directory Object

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/exports';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO PUBLIC;

📎 Refer: Create Directory

✅ 4. Take a Backup of PFILE

CREATE PFILE='/u01/exports/init_`uname -n`.ora' FROM SPFILE;

📌 Helps to manually review or rebuild SPFILE later if needed.

✅ 5. Check for Running Data Pump Jobs

COL owner_name FOR A15
COL job_name FOR A20
COL state FOR A15
SELECT owner_name, job_name, state FROM dba_datapump_jobs;

SELECT * FROM v$session_longops WHERE opname LIKE 'EXPORT%';

📌 Avoid overlapping with other running jobs.

✅ 6. Export Full Metadata Only

nohup expdp '"/ as sysdba"' full=y content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=full_meta.dmp logfile=full_meta.log &

✅ 7. Export 5 Tables

nohup expdp '"/ as sysdba"' tables=(HR.EMPLOYEES, HR.DEPARTMENTS, HR.JOBS, HR.LOCATIONS, HR.REGIONS) directory=DATA_PUMP_DIR dumpfile=5_tab.dmp logfile=5_tab.log &

📌 This exports 5 specific tables from the HR schema. The tables=(...) format improves readability for multiple objects.

📎 If you have a longer list of tables or want to keep things organized, consider using a .par file instead.
Refer: .par File in Oracle Data Pump

You can replace the above command with:

nohup expdp '"/ as sysdba"' parfile=5_tables.par &

Example 5_tables.par:

tables=(HR.EMPLOYEES, HR.DEPARTMENTS, HR.JOBS, HR.LOCATIONS, HR.REGIONS)
directory=DATA_PUMP_DIR
dumpfile=sample_tab.dmp
logfile=sample_tab.log

✅ 8. Export Grants, Synonyms, DB Links

-- Export only GRANT metadata (object/system privileges) from the database
nohup expdp '"/ as sysdba"' include=GRANT content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=grants.dmp logfile=grants.log &

-- Export all SYNONYMs (private and public) for reuse in target database
nohup expdp '"/ as sysdba"' include=SYNONYM content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=synonyms.dmp logfile=synonyms.log &

-- Export all DATABASE LINKS (internal and external metadata)
nohup expdp '"/ as sysdba"' include=DB_LINK content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=dblinks.dmp logfile=dblinks.log &

📌 These exports help preserve critical metadata objects—grants, synonyms, and DB links—that are often missed during schema-only or full refreshes.

✅ 9. DB Users Profile and Status Backup

--Backup User Profiles and Status
SET lines 200 pages 1000 
COL username FOR A20 
COL account_status FOR A20 
COL profile FOR A20
SPOOL user_profiles.sql
SELECT username, account_status, profile FROM dba_users;
SPOOL OFF;

-- Generate ALTER USER ... ACCOUNT UNLOCK statements for users whose status is OPENSET LINESIZE 200
SET PAGESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL unlock_users.sql
SELECT 'ALTER USER "' || username || '" ACCOUNT UNLOCK;' 
FROM dba_users 
WHERE account_status = 'OPEN';
SPOOL OFF


-- Generate ALTER USER ... ACCOUNT LOCK statements for users in LOCKED or EXPIRED state
SET LINESIZE 200
SET PAGESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL lock_users.sql
SELECT 'ALTER USER "' || username || '" ACCOUNT LOCK;' 
FROM dba_users 
WHERE account_status IN (
  'LOCKED', 
  'LOCKED(TIMED)', 
  'EXPIRED', 
  'EXPIRED(GRACE)'
);
SPOOL OFF

📌 Helps restore original state of user access after refresh.

✅ 10. Export 2 Schemas

nohup expdp '"/ as sysdba"' schemas=HR,SCOTT directory=DATA_PUMP_DIR dumpfile=hr_scott.dmp logfile=hr_scott.log parallel=4 &

✅ 11. Backup Password Hashes for Specific Users

-- Capture password hashes (PASSWORD and SPARE4 columns) for selected users for later restoration
SET LINESIZE 200
SET PAGESIZE 1000
COL name     FOR A20
COL password FOR A35
COL spare4   FOR A35
SPOOL user_passwords.sql
SELECT name, password, spare4 FROM sys.user$ WHERE name IN ('HR', 'SCOTT');
SPOOL OFF;

📌 This captures both the legacy and modern password hashes (password for pre-11g, spare4 for 11g+). Required to restore passwords accurately after DB refresh.

✅ 12. Profile Backup Per User

-- Backup the profile assignment for each user to reapply later if profile settings are lost or reset
SET LINESIZE 200
SET PAGESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL user_profile_set.sql
SELECT 'ALTER USER "' || username || '" PROFILE "' || profile || '";' 
FROM dba_users;
SPOOL OFF;

📌 Ensures each user can be restored to their original profile after the refresh or password reset operations.

✅ 13. User DDL and Complete DDL

--Backup basic CREATE USER statements with password hash and profile for all regular users

SET LINESIZE 200
SET PAGESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL create_users.sql
SELECT 'CREATE USER "' || name || '" IDENTIFIED BY VALUES ''' || password || ''' PROFILE "' || profile || '";'
FROM sys.user$
WHERE type# = 1;
SPOOL OFF;

📌 This captures basic user creation with hashed password (IDENTIFIED BY VALUES) and profile association. Suitable for quick recovery.

--Backup full CREATE USER DDL using DBMS_METADATA for all users

SET LINESIZE 200
SET PAGESIZE 1000
SPOOL user_ddl_full.sql
SELECT DBMS_METADATA.GET_DDL('USER', username) FROM dba_users;
SPOOL OFF;

📌 Provides the complete DDL including default tablespaces, quotas, profile, and account lock/unlock status.

✅ 14. Advanced Password Dump (All Users)

-- Export password hashes (Oracle 10g/11g/12c+) to preserve authentication during refresh
-- Handles all possible combinations of `password` (pre-12c) and `spare4` (12c+ schema)

SET LINESIZE 200
SET PAGESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SPOOL all_user_passwords.sql
SELECT 
  name AS username,
  password AS "10g/11g_HASH",
  spare4  AS "12c_HASH"
FROM sys.user$
WHERE (spare4 IS NULL AND password IS NOT NULL)
   OR (spare4 IS NOT NULL AND password IS NOT NULL)
   OR (spare4 IS NOT NULL AND password IS NULL);
SPOOL OFF;

📌 This script captures user password hashes across Oracle versions, useful for restoring user credentials post-refresh.

✅ 15. All User Grants Backup

SET LINESIZE 200
SET PAGESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL user_grants.sql
-- System privileges
SELECT 'GRANT ' || privilege || ' TO "' || grantee || '";'
FROM dba_sys_privs;
-- Role grants (with admin option)
SELECT 'GRANT ' || granted_role || ' TO "' || grantee || '"' ||
       DECODE(admin_option, 'YES', ' WITH ADMIN OPTION;', ';')
FROM dba_role_privs;
-- Object-level privileges
SELECT 'GRANT ' || privilege || ' ON "' || owner || '"."' || table_name || '" TO "' || grantee || '";'
FROM dba_tab_privs;
-- Tablespace quotas
SELECT 'ALTER USER "' || username || '" QUOTA ' || 
       CASE WHEN max_bytes = -1 THEN 'UNLIMITED'
            ELSE TO_CHAR(ROUND(max_bytes / 1024 / 1024)) || 'M'
       END || ' ON "' || tablespace_name || '";'
FROM dba_ts_quotas
WHERE max_bytes <> 0;
SPOOL OFF;

📌 This script generates role grant statements for each user, appending WITH ADMIN OPTION where applicable, to accurately restore user privileges post-refresh.

✅ 16. DB Links Backup Per User

-- Output user-wise DB Link definitions including internal credentials
SET LINES 500 PAGES 500
COL owner FOR A15
COL db_link FOR A30
COL username FOR A20
COL host FOR A40
-- Optional: View external DB links from DBA_DB_LINKS
SELECT owner, db_link, username FROM dba_db_links;
-- Backup internal DB links using SYS.LINK$ and SYS.USER$
SPOOL dblink_sql_prompt.sql
SELECT 
  'CREATE ' ||
  DECODE(U.NAME, 'PUBLIC', 'PUBLIC ', '') ||
  'DATABASE LINK "' ||
  DECODE(U.NAME, 'PUBLIC', '', U.NAME || '.') ||
  L.NAME || '" CONNECT TO "' ||
  L.USERID || '" IDENTIFIED BY VALUES ''' ||
  L.PASSWORDX || ''' USING ''' ||
  L.HOST || ''';' AS dblink_ddl
FROM 
  sys.link$ L,
  sys.user$ U
WHERE 
  L.OWNER# = U.USER#;
SPOOL OFF;

📌 This script generates valid CREATE DATABASE LINK statements, including password hashes from internal data dictionary views. Useful when DBA_DB_LINKS doesn’t expose credentials (common in secured environments).

✅ Pre-RMAN Duplicate: Cleanup Old Database Files

🔷 For Filesystem (Non-ASM) Databases

  1. Shutdown the target database (if running):
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
EXIT;
  1. Delete old database files:

⚠️ Double-check paths before deletion.

rm -rf /u01/app/oracle/oradata/ORCL/*     # datafiles, tempfiles
rm -rf /u01/app/oracle/fast_recovery_area/ORCL/*  # control files, archivelogs
rm -rf /u01/app/oracle/admin/ORCL/adump/*  # optional: audit dump
  1. Optional: Check for log/archive/config files elsewhere:
find /u01 -name "*.dbf"
find /u01 -name "*.ctl"
find /u01 -name "*.log"

🔷 For ASM-Based Databases

  1. Shutdown the database (if running):
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
EXIT;
  1. List files to be removed:
-- Check datafiles:
SELECT name FROM v$datafile;

-- Check tempfiles:
SELECT name FROM v$tempfile;

-- Check control files:
SHOW PARAMETER control_files;

-- Check redo logs:
SELECT member FROM v$logfile;
  1. Delete files using RMAN:
rman target /

# Example: Delete all files in +DATA and +FRA
DELETE NOPROMPT DATAFILE ALL;
DELETE NOPROMPT CONTROLFILE;
DELETE NOPROMPT REDOLOG;
DELETE NOPROMPT TEMPFILE ALL;

⚠️ If DELETE fails due to missing targets, use asmcmd for manual deletion.

  1. Manual cleanup using asmcmd (if needed):
asmcmd
cd +DATA/ORCL
rm -r *

cd +FRA/ORCL
rm -r *

✅ Safety Tips

  • Always verify paths before deleting.
  • Keep a backup or take snapshots if on test/UAT environments.
  • After cleanup, confirm that the oradata, fra, or ASM paths are empty before starting RMAN duplicate.

RMAN Duplicate – (Backup-Based Refresh from Production to Target)

This setup duplicates the production database to the target using RMAN backup—ideal for full refreshes using a specific SCN or timestamp. We’ll use a .txt RMAN script and a .sh wrapper for running the duplicate in background.

✅ Pre-Steps on Target (Before Running RMAN Duplicate)

  1. Ensure Oracle environment is set: ORACLE_SID=ORCL
  2. Start the target DB in NOMOUNT with PFILE:
sqlplus / as sysdba
STARTUP NOMOUNT PFILE='/u01/exports/init_orcl.ora';
  1. Ensure backup pieces are copied from Production to target path:
    Example: /u01/exports/backup/
  2. Ensure password file is in place: cp /tmp/orapwPROD /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwORCL

✅ Create RMAN Script File (.txt)

Create a plain text file (e.g., rman_duplicate.txt) containing the RMAN commands:

📝 rman_duplicate.txt – Duplicate from Backup with SCN

RUN {
  SET UNTIL SCN 123456789;
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;
  DUPLICATE DATABASE TO ORCL
    BACKUP LOCATION '/u01/exports/backup'
    NOFILENAMECHECK;
}

OR

⏱ Using Timestamp Instead:

RUN {
  SET UNTIL TIME "TO_DATE('2024-06-01 02:30:00', 'YYYY-MM-DD HH24:MI:SS')";
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;
  DUPLICATE DATABASE TO ORCL
    BACKUP LOCATION '/u01/exports/backup'
    NOFILENAMECHECK;
}

📝 Save this as rman_duplicate.txt in /u01/scripts or any custom location.

✅ Create Shell Script Wrapper (.sh)

Create a .sh shell file that wraps the RMAN call:

🖥️ rman_run_duplicate.sh

#!/bin/bash
export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

rman target / cmdfile=/u01/scripts/rman_duplicate.txt log=/u01/scripts/rman_duplicate.log

📌 Make it executable:

chmod +x /u01/scripts/rman_run_duplicate.sh

✅ Run RMAN Duplicate in Background with nohup

nohup /u01/scripts/rman_run_duplicate.sh &

You can tail the log:

tail -f /u01/scripts/rman_duplicate.log

✅ Tips and Notes

  • NOFILENAMECHECK – Use only if file structure is the same on source & target.
  • Avoid running from SQL prompt interactively—use nohup to prevent session loss.
  • SET UNTIL helps point-in-time duplication, use current SCN for latest consistent state.

Post Stuff – (Target Database)

These post-refresh steps are performed on the target database after a successful RMAN duplicate. This phase restores essential configurations, users, metadata, and objects that were backed up before the duplication. It ensures the environment is restored to the desired pre-refresh state and is ready for use.

✅ 1. Confirm RMAN Duplicate Completed Successfully

cat /u01/scripts/rman_duplicate.log | grep -i "Finished Duplicate Db"

📌 Ensure there are no errors before proceeding.

✅ 2. Recreate Directory Object if Dropped During Duplicate

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/exports';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO PUBLIC;

✅ 3. Verify Database Status

set lines 200 pages 200
col name for a15
col db_unique_name for a15
col open_mode for a15
col log_mode for a15
col logins for a15
col instance_name for a15
col HOST_NAME for a15
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select name,db_unique_name,created,open_mode,log_mode,logins,instance_name,database_role,host_name,startup_time from v$instance,v$database;

📌 Database should be in OPEN state and role should be PRIMARY.

✅ 4. Import Full Metadata

nohup impdp '"/ as sysdba"' full=y content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=full_meta.dmp logfile=full_meta_imp.log parallel=4 &

✅ 5. Drop Existing Users Before Reimport (Example: HR, SCOTT)

Sometimes, users cannot be dropped due to active sessions. Use this 3-step method to cleanly drop them:

Step 1: Lock the Schemas to Prevent New Connections

ALTER USER HR ACCOUNT LOCK;
ALTER USER SCOTT ACCOUNT LOCK;

📌 This ensures no new sessions are created while you’re trying to drop the users.

Step 2: Identify and Kill Active Sessions (If Any)

-- Generate kill commands for HR and SCOTT
SELECT 
  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command
FROM v$session
WHERE username IN ('HR', 'SCOTT');

📌 Run the output of this query to terminate active sessions that may prevent the drop.

Example output:

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;
ALTER SYSTEM KILL SESSION '124,457' IMMEDIATE;

Step 3: Drop the Users

DROP USER HR CASCADE;
DROP USER SCOTT CASCADE;

📌 Now that the sessions are terminated and the users are locked, the drop should succeed without error.

✅ 6. Validate Users No Longer Exist

SET lines 200 pages 100
COL username FOR A20
SELECT username FROM dba_users WHERE username IN ('HR','SCOTT');

📌 Should return no rows.

✅ 7. Import Two Schemas

nohup impdp '"/ as sysdba"' schemas=HR,SCOTT directory=DATA_PUMP_DIR dumpfile=hr_scott.dmp logfile=hr_scott_imp.log parallel=4 &

✅ 8. Verify Users Are Created & Unlocked

SET lines 200 pages 100
COL username FOR A20
COL account_status FOR A20
COL created FOR A30
SELECT username, account_status, created FROM dba_users WHERE username IN ('HR','SCOTT');

📌 Status should be OPEN and creation time should reflect import time.

✅ 9. Import 5 Tables

nohup impdp '"/ as sysdba"' tables=HR.EMPLOYEES,HR.DEPARTMENTS,HR.JOBS,HR.LOCATIONS,HR.REGIONS directory=DATA_PUMP_DIR dumpfile=5_tab.dmp logfile=5_tab_imp.log parallel=4 &

✅ 10. Import Grants

nohup impdp '"/ as sysdba"' include=GRANT directory=DATA_PUMP_DIR dumpfile=grants.dmp logfile=grants_imp.log parallel=4 &

✅ 11. Import Synonyms

nohup impdp '"/ as sysdba"' include=SYNONYM directory=DATA_PUMP_DIR dumpfile=synonyms.dmp logfile=synonyms_imp.log parallel=4 &

✅ 12. Reset Passwords and Restore Profiles

-- Step 1: Set profile to DEFAULT for all users
SPOOL reset_profiles.sql
SELECT 'ALTER USER ' || username || ' PROFILE DEFAULT;' FROM dba_users;
SPOOL OFF;

-- Step 2: Restore passwords from backup script
@user_passwords.sql

-- Step 3: Restore original profiles
@user_profile_set.sql

✅ 13. Restore DB Links (With Drop & Re-Import Logic)

-- ================================================
-- Step 1: List All DB Links (User-wise)
-- ================================================
SET LINESIZE 150
SET PAGESIZE 1000
COL owner     FOR A20
COL db_link   FOR A35
COL username  FOR A20
-- View current DB links grouped by owner
SELECT owner, db_link, username 
FROM dba_db_links 
ORDER BY owner, db_link;

-- ================================================
-- Step 2: Drop DB Links for a Specific Schema
-- ================================================
-- Replace &schema with actual schema name like HR, SCOTT etc.
-- This procedure drops all DB links owned by a given user.

CREATE OR REPLACE PROCEDURE &schema..drop_db_link AS
  CURSOR c_dbl IS 
    SELECT db_link FROM user_db_links;
BEGIN
  FOR c_1 IN c_dbl LOOP
    EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || c_1.db_link;
  END LOOP;
END drop_db_link;
/

-- Execute the procedure to drop DB links
EXEC &schema..drop_db_link;
-- Confirm that DB links for the schema are dropped
SELECT db_link 
FROM dba_db_links 
WHERE owner = UPPER('&schema');
-- Drop the temporary procedure after use
DROP PROCEDURE &schema..drop_db_link;

-- 📌 NOTE:
-- You must reconnect to SQL*Plus or re-authenticate between schemas
-- if you’re doing this for multiple users.

-- ================================================
-- Step 3: Import Backed Up DB Links from Dump
-- ================================================
-- Restore DB links taken from pre-refresh backup using Data Pump.

-- Run this from OS prompt:
-- It imports only DB links from the specified dump file.

nohup impdp '"/ as sysdba"' include=DB_LINK directory=DATA_PUMP_DIR dumpfile=dblinks.dmp logfile=dblinks_imp.log &

✅ 14. Compile All Invalid Objects

@?/rdbms/admin/utlrp.sql

✅ 15. Verify RMAN Settings (Compare with Source Backup)

rman target /
SHOW ALL;

📌 Cross-verify settings with what was captured in Pre Stuff.

✅ 16. Register Database with RMAN Catalog (Optional)

rman target / catalog rman/password@catalog_db_name
REGISTER DATABASE;

✅ 17. Take Manual Archive Log & Full Backup

rman target /

-- Archive Log Backup
BACKUP ARCHIVELOG ALL;

-- Full DB Backup
BACKUP DATABASE PLUS ARCHIVELOG;

ADVERTISEMENT