Dear all,

I have a cold backup taken on 29-Jan-2012 and have archive logs from the point the database started after cold backup to uptil now. Now, I want to create a new database on a different node with the complete data of the production database with these files. Pls guide me how it can be done.

Regards,
Charan

Google is your friend ....

Hi,

I found the solution for creating upto date database with cold backup and archived logs, please look at the below process and let me know in case of any mistakes.

Our environment

OS - Linux x86-64, SLES 10
Oracle apps- R 12.1.1
DB version 11.1.0.7

Pre-cloning tasks ( on souce system )

Assume that the pre-cloning tasks are done on 10-FEB-2012 6 PM

On DB Tier

1. cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/

2. perl adpreclone.pl dbTier pwd=apps

3. Generate control file script

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Control file generation script will be saved in backuground_dump_dest path

exact name of the generated contril file will be written alert_log file

4. stop database and listener

a) In the above mentioned path run the below script

sh addbctl stop immediate

b) Again in the same path

sh addlnctl stop LISTNER_NAME

5. Gunzip the entire db directory

My database is present in /u01/PRD/db
My ORACLE_HOME /u01/PRD/db/tech_st/11.1.0
My ORACLE_SID=PRD

tar -czf /u02/backup/PRD.tar.gz /u01/PRD/db



DONE

Again assume that you need to create new clone on 16-FEB-2012 with the cold backup taken on 10-FEB-2012 and the archive logs generated there after in the source database

1. Archive the latest log file with the below command

ALTER SYSTEM ARCHIVE LOG CURRENT;



On Target system :


1. Create a directory for keeping archive logs

mkdir /u01/TESTArchive/

copy all the archive logs from the source system to this directory generated after the cold backup was taken

chmod and chown to owner of test db in linux bos for archive logs directory

2. Copy the control file script and edit the file in such a way that only the below content exists

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607
LOGFILE
GROUP 1 (
'/TARGET_NODE_PATH/db/apps_st/data/log01a.dbf',
'/TARGET_NODE_PATH/db/apps_st/data/log01b.dbf'
) SIZE 1000M,
GROUP 2 (
'/TARGET_NODE_PATH/db/apps_st/data/log02a.dbf',
'/TARGET_NODE_PATH/db/apps_st/data/log02b.dbf'
) SIZE 1000M
DATAFILE
'/TARGET_NODE_PATH/db/apps_st/data/system01.dbf',
.
.
.
.- all dbf files here -
.
.
.
;


name the conntrol file TestControlFile.sql

3. suppose you want to clone in /u02/TEST directory unzip the backup taken from source to this directory

a) copy the backup taken to target node

b) unzip with the below command

tar -xvf PRD.tar.gz -C /u02/TEST

4. After unzipping change owner and mode as root user, go to /u02/TEST directory

chown -R test:dba db

chmod -R 777 db

5. Login as os db user (test)

edit the bash_profile and set the needed 3 environmental variables

ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH

6. Go to the path where dbf files are present

delete contriol files
delete log files
delete temp files

7. Now go to $ORACLE_HOME/dbs

a) cp initPRD.ora initTEST.ora

b) edit initTEST.ora

change database name
change the paths according to target node
change the archive log path
change the ifile name

c) cp ifile_CONTEXT_NAME ifile_TAGET_CONTEXT_NAME

8) In initTEST.ora file we have edited dump directory and outbound/CONTEXT_NAME directory to target node paths,

create those direcotries in target node

mkdir $ORACLE_HOME/admin/CONTEXT_NAME

mkdir $ORACLE_HOME/appsutil/oubound/CONTEXT_NAME

9) relogin as db owner in linux box

su - test

sqlplus / as sysdba

1. startup nomount

2. Create control file, run the script we have

@TestControlFile.sql

3. RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

It will promptto apply archive logs and suggests the archives generated after cold backup

apply one by one manually

if all the archives are done then it will ask for latest archive which you dont have

just give CANCEL

4. ALTER DATABASE OPEN RESETLOGS

After this control files and redo's would have been generated in dbf file paths

Now time to create temp files

ALTER TABLESPACE TEMP1 ADD TEMPFILE '/PATH/db/apps_st/data/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE TEMP1 ADD TEMPFILE '/PATH/db/apps_st/data/temp02.dbf' SIZE 2048M REUSE AUTOEXTEND OFF;

5. shutdown immediate

------------NOW DATABASE WILL HAVE UPTO DATE DATA-----

NOW DO CLONING SUITABLE FOR EBIZ environment

After shutting down database

6. Go to $ORACLE_HOME/dbs

delete initTEST.ora

delete ifile_TAGET_CONTEXT_NAME

delete the two directories created

$ORACLE_HOME/admin/CONTEXT_NAME

$ORACLE_HOME/appsutil/outbound/CONTEXT_NAME

in vi.bash_profile delete everything and put only the below path

. /PATH/db/tech_st/11.1.0/CONTEXT_NAME.env

7. re login as test user su - test

Go to $ORACLE_HOME/appsutil/clone/bin

perl adcfgclone dbTier

do normal cloning steps

8. re login as test user and all your env variables will be set

9. You can clone apps tier and assign this database to apps tier.

------END-----------

suggestions please

Did your solution work? If not, what error/s were shown?