Oracle Apps DBA

Move Grid Control Agent Repository To Another Server

2009-12-22T23:29:00.000-08:00

Change Grid Control Agent Repository To Another Server

1. Stop the agent.

AGENT_HOME/bin: emctl stop agent

[oracle@test ~]$ $AGENT_HOME/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

2. Edit the AGENT_HOME/sysman/config/emd.properties file. Change the following lines to port 4889. Make sure to back up this file before editing:
REPOSITORY_URL=http://:4889/em/upload/
emdWalletSrcUrl=http://:4889/em/wallets/emd

3. Delete these files from the agent home

rm -r AGENT_HOME/sysman/log/*.*
rm -r AGENT_HOME/sysman/emd/state/*
rm -r AGENT_HOME/sysman/emd/upload/*
rm AGENT_HOME/sysman/emd/lastupld.xml
rm AGENT_HOME/sysman/emd/agntstmp.txt
rm AGENT_HOME/sysman/emd/protocol.ini

4. Start the agent
AGENT_HOME/bin: emctl start agent

[oracle@test ~]$ $AGENT_HOME/bin/emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Starting agent ....... started.

5. Issue an agent clearstate from the agent home

AGENT_HOME/bin: emctl clearstate agent

[oracle@test ~]$ $AGENT_HOME/bin/emctl clearstate agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully

6. Now secure the agent

[oracle@test ~]$ emctl secure agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Enter Agent Registration password :
Agent is already stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Done.
Check if HTTPS Upload URL is accessible from the agent... Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode... Done.
EMD_URL set in /home/oracle/OracleHomes1/agent10g/sysman/config/emd.properties
Securing agent... Successful.

7. Force an upload to the OMS

AGENT_HOME/bin: emctl upload agent

[oracle@test ~]$ emctl upload agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

[oracle@test ~]$ emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /home/oracle/OracleHomes1/agent10g
Agent binaries : /home/oracle/OracleHomes1/agent10g
Agent Process ID : 22942
Parent Process ID : 22568
Agent URL : https://test.server.com:3872/emd/main/
Repository URL : https://sys104.server.in:1159/em/upload
Started at : 2009-12-23 12:20:59
Started by user : oracle
Last Reload : 2009-12-23 12:20:59
Last successful upload : 2009-12-23 12:23:01
Total Megabytes of XML files uploaded so far : 31.26
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 8.95%
Last successful heartbeat to OMS : 2009-12-23 12:24:05
---------------------------------------------------------------
Agent is Running and Ready
[oracle@test ~]$

Install Oracle JDeveloper 11g 11.1.1.2

2009-12-09T03:34:00.000-08:00

Install Oracle JDeveloper 11g 11.1.1.2

1. Download Oracle JDeveloper 11g

2. Unzip

3. Run runinstaller

4. Click Next

5. Click Next

Note : Directory

6. Click Next

Note : Choose Type

7 Click Next

8.Click Next

9. Click Next

10. Click Next

11. Click Next

12 Click Next

13. Click Done

Agent Failed to start HTTP listener

2009-12-09T02:55:00.000-08:00

Enterprise Manager Grid Control Agent Failed to start HTTP listener

[oracle@egrouptest ~]$ emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Starting agent ....... failed.
Failed to start HTTP listener.

Solutions

1. Go to $AGENT_HOME/sysman/config

2. Modify the emd.properties file as follows:
Change the parameter

AgentListenOnAllNICs=TRUE
to
AgentListenOnAllNICs=FALSE

Note: This will cause the Agent to only listen on the specific NIC as stated in the EMD_URL parameter in the emd.properties configuration file.

2. Startup the Agent
/bin/emctl start agent

[oracle@egrouptest agent10g]$ emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Starting agent ........ started.

Enable Trace in Oracle E-Business Suite R12

2009-12-07T04:47:00.000-08:00

Debugging tool for HTTP, OC4J and OPMN in E-Business Suite R12

How To Enable and Collect Debug for HTTP, OC4J and OPMN

Shutdown the http server, oc4j and opmn services

adapcctl.sh stop
adoacorectl.sh stop
adformsctl.sh stop
adoafmctl.sh stop
adopmnctl.sh stop

Enable http ODL logging
-----------------------
$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf

OraLogMode oracle
OraLogSeverity TRACE:32
OraLogDir $LOG_HOME/ora/10.1.3/Apache/oracle

Please use the full path to $LOG_HOME e.g. OraLogDir /u01/inst/apps/JCB_atg/logs/ora/10.1.3/Apache/oracle

mkdir $LOG_HOME/ora/10.1.3/Apache/oracle

Increase OC4J logging for oacore
-------------------------------
edit j2ee-logging.xml adjust the following in file:

$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/j2ee-logging.xml

Increase OC4J logging for forms
------------------------------
edit j2ee-logging.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/j2ee-logging.xml

edit orion-web.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml

debug_mode

true

Increase OPMN Logging
-------------------------------
edit opmn.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

Note : Check log Location /u01/inst/apps/JB_atg/logs/ora/10.1.3/opmn/opmn.dbg

Startup the http server, oc4j and opmn (reverse the startup sequence above)

Creating physical standby using RMAN duplicate

2009-11-17T05:09:00.000-08:00

Creating physical standby using RMAN duplicate without shutting down the primary

Database Name :- orcl
Primary db_unique_name :- orcl
standby db_unique_name :- stdby

1. Check if archiving enabled on primary.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

(OR)

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/oracle/product/10.2.0/oradata/orcl/archive/
Oldest online log sequence 1
Current log sequence 2

If NoArchivelog Mode,Enable Archive log Mode.

Enable Archive Log Mode.

SQL> shut immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

Now Check Archive Log,

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

(OR)

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/product/10.2.0/oradata/orcl/archive/
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

2. Create password file if not present on primary.

3. Enable Force Logging on primary.

SQL> select FORCE_LOGGING from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select FORCE_LOGGING from v$database;

FOR
---
YES

4. Configure a Standby Redo Log on primary.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log4a.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log5a.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log6a.log' size 50m;

Database altered.

5. Modify the primary initialization parameter for dataguard on primary.

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdby)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/oracle/product/10.2.0/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

System altered.

Note : Create Archive folder

SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';

System altered.

SQL>alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set FAL_SERVER=stdby;

System altered.

SQL> alter system set FAL_CLIENT=orcl;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/stdby/','/home/oracle/oracle/product/10.2.0/oradata/orcl/' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/stdby/','/home/oracle/oracle/product/10.2.0/oradata/orcl/' scope=spfile;

System altered.

6. Run the backup job at the primary by connecting to target and catalog DB(if any)

Connect to target(i.e. prim via connect string) and catalog database(if any)

$rman target / catalog RMAN/RMAN@catdb

if no catalog database,

$rman target /

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
backup archivelog all format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
backup current controlfile for standby format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
}

RMAN> run
2> {
allocate channel c1 type disk;
3> 4> allocate channel c2 type disk;
5> backup database format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
6> backup archivelog all format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
7> backup current controlfile for standby format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
8> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=133 devtype=DISK

allocated channel: c2
channel c2: sid=134 devtype=DISK

.....
....
.....

channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including standby control file in backupset
channel c1: starting piece 1 at 16-NOV-09
channel c1: finished piece 1 at 16-NOV-09
piece handle=/home/oracle/oracle/product/10.2.0/oradata/backup/06kugriq_1_1 tag=TAG20091116T170738 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-NOV-09
released channel: c1
released channel: c2

7. Create parameter file on primary copy it to standby and make the necessary changes,

SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initstdby.ora' from spfile;

File created.

Make the necessary changes on the initstdby.ora file on standby.

db_name=orcl
db_unique_name=stdby
log_archive_config='DG_CONFIG=(orcl,stdby)'
log_archive_dest_1='LOCATION=/home/oracle/oracle/product/10.2.0/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
log_archive_dest_state_1=ENABLE
db_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/orcl/','/home/oracle/oracle/product/10.2.0/oradata/stdby/'
log_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/orcl/','/home/oracle/oracle/product/10.2.0/oradata/stdby/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
standby_archive_dest='/home/oracle/oracle/product/10.2.0/oradata/stdby/arch/'
standby_file_management=AUTO
fal_client=stdby
fal_server=orcl

8. Create Password file on standby.

create the same password as that of primary. Alternatively we can copy the password file from primary on standby.

For example from primary copry the password file,

$cp orapworcl orapwstdby

9. Create necessary directories and start the auxiliary instance on standby

$mkdir udump bdump cdump adump

$export ORACLE_SID=stdby

$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 16 17:30:41 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 176164676 bytes
Database Buffers 419430400 bytes
Redo Buffers 7163904 bytes
SQL>exit;

10. Create net services on both primary and standby,

In listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_2)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = stdby.oracle.com)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_2)
(SID_NAME = stdby)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
)

In tnsnames.ora

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby.oracle.com)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)

11. Use RMAN duplicate for standby on standby,

on primary,

sql> alter system archive log current;

on standby,

Connect to target(i.e. prim via connect string) and catalog database(if any) and this auxiliary database by,

$rman target /@orcl catalog RMAN/RMAN@catdb auxiliary /

if no catalog database,

$rman target sys/oracle@orcl auxiliary /

[oracle@sys109 stdby]$ rman target sys/oracle@orcl auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 16 17:33:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1230876740)
connected to auxiliary database: STDBY (not mounted)

RMAN> duplicate target database for standby dorecover;

Starting Duplicate Db at 16-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn 510230;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

....
....
....

media recovery complete, elapsed time: 00:00:05
Finished recover at 16-NOV-09
Finished Duplicate Db at 16-NOV-09

RMAN>exit

12 .Start the MRP(managed recovery process) on standby,

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ---------------- --------------
ORCL stdby PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect;

Database altered.

13. Enable the log_archive_dest_2 on primary, which is to send the logs to standby server.

SQL>alter system set log_archive_dest_state_2=enable;

System altered.

14 . Check the standby whether it is in SYNC with primary,

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

select sequence#,applied from v$archived_log order by sequence#;

Integration Oracle Apex With Weblogic Server

2009-11-05T06:29:00.000-08:00

Integration Oracle Apex With Weblogic Server

1. Download Oracle WebLogic

2. Goto the Admin console.

3. Choose "Deployments"

4. Click Install

5. load apex.war (available for download on the APEX Listener Download Page)

6. Choose all the defaults

7. visit : http://host:port/apex/Config and setup the db
(host:port Your weblogic server host:port example: http://localhost:7001/)

8. Recursively copy the apex/images directory to the webserver's /i

Note : I have copy apex/images folder to (/home/oracle/Oracle/Middleware/wlserver_10.3/samples/server/examples/build/mainWebApp/i)

ORA-29540: class oracle/CDC/PublishApi does not exist

2009-10-30T03:19:00.000-07:00

ORA-29540: class oracle/CDC/PublishApi does not exist

SQL> BEGIN
2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3 owner => 'cdcpub',
4 change_table_name => 'products_ct',
5 change_set_name => 'PRODUCTS_CS',
6 source_schema => 'MFCAMS',
7 source_table => 'PRODUCTS',
8 column_type_list => 'PROD_ID NUMBER(6), PROD_NAME VARCHAR2(50),PROD_LIST_PRICE NUMBER(8,2),JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)',
9 capture_values => 'both',
10 rs_id => 'y',
11 row_id => 'y',
12 user_id => 'y',
13 timestamp => 'y',
14 object_id => 'n',
15 source_colmap => 'n',
16 target_colmap => 'y',
17 options_string => 'TABLESPACE USER_DATA');
18 END;
19 /
BEGIN
*
ERROR at line 1:
ORA-29540: class oracle/CDC/PublishApi does not exist
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Solutions

1.Run the sql rmcdc.sql

SQL>$ORACLE_HOMErdbms/admin/rmcdc.sql

2. Run the sql initcdc.sql

SQL>$ORACLE_HOME/rdbms/admin/initcdc.sql

ORA-29538: Java not installed

2009-10-30T02:48:00.000-07:00

ORA-29538: Java not installed

ERROR at line 1:
ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Solutions

$ORACLE_HOME/javavm/install

To load this package into the database, go to the directory location and login to SQL*Plus as the "SYS" user. Then, type the following:

SQL> spool initjvm.log
SQL> @initjvm.sql

Install and configure Java Development Kit 6 (jdk 6) on Oracle Enterprise Linux.

2009-10-29T04:35:00.000-07:00

Download Java Development Kit 6

You can download JDK 6 versions from e.g. Sun's web site.

Select "Accept License Agreement".
Then select "Linux self-extracting file" for Linux Platform.
Save the file "jdk-6--linux-i586.bin" to e.g . /usr/local or

Install JDK or JRE

# cd /usr/local
# chmod +x jdk-6-linux-i586.bin
# ./jdk-6-linux-i586.bin
... ...
Do you agree to the above license terms? [yes or no]
yes

Press the spacebar to read Sun Microsystems, Inc. Binary Code License Agreement, then input "yes" and press Enter.
... ...
... ...
Done.

Set the environment variables

You can add the following lines to /etc/profile or .bash_profile file in your home directory. And then run "source /etc/profile" or ". .bash_profile" so that the environment variables can take effect.

export JAVA_HOME=/usr/local/jdk1.6.0
export PATH=$JAVA_HOME/bin:$PATH

Check the JDK version

# java -version
java version "1.6.0"
Java(TM) SE Runtime Environment (build 1.6.0-b105)
Java HotSpot(TM) Client VM (build 1.6.0-b105, mixed mode, sharing)

Transprotable Tablespace using export/import

2009-10-21T23:59:00.000-07:00

Transprotable Tablespace using export/import

Source

1. Create tablespace.

SQL> CREATE TABLESPACE TT_DATA
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA' SIZE 50M;

SQL> CREATE TABLESPACE TT_INDEX
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA' SIZE 50M;

2. Create User.

SQL> create user ttbs identified by ttbs default tablespace tt_data temporary tablespace temp;

SQL> grant connect, resource to ttbs;

SQL> alter user ttbs quota unlimited on tt_index;

SQL> select name from v$tablespace where name like 'TT_%';

NAME
------------------------------
TT_DATA
TT_INDEX

SQL> select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA NO ONLINE
TT_INDEX NO ONLINE

3. Create table and index

SQL> create table ttbs.testtable (sno number, name varchar2(10)) tablespace tt_data;

SQL> insert into ttbs.testtable values (1001, 'Doyensys');

SQL> commit;

SQL> create index ttbs.i_testtable on ttbs.testtabale(sno) tablespace tt_index;

SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1

SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1

4.Source tablespace change read-only mode.

SQL>alter tablespace TT_DATA read only;

SQL>alter tablespace TT_INDEX only;

SQL> execute dbms_tts.transport_set_check('TT_DATA,TT_INDEX', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

select file_name from dba_data_files where tablespace_name in ('TT_DATA','TT_INDEX');

SQL> select file_name from dba_data_files
where tablespace_name in ('TT_DATA','TT_INDEX');

FILE_NAME
--------------------------------------------------------------------------------

F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA
F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA

5. Export the metadata using transportable tablespace

exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=TT_DATA,TT_INDEX statistics=none

6. Copy TT_DATA01.ORA,TT_INDEX01.ORA datafiles and exp_tts.dmp to target server.

7 . Once copy is complete,Put source tablespace read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;

Target

7. create user.

create user ttbs identified by ttbs default tablespace user_data temporary tablespace temp;

grant connect, resource to ttbs;

8. import metadata

imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=ttbs touser=ttbs transport_tablespace=y datafiles=F:\oracle\oradata\orcl\TT_DATA01.ORA,F:\oracle\oradata\orcl\TT_INDEX01.ORA

SQL>select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES READ ONLY
TT_INDEX YES READ ONLY

9. target tablespace put read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;

SQL> select tablespace_name,plugged_in,status from dba_tablespaces
2 where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES ONLINE
TT_INDEX YES ONLINE

SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1

SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1

Create Recovery catalog using RMAN

2009-10-21T23:35:00.000-07:00

Create Recovery catalog using RMAN

1. Create tablespace for Catalog

CREATE TABLESPACE rman_data
DATAFILE '/u01/oracle/product/oradata/orcl/rman_data01.dbf' SIZE 50M;

2. Create user for RMAN

CREATE USER rman
IDENTIFIED BY rman
DEFAULT TABLESPACE rman_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON rman_data;

3. Grant user.

GRANT create session TO rman;
GRANT recovery_catalog_owner TO rman;

4. create catalog

rman catalog rman/rman

RMAN> create catalog tablespace rman_data;

5. Register target database.

rman target / catalog rman/rman@rman_cat_db

RMAN> register database;

ORA-29341: The transportable set is not self-contained

2009-10-21T23:14:00.000-07:00

Transportable tablespace using export of index tablespace given error

H:\oracle\oradata\doyen\back_test_tt>exp "'/ as sysdba'" file=TT_INDEX.dmp
log=TT_INDEX.log transport_tablespace=y tablespaces=TT_INDEX statistics=none

Export: Release 10.2.0.3.0 - Production on Wed Oct 21 12:24:06 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29341 encountered
ORA-29341: The transportable set is not self-contained
ORA-06512: at "SYS.DBMS_PLUGTS", line 1387
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

Solutions:

SQL> execute dbms_tts.transport_set_check('TT_INDEX', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

ORA-27054: NFS file system where the file is created or resides

2009-10-21T09:37:00.000-07:00

Starting backup at 21-OCT-09
no parent backup or copy of datafile 18 found
channel c1: starting datafile copy
input datafile fno=00018 name=/home/oracle/product/10.2.0/oradata/orcl/kkk.dbf
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 10/21/2009 21:26:48
ORA-19504: failed to create file "/data/files/kkk.dbf"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3

Solutions

Linux

Add the following entry in /etc/fstab

hard,rw,noac,rsize=32768,wsize=32768,suid,proto=tcp,vers=3

Oracle Application Express enable SSL

2009-10-09T04:57:00.000-07:00

Oracle Apex configure SSL

Oracle Application Express enable SSL (http to https)

Goto Application Server
$ORACLE_HOME/opmn/conf/opmn.xml

find for ssl-disabled
change to ssl-enabled
Save opmn.xml

Add the entry in httpd.conf file.

RewriteEngine On
RewriteCond %{SERVER_PORT} !^443$
RewriteRule ^/pls/apex/(.*)$ https://example.com/pls/apex/$1 [L,R]

Apache2.0 proxy for Oracle Apex

2009-10-09T04:46:00.000-07:00

Apache2.0 proxy for Oracle Apex

User => HTTP2.x => OHS => APEX

Add the entry in httpd.conf file.

ProxyPass /apex http://192.168.5.25:8080/apex
ProxyPassReverse /apex http://192.168.5.25:8080/apex
ProxyPass /i http://192.168.5.25/i
ProxyPassReverse /i http://192.168.5.25/i

Oracle Streams SET_KEY_COLUMNS

2009-10-09T04:05:00.000-07:00

Oracle Streams SET_KEY_COLUMNS

When an apply process applies changes to a table, substitute key columns can either replace the primary key columns for a table that has a primary key or act as the primary key columns for a table that does not have a primary key. Set the substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. This setting applies to all of the apply processes that apply local changes to the database.

For Example,

Source EMP table have only one primary column.but i need target table two primay column(emp_id,worker_id).

BEGIN
DBMS_APPLY_ADM.SET_KEY_COLUMNS(
object_name => 'SCOTT.EMP',
column_list => 'EMP_ID,WORKER_ID');
END;
/

Note: You must specify an unconditional supplemental log group at the source database for all of the columns specified as substitute key columns in the column_list or column_table parameter at the destination database

Oracle Streams Delete Column

2009-10-09T03:46:00.000-07:00

Oracle Streams Delete Column.

Source Schame Name : SCOTT

Source Table Name : EMP

Target Schame Name : SCOTT

Target Table Name : EMP

Delete Column Names : SAL,JOB

Two Steps.

1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.

1.Rule-based transformation.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='EMP';

begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'SAL',
operation => 'ADD');
END;
/

begin
DBMS_STREAMS_ADM.DELETE_COLUMN(
rule_name => 'STRMADMIN.EMP34',
table_name => 'SCOTT.EMP',
column_name => 'JOB',
operation => 'ADD');
END;
/

2.Rename columns in the LCR using a DML Apply Handler.

rem Create DML handler procedure

CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN SYS.ANYDATA)
IS

lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
row_empno SYS.ANYDATA;
v_empno NUMBER;

BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();

-- Filter out required row and and columns

IF object_owner = 'SCOTT' and
object_name = 'EMP' and
dmlcommand IN ('INSERT','UPDATE','DELETE') THEN

-- Remove Columns
lcr.delete_column('SAL','*');
lcr.delete_column('JOB','*');

LCR.EXECUTE(TRUE);
END IF;
END;
/

rem Set the DML Handler for the INSERT operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.EMP_DML_HANDLER',
apply_database_link=> NULL);
END;
/

Oracle Streams Rename Column

2009-10-09T03:01:00.000-07:00

Oracle Streams Rename Column.

Source Schame Name : EMP

Source Table Name : MANAGER

Column Names : MANAGER_ID,MANAGER_NAME,EMPLOYEE

Target Schame Name : EMP

Target Table Name : MANAGER

Column Names : MANAGER_ID,MANAGER_NAME,WORKER

Two Steps.

1.Rule-based transformation.
2.Rename columns in the LCR using a DML Apply Handler.

1.Rule-based transformation.

Create Capture rule.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';

BEGIN
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'MANAGER33',
table_name => 'EMP.MANAGER',
from_column_name => 'EMPLOYEE',
to_column_name => 'WORKER',
value_type => 'NEW',
step_number => 0,
operation => 'ADD');
END;
/

2.Rename columns in the LCR using a DML Apply Handler.

CREATE OR REPLACE PROCEDURE rename_column_EMPLOYEE (in_any in sys.anydata)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(30);
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();

-- Filter out required owner and table name
IF lcr.get_object_owner() = 'EMP' AND lcr.get_object_name() = 'MANAGER' THEN
IF (lcr.get_value('NEW','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','NEW');
END IF;
IF (lcr.get_value('OLD','EMPLOYEE') is not null) THEN
lcr.rename_column('EMPLOYEE','WORKER','OLD');
END IF;
LCR.EXECUTE(TRUE);
END IF;
END;
/

rem Set the DML Handler for the INSERT operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'SCOTT.EMP',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.RENAME_COLUMN_EMPLOYEE',
apply_database_link=> NULL);
END;
/

Oracle Streams Rename Schema

2009-10-09T02:48:00.000-07:00

Oracle Streams Rename Schema.

Source Schame Name : EMP

Target Schame Name : WORKER

Create Capture rule.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'EMP.MANAGER',
streams_type => 'capture',
streams_name => 'stream1_capture',
queue_name =>'streams_queue_cap',
include_dml => TRUE,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true );
END;
/

SELECT RULE_NAME,STREAMS_TYPE,SCHEMA_NAME FROM dba_Streams_Rules WHERE OBJECT_NAME='MANAGER';

BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA(
rule_name => 'STRMADMIN.MANAGER35',
from_schema_name => 'EMP',
to_schema_name => 'WORKER',
step_number => 0,
operation => 'ADD');
END;
/

Oracle Streams Add New Column in Target Table

2009-10-08T10:47:00.000-07:00

Oracle Streams Add New Column in target Table

CREATE OR REPLACE PROCEDURE test_im_amc(evt IN SYS.ANYDATA)
IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
-- Access the LCR. RC holds the return code.
rc := evt.GETOBJECT(lcr);
--add a new column called commit_scn
lcr.ADD_COLUMN('new','COMMIT_SCN',SYS.AnyData.ConvertNumber(lcr.GET_SCN()));
-- Apply row LCR
lcr.EXECUTE(true);
END;

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the UPDATE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

rem Set the DML Handler for the DELETE operations

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER
(object_name => 'ms.test_im',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => FALSE,
user_procedure => 'STRMADMIN.test_im_amc',
apply_database_link=> NULL);
END;
/

Manual Installation of Spatial 10g

2009-06-29T02:42:00.000-07:00

Steps for Manual Installation of Spatial 10g

Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications and geographic information system (GIS) applications. When spatial data is stored in an Oracle database, it can be easily manipulated, retrieved, and related to all other data stored in the database.

To be able to do a successful Spatial 10g installation you need to have the following products already installed:

JServer JAVA Virtual Machine
Oracle interMedia
Oracle XML Database

SQL> select comp_id,version,status from dba_registry where comp_id in ('JAVAVM','ORDIM','XDB');

If the MDSYS user does NOT exist:

create the user MDSYS by running following command:

SQL> create user MDSYS identified by xxxxx default tablespace SYSAUX account lock;

grant the required privileges to MDSYS by running:

SQL> @?/md/admin/mdprivs.sql

If the MDSYS user already exists then you are advised to verify if the installation has been done correctly and de-install Spatial first in case of re-installation.

See the verification checks at the bottom of this note and a link to de-installation note.

Install Spatial by executing the steps shown below. Note you need to run this as a SYSDBA user!

SQL> connect / as sysdba
SQL> spool spatial_installation.lst
SQL> @?/md/admin/mdinst.sql
SQL> spool off

SQL> alter user MDSYS account lock;

Execute the following steps to verify if Spatial is installed correctly:

SQL> connect / as sysdba
SQL> set serveroutput on
SQL> execute validate_sdo;
SQL> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';
SQL> select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;

A sample valid 10.2.0.4.0 installation shows the following output:

SQL> execute validate_sdo;

PL/SQL procedure successfully completed.

SQL> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';

COMP_ID CONTROL
------------------------------ ------------------------------
SCHEMA VERSION STATUS
------------------------------ ------------------------------ -----------
COMP_NAME
--------------------------------------------------------------------------------
SDO SYS
MDSYS 10.2.0.4.0 VALID
Spatial

no rows selected

XDB SCHEMA INSTALL STEPS

2009-06-28T23:19:00.000-07:00

XDB SCHEMA INSTALL STEPS:

1. Connect as sysdba and run the catqm.sql script. Maintain a spool of the
script running:

UNIX: $ORACLE_HOME/rdbms/admin subdirectory
WINDOWS: $ORACLE_HOME\rdbms\admin subdirectory

The catqm.sql script requires the following parameters be passed to it when
run:
A. XDB user password
B. XDB user default tablespace (You can use any tablespace other than system undo and temp. This tablespace has to exist prior to running the script.)
C. XDB user temporary tablespace
Therefore the syntax to run catqm.sql will be:
SQL> catqm.sql A B C

For Example:
SQL> set echo on
SQL> spool xdb_install.log
SQL>@?/rdbms/admin/catqm.sql XDB XDB TEMP
SQL> select comp_name, status, version from DBA_REGISTRY where comp_name= 'Oracle XML Database'

Manual installation of Oracle Text 10gR2

2009-06-28T23:02:00.000-07:00

Manual installation of Oracle Text 10gR2

You must install Oracle Data Mining (ODM)

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK

Where:
CTXSYS - is the ctxsys user password
SYSAUX - is the default tablespace for ctxsys
TEMP - is the temporary tablespace for ctxsys
LOCKNOLOCK - ctxsys user account will be locked or not

To manually install US default preferences, for example, log into sqlplus as CTXSYS, and run 'drdefus.sql' as described below:

SQL> connect CTXSYS/password@tns_alias
SQL> @?/ctx/admin/defaults/drdefus.sql
SQL> spool off

How To Manually Install Data Mining

2009-06-28T22:54:00.000-07:00

How To Manually Install Data Mining

Oracle Data Mining (ODM) embeds data mining within the Oracle database. The data never leaves the database -- the data, data preparation, model building, and model scoring results all remain in the database. This enables Oracle to provide an infrastructure for application developers to integrate data mining seamlessly with database applications.

SQL> start %ORACLE_HOME%\rdbms\admin\dminst.sql SYSAUX TEMP
SQL> start %ORACLE_HOME%\rdbms\admin\odmpatch.sql
SQL> start %ORACLE_HOME%\rdbms\admin\utlrp.sql

SQL> select COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Oracle Data Mining';

Upgrade Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12

2009-05-27T02:13:00.000-07:00

Upgrade Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12

Oracle recommends that Oracle E-Business Suite 12 customers move to the latest and therefore most secure certified version of the Sun JRE Native Plug-in on either stream (JRE 5.0.x or JRE 6.0.x).

1. Check Version.

D:\oracle\PROD\apps\apps_st\appl>grep plugin %CONTEXT_FILE%
[!-- JDK plugins --]
[sun_plugin_ver oa_var="s_sun_plugin_ver"]1.5.0_13[/sun_plugin_ver]

2.Download the JRE Plug-in Interoperability Patch

Download the JRE Plug-in Oracle E-Business Suite interoperability patch 4377566

3.Download the Java Runtime Environment (JRE) 6 plug-in from

http://java.sun.com/javase/downloads/index.jsp

Select the Java Runtime Environment (JRE) 6 Update X ( where X is the current latest version)
Download the Windows Offline Installation version of the Plug-in.
If an earlier version of the JRE 6 Plug-in is required, it can be downloaded from the Sun archive site from http://java.sun.com/products/archive/ .

Select the required release from the drop down menu under, Java 2 Platform Standard Edition (J2SE) -> JDK/JRE - 6 .
Select the Download JRE.
Download the Windows Offline Installation, Multi-language version of the Plug-in.

4.Rename the JRE 6 Plug-in

Rename the downloaded JRE Native Plug-in file from jre-6_uxx-windows-i586-p.exe to j2se160xx.exe

(Where xx = the version number. Using jre-6_u13-windows-i586-p.exe as an example this would be j2se16013.exe).

5.Place the JRE 6 Plug-in on Web Application Tier

Move the j2se1600X.exe file to the web application tier and place it in %[COMMON_TOP]%\webapps\oacore\util\jinitiator..

6.Stop Apps or Shutdown Apache

Shutdown Apache (on normal mode) by running adapcctl.sh stop or by running adstpall.sh /

7.Enable 'Maintenance Mode' from adadmin

Enable 'Maintenance Mode' from adadmin, (Select menu option 5 > Change Maintenance Mode >Select menu option 1 > Enable Maintenance Mode)

7.Apply the patch driver in the interop patch using AutoPatch.

Apply the patch 4377566

8.Run the txkSetPlugin.cmd Script

Run the %FND_TOP%\bin\txkSetPlugin.cmd script against the web application tier node.

txkSetPlugin.cmd [appsora_path] [apps_pwd] [jversion]

9.Disable 'Maintenance Mode' through adadmin

Disable 'Maintenance Mode' from adadmin, (Select menu option 5 > Change Maintenance Mode > Menu option 2 > Disable Maintenance Mode)

10.Restart Apache or Start Apps

Restart Apache (on normal mode) by running adapcctl.sh start or adstrtal.sh /

txkSetPlugin.cmd D:\oracle\PROD\apps\apps_st\appl\APPSSIT_sitartha.cmd xxxxxx 16013

11.Check version Now.
D:\oracle\PROD\inst\apps\SIT_sitartha>grep plugin %CONTEXT_FILE%
[!-- JDK plugins --]
[sun_plugin_ver oa_var="s_sun_plugin_ver"]1.6.0_13[/sun_plugin_ver]