DB Link Creation
1. Create The DB Link between SOURCE to TARGET instances in FSG
Query :
* All the FSG work that we have developed in FINSIT (Source) are needs to be transferred to SAND and UAT (next week).
Solution :
Source Database / Instance = SSEFINST
Target Database / Instance = SSESAND
Step 1 : Add the TNS entry’s between two instances. ( the tns names are
ssefinst, ssesand)
From Source Side: Add the TNS entry of TARGET
From Target side : Add the TNS entry of SOURCE
** Example :
Open Database side tnsnames.ora file :
Vi tnsnames.ora
ssefinst =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=porud440.uk.ssegroup.net)(PORT=1529))
(CONNECT_DATA=
(SERVICE_NAME=ssefinst)
(INSTANCE_NAME=ssefinst)
)
)
ssesand=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=porud440.uk.ssegroup.net)(PORT=1524))
(CONNECT_DATA=
(SERVICE_NAME=ssesand)
(INSTANCE_NAME=ssesand)
)
)
Step 2 : Check the TNS connectivity between two instances.
· From TARGET side
Ø tnsping < source tns name >
· From SOURCE side
Ø tnsping < target tns name >
Step 3: Create the DB Link on the TARGET Database side , Metalink doc id: 1034013.6
Login Front end, as sysadmin /
àselect the “General Ledger Super User” Responsibility
à Setup à System à Database Links.
àClick on “ New Database Link “
Enter the parameters.
Database Name : < Target Database >
Description :
Connect String : < Target TNS name >
Domain name : < Target server Domain Name >
Apps user name : APPS
Apps pwd : < Target DB Apps pwd>
Step 4 : Submit the “FSO Transfer” concurrent request From TARGET side
This can only be run from the ‘target’ environment.
à Login as “ General Ledger Super User “ Responsibility à View Request à Submit New
Request
====================== 2 ===================
1. In ssefinst .. the user is already existing. For creating db link for user.
CREATE DATABASE LINK BOSS_RO_SSEFINST
CONNECT TO EIMS_RO
IDENTIFIED BY EIMS_RO123456789
USING 'SSEFINST';
à Check the Db Link created
View
Purpose
DBA_DB_LINKSLists all database links in the database.
ALL_DB_LINKSLists all database links accessible to the connected user.
USER_DB_LINKSLists all database links owned by the connected user.
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
à For test the db link
* Connect the eims_ro user and try to sql query’s
SQL> Select * from tab@SSEFINST
---à drop the DB link
DROP DATABASE LINK sales.us.oracle.com;
DROP PUBLIC DATABASE LINK sales.us.oracle.com;
SQL > GRANT CREATE SESSION TO EIMS_RO
RMAN Duplicate Database Creation Document
------------------------------------------------------------
Target database Name : DEVNW
Catalog database : ORCL
Duplicate auxiliary : TEST
Step 1 : At Target database side :
== create pfile for duplicate
Sql> create pfile from spfile
$ /p01/clone/dev10gdb/10.2.0/dbs> cp initDEVNW.ora initTEST.ora
$ vi initTEST.ora
Note : change the dbname, controlfilelocation, bdump, udump,cdump, …etc locations as per the
new duplicate location
db_file_name_convert=("/p01/clone/devdata", "/t01/clone/devdata/")
log_file_name_convert=("/p01/clone/devdata", "/t01/clone/devdata/")
remote_login_passwordfile = exclusive
=== create password file for TARGET database for connect rman
/p01/clone/dev10gdb/10.2.0/dbs$ export ORACLE_SID=DEVNW
echo $ORACLE_SID
orapwd file=orapw$ORACLE_SID password=sadha force=y
Chmod –R 777 orapwDEVNW
== create password file for duplicate
/p01/clone/dev10gdb/10.2.0/dbs$ export ORACLE_SID=TEST
Echo $ORACLE_SID
orapwd file=orapw$ORACLE_SID password=sadha force=y
Note : password is the same as the target database rman connect password
Chmod –R 777 orapwTEST
==== connect duplicate database start in nomount from Target db side ( Production side).
export ORACLE_SID=TEST
$ sqlplus sys/manager as sysdba
startup force nomount pfile='/p01/clone/dev10gdb/10.2.0/dbs/initTEST.ora'
sql> select instance_name,status from v$instance;
=== Listener configuration
/p01/clone/dev10gdb/10.2.0/network/admin/DEVNW_erpprod$ vi listener.ora
DEVNW =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCDEVNW))
(ADDRESS= (PROTOCOL= TCP)(Host= erpprod.pittilam.com )(Port= 1571))
)
SID_LIST_DEVNW =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /p01/clone/dev10gdb/10.2.0)
(SID_NAME = DEVNW)
)
(SID_DESC =
(GLOBAL_DBNAME=TEST)
(ORACLE_HOME = /p01/clone/dev10gdb/10.2.0)
(SID_NAME=TEST)
)
)
Note : This SID_DESC ,GLOBAL_DBNAME .. 4 new lines adding for duplicate
===========================================================
** tnsnames .ora
/p01/clone/dev10gdb/10.2.0/network/admin/DEVNW_erpprod$ vi tnsnames.ora
DEVNW=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=erpprod.pittilam.com)(PORT=1571))
(CONNECT_DATA=
(SID=DEVNW)
)
)
TEST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=erpprod.pittilam.com)(PORT=1571))
(CONNECT_DATA=
(SID=TEST)
)
)
Note : This TEST=... 5 new lines adding for duplicate
Note : stop listener & database
Start listener
Start database
======================================================
At catalog side : SID : ORCL
== source env file
/t01/oracle/OraHome_1
vi orcl.env
export ORACLE_HOME=/t01/oracle/OraHome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
:wq!
$. ./orcl.env
=== tnsnames.ora configuration
/t01/oracle/OraHome_1/network/admin
$ vi tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpprod.pittilam.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
TO_PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)
TO_CLONE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host= erpprod.pittilam.com)(PORT = 1571))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVNW)
)
)
TO_TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host= erpprod.pittilam.com)(PORT = 1571))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
Setup RMAN Catalog side: ORCL side
create tablespace rmants_clone datafile '/t01/oracle/oradata/orcl/rmants_clone01.dbf' size 900M
CREATE USER rmanclone IDENTIFIED BY rmanclone
DEFAULT TABLESPACE RMANTS_CLONE
QUOTA UNLIMITED ON rmants_clone;
GRANT connect, resource, recovery_catalog_owner TO rmanclone
----------------------------------------------------------------------------------------------------------------------------
Create catalog
bash-3.00$ rman catalog rmanclone/rmanclone;
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 11 12:42:49 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog;
recovery catalog created
To register the Target database
$ rman catalog rmanclone/rmanclone target sys/sadha@TO_CLONE
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Nov 19 11:03:43 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DEVNW (DBID=114016787)
connected to recovery catalog database
RMAN> register database
RMAN> SHOW ALL;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/p01/clone/dev10gdb/10.2.0/dbs/snapcf_DEVNW.f'; # default
Take full backup database
RMAN> backup incremental level 0 tag = ‘Weekly_full_backup’ database plus archivelog;
Note : Before take backup command you have to configure at TARGET side Rman configuration.
At TARGET SIDE : RMAN configuration with flash backup area
Rman configuration . Target side.
Startup mount
Step 1 : configure a flash back area
Sql> alter system set db_recovery_file_dest_size=20G
Sql> alter system set db_recovery_file_dest=
C: \app\Administrator\flash_recovery_area\sadhatest’
’/u01/oracle/flash_area’ scope=both
alter system set db_recovery_file_dest=’/B01/rman_oracle_backup/flash_recovery_area’ scope=both
Step 2. Archive log put in two locations , archive log enable
a). Sql> shut immediate
Startup mount
Alter database archivelog; for disable: alter database noarchivelog
Alter database open
b). flash recovery area --
Alter system set alter system set log_archive_dest_10=’location=USE_DB_RECOVERY_FILE_DEST
c). Another mount point ----
Alter system set log_archive_dest_1=’location=/t01/oracle/archive_bkp’
Sql> show parameter log_archive_dest;
Sql> select name,status from v$archived_log
Step 3. Enable block change tracking for incremental backup
Sql> alter system set db_create_file_dest=’/u01/oracle/block_change_area’
Sql> alter database enable block change tracking using file ‘/u01/oracle/block_change_area/bcf.dbf’
alter database enable block change tracking using file '/B01/rman_oracle_backup/block_chage_location/bcf.dbf' REUSE;
Step 4. Online redo log files put in the flash area
Sql > alter database add logfile;
Sql> alter database add standby logfile
Step 5> Sql> archive log list;
Alter system switch log file; ---------- > manually log switch occur
Alter database backup control file to trace ------ > for take the control file into trace file
Catalog side:
Connect RMAN and Take full backup database of target database
RMAN> backup incremental level 0 tag = ‘Weekly_full_backup’ database plus archivelog;
For duplicate database at Catalog side
$ rman catalog rmanclone/rmanclone target sys/sadha@TO_CLONE auxiliary sys/sadha@TO_TEST
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Nov 19 11:25:46 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DEVNW (DBID=114016787)
connected to recovery catalog database
connected to auxiliary database (not started)
RMAN> run {
set until time '17-OCT-2009';
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
duplicate target database to 'test';
}
Note : if any file path error occur , you can use below command
DUPLICATE TARGET DATABASE to 'test' NOFILENAMECHECK;
For oracle apps
Move devdb , dev10gdb,devappl,devcomn,devora folders to new duplicate location .. /t01/clone/
And rename folders name
=== run adcfgclone.pl at dbTier
Run adcfgclone.pl at appsTier
Note : don’t Remove old rman backup until check the TARGET db is running fine ,
check once bounce the database
Error at Target database
SQL> startup
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2074608 bytes
Variable Size 436209680 bytes
Database Buffers 146800640 bytes
Redo Buffers 14700544 bytes
Database mounted.
ORA-00305: log 4 of thread 1 inconsistent; belongs to another database
ORA-00312: online log 4 thread 1:
'/p01/clone/rman_backup/flash_back_area/DEVNW/onlinelog/o1_mf_4_5how3sjy_.log'
ORA-00305: log 4 of thread 1 inconsistent; belongs to another database
ORA-00312: online log 4 thread 1:
'/p01/clone/rman_backup/block_change_area/DEVNW/onlinelog/o1_mf_4_5how3r28_.log'
Restore from RMAN backup
Connect rman
RMAN> startup nomount
Restore control file
Alter database mount;
Restore database;
Recover database;
Alter database open resetlogs;
==================== linux =============
# tnsnames.ora Network Configuration File: /data/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testr12.pittioffice.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
AUX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testr12.pittioffice.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AUX)
)
)
# listener.ora Network Configuration File: /data/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = testr12.pittioffice.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=aux)
(ORACLE_HOME = /data/oracle)
(SID_NAME = aux)
)
)
[oracle@testr12 dbs]$ vi initaux.ora
prod.__db_cache_size=616562688
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=289406976
prod.__streams_pool_size=0
*.audit_file_dest='/backup/aux_backup/adump'
*.background_dump_dest='/backup/aux_backup/bdump'
*.user_dump_dest='/backup/aux_backup/udump'
*.compatible='10.2.0.1.0'
*.control_files='/backup/aux_backup/data/control01.ctl','/backup/aux_backup/data/control02.ctl','/backup/aux_backup/data/control03.ctl'
*.core_dump_dest='/backup/aux_backup/cdump'
*.db_block_size=8192
*.db_create_file_dest='/backup/aux_backup/block_change_area'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='aux'
*.db_recovery_file_dest_size=171798691840
*.db_recovery_file_dest='/backup/aux_backup/flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.job_queue_processes=10
*.log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=306184192
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=920649728
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert=("/data/oracle/oradata/prod","/backup/aux_backup/data/")
log_file_name_convert=("/data/oracle/oradata/prod","/backup/aux_backup/data/")
[oracle@testr12 dbs]$ ls -ltr orapw*
-rw-r----- 1 oracle dba 1536 Dec 15 14:35 orapwprod
-rw-r----- 1 oracle dba 1536 Dec 17 15:21 orapwaux
[oracle@testr12 dbs]$
Database up gradation from 10g ( 10.2.0.4 ) to 11g ( 11.1.0.7) in Oracle Application 1i Metalink Note : 452783.1
Step 1 : Down Load the Database Software from (edelivery.oracle.com/ or. http://www.oracle.com/tehnology/software/products/database/index.html
1. 11g Database ( Base versions 11.1.0.6) --- V14215-01_1of2.zip
2. Oracle Database 11g Examples ( formerly Companion ) ---- aix.ppc64_11gR1_examples.zip
3. 11g Database (11.1.0.7) Patch set ---- p6890831_111070_AIX5L_1of2.zip
Step 2: Stop the Oracle Application services , and Shutdown the Database and Db Listener.
Take Full Cold Backup.
Step 3: Create a New 11g Database Home:
/B01/oracle $ mkdir test11gdb
Step 4 : Install the 11g Database only Software only.
$ export Oracle Base = /B01/oracle/test11gdb …. Export Oracle SID = TESTNW
cd /t01/clone/others/patches/upgrd_db11g/database_11g/database$
$. /runInstaller -invPtrLoc /B01/oracle/test11gdb /oraInst.loc
Note: For oracle Inventory Location
Create OraInst.loc file in the ( at New oracle Base /B01/oracle/test11gdb location)
Create directory .. mkdir oraInventory
$ Vi oraInst.loc
Inventory_loc = /B01/oracle/test11gdb/oraInventory
Inst_group = dba
**** next… next… next. -----------------------------------------------------------
Oracle Base Location : /B01/oracle/test11gdb
Oracle Home : /B01/oracle/test11gd/product/11.1.0/db_1 ----------- system will create.
Install ** Software Only **** …… Finish … The Oracle 11g base verion 11.1.0.6 will installed
Step 5: Install the Oracle Database 11g Examples ( formerly Companion )
In the Default New Oracle_home ( /B01/oracle/test11gdb/product/11.1.0/db_1 ).
Source Oracle_home / Oracle Sid
$ cd /t01/clone/others/patches/upgrd_db11g/11g_Example_cd_companion/examples
$ ./runInstaller
**** next … next … some of the products like /ctx/sample directory will create. ---- finsh
---------------------------------------------------------------------------------------------------------------------------
6: Install the 11g Database (11.1.0.7) Patch set
$ cd /t01/clone/others/patches/upgrd_db11g/11gPatchset/Disk1
$ ./runInstaller -invPtrLoc /B01/oracle/test11gdb /oraInst.loc
--------- next … next …. Next… the patchset will installed i.e 11.1.0.7
Step 7 : Create nls/data/9idata diretory.
$ORACLE_HOME/nl/data/old/cr9idata.pl -----created
$Oracle_home/nls/data/9idata directory.
Step 8 . Apply additional 11.1.0.7 RDBMS Patches. As per the 452783.1 Metalink ID
6530141 , 6815733 , 6972189 , 7111245 , 7253531 , 7295298 , 7486407 , 8940108
9743057.
Opatch utility for apply Database patches.
Create one environment file :
bash-3.00$ cat s.env
export ORACLE_HOME=/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1
export ORACLE_SID=TESTNW
export TNS_ADMIN=/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/network/admin/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch:$PATH
export ORACLE_BASE=/B01/oracle/test11gdb/11.1.0
bash-3.00$
-bash-3.00$./opatch version --- find the opatch version
bash-3.00$./opatch apply < DB Patch Location / Patch Direcotry>
$ opatch apply /B01/patches/DB_patches/6530141 ------ for apply the patch
opatch rollback -id -- roll back the applied patch
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
$./opatch lsinventory ------ for find out Patch applied or not.
****** any inventory corruption error
opatch lsinventory –detail
Note:
For Oracle Universal Installer version 10.2.0.2.0 and above, you will have the following scripts in Oracle home to recover from Oracle home inventory corruption:
detachHome.bat / detachHome.sh: Use this script if the Oracle home is corrupted or needs to be updated.
· attachHome.bat / attachHome.sh: Use this script if the Oracle home needs to be added to the inventory.
If Problem Not solved
** Move the /etc/Orainventory Directory …for backup.
Create New /etc/oraInventory directory …
bash-3.00$ pwd at 11g New home. We can find *.sh files.
$find . –name detachHome.sh -print
/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/oui/bin
-rwxrwxr-x 1 oradev dba 213 Jan 29 12:08 detachHome.sh
-rwxrwxr-x 1 oradev dba 306 Jan 29 12:08 attachHome.sh
And run $sh attachHome.sh -------- for new Oracle_home add in New inventory
===================================================================================
Step 9 : For Database Upgradation – Run Pre –Upgrade tool Process. Run utlu111i.sql from Old _ Oracle Home (10g)
a). Copy the utlu111i.sql (/B01/oracle/test11gdb/product/11.1.0/db_1/rdbms/admin) to
/tmp Directory.
b). Stop all the services/ Tns listener / any other in the Instance.
c). Source the enviro nment at Old_Oracle _Home ( /B01/oracle/test10gdb/10.2.0).
$ sqlplus ‘/as sysdba’
Sql> startup ---------- startup the OLD DB from Old_oracle HOME
Sql> spool upgrade_info.log
Sql> @/tmp/utilu11i.sql
Sql> spool off
Sql> shut immediate;
*** open upgrade_info.log file and take correct action plan.
Step 10. Create new Database Listener
$ source the 11g New Database Home
$./netca
Note : ** Give As per the OLD Listener … Same Lisener name : TESTNW … Same Port : 1571
Step 11: Check the Time zone version .. compatibilities
Step 12: Start the Upgradation.
Source the New Database Home . and SID ,Path …
New Listener should be up.
$ cd ORACLE_HOME/bin
$./dbua
· chose … Database Don’t Move.
· Next… next… password : DBSNMP..SYSMAN -> pitti ……………….. FINISH.
------------- OR ----------------
For manual upgrade without use ./dbua
Shut down the database:
SQL> SHUTDOWN
Enter the following SQL*Plus commands:
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
************* Upgraded from source 10.2.0.4 to Target 11.1.0.7 **************
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Modify initialization Parameters Metalink Note : 216205.1 for Oracle 11i ( 11.1.0.7 )
See the Page No: 10
------------------------------------------------------------------------------------------------------------------------------------
After the Database Upgrade
i). Fix Korean lexers
$sqlplus ‘/as sysdba’
@ORACLE_HOME/ctx/sample/script drkorean.sql
ii). Run adgrants.sql
copy $APPL_TOP/admin/agrants.sql to New_Oracle_home/rdbms/admin
$sqlplus ‘/as sysdba’ @ adgrants.sql APPLSYS
III). Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch115/adctxprv.sql to New_Oracle_home/rdbms/admin
$ sqlplus apps/apps @adctxprv.sql MANAGER CTXSYS
================================================================================
IV). Implement and run AUTOCONFIG.
a).
a. Log in to server with applmgr userb. source /oracle/tst10appl/[context_name].envc. perl $AD_TOP/bin/admkappsutil.pld. cp $APPL_TOP/admin/out/appsutil.zip /B01/oracle/test11gdb/product/11.1.0/db_1/e. Login to server with oracle userf. cd /B01/oracle/test11gdb/product/11.1.0/db_1 -- NEW 11g HOMEg. unzip –o appsutil.zip
b). source the environment
bash-3.00$ cat s.env
export ORACLE_HOME=/B01/oracle/test11gdb/product/11.1.0/db_1
export ORACLE_SID=TESTNW
export TNS_ADMIN=/B01/oracle/test11gdb/product/11.1.0/db_1/network/admin/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch:$PATH
export ORACLE_BASE=/B01/oracle/test11gdb/11.1.0
$ s.env ------------- source the environment.
c). ** create .xml file create.
$cd /B01/oracle/test11gdb/product/11.1.0/db_1/bin
$perl adbldxml.pl tier=db appsuser=apps appspass=apps
Host name : erpprod
SID : TESTNW
Xdisplay : erpprod:0.0
d). *** Run Auto Config from db_tier.
$cd /B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/bin
$ ./adconfig.sh
Enter the context file path =/B01/oracle/test11gdb/product/11.1.0/db_1/appsutil/TESTNW_erpprod.xml
*** After complete the autoconfig the Environment file will automatically created in 11g oracle_home
Cd /B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1
ash-3.00$ ls *.env
TESTNW_erpprod.env
e). * Note : autoconfing core dump error ,
Solution : take ,xml backup , and modified with perl verision 5.8.3 , and re-run autoconfig.
$ cp TESTNW_erpprod.xml TESTNW_erpprod.xml_bak07_feb_2011
$ vi TESTNW_erpprod.xml
/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/bin/perl
/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/5.8.3/aix-thread-multi:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/5.8.3:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/site_perl/5.8.3/aix-thread-multi:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/site_perl/5.8.3:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/site_perl:.:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/appsutil/perl:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/appsutil/perl
· - re – run autoconfig at db_tier.
==============================================================================
f). Note : any temp datafile error while run autoconfing.
HELP : RE- CREATE / Add the temp data file for NEW TEMPORARY TABLESPACE
--------------------------------------------------------------------------------------------------
select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'
CREATE TEMPORARY TABLESPACE temp1
TEMPFILE '/p01/oracle/proddata/temp01.dbf' SIZE 5000M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; --- drop old one.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1
ALTER TABLESPACE temp1 ADD TEMPFILE '/p01/oracle/proddata/temp02.dbf' SIZE 5000M REUSE
AUTOEXTEND ON NEXT 1m MAXSIZE UNLIMITED
v). Gather statistics for Sys schema.
Copy $APPL_TOP/admin/adstats.sql to New_oracle_home/rdbms/admin
Sql> shutdown normal
Sql> startup restrict;
Sql> @adstats.sql
Sql> shutdown normal ;
Sql> startup.
VI. Re-Create custom database links
Sql> select db_link from dba_db_links;
Sql> drop database link ( custom database link )
Sql> create database link < custom database link )
Connect to user identified by password using ‘‘TESTNW= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=erpprod.pittilam.com)(PORT=1571)) (CONNECT_DATA= (SID=TESTNW) WHERE user / pwd /port number / sid . net lisener ) )
VII . Re- create grants and synonyms.
At Application side : source the environment
$ adadmin
Select “Application file system “ menu à Recreate grants and synonyms for APPS Schema.
VII. Restart the Application Server Processes.
Modify initialization Parameters Metalink Note : 216205.1 for Oracle 11i ( 11.1.0.7 )
Through dbua create new initTESTNW.ora file.
***** bash-3.00$ cat initTESTNW.ora
TESTNW.__db_cache_size=230686720
TESTNW.__java_pool_size=67108864
TESTNW.__large_pool_size=117440512
TESTNW.__oracle_base='/B01/oracle/test11gdb/11.1.0'#ORACLE_BASE set from environment
TESTNW.__pga_aggregate_target=1073741824
TESTNW.__sga_target=1073741824
TESTNW.__shared_io_pool_size=0
TESTNW.__shared_pool_size=629145600
TESTNW.__streams_pool_size=4194304
*._b_tree_bitmap_plans=FALSE# adding as per metalink docu
*._optimizer_autostats_job=false# adding as per metalink docu
*.aq_tm_processes=1
*.audit_file_dest='/B01/oracle/test11gdb/11.1.0/TESTNW/admin/adump'
*.compatible='11.1.0'
*.control_files='/B01/oracle/testdata/cntrl01.dbf','/B01/oracle/testdb/cntrl02.dbf','/B01/oracle/testdata/cntrl03.dbf'
*.cursor_sharing='EXACT'
*.db_block_checking='FALSE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_files=512
*.db_name='TESTNW'
*.diagnostic_dest='/B01/oracle/test11gdb/11.1.0'
*.dml_locks=10000
*.java_pool_size=67108864
*.job_queue_processes=2
*.large_pool_size=117440512
*.local_listener='LISTENER_TESTNW'
*.log_buffer=14238720
*.log_checkpoint_interval=100000
*.log_checkpoint_timeout=1200
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20480'
*.nls_comp='binary'
*.nls_date_format='DD-MON-RR'
*.nls_language='american'
*.nls_length_semantics='BYTE'
*.nls_numeric_characters='.,'
*.nls_sort='binary'
*.nls_territory='america'
*.olap_page_pool_size=4194304
*.open_cursors=600
*.optimizer_secure_view_merging=FALSE
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=1073741824
*.plsql_code_type='INTERPRETED'
*.plsql_optimize_level=2
*.processes=400
*.query_rewrite_enabled='true'
*.sec_case_sensitive_logon=FALSE# adding as per metalink docu
*.session_cached_cursors=500
*.sessions=445
*.sga_target=1073741824
*.shared_pool_reserved_size=157286400
*.shared_pool_size=314572800
*.undo_management='AUTO'
*.undo_tablespace='APPS_UNDOTS1'
*.utl_file_dir='/usr/tmp','/usr/tmp','/usr/tmp','/p01/oracle/proddb/9.2.0/appsutil/outbound/PROD_prod','/B01/oracle/test10gdb/10.2.0/appsutil/outbound/TESTNW_erpprod','/usr/tmp'
*.workarea_size_policy='AUTO'
bash-3.00$
Query :