Occassionally you would be requested to refresh the UAT environment with a copy of cold backup of the production database. Be prepared with controlfile script which you can generate it from the production database and transfer it to the UAT
1 Step: have you control file creation script ready. You can generate a control file by the below command in the production database and transfer the file to the UAT.
SQL>alter database backup controlfile to trace as '/u01/temp/createctlfile.sql'; 2. Edit the controlfile script "createctlfile.sql", which would be something like the below in color blue.CREATE CONTROLFILE SET DATABASE "UAT" RESETLOGS NOARCHIVELOG
MAXLOGFILES 20
MAXLOGMEMBERS 4
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 500
LOGFILE
GROUP 1 '/u01/apps1/oracle/oradata/UAT/redo01.log' SIZE 300M,
GROUP 2 '/u02/apps1/oracle/oradata/UAT/redo02.log' SIZE 300M,
GROUP 3 '/u03/apps1/oracle/oradata/UAT/redo03.log' SIZE 300M
-- STANDBY LOGFILE
DATAFILE
'/u01/apps1/oracle/oradata/UAT/system01.dbf',
'/u01/apps1/oracle/UAT/undotbs01.dbf',
'/u01/apps1/oracle/UAT/users01.dbf',
'/u01/apps1/oracle/UAT/data01.dbf',
'/u01/apps1/oracle/UAT/tools01.dbf',
'/u01/apps1/oracle/UAT/perfstat01.dbf',
'/u01/apps1/oracle/UAT/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;
3. Below is the command to generate the script that creates the TEMP tablespace
set long 2000
SQL>select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;
4. Shutdown UAT Database as below.
shutdown immediate
5. Copy the cold backup files using the OS commands from production database to the UAT environment.
6. Startup the database by using nomount
startup nomount
7. create the control file at the UAT by running the script below.
SQL>@/u01/temp/createctlfile.sql
8. Now open the database using resetlogs command
SQL>alter database open resetlogs;
9. Create temp tablespace from the script generated at step 3:
CREATE TEMPORARY TABLESPACE "TEMP3" TEMPFILE
'/u01/apps1/oracle/oradata/UAT/TEMP03.dbf' SIZE 5242880000
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576