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

How to point an agent to different OMS

Environment: AIX5L, Oracle agent 10.2.0.3
2008-01-04

1. Connect to target host as a user which owns Oracle agent installation (home)
2. go to $ORACLE_HOME/bin
3. stop the agent:
emctl stop agent
4. edit emd.properties
vi $ORACLE_HOME/sysman/config/emd.properties
5. replace old OMS with new OMS.
REPOSITORY_URL=http://host:4889/em/upload
or for secure
REPOSITORY_URL=https://host:1159/em/upload
6. check agentTZRegion=Australia/Sydney (or Australia/NSW)
7. go to sysman/emd
cd $ORACLE_HOME/sysman/emd
8. remove the following files:
rm -rf protocol.ini agntstmp.txt lastupld.xml recv/* collection/* upload/* state/*
9. go to bin
cd $ORACLE_HOME/bin
10. clean agent state:
emctl clearstate agent
11. unsecure agent:
emctl unsecure agent
12. (optional) secure agent again:
emctl secure agent passwd where passwd is a registration password
13. start agent:
emctl start agent
14. check the status:
emctl status agent
15. upload metric data (in 30 seconds or more):
emctl upload agent
16. check availability of targets in new EM GC
17. configure credentials of new connected targets within EM for all administrators whom belongs the targets.

In case of some problems during registration of targets in EM or errors during upload process (15) do the following:
1. Delete all targets from EM
2. Stop the agent on a host
3. Delete agent from EM
4. Change targets.xml file (save a copy) to remove everything but leave entries for oracle_emd (entry for agent) and for host (for server since there is no chance to add host in EM – it should be discovered)
5. Remove files from sysman/emd based on 8
6. Do steps 9-17


Metalink:
Subject: How to - Point an Agent to a different Grid Control OMS and Repository? Doc ID: Note:413228.1 Type: HOWTO Last Revision Date: 19-NOV-2007 Status: PUBLISHED

Oracle Generic Heterogeneous Services and Transparent Gateways

RHEL 4.5 Oracle 10.2

1. install package unixODBC
2. install package freeTDS, if u don’t have it for ur linux go to www.freetds.org and get the source
3. configure /etc/freetds.conf (path can be different)
4. try to connect with tsql
5. configure files /etc/odbc.ini, /etc/odbcinst.ini, see www.unixodbc.org for details
6. try to connect with isql (user and password are obligatory parameters)
7. create and customize an initialization file for your Generic Connectivity agent $ORACLE_HOME/hs/admin/inithsodbc.ora

# HS init parameters

#

HS_FDS_CONNECT_INFO = mssql

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

8. add record in tnsnames.ora

HS1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = tcp)(HOST = sydora2)(PORT = 1521))

)

(CONNECT_DATA =

(SID = hsodbc)

)

(HS=OK)

)

9. add record in listener.ora

SID_LIST_LISTENER_SYDORA2 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = hsodbc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1)

(PROGRAM = hsodbc)

(ENVS=LD_LIBRARY_PATH=/opt/oracle/app/oracle/product/10.2.0/db_1/lib:/usr/local/lib:/usr/lib)

)

)

10. restart listener
11. create and test db link
12. repeat steps on another node if it is a cluster and test it on each node

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

Oracle LogMiner (1)

Environment:

Oracle 9i (9.2.0.6)

select * from v$logfile;
-- using online redo log
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/redo/01/dckidq01/redo11.log', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

select * from V$LOGMNR_CONTENTS
where seg_name ='S_PERIOD';
-- end and cleanup
EXECUTE DBMS_LOGMNR.END_LOGMNR;