Database Refresh Activity

Oracle Database Refresh is the process of copying or refreshing the data from a source database (typically production) to a target database (often a non-production environment such as development or test). This can be done through various methods like RMAN, Data Pump (EXPDP/IMPDP), or Cloning. Here are the detailed steps for the most common method: RMAN Duplicate or Restore & Recovery.

Prerequisites

• Source Database: The production (or other) database you are copying from.

• Target Database: The test, development, or non-production database.

• Database Backups: RMAN full backups, archive logs, and control files of the source database.

• TNS Configuration: Make sure both source and target databases can communicate via TNS.

• Disk Space: Ensure sufficient disk space for the restore.

• Environment Setup: The target environment should be prepared with necessary Oracle software and configuration.

Method 1: RMAN Duplicate for Database Refresh

Step 1: Prepare the Target Environment

• Install the Oracle software if it’s not already installed on the target server.

• Ensure the same version of Oracle is installed on both the source and target systems.

• Configure Oracle parameters, such as ORACLE_HOME, ORACLE_SID, etc., on the target server.

Step 2: Take RMAN Backup of Source Database

Ensure you have an RMAN backup of the source database, including the control file and archive logs.

sql

Copy code

RUN {

  BACKUP DATABASE PLUS ARCHIVELOG;

  BACKUP CURRENT CONTROLFILE;

}

Step 3: Transfer Backup Files

Copy the RMAN backup files, control file, and any archive logs from the source server to the target server using scp or another file transfer method.

Step 4: Create Parameter File (PFILE) on Target

On the target server, create a new parameter file (PFILE) by copying the source database PFILE and modifying it for the new environment. Update parameters like db_name, db_unique_name, control_files, log_file paths, etc.

bash

Copy code

vi $ORACLE_HOME/dbs/initTARGET.ora

# Update the following parameters:

db_name=targetdb

control_files=/u01/app/oracle/oradata/targetdb/control01.ctl

db_file_name_convert=('/u01/app/oracle/oradata/sourcedb/', '/u01/app/oracle/oradata/targetdb/')

log_file_name_convert=('/u01/app/oracle/oradata/sourcedb/', '/u01/app/oracle/oradata/targetdb/')

Step 5: Start Target Database in NOMOUNT Mode

Start the target instance in NOMOUNT mode.

sql

Copy code

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19c/db_1/dbs/initTARGET.ora';

Step 6: Duplicate the Database using RMAN

Use RMAN to connect to both the source and target databases and start the duplication process.

bash

Copy code

rman TARGET sys/password@SOURCE AUXILIARY sys/password@TARGET

RMAN> DUPLICATE TARGET DATABASE TO TARGETDB

      SPFILE

      BACKUP LOCATION '/u01/app/oracle/backup'

      NOFILENAMECHECK;

This will create the new database by copying the data from the source to the target and applying the necessary logs to synchronize it.

Step 7: Update or Modify DB Configuration

• If needed, adjust parameters like db_file_name_convert and log_file_name_convert for the new target environment in the parameter file (SPFILE).

• Change any other necessary initialization parameters like open_cursors, memory_target, etc., based on the target environment’s resource configuration.

Step 8: Open the Database

After the duplication, you need to open the database with resetlogs:

sql

Copy code

sqlplus / as sysdba

ALTER DATABASE OPEN RESETLOGS;

Step 9: Verify Database Integrity

• Check if all the datafiles, tablespaces, and logs are present and operational.

• Query the v$datafile, v$tablespace, v$logfile views to confirm that all database files are accounted for.

Step 10: Post-Refresh Steps

• Rebuild any database-specific objects such as jobs, directories, or links that are environment-specific.

• Recreate database links if necessary, pointing them to the appropriate environments.

• Update any user credentials or test data as required for the refreshed database environment.

• Run gather schema stats if performance testing will be conducted on the refreshed database.

Method 2: Using RMAN Backup and Restore

Step 1: Take Full Backup of Source Database

• Take a full RMAN backup of the source database along with archive logs:

sql

Copy code

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

• Backup the control file and server parameter file:

sql

Copy code

RMAN> BACKUP CURRENT CONTROLFILE;

Step 2: Transfer Backup to Target System

• Transfer the full backup and archive logs from the source system to the target server.

Step 3: Prepare Target Server

• Install the Oracle software.

• Create a new PFILE for the target database, setting the parameters like db_name, control_files, and datafile locations.

Step 4: Restore the Backup on the Target System

• Start the target database in NOMOUNT mode:

sql

Copy code

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/path_to_pfile';

• Use RMAN to restore the control file and mount the database:

bash

Copy code

RMAN> RESTORE CONTROLFILE FROM '/path_to_backup/controlfile.bkp';

RMAN> ALTER DATABASE MOUNT;

• Restore the datafiles and apply the archive logs to recover the database:

bash

Copy code

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

Step 5: Open the Database with RESETLOGS

sql

Copy code

ALTER DATABASE OPEN RESETLOGS;

Post-Refresh Activities

1. Change Environment-Specific Data: Modify the target database to adjust for environment-specific data, such as removing or modifying production data to protect sensitive information.

2. Update Initialization Parameters: Change any environment-specific initialization parameters to suit the target environment.

3. Recreate Jobs and Database Links: If the source has jobs or database links pointing to production systems, recreate them with the appropriate values for the test/development environment.

4. Validate the Refresh: Ensure the integrity of the refreshed database by running health checks and verifying key data.

5. Rebuild Indexes/Statistics: Run DBMS_STATS.GATHER_DATABASE_STATS to rebuild statistics if the target is used for performance testing.

Conclusion

Oracle Database Refresh can be performed using RMAN Duplicate or RMAN backup/restore. RMAN Duplicate simplifies the process by automating most of the steps, while backup/restore provides more manual control. Post-refresh activities like changing environment-specific data, modifying jobs, and verifying integrity ensure the database is ready for development or testing activities.

Performing an Oracle database refresh using EXPDP (Data Pump Export) and IMPDP (Data Pump Import) involves several steps to ensure a successful migration of data from a source database to a target database. This method is commonly used for schema-level, tablespace-level, or full database refreshes in test, development, or non-production environments.

Here is a step-by-step guide for performing a detailed database refresh using EXPDP/IMPDP.

1. Pre-requisites for EXPDP/IMPDP Database Refresh

Before beginning the refresh process, you need to complete some setup tasks:

1.1 Prepare Source Database

• Ensure that the source database is running and the schemas or data you want to export are consistent.

• You might want to quiesce the database or use Flashback to get a consistent export.

• Ensure you have enough disk space in the directory where the export dump files will be written.

1.2 Prepare Target Database

• Install Oracle Database software if it is a new environment.

• Ensure the target database is up and running with the necessary tablespaces or schemas if needed.

• Create directory objects in both source and target databases for storing and reading dump files.

• Ensure that sufficient privileges (like DBA) are granted to the user performing the operations.

2. Create Directory Objects on Both Databases

A directory object is necessary for Data Pump to know where to store and retrieve the dump files. You need to create a directory object on both the source and target databases.

2.1 On Source Database

sql

Copy code

CREATE DIRECTORY expdp_dir AS '/u01/app/oracle/backup';

GRANT READ, WRITE ON DIRECTORY expdp_dir TO dba_user;

2.2 On Target Database

sql

Copy code

CREATE DIRECTORY impdp_dir AS '/u01/app/oracle/backup';

GRANT READ, WRITE ON DIRECTORY impdp_dir TO dba_user;

3. Data Pump Export (EXPDP) from Source Database

Use the expdp command to export data from the source database. Depending on your needs, you can export a full database, schema, or specific tables.

3.1 Full Database Export

This exports the entire database, including all schemas, tables, and objects. Useful for refreshing the entire target database.

bash

Copy code

expdp system/password@source_db FULL=Y DUMPFILE=full_db_%U.dmp LOGFILE=exp_full_db.log DIRECTORY=expdp_dir FILESIZE=5G

3.2 Schema-Level Export

This exports specific schemas from the source database.

bash

Copy code

expdp system/password@source_db SCHEMAS=schema_name DUMPFILE=schema_name_%U.dmp LOGFILE=exp_schema.log DIRECTORY=expdp_dir FILESIZE=5G

3.3 Table-Level Export

This exports specific tables from the source schema.

bash

Copy code

expdp system/password@source_db TABLES=schema_name.table1,schema_name.table2 DUMPFILE=tables_%U.dmp LOGFILE=exp_tables.log DIRECTORY=expdp_dir FILESIZE=5G

3.4 Using Flashback for Consistent Export

To maintain consistency, especially for large databases, use the FLASHBACK_TIME or FLASHBACK_SCN option.

bash

Copy code

expdp system/password@source_db FULL=Y FLASHBACK_TIME=SYSTIMESTAMP DUMPFILE=full_db_%U.dmp LOGFILE=exp_full_db.log DIRECTORY=expdp_dir

3.5 Using Compression (Optional)

You can also compress the dump file to save space.

bash

Copy code

expdp system/password@source_db SCHEMAS=schema_name DUMPFILE=schema_name_%U.dmp LOGFILE=exp_schema.log DIRECTORY=expdp_dir COMPRESSION=ALL

4. Transfer Dump Files to Target Server

After the export completes, you need to transfer the dump files to the target server. Use scp, rsync, or any other secure file transfer tool.

bash

Copy code

scp /u01/app/oracle/backup/full_db_*.dmp oracle@target_server:/u01/app/oracle/backup/

5. Pre-Import Preparation on Target Database

Before importing data into the target database, make sure the environment is prepared:

5.1 Drop or Truncate Existing Schemas/Tables (if needed)

If the schema or objects already exist on the target, they need to be dropped or truncated to avoid conflicts during the import process.

Drop Schema:

sql

Copy code

DROP USER schema_name CASCADE;

Truncate Tables:

sql

Copy code

TRUNCATE TABLE schema_name.table_name;

5.2 Create Tablespaces (if needed)

If you’re importing into a new database or refreshing tablespaces, ensure that the required tablespaces exist.

sql

Copy code

CREATE TABLESPACE users DATAFILE '/u01/app/oracle/oradata/target/users01.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

6. Data Pump Import (IMPDP) into Target Database

Use the impdp command to import the exported data into the target database.

6.1 Full Database Import

To import the entire database:

bash

Copy code

impdp system/password@target_db FULL=Y DUMPFILE=full_db_%U.dmp LOGFILE=imp_full_db.log DIRECTORY=impdp_dir

6.2 Schema-Level Import

To import specific schemas:

bash

Copy code

impdp system/password@target_db SCHEMAS=schema_name DUMPFILE=schema_name_%U.dmp LOGFILE=imp_schema.log DIRECTORY=impdp_dir

6.3 Table-Level Import

To import specific tables:

bash

Copy code

impdp system/password@target_db TABLES=schema_name.table1,schema_name.table2 DUMPFILE=tables_%U.dmp LOGFILE=imp_tables.log DIRECTORY=impdp_dir

6.4 Using REMAP_SCHEMA and REMAP_TABLESPACE

To import schemas into a different schema or tablespace in the target database:

Remap Schema:

bash

Copy code

impdp system/password@target_db SCHEMAS=source_schema DUMPFILE=schema_name_%U.dmp LOGFILE=imp_schema.log DIRECTORY=impdp_dir REMAP_SCHEMA=source_schema:target_schema

Remap Tablespace:

bash

Copy code

impdp system/password@target_db DUMPFILE=full_db_%U.dmp LOGFILE=imp_full_db.log DIRECTORY=impdp_dir REMAP_TABLESPACE=old_ts:new_ts

6.5 Exclude/Include Specific Objects

If you want to exclude or include specific objects like indexes or constraints:

Exclude:

bash

Copy code

impdp system/password@target_db SCHEMAS=schema_name DUMPFILE=schema_name_%U.dmp LOGFILE=imp_schema.log DIRECTORY=impdp_dir EXCLUDE=INDEX

Include:

bash

Copy code

impdp system/password@target_db SCHEMAS=schema_name DUMPFILE=schema_name_%U.dmp LOGFILE=imp_schema.log DIRECTORY=impdp_dir INCLUDE=TABLE:"LIKE '%EMP%'"

7. Post-Import Activities

After importing the data, perform the following tasks to ensure that the database is ready for use:

7.1 Compile Invalid Objects

Sometimes objects may become invalid during the import process. Recompile them:

sql

Copy code

EXEC DBMS_UTILITY.compile_schema('SCHEMA_NAME');

7.2 Gather Statistics

Gathering statistics is important for database performance. It can be done at the schema or table level.

sql

Copy code

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

7.3 Recreate Database Links

If the source database has database links, you may need to recreate them on the target with appropriate changes to point to the right environment.

sql

Copy code

CREATE DATABASE LINK dblink_name CONNECT TO username IDENTIFIED BY password USING 'tns_entry';

7.4 Rebuild Indexes (if excluded)

If you excluded indexes during the import process, rebuild them to optimize performance.

sql

Copy code

ALTER INDEX index_name REBUILD;

8. Automation Using Cron Jobs (Optional)

To automate the refresh process, you can set up a cron job that triggers the export/import process at regular intervals.

Example: Daily Export

You can create a shell script to run expdp and schedule it in crontab.

bash

Copy code

0 2 * * * /u01/app/oracle/scripts/daily_expdp.sh

The daily_expdp.sh script:

bash

Copy code

#!/bin/bash

expdp system/password@source_db SCHEMAS=your_schema DUMPFILE=schema_%U.dmp LOGFILE=exp_schema.log DIRECTORY=expdp_dir FILESIZE=5G

Summary

By following these detailed steps, you can perform a smooth and efficient database refresh using EXPDP and IMPDP. This approach is highly flexible, allowing you to refresh entire databases, specific schemas, or tables. With options like remapping schemas and tablespaces, or excluding objects, you can tailor the refresh to your specific requirements. Post-import activities like compiling objects, gathering statistics, and rebuilding indexes ensure that the database is optimized for performance in the target environment.