{"id":4229,"date":"2025-06-08T10:55:00","date_gmt":"2025-06-08T10:55:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4229"},"modified":"2025-06-15T22:46:22","modified_gmt":"2025-06-15T22:46:22","slug":"full-db-refresh","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/full-db-refresh\/","title":{"rendered":"Full DB Refresh"},"content":{"rendered":"\n

Pre Stuff on Source Database<\/mark><\/h2>\n\n\n\n

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

\u2705 1. Check Database Status<\/h3>\n\n\n\n
set lines 200 pages 200\ncol name for a15\ncol db_unique_name for a15\ncol open_mode for a15\ncol log_mode for a15\ncol logins for a15\ncol instance_name for a15\ncol HOST_NAME for a15\nalter session set nls_date_format='DD\/MM\/YYYY HH24:MI:SS';\nselect name,db_unique_name,created,open_mode,log_mode,logins,instance_name,database_role,host_name,startup_time from v$instance,v$database;<\/code><\/pre>\n\n\n\n
\ud83d\udccc Make sure the database is OPEN and in the correct role (PRIMARY).<\/em>
\ud83d\udcce Refer: Check Database Status<\/a><\/p>\n\n\n\n
\u2705 2. Connect to RMAN and Take Controlfile\/Spfile Backup<\/h3>\n\n\n\n
rman target \/\nSHOW ALL;\n\nBACKUP CURRENT CONTROLFILE;\nBACKUP SPFILE;<\/code><\/pre>\n\n\n\n
\ud83d\udccc SPFILE and CONTROLFILE backups are essential for recovery and duplication.<\/em><\/p>\n\n\n\n
\u2705 3. Create Directory Object<\/h3>\n\n\n\n
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '\/u01\/exports';\nGRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO PUBLIC;<\/code><\/pre>\n\n\n\n
\ud83d\udcce Refer: Create Directory<\/a><\/p>\n\n\n\n
\u2705 4. Take a Backup of PFILE<\/h3>\n\n\n\n
CREATE PFILE='\/u01\/exports\/init_`uname -n`.ora' FROM SPFILE;<\/code><\/pre>\n\n\n\n
\ud83d\udccc Helps to manually review or rebuild SPFILE later if needed.<\/em><\/p>\n\n\n\n
\u2705 5. Check for Running Data Pump Jobs<\/h3>\n\n\n\n
COL owner_name FOR A15\nCOL job_name FOR A20\nCOL state FOR A15\nSELECT owner_name, job_name, state FROM dba_datapump_jobs;\n\nSELECT * FROM v$session_longops WHERE opname LIKE 'EXPORT%';<\/code><\/pre>\n\n\n\n
\ud83d\udccc Avoid overlapping with other running jobs.<\/em><\/p>\n\n\n\n
\u2705 6. Export Full Metadata Only<\/h3>\n\n\n\n
nohup expdp '\"\/ as sysdba\"' full=y content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=full_meta.dmp logfile=full_meta.log &<\/code><\/pre>\n\n\n\n
\u2705 7. Export 5 Tables<\/h3>\n\n\n\n
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 &<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udccc This exports 5 specific tables from the HR schema. The tables=(...)<\/code> format improves readability for multiple objects.<\/em><\/p>\n\n\n\n
\ud83d\udcce If you have a longer list of tables<\/strong> or want to keep things organized, consider using a .par<\/code> file instead.
Refer: <\/a>.par File in Oracle Data Pump<\/a><\/p>\n\n\n\n
You can replace the above command with:<\/p>\n\n\n\n
nohup expdp '\"\/ as sysdba\"' parfile=5_tables.par &<\/code><\/code><\/pre>\n\n\n\n
Example 5_tables.par<\/code>:<\/p>\n\n\n\n
tables=(HR.EMPLOYEES, HR.DEPARTMENTS, HR.JOBS, HR.LOCATIONS, HR.REGIONS)\ndirectory=DATA_PUMP_DIR\ndumpfile=sample_tab.dmp\nlogfile=sample_tab.log<\/code><\/code><\/pre>\n\n\n\n
\u2705 8. Export Grants, Synonyms, DB Links<\/h3>\n\n\n\n
-- Export only GRANT metadata (object\/system privileges) from the database\nnohup expdp '\"\/ as sysdba\"' include=GRANT content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=grants.dmp logfile=grants.log &\n\n-- Export all SYNONYMs (private and public) for reuse in target database\nnohup expdp '\"\/ as sysdba\"' include=SYNONYM content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=synonyms.dmp logfile=synonyms.log &\n\n-- Export all DATABASE LINKS (internal and external metadata)\nnohup expdp '\"\/ as sysdba\"' include=DB_LINK content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=dblinks.dmp logfile=dblinks.log &<\/code><\/pre>\n\n\n\n
\ud83d\udccc These exports help preserve critical metadata objects\u2014grants, synonyms, and DB links<\/strong>\u2014that are often missed during schema-only or full refreshes.<\/p>\n\n\n\n
\u2705 9. DB Users Profile and Status Backup<\/h3>\n\n\n\n
--Backup User Profiles and Status\nSET lines 200 pages 1000 \nCOL username FOR A20 \nCOL account_status FOR A20 \nCOL profile FOR A20\nSPOOL user_profiles.sql\nSELECT username, account_status, profile FROM dba_users;\nSPOOL OFF;\n\n-- Generate ALTER USER ... ACCOUNT UNLOCK statements for users whose status is OPENSET LINESIZE 200\nSET PAGESIZE 1000\nSET ECHO OFF\nSET FEEDBACK OFF\nSET HEADING OFF\nSPOOL unlock_users.sql\nSELECT 'ALTER USER \"' || username || '\" ACCOUNT UNLOCK;' \nFROM dba_users \nWHERE account_status = 'OPEN';\nSPOOL OFF\n\n\n-- Generate ALTER USER ... ACCOUNT LOCK statements for users in LOCKED or EXPIRED state\nSET LINESIZE 200\nSET PAGESIZE 1000\nSET ECHO OFF\nSET FEEDBACK OFF\nSET HEADING OFF\nSPOOL lock_users.sql\nSELECT 'ALTER USER \"' || username || '\" ACCOUNT LOCK;' \nFROM dba_users \nWHERE account_status IN (\n  'LOCKED', \n  'LOCKED(TIMED)', \n  'EXPIRED', \n  'EXPIRED(GRACE)'\n);\nSPOOL OFF\n<\/code><\/pre>\n\n\n\n
\ud83d\udccc Helps restore original state of user access after refresh.<\/em><\/p>\n\n\n\n
\u2705 10. Export 2 Schemas<\/h3>\n\n\n\n
nohup expdp '\"\/ as sysdba\"' schemas=HR,SCOTT directory=DATA_PUMP_DIR dumpfile=hr_scott.dmp logfile=hr_scott.log parallel=4 &<\/code><\/pre>\n\n\n\n
\u2705 11. Backup Password Hashes for Specific Users<\/h3>\n\n\n\n
-- Capture password hashes (PASSWORD and SPARE4 columns) for selected users for later restoration\nSET LINESIZE 200\nSET PAGESIZE 1000\nCOL name     FOR A20\nCOL password FOR A35\nCOL spare4   FOR A35\nSPOOL user_passwords.sql\nSELECT name, password, spare4 FROM sys.user$ WHERE name IN ('HR', 'SCOTT');\nSPOOL OFF;<\/code><\/pre>\n\n\n\n
\ud83d\udccc This captures both the legacy and modern password hashes (password<\/code> for pre-11g, spare4<\/code> for 11g+). Required to restore passwords accurately after DB refresh.<\/em><\/p>\n\n\n\n
\u2705 12. Profile Backup Per User<\/h3>\n\n\n\n
-- Backup the profile assignment for each user to reapply later if profile settings are lost or reset\nSET LINESIZE 200\nSET PAGESIZE 1000\nSET ECHO OFF\nSET FEEDBACK OFF\nSET HEADING OFF\nSPOOL user_profile_set.sql\nSELECT 'ALTER USER \"' || username || '\" PROFILE \"' || profile || '\";' \nFROM dba_users;\nSPOOL OFF;<\/code><\/pre>\n\n\n\n
\ud83d\udccc Ensures each user can be restored to their original profile after the refresh or password reset operations.<\/em><\/p>\n\n\n\n
\u2705 13. User DDL and Complete DDL<\/h3>\n\n\n\n
--Backup basic CREATE USER statements with password hash and profile for all regular users\n\nSET LINESIZE 200\nSET PAGESIZE 1000\nSET ECHO OFF\nSET FEEDBACK OFF\nSET HEADING OFF\nSPOOL create_users.sql\nSELECT 'CREATE USER \"' || name || '\" IDENTIFIED BY VALUES ''' || password || ''' PROFILE \"' || profile || '\";'\nFROM sys.user$\nWHERE type# = 1;\nSPOOL OFF;<\/code><\/pre>\n\n\n\n
\ud83d\udccc This captures basic user creation with hashed password (IDENTIFIED BY VALUES<\/code>) and profile association. Suitable for quick recovery.<\/em><\/p>\n\n\n\n
--Backup full CREATE USER DDL using DBMS_METADATA for all users\n\nSET LINESIZE 200\nSET PAGESIZE 1000\nSPOOL user_ddl_full.sql\nSELECT DBMS_METADATA.GET_DDL('USER', username) FROM dba_users;\nSPOOL OFF;<\/code><\/pre>\n\n\n\n
\ud83d\udccc Provides the complete DDL including default tablespaces, quotas, profile, and account lock\/unlock status.<\/em><\/p>\n\n\n\n
\u2705 14. Advanced Password Dump (All Users)<\/h3>\n\n\n\n
-- Export password hashes (Oracle 10g\/11g\/12c+) to preserve authentication during refresh\n-- Handles all possible combinations of `password` (pre-12c) and `spare4` (12c+ schema)\n\nSET LINESIZE 200\nSET PAGESIZE 1000\nSET ECHO OFF\nSET FEEDBACK OFF\nSET HEADING ON\nSPOOL all_user_passwords.sql\nSELECT \n  name AS username,\n  password AS \"10g\/11g_HASH\",\n  spare4  AS \"12c_HASH\"\nFROM sys.user$\nWHERE (spare4 IS NULL AND password IS NOT NULL)\n   OR (spare4 IS NOT NULL AND password IS NOT NULL)\n   OR (spare4 IS NOT NULL AND password IS NULL);\nSPOOL OFF;<\/code><\/pre>\n\n\n\n
\ud83d\udccc This script captures user password hashes across Oracle versions, useful for restoring user credentials post-refresh.<\/em><\/p>\n\n\n\n
\u2705 15. All User Grants Backup<\/h3>\n\n\n\n
SET LINESIZE 200\nSET PAGESIZE 1000\nSET ECHO OFF\nSET FEEDBACK OFF\nSET HEADING OFF\nSPOOL user_grants.sql\n-- System privileges\nSELECT 'GRANT ' || privilege || ' TO \"' || grantee || '\";'\nFROM dba_sys_privs;\n-- Role grants (with admin option)\nSELECT 'GRANT ' || granted_role || ' TO \"' || grantee || '\"' ||\n       DECODE(admin_option, 'YES', ' WITH ADMIN OPTION;', ';')\nFROM dba_role_privs;\n-- Object-level privileges\nSELECT 'GRANT ' || privilege || ' ON \"' || owner || '\".\"' || table_name || '\" TO \"' || grantee || '\";'\nFROM dba_tab_privs;\n-- Tablespace quotas\nSELECT 'ALTER USER \"' || username || '\" QUOTA ' || \n       CASE WHEN max_bytes = -1 THEN 'UNLIMITED'\n            ELSE TO_CHAR(ROUND(max_bytes \/ 1024 \/ 1024)) || 'M'\n       END || ' ON \"' || tablespace_name || '\";'\nFROM dba_ts_quotas\nWHERE max_bytes <> 0;\nSPOOL OFF;<\/code><\/pre>\n\n\n\n
\ud83d\udccc This script generates role grant statements for each user, appending WITH ADMIN OPTION<\/code> where applicable, to accurately restore user privileges post-refresh.<\/p>\n\n\n\n
\u2705 16. DB Links Backup Per User<\/h3>\n\n\n\n
-- Output user-wise DB Link definitions including internal credentials\nSET LINES 500 PAGES 500\nCOL owner FOR A15\nCOL db_link FOR A30\nCOL username FOR A20\nCOL host FOR A40\n-- Optional: View external DB links from DBA_DB_LINKS\nSELECT owner, db_link, username FROM dba_db_links;\n-- Backup internal DB links using SYS.LINK$ and SYS.USER$\nSPOOL dblink_sql_prompt.sql\nSELECT \n  'CREATE ' ||\n  DECODE(U.NAME, 'PUBLIC', 'PUBLIC ', '') ||\n  'DATABASE LINK \"' ||\n  DECODE(U.NAME, 'PUBLIC', '', U.NAME || '.') ||\n  L.NAME || '\" CONNECT TO \"' ||\n  L.USERID || '\" IDENTIFIED BY VALUES ''' ||\n  L.PASSWORDX || ''' USING ''' ||\n  L.HOST || ''';' AS dblink_ddl\nFROM \n  sys.link$ L,\n  sys.user$ U\nWHERE \n  L.OWNER# = U.USER#;\nSPOOL OFF;\n<\/code><\/pre>\n\n\n\n
\ud83d\udccc This script generates valid CREATE DATABASE LINK<\/code> statements, including password hashes from internal data dictionary views. Useful when DBA_DB_LINKS<\/code> doesn\u2019t expose credentials (common in secured environments).<\/em><\/em><\/p>\n\n\n\n
\u2705 Pre-RMAN Duplicate: Cleanup Old Database Files<\/mark><\/h2>\n\n\n\n
\ud83d\udd37 For Filesystem (Non-ASM) Databases<\/h3>\n\n\n\n
    \n
  1. Shutdown the target database (if running):<\/strong><\/li>\n<\/ol>\n\n\n\n
    sqlplus \/ as sysdba\nSHUTDOWN IMMEDIATE;\nEXIT;<\/code><\/code><\/pre>\n\n\n\n
      \n
    1. Delete old database files:<\/strong><\/li>\n<\/ol>\n\n\n\n
      \n
      \u26a0\ufe0f Double-check paths before deletion.<\/strong><\/p>\n<\/blockquote>\n\n\n\n
      rm -rf \/u01\/app\/oracle\/oradata\/ORCL\/*     # datafiles, tempfiles\nrm -rf \/u01\/app\/oracle\/fast_recovery_area\/ORCL\/*  # control files, archivelogs\nrm -rf \/u01\/app\/oracle\/admin\/ORCL\/adump\/*  # optional: audit dump<\/code><\/code><\/pre>\n\n\n\n
        \n
      1. Optional:<\/strong> Check for log\/archive\/config files elsewhere:<\/li>\n<\/ol>\n\n\n\n
        find \/u01 -name \"*.dbf\"\nfind \/u01 -name \"*.ctl\"\nfind \/u01 -name \"*.log\"<\/code><\/code><\/pre>\n\n\n\n
        \ud83d\udd37 For ASM-Based Databases<\/h3>\n\n\n\n
          \n
        1. Shutdown the database (if running):<\/strong><\/li>\n<\/ol>\n\n\n\n
          sqlplus \/ as sysdba\nSHUTDOWN IMMEDIATE;\nEXIT;<\/code><\/code><\/pre>\n\n\n\n
            \n
          1. List files to be removed:<\/strong><\/li>\n<\/ol>\n\n\n\n
            -- Check datafiles:\nSELECT name FROM v$datafile;\n\n-- Check tempfiles:\nSELECT name FROM v$tempfile;\n\n-- Check control files:\nSHOW PARAMETER control_files;\n\n-- Check redo logs:\nSELECT member FROM v$logfile;<\/code><\/code><\/pre>\n\n\n\n
              \n
            1. Delete files using RMAN:<\/strong><\/li>\n<\/ol>\n\n\n\n
              rman target \/\n\n# Example: Delete all files in +DATA and +FRA\nDELETE NOPROMPT DATAFILE ALL;\nDELETE NOPROMPT CONTROLFILE;\nDELETE NOPROMPT REDOLOG;\nDELETE NOPROMPT TEMPFILE ALL;<\/code><\/code><\/pre>\n\n\n\n
              \n
              \u26a0\ufe0f If DELETE fails due to missing targets, use asmcmd<\/code> for manual deletion.<\/p>\n<\/blockquote>\n\n\n\n
                \n
              1. Manual cleanup using asmcmd<\/code> (if needed):<\/strong><\/li>\n<\/ol>\n\n\n\n
                asmcmd\ncd +DATA\/ORCL\nrm -r *\n\ncd +FRA\/ORCL\nrm -r *<\/code><\/code><\/pre>\n\n\n\n
                \u2705 Safety Tips<\/h2>\n\n\n\n
                  \n
                • Always verify<\/strong> paths before deleting.<\/li>\n\n\n\n
                • Keep a backup<\/strong> or take snapshots if on test\/UAT environments.<\/li>\n\n\n\n
                • After cleanup, confirm that the oradata<\/code>, fra<\/code>, or ASM paths are empty<\/strong> before starting RMAN duplicate.<\/li>\n<\/ul>\n\n\n\n
                  RMAN Duplicate \u2013  (Backup-Based Refresh from Production to Target)<\/mark><\/h2>\n\n\n\n
                  This setup duplicates the production database<\/strong> to the target<\/strong> using RMAN backup<\/strong>\u2014ideal for full refreshes using a specific SCN or timestamp. We\u2019ll use a .txt<\/code> RMAN script and a .sh<\/code> wrapper for running the duplicate in background.<\/p>\n\n\n\n
                  \u2705 Pre-Steps on Target (Before Running RMAN Duplicate)<\/h2>\n\n\n\n
                    \n
                  1. Ensure Oracle environment is set<\/strong>: ORACLE_SID=ORCL<\/code><\/li>\n\n\n\n
                  2. Start the target DB in NOMOUNT with PFILE<\/strong>: <\/li>\n<\/ol>\n\n\n\n
                    sqlplus \/ as sysdba<\/code>\nSTARTUP NOMOUNT PFILE='\/u01\/exports\/init_orcl.ora'; <\/code><\/code><\/pre>\n\n\n\n
                      \n
                    1. Ensure backup pieces are copied from Production to target path<\/strong>:
                      Example: \/u01\/exports\/backup\/<\/code><\/li>\n\n\n\n
                    2. Ensure password file is in place<\/strong>: cp \/tmp\/orapwPROD \/u01\/app\/oracle\/product\/19.0.0\/dbhome_1\/dbs\/orapwORCL<\/code><\/li>\n<\/ol>\n\n\n\n
                      \u2705 Create RMAN Script File (.txt)<\/h2>\n\n\n\n
                      Create a plain text file (e.g., rman_duplicate.txt<\/code>) containing the RMAN commands:<\/p>\n\n\n\n
                      \ud83d\udcdd rman_duplicate.txt<\/code> \u2013 Duplicate from Backup with SCN<\/h3>\n\n\n\n
                      RUN {\n  SET UNTIL SCN 123456789;\n  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;\n  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;\n  DUPLICATE DATABASE TO ORCL\n    BACKUP LOCATION '\/u01\/exports\/backup'\n    NOFILENAMECHECK;\n}<\/code><\/code><\/pre>\n\n\n\n
                      OR<\/p>\n\n\n\n
                      \u23f1 Using Timestamp Instead:<\/h3>\n\n\n\n
                      RUN {\n  SET UNTIL TIME \"TO_DATE('2024-06-01 02:30:00', 'YYYY-MM-DD HH24:MI:SS')\";\n  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;\n  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;\n  DUPLICATE DATABASE TO ORCL\n    BACKUP LOCATION '\/u01\/exports\/backup'\n    NOFILENAMECHECK;\n}<\/code><\/code><\/pre>\n\n\n\n
                      \n
                      \ud83d\udcdd Save this as rman_duplicate.txt<\/code> in \/u01\/scripts<\/code> or any custom location.<\/p>\n<\/blockquote>\n\n\n\n
                      \u2705 Create Shell Script Wrapper (.sh)<\/h2>\n\n\n\n
                      Create a .sh<\/code> shell file that wraps the RMAN call:<\/p>\n\n\n\n
                      \ud83d\udda5\ufe0f rman_run_duplicate.sh<\/code><\/h3>\n\n\n\n
                      #!\/bin\/bash\nexport ORACLE_SID=ORCL\nexport ORACLE_HOME=\/u01\/app\/oracle\/product\/19.0.0\/dbhome_1\nexport PATH=$ORACLE_HOME\/bin:$PATH\n\nrman target \/ cmdfile=\/u01\/scripts\/rman_duplicate.txt log=\/u01\/scripts\/rman_duplicate.log<\/code><\/code><\/pre>\n\n\n\n
                      \n
                      \ud83d\udccc Make it executable:<\/p>\n<\/blockquote>\n\n\n\n
                      chmod +x \/u01\/scripts\/rman_run_duplicate.sh<\/code><\/code><\/pre>\n\n\n\n
                      \u2705 Run RMAN Duplicate in Background with nohup<\/h2>\n\n\n\n
                      nohup \/u01\/scripts\/rman_run_duplicate.sh &<\/code><\/code><\/pre>\n\n\n\n
                      You can tail the log:<\/p>\n\n\n\n
                      tail -f \/u01\/scripts\/rman_duplicate.log<\/code><\/code><\/pre>\n\n\n\n
                      \u2705 Tips and Notes<\/h2>\n\n\n\n
                        \n
                      • NOFILENAMECHECK<\/code> \u2013 Use only if file structure is the same on source & target.<\/li>\n\n\n\n
                      • Avoid running from SQL prompt interactively\u2014use nohup<\/code> to prevent session loss.<\/li>\n\n\n\n
                      • SET UNTIL<\/code> helps point-in-time duplication, use current SCN for latest consistent state.<\/li>\n<\/ul>\n\n\n\n
                        Post Stuff \u2013 (Target Database)<\/mark><\/h2>\n\n\n\n
                        These post-refresh steps are performed on the target database<\/strong> 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.<\/p>\n\n\n\n
                        \u2705 1. Confirm RMAN Duplicate Completed Successfully<\/h3>\n\n\n\n
                        cat \/u01\/scripts\/rman_duplicate.log | grep -i \"Finished Duplicate Db\"<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Ensure there are no errors before proceeding.<\/em><\/p>\n\n\n\n
                        \u2705 2. Recreate Directory Object if Dropped During Duplicate<\/h3>\n\n\n\n
                        CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '\/u01\/exports';\nGRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO PUBLIC;<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 3. Verify Database Status<\/h3>\n\n\n\n
                        set lines 200 pages 200\ncol name for a15\ncol db_unique_name for a15\ncol open_mode for a15\ncol log_mode for a15\ncol logins for a15\ncol instance_name for a15\ncol HOST_NAME for a15\nalter session set nls_date_format='DD\/MM\/YYYY HH24:MI:SS';\nselect name,db_unique_name,created,open_mode,log_mode,logins,instance_name,database_role,host_name,startup_time from v$instance,v$database;<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Database should be in OPEN state and role should be PRIMARY.<\/em><\/p>\n\n\n\n
                        \u2705 4. Import Full Metadata<\/h3>\n\n\n\n
                        nohup impdp '\"\/ as sysdba\"' full=y content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=full_meta.dmp logfile=full_meta_imp.log parallel=4 &\n<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 5. Drop Existing Users Before Reimport (Example: HR, SCOTT)<\/h2>\n\n\n\n
                        Sometimes, users cannot be dropped due to active sessions. Use this 3-step method to cleanly drop them:<\/p>\n\n\n\n
                        Step 1: Lock the Schemas to Prevent New Connections<\/h4>\n\n\n\n
                        ALTER USER HR ACCOUNT LOCK;\nALTER USER SCOTT ACCOUNT LOCK;<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc This ensures no new sessions are created while you’re trying to drop the users.<\/em><\/p>\n\n\n\n
                        Step 2: Identify and Kill Active Sessions (If Any)<\/h4>\n\n\n\n
                        -- Generate kill commands for HR and SCOTT\nSELECT \n  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command\nFROM v$session\nWHERE username IN ('HR', 'SCOTT');<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Run the output of this query to terminate active sessions that may prevent the drop.<\/em><\/p>\n\n\n\n
                        Example output:<\/p>\n\n\n\n
                        ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;\nALTER SYSTEM KILL SESSION '124,457' IMMEDIATE;<\/code><\/code><\/pre>\n\n\n\n
                        Step 3: Drop the Users<\/h4>\n\n\n\n
                        DROP USER HR CASCADE;\nDROP USER SCOTT CASCADE;<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Now that the sessions are terminated and the users are locked, the drop should succeed without error.<\/em><\/p>\n\n\n\n
                        \u2705 6. Validate Users No Longer Exist<\/h3>\n\n\n\n
                        SET lines 200 pages 100\nCOL username FOR A20\nSELECT username FROM dba_users WHERE username IN ('HR','SCOTT');<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Should return no rows.<\/em><\/p>\n\n\n\n
                        \u2705 7. Import Two Schemas<\/h3>\n\n\n\n
                        nohup impdp '\"\/ as sysdba\"' schemas=HR,SCOTT directory=DATA_PUMP_DIR dumpfile=hr_scott.dmp logfile=hr_scott_imp.log parallel=4 &<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 8. Verify Users Are Created & Unlocked<\/h3>\n\n\n\n
                        SET lines 200 pages 100\nCOL username FOR A20\nCOL account_status FOR A20\nCOL created FOR A30\nSELECT username, account_status, created FROM dba_users WHERE username IN ('HR','SCOTT');<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Status should be OPEN and creation time should reflect import time.<\/em><\/p>\n\n\n\n
                        \u2705 9. Import 5 Tables<\/h3>\n\n\n\n
                        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 &<\/span><\/code><\/code><\/pre>\n\n\n\n
                        \u2705 10. Import Grants<\/h3>\n\n\n\n
                        nohup impdp '\"\/ as sysdba\"' include=GRANT directory=DATA_PUMP_DIR dumpfile=grants.dmp logfile=grants_imp.log parallel=4 &\n<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 11. Import Synonyms<\/h3>\n\n\n\n
                        nohup impdp '\"\/ as sysdba\"' include=SYNONYM directory=DATA_PUMP_DIR dumpfile=synonyms.dmp logfile=synonyms_imp.log parallel=4<\/span> &\n<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 12. Reset Passwords and Restore Profiles<\/h3>\n\n\n\n
                        -- Step 1: Set profile to DEFAULT for all users\nSPOOL reset_profiles.sql\nSELECT 'ALTER USER ' || username || ' PROFILE DEFAULT;' FROM dba_users;\nSPOOL OFF;\n\n-- Step 2: Restore passwords from backup script\n@user_passwords.sql\n\n-- Step 3: Restore original profiles\n@user_profile_set.sql<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 13. Restore DB Links (With Drop & Re-Import Logic)<\/h3>\n\n\n\n
                        -- ================================================\n-- Step 1: List All DB Links (User-wise)\n-- ================================================\nSET LINESIZE 150\nSET PAGESIZE 1000\nCOL owner     FOR A20\nCOL db_link   FOR A35\nCOL username  FOR A20\n-- View current DB links grouped by owner\nSELECT owner, db_link, username \nFROM dba_db_links \nORDER BY owner, db_link;\n\n-- ================================================\n-- Step 2: Drop DB Links for a Specific Schema\n-- ================================================\n-- Replace &schema with actual schema name like HR, SCOTT etc.\n-- This procedure drops all DB links owned by a given user.\n\nCREATE OR REPLACE PROCEDURE &schema..drop_db_link AS\n  CURSOR c_dbl IS \n    SELECT db_link FROM user_db_links;\nBEGIN\n  FOR c_1 IN c_dbl LOOP\n    EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || c_1.db_link;\n  END LOOP;\nEND drop_db_link;\n\/\n\n-- Execute the procedure to drop DB links\nEXEC &schema..drop_db_link;\n-- Confirm that DB links for the schema are dropped\nSELECT db_link \nFROM dba_db_links \nWHERE owner = UPPER('&schema');\n-- Drop the temporary procedure after use\nDROP PROCEDURE &schema..drop_db_link;\n\n-- \ud83d\udccc NOTE:\n-- You must reconnect to SQL*Plus or re-authenticate between schemas\n-- if you\u2019re doing this for multiple users.\n\n-- ================================================\n-- Step 3: Import Backed Up DB Links from Dump\n-- ================================================\n-- Restore DB links taken from pre-refresh backup using Data Pump.\n\n-- Run this from OS prompt:\n-- It imports only DB links from the specified dump file.\n\nnohup impdp '\"\/ as sysdba\"' include=DB_LINK directory=DATA_PUMP_DIR dumpfile=dblinks.dmp logfile=dblinks_imp.log &<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 14. Compile All Invalid Objects<\/h3>\n\n\n\n
                        @?\/rdbms\/admin\/utlrp.sql<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 15. Verify RMAN Settings (Compare with Source Backup)<\/h3>\n\n\n\n
                        rman target \/\nSHOW ALL;<\/code><\/code><\/pre>\n\n\n\n
                        \ud83d\udccc Cross-verify settings with what was captured in Pre Stuff.<\/em><\/p>\n\n\n\n
                        \u2705 16. Register Database with RMAN Catalog (Optional)<\/h3>\n\n\n\n
                        rman target \/ catalog rman\/password@catalog_db_name\nREGISTER DATABASE;<\/code><\/code><\/pre>\n\n\n\n
                        \u2705 17. Take Manual Archive Log & Full Backup<\/h3>\n\n\n\n
                        rman target \/\n\n-- Archive Log Backup\nBACKUP ARCHIVELOG ALL;\n\n-- Full DB Backup\nBACKUP DATABASE PLUS ARCHIVELOG;<\/code><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
                        Pre Stuff on Source Database This phase is executed on the target database \u2014 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 […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4229","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4229","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}