In this article, we will demonstrate Offline Restore or clone Database on the same server with a new name or rename database in Oracle. This approach is recommended for small to mid-size databases along with downtime to copy datafiles to the new location, If no RMAN backup schedule i.e database in NO ARCHIVE LOG mode. Let’s begin steps for the same and below are details of source & target components.
1. Create directory: Create directories for AUDIT and physical files as required. 990f02
2. PFILE and CONTROLFILE Backup: Take PFILE backup and generate CONTROLFILE to trace (text format). Here we have two options to generate in text format either to the desire location or to the USER_DUMP_DEST location for CONTROLFILE backup.
3. Capture Physical files: Capture details of physical files. Click here to know about How to find all physicals files in Oracle.
4. Copy datafiles: Copy datafiles & redolog files to the desire location as created in Step 1. Make sure before that clean shutdown database and downtime started from here.
NOTE: Follow the same steps to clone on other servers, instead of command “cp” execute “scp” to transfer all require files to target servers.
5. Prepare PFILE & CONTROLFILE: While copy datafiles are in progress so in between that prepare PFILE & CONTROLFILE for new database LABDB02. Edit the same file which we have taken in Step 2.
5.1. PFILE: Edit PFILE to replace parameter value of DB_NAME to LABDB02, AUDIT LOCATION, and other parameters as required. Below is a sample of PFILE:
5.2. CONTROLFILE: To prepare rename database script or clone or offline restore we require to create CONTROLFILE, edit the trace file which is taken in STEP 2 i.e create CONTROLFILE with new database name LABDB02. Key points to remember to edit and below is SAMPLE file.
=> Remove all
lines in trace file till up to line STARTUP NOMOUNT i.e remove
lines that are comments (line started “–“).
=> Choose wisely options ARCHIVE/NOARCHIVE while creating CONTROLFILE with option RESETLOGS.
=> Replace database name to LABDB02 and keyword REUSE to SET in line CREATE CONTROLFILE command.
=> Replace path of redolog, datafile & tempfile to the new location where we copied files.
=> Make sure file extension should be .sql in case want to execute as a script on SQL prompt.
6: Validate copied datafiles: Validate files after finish Step 3 and start database LABDB01.
7. Add entry in oratab: Using any editor command-line tool to add an entry in the next line for new name DB in /etc/oratab.
8. Start a new database: Start a new database LABDB02, lets follow below:
8.1. Startup in NOMOUNT: Start new database LABDB02 in NOMOUNT stage using PFILE and make sure CONTROLFILEs pointing to correct location.
8.2. OPEN database: Start the new database in the open stage. Before executing this step make sure all files are routed to the correct location where physical files exist along with the correct new database name in the file labdb02_Controlfile.sql.
9. Validate database new name, Control files along with physical files: Now Validate the database name, CONTROLFILES along with all physical files after cloning from LABDB01 to LABDB02. Click here to know more about How to find all physicals files in Oracle.
10. SPFILE & PASSWORD FILE: Create SPFILE using PFILE and restart the database, upon successful restart also create PFILE in the default location and PASSWORD FILE as require.
11. Validate database services and update TNS entries: Validate database services in LISTENER and add TNS entry for LABDB02 as require.