After cloning VM with Oracle database

Contents

Environment. 1

Modifying Oracle Home. 1

Changing the DBID and Database Name with DBNEWID utility. 1

Re-creating control files. 1

Changing the DBID.. 1

 

Environment

Oracle Linux Server release 7.3, Oracle database 12.1.0.2

Modifying Oracle Home

Check /etc/oratab:

1.       Rename database in /etc/oratab (after recreating control file if doing so)

2.       Remove/comment out other databases in /etc/oratab if needed.

Check tnsnames.ora:

1.       Modify if necessary. It can contain some Listener entries.

2.       Create an entry for the new database

Listener:

·         Change HOST in listener.ora

·         Startup listener

Changing the DBID and Database Name with DBNEWID utility

If you just need to change DBID and DB name use nid, otherwise consider recreating control file.

Changing the DBID and Database Name with DBNEWID utility (nid):

https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544

sqlplus / as sysdba

shutdown immediate

startup mount

exit

 

nid TARGET=SYS DBNAME=new_name

 

Re-creating control files

If you need to change more than just DB name, e.g. directory name, then it is easier to recreate the control file.

sqlplus / as sysdba

alter database backup controlfile to trace;

 

Go to “trace” directory e.g.:

/opt/oracle/diag/rdbms/mdrt115/MDRT115/trace

Or find where the trace directory is:

select * from V$DIAG_INFO;

Copy the trace file to SQL file, e.g. recreate_cf.sql

Modify the SQL script:

1.       Remove all the lines upto the statement “STARTUP NOMOUNT”

2.       Change the database name

3.       Rename directories for datafiles and redo files.

4.       Add SET after REUSE

5.       RESETLOGS  NOARCHIVELOG

 

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE set DATABASE "MDRP115" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/data001/oradata/MDRP115/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/data001/oradata/MDRP115/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/data001/oradata/MDRP115/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/data001/oradata/MDRP115/system01.dbf',

  '/data001/oradata/MDRP115/sysaux01.dbf',

  '/data001/oradata/MDRP115/undotbs01.dbf',

  '/data001/oradata/MDRP115/users01.dbf',

  '/data001/oradata/MDRP115/XMETA001.dbf',

  '/data001/oradata/MDRP115/XMETASR001.dbf',

  '/data001/oradata/MDRP115/IAUSER001.dbf',

  '/data001/oradata/MDRP115/DSODBSPACE01.dbf',

  '/data001/oradata/MDRP115/QSSRDSPACE01.dbf',

  '/data001/oradata/MDRP115/ESDBSPACE01.dbf'

CHARACTER SET AL32UTF8

;

 

·         Shutdown the database with immediate option. Do not abort.

shutdown immediate

·         create pfile from spfile;

·         rename data file directories if needed (and mount points in /etc/fstab)

·         rename the just created pfile

mv initMDRT115.ora initMDRP115.ora

·         edit the new pfile initMDRP115.ora and change dbname and directory names:

.audit_file_dest

.control_files

.db_name

.dispatchers

·         Make sure directory for parameter audit_file_dest exists.

·         Modify /etc/oratab, create an entry for the new database.

·         Set new env variables (oraenv)

·         Create new spfile from pfile

create spfile from pfile;

·         Run SQL script to re-create control file

@recreate_cf.sql

ORACLE instance started.

 

Total System Global Area 1493172224 bytes

Fixed Size                  2924592 bytes

Variable Size             536874960 bytes

Database Buffers          939524096 bytes

Redo Buffers               13848576 bytes

 

Control file created.

 

·         Open database

ALTER DATABASE OPEN RESETLOGS;

·         Add TEMP

ALTER TABLESPACE TEMP ADD TEMPFILE '/data001/oradata/MDRP115/temp01.dbf'   SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

·         Create password file

cd $OH/dbs

orapwd FILE=orapwMDRP115

 

Re-creating control file does not change DBID!

 

Changing the DBID

sqlplus / as sysdba

shutdown immediate

startup mount

exit

 

nid TARGET=SYS

 

DBNEWID: Release 12.1.0.2.0 - Production on Thu Apr 27 13:57:30 2017

 

Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

 

Password:

Connected to database MDRP115 (DBID=3410461342)

 

Connected to server version 12.1.0

 

Control Files in database:

    /data001/oradata/MDRP115/control01.ctl

    /data001/oradata/MDRP115/control02.ctl

 

Change database ID of database MDRP115? (Y/[N]) => y

 

Proceeding with operation

Changing database ID from 3410461342 to 356948765

    Control File /data001/oradata/MDRP115/control01.ctl - modified

    Control File /data001/oradata/MDRP115/control02.ctl - modified

    Datafile /data001/oradata/MDRP115/system01.db - dbid changed

    Datafile /data001/oradata/MDRP115/sysaux01.db - dbid changed

    Datafile /data001/oradata/MDRP115/undotbs01.db - dbid changed

    Datafile /data001/oradata/MDRP115/users01.db - dbid changed

    Datafile /data001/oradata/MDRP115/XMETA001.db - dbid changed

    Datafile /data001/oradata/MDRP115/XMETASR001.db - dbid changed

    Datafile /data001/oradata/MDRP115/IAUSER001.db - dbid changed

    Datafile /data001/oradata/MDRP115/DSODBSPACE01.db - dbid changed

    Datafile /data001/oradata/MDRP115/QSSRDSPACE01.db - dbid changed

    Datafile /data001/oradata/MDRP115/ESDBSPACE01.db - dbid changed

    Datafile /data001/oradata/MDRP115/temp01.db - dbid changed

    Control File /data001/oradata/MDRP115/control01.ctl - dbid changed

    Control File /data001/oradata/MDRP115/control02.ctl - dbid changed

    Instance shut down

 

Database ID for database MDRP115 changed to 356948765.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

 

sqlplus / as sysdba

startup mount

ALTER DATABASE OPEN RESETLOGS;

 

Cloning Oracle 11.2 database installation on Solaris

Environment

Oracle 11.2.0.2, Solaris 10

Packing Source Oracle Home

export CLONE=/oracle/stage/clone11.2.0.2

echo $CLONE

cd $ORACLE_HOME

Optionally you can create Exclude List for tar command to exclude some files from source home.

Log files:

find . -name "*.log" >$CLONE/ExcludeList.txt
find . -name "*.err" >>$CLONE/ExcludeList.txt

Database templates:

find ./assistants/dbca/templates >>$CLONE/ExcludeList.txt
Network files:

find ./network/admin >>$CLONE/ExcludeList.txt

Check your Exclude file:

cat $CLONE/ExcludeList.txt

tar -cvfX - $CLONE/ExcludeList.txt . | gzip -c >$CLONE/oracle_home11.2.tar.gz

If source and target are on the same host you can use

cp -Rp $ORACLE_HOME

Ignore errors for files owned by root (nmb, nmo, etc.)

Cloning Oracle Home

Check or create oraInst.loc

cat /var/opt/oracle/oraInst.loc

Optionally J check known issues in

Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) [ID 1154613.1]

Navigate to new Oracle home:

cd /oracle/product/11.2.0.2/db_2

or if you have set the variables

cd $ORACLE_HOME

pwd

Unpack Oracle home to new location

gunzip < /oracle/stage/clone11.2.0.2/oracle_home11.2.tar.gz | tar xvf –

unset LD_LIBRARY_PATH

unset LD_LIBRARY_PATH_64

Check that other variables do not point to different Oracle home or unset them

echo $PATH

unset ORACLE_HOME

unset ORACLE_BASE

unset TNS_ADMIN

cd clone/bin

perl clone.pl ORACLE_HOME="/oracle/product/11.2.0.2/db_2" ORACLE_HOME_NAME="OraDB11g_home2" ORACLE_BASE="/oracle" OSDBA_GROUP=dba OSOPER_GROUP=oper

as root run

/oracle/rdbms/11.2.0.2/ora10R4/root.sh

Update

listener.ora

tnsnames.ora

Useful Links

Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) [ID 1154613.1]

How To Clone An Existing RDBMS Installation Using OUI [ID 300062.1]

FAQs on RDBMS Oracle Home Cloning Using OUI [ID 565009.1]

Oracle Documentation - Cloning an Oracle Home - 11.2 for Solaris

Clone oracle 10g database software on Solaris

Environment
Oracle 10.2.0.4, Solaris 10

Cloning
1. Install oracle software, recommended patches and CPU on source host.
2. Oracle recommends to shutdown any databases, listeners, agents etc before copying files.
3. prepare exception list
export CLONE=/oracle/stage/clone10.2.0.4
cd $ORACLE_HOME
find . -name "*.log" >$CLONE/ExcludeList.txt
find . -name "*.err" >>$CLONE/ExcludeList.txt
# network/admin can be useful for next installation
#find ./network/admin >>$CLONE/ExcludeList.txt
find ./oc4j/j2ee/OC4J_DBConsole_* >>$CLONE/ExcludeList.txt
find `hostname |cut -d "." -f 1`*_* >>$CLONE/ExcludeList.txt
# DBCA templates, If u have them
find ./assistants/dbca/templates >>$CLONE/ExcludeList.txt
cat $CLONE/ExcludeList.txt

4. archive source Oracle home
cd $ORACLE_HOME
tar -cvfX - $CLONE/ExcludeList.txt . | gzip -c >$CLONE/oracle_home.tar.gz

5. copy tar.gz file to target host
6. extract oracle home as root to preserve permissions
# cd $ORACLE_HOME
cd /oracle/rdbms/10.2.0/ora10P1
gunzip < /oracle/stage/clone10.2.0.4/oracle_home.tar.gz | tar xvf -
# if you need to change owner of installation. Owner group is the same.
find . -user ora10T4 -exec chown ora10P1 {} \;

7. on target host as oracle user
cd $ORACLE_HOME/rdbms/lib
mv config.o config.o_backup

8. Solaris specific. To change group (dba) that owns the installation modify
vi $ORACLE_HOME/rdbms/lib/config.s
otherwise go to next step

9. Solaris specific
IGNORE make -f ins_rdbms.mk config.o It returns: make: Fatal error: Command failed for target `config.o'
make -f ins_rdbms.mk ioracle

10. run Installer
cd $ORACLE_HOME/oui/bin
detach oracle home if it exists
./runInstaller -detachHome ORACLE_HOME="/oracle/rdbms/10.2.0/ora10P1"
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/oracle/rdbms/10.2.0/ora10P1" ORACLE_HOME_NAME="db_home_10g2_1"

and run as root

$ORACLE_HOME/root.sh

11. (optional) Let other users use Oracle Home.
cd $ORACLE_HOME/install
./changePerm.sh

Useful links

1. Metalink DocID 300062.1 How To Clone An Existing RDBMS Installation Using OUI
2. Metalink DocID 558478.1 Cloning A Database Home And Changing The User/Group That Owns It
3. Oracle Software Cloning Using Oracle Universal Installer

clone oracle 10g database software on AIX

Environment
Oracle 10.2.0.4, CPU Jan 2009, recommended bundle #3
UNIX AIX 5.3.0.0 (both source and target hosts, command oslevel)
source unix user orax104
target unix user orax115

Cloning

1. Install oracle software, recommended patches and CPU on source host.
2. Oracle recommends to shutdown any databases, listeners, agents etc before copying files.
3. prepare exception list
export CLONE=/oracle/export/install
cd $ORACLE_HOME
find . -name "*.log" >$CLONE/ExcludeList.txt
find . -name "*.err" >>$CLONE/ExcludeList.txt
# network/admin can be useful for next installation
#shrept.lst is needed for patch 7226548 (lock_SGA on AIX)
#find ./network/admin >>$CLONE/ExcludeList.txt
find ./oc4j/j2ee/OC4J_DBConsole_* >>$CLONE/ExcludeList.txt
find `hostname |cut -d "." -f 1`*_* >>$CLONE/ExcludeList.txt
# DBCA templates, If u have them
find ./assistants/dbca/templates >>$CLONE/ExcludeList.txt
cat $CLONE/ExcludeList.txt

4. archive Oracle home
cd $ORACLE_HOME
tar -X $CLONE/ExcludeList.txt -cvf - . | gzip -c >$CLONE/oracle_home.tar.gz

5. copy tar.gz file to target host
6. extract oracle home
cd /oracle/export/install
# file was renamed
gunzip rdbms_10.2.0.4_CPUJAN2009_generic_bundle3.tar.gz
# as root to preserve permissions
# cd $ORACLE_HOME
cd /oracle/rdbms/10.2.0/orax115
tar xvf /oracle/export/install/rdbms_10.2.0.4_CPUJAN2009_generic_bundle3.tar
# change owner. Owner group is the same (oinstall)
find . -user orax104 -exec chown orax115 {} \;

7. on target host as oracle user
cd $ORACLE_HOME/rdbms/lib
mv config.o config.o_backup
cd $ORACLE_HOME/oui/bin
#detach oracle home if it exists
./runInstaller -detachHome ORACLE_HOME="/oracle/rdbms/10.2.0/orax115"
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/oracle/rdbms/10.2.0/orax115" ORACLE_HOME_NAME="db_home_10g2_1"

8. (optional) Let other users use Oracle Home.
cd $ORACLE_HOME/install
./changePerm.sh

Useful links:

1. Metalink DocID 300062.1 How To Clone An Existing RDBMS Installation Using OUI
2. Metalink DocID 558478.1 Cloning A Database Home And Changing The User/Group That Owns It
3. Oracle Software Cloning Using Oracle Universal Installer
4. http://www.pythian.com/blogs/1041/oracle-silent-mode-part-3-cloning-software-and-databases

cloning host with oracle database 9.2, 10.2

scenario:
host was cloned to VMware.
oracle version 9.2.0.6
windows server 2003

it was also repeated with
oracle database 10.2.03
windows server 2003 SP1

task:
rename database, instance and listener on new host
old db name: dcsdrp01
new db name: dcsdrt01
solution:

1. change tnsnames.ora for existing database (or u might connect to real production if u use tns)

2. add new database in tnsnames.ora

3. change listener.ora if u need listener (it has wrong host name)

4. create proper directories under d:\oracle\admin (depends on name convention and directory structure)

5. prepare new init.ora, default location for init.ora in windows is $ORACLE_HOME\database

6. prepare script to recreate control file.

use alter database backup controlfile to trace; and look in udump directory.

7. shutdown new database

8. configure listener.ora for new database

9. create password file for new database

orapwd file=D:\oracle\ora92\database\PWDDCSDRT01.ora password=password entries=10

10.  create windows service and START it! (you can not login to idle instance without it!)

oradim -new -sid DCSDRT01 -intpwd password -startmode a -pfile D:\oracle\admin\DCSDRT01\pfile\init.ora

11.  make sure original database (dcsdrp01) is down

12.  rename directories according to new init.ora file.

13.  delete current control files

14.  set ORACLE_SID=dcsdrt01

15.  sqlplus "/ as sysdba"

16.  startup nomount

17.  select * from v$instance;

18.  @new_controlfile.sql

19. alter database open resetlogs;

20. alter database rename global_name to dcsdrt01;

21. alter system switch logfile;

22. check connectivity thru listener

connect sys/password@dcsdrt01 as sysdba;

alter user system identified by password;

connect system/password@dcsdrt01

23. delete windows service of old instance

oradim -delete -sid dcsdrp01

24. disable or delete windows service of old listener

sc delete OracleOUIHome_dcsdrc2TNSListenerlistener_9i_dcsdrp01