DB Restoration | Standard Oracle Database (no EBS, ASM, or PDB configurations).
Background
Objective:
Restore the TEST2 test instance using an existing production backup.
Pre-Restoration Step:
A backup of the current TEST database was performed prior to initiating the restoration process.
Database Specifications:
- Size: Approximately 3.5 TB
- Architecture: Standard Oracle Database (no EBS, ASM, or PDB configurations).
- Storage: All database-related files, including
DB_HOME, are located under the/u01mount point. - Compute Resources: The instance is equipped with 48 CPUs; 16 RMAN channels were allocated to optimize the restoration process.
Pre-Restoration Checks
Production DB size:
SQL> SELECT SUM(bytes)/1024/1024/1024 AS "Total Database Size (GB)" FROM dba_data_files;
Mount point space: Ensure there is enough disk space to accommodate the restored datafiles, control files, and log files.
spfile path:
SQL> SELECT value FROM v$parameter WHERE name = 'spfile';
Log_File path:
Check and copy the log file path and temp file path:
SQL> SELECT GROUP#, MEMBER FROM v$logfile;
SQL> SELECT file#, bytes, name FROM v$tempfile;
Check the Current Database Status:
Ensure the current database is properly shut down before starting the restoration.
SQL> select status from v$instance;
Verify Listener Status:
Ensure the Oracle listener is up and running to handle incoming connections.
$ lsnrctl status
Confirm Initialization Parameters:
Ensure the initialization parameters (especially those that will be modified during restoration) are correctly set.
SQL> show parameter control_files;
SQL> show parameter db_name;
SQL> show parameter audit_file_dest;
SQL> show parameter db_recovery_file_dest;
SQL> show parameter diagnostic_dest;
SQL> show parameter local_listener;
Verify Network Configuration:
Ensure that the network configuration, such as tnsnames.ora and listener.ora, is correctly set up to avoid connectivity issues.
Check tnsnames.ora:
$ cat $ORACLE_HOME/network/admin/tnsnames.ora
Check listener.ora
$ cat $ORACLE_HOME/network/admin/listener.ora
Validate Archive Log Mode:
Ensure the database is in ARCHIVELOG mode if required, as this affects how redo logs are handled during the restoration.
SQL> archive log list;
Ensure Pfile/Spfile Configuration:
Verify that the parameter files (pfile/spfile) are correctly configured and available for use during the restoration.
$ ls $ORACLE_HOME/dbs/init*.ora
$ ls $ORACLE_HOME/dbs/spfile*.ora
Confirm Database User and Permissions:
Ensure that the required database users have the necessary permissions to perform the restoration.
SQL> select * from dba_role_privs where grantee='USERNAME';
SQL> select * from dba_sys_privs where grantee='USERNAME';
Restoration Tasks
1. Get Backup from same server
Before getting backup, you need to check the LOG_MODE; it should be ARCHIVELOG
SQL> SELECT name, open_mode, log_mode, database_role FROM v$database;
If it is not in ARCHIVELOG mode, execute the following command:
SQL> ALTER DATABASE ARCHIVELOG;
Backup script creation:
$ vi backup_script.sh
Change the script permissions from the root user:
# chmod -R 775 backup_script.sh
Run the backup script using nohup:
# nohup sh backup_script.sh &
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 <<Has to be change>>
export ORACLE_SID=TEST2 <<Has to be change>>
export PATH=$ORACLE_HOME/bin:$PATH
fdate=`date "+%d-%b-%Y"`
ddate=`date -d "-5 days" "+%d-%b-%Y"`
BACKUP_LOCATION=/mnt/backup/TEST2_BACKUP; <<Has to be change>>
export BACKUP_LOCATION
BACKLOGDIR=/mnt/backup/TEST2_BACKUP <<Has to be change>>
export BACKLOGDIR
LOGFILE=$BACKLOGDIR/RMAN_`date +%d%m%Y-%H%M`.log
export LOGFILE;
cd $BACKUP_LOCATION
mkdir RMAN_$fdate
echo " RMAN PROD_DATABASE backup started at `date`" >> $LOGFILE
$ORACLE_HOME/bin/rman nocatalog <<EOF | tee $LOGFILE
connect target /
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset filesperset 5 incremental level 0 database format
'$BACKUP_LOCATION/RMAN_$fdate/%d_data_full_%s_%p_%u' plus archivelog format '$BACKUP_LOCATION/RMAN_$fdate/Arch_%d_%u';
backup current controlfile format '$BACKUP_LOCATION/RMAN_$fdate/%d_control_%s_%p_%u';
release channel d1;
release channel d2;
}
EOF
2. Create pfile from spfile
We can create a Pfile using the same server's spfile. Alternatively, we can copy one from the production database. However, copying a production Pfile requires modifying all parameters.
When using the same server's spfile, we only need to change two specific parameters:
- db_name to 'production DB_NAME' (from the TEST database name)
- Comment out local_listener (change it to #local_listener='LISTENER_TEST2')
3. Drop Database
SQL> shutdown immediate;
SQL> startup mount exclusive restrict;
SQL> drop database;
After dropping the database, all data files, redo log files, and control files are removed. However, we need to check and delete those files if they are not removed, as well as archive files.
4. Database Start using Pfile
If needed, we can copy the Pfile to $ORACLE_HOME/dbs. If we copy it to that location, back up the old Pfile and copy the new one, or we can use the Pfile in its current location. Before restarting the database using the Pfile, we need to check if every file path is there or not. If not, we have to create them.
If we are in the init file location:
SQL> startup nomount pfile='initNEWTEST2.ora';
If we are in a different location:
SQL> startup nomount pfile='/tmp/initNEWTEST2.ora';
5. Restore the control files
After restoring the control file, we need to mount the database:
RMAN> alter database mount;
6. Catalog DB file
RMAN> catalog start with 'TEST/PROD_BACKUP/RMAN_08-May-2024';
When we catalog the database files, permission is required to catalog. Before giving YES, we have to check if there are only cataloged database files or not. Don’t catalog unnecessary files.
7. Restore DB
We can restore using commands or a script. We can create channels to reduce the restoration time. Before allocating channels, we need to check CPU capacity and allocate channels accordingly. For example, if there are 48 CPUs, we can allocate 24 channels.
Without allocating channels:
RMAN> restore database;
With allocating channels:
restore.cmd
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
set newname for database to '/u01/app/oradata/data/%b'; <<Have to change data file path>
If we are running using nohup:
nohup rman target / cmdfile restore.cmd &
Restoration can take more time, so you can perform the restoration using nohup or VNC.
8. Recover Database
recover.cmd
nohup rman target / cmdfile restore.cmd &
Restoration can take more time, so you can perform the restoration using nohup or VNC.
9. Log file path checking
Check the redo log file path. If the path is different, we have to change it to the old path.
Before making any changes, the database should be in mount mode.
SQL> select GROUP#,MEMBER from v$logfile;
In this database, the redo log file path is different. It should be changed to '/u01/app/oradata/redo_logs/'. So, we alter the path.
SQL> ALTER DATABASE RENAME FILE 'current_path' TO 'new_path';
Here's an example of how you can change the paths of all redo log files in one go using a PL/SQL loop:
v_old_path VARCHAR2(4000) := '/old/path/';
v_new_path VARCHAR2(4000) := '/new/path/';
BEGIN FOR redo_log IN (SELECT GROUP#, MEMBER FROM v$logfile)
LOOP EXECUTE IMMEDIATE 'ALTER DATABASE RENAME FILE ''' || redo_log.MEMBER || ''' TO ''' || REPLACE(redo_log.MEMBER, v_old_path, v_new_path) || '''';
END LOOP;
END;
/
Check the temp file path. If the path is different, we have to change it to the old path.
Rename tempfiles
SQL> SELECT file#, name FROM v$tempfile;
SQL> ALTER DATABASE TEMPFILE 'current_path' TO 'new_path';
OR
Drop and add new tempfiles
Drop tempfiles
SQL> ALTER DATABASE TEMPFILE 'tempfile_path' DROP INCLUDING DATAFILES;
Add tempfiles
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/new/path/temp01.dbf' SIZE 1G AUTOEXTEND
ON NEXT 250M MAXSIZE 32767M;
Here's an example of how you can change the path of all tempfiles in one go using a PL/SQL loop:
v_old_path VARCHAR2(4000) := '/old/path/';
v_new_path VARCHAR2(4000) := '/new/path/';
BEGIN FOR temp_file IN (SELECT file#, name FROM v$tempfile) LOOP EXECUTE IMMEDIATE 'ALTER DATABASE TEMPFILE ''' || temp_file.name || ''' TO ''' || REPLACE(temp_file.name, v_old_path, v_new_path) || '''';
END LOOP;
END;
/
Comments
Post a Comment