Move Grid Control Agent Repository To Another Server

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

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

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

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

This summary is not available. Please click here to view the post.

Integration Oracle Apex With Weblogic Server

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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]

Oracle Streams - Strart and Stop

Oracle Streams - Strart and Stop

Start Streams.

1. Start Capture.
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'STREAM1_CAPTURE');
END;
/

2. Start Progapation.
BEGIN
dbms_propagation_adm.start_propagation(
PROPAGATION_NAME=>'STREAM1_PROPOGATION');
END;
/

3. Start Apply.
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'APPLY1_STREAM');
END;
/

Stop Streams.

1. Stop Apply.
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'APPLY1_STREAM');
END;
/

2. Stop Progagation
BEGIN
dbms_propagation_adm.stop_propagation(
PROPAGATION_NAME=>'STREAM1_PROPOGATION');
END;
/

3. Stop Capture.
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(
capture_name => 'STREAM1_CAPTURE');
END;
/

Oracle Streams Configure and Sample - Heart Beat

Oracle Streams Configure
Oracle Live(Prod) Database Name : ORCL
Oracle Downstreams Database Name : Power
Oracle Target Database Name : Blue

1.Configure Oracle Net so that the ALL database can communicate.
2.Preparing to Copy Redo Log Files for Downstream Capture

Source(Live db ORCL) initORCL.ora
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,power)'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ABLE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_5='SERVICE=power ARCH OPTIONAL NOREGISTER REOPEN=60 TEMPLATE=D:\TEST\arc%s%t%r.arc DB_UNIQUE_NAME=power'

Downstream(Power) initpower.ora
alter system set log_Archive_config='DG_CONFIG=(ORCL,POWER)'

Follwing Steps in Live DB(Prod)

3. Create Sample table and upadte every 1 minute sample data.
create user ms identified by ms;
ALTER USER MS Default tablespace users Quota unlimited on users;
create table ms.stream_heartbeat (GLOBAL_NAME VARCHAR2(4000),LAST_ACTIVITY_DATE DATE) TABLESPACE USERS;
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);
INSERT INTO STREAM_HEARTBEAT ((SELECT GLOBAL_NAME FROM GLOBAL_NAME),SYSDATE)

4. Enable Supplemental log.
ALTER TABLE ms.stream_heartbeat ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY ) COLUMNS;

5. Create sample procedure (Streams Heart Beat)
create or replace procedure proc_stream_heartbeat_ms IS
begin
update ms.stream_heartbeat
set last_Activity_Date = sysdate;
end proc_stream_heartbeat_ms;

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Streams_ms_Heartbeat',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN ms.proc_stream_heartbeat_ms; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
end_date => NULL,
enabled => TRUE,
comments => 'Heartbeat job for ms Schema');
End;

BEGIN
DBMS_SCHEDULER.DISABLE('Streams_ms_Heartbeat');
-- and then
DBMS_SCHEDULER.ENABLE('Streams_ms_Heartbeat');
END;

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

6. Create Streams User.
Create user strmadmin Identified by st$1$1admin ;
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';

7. Connet Streams User and run instantiation.
DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@blue(
source_object_name => 'ms.stream_heartbeat ',
source_database_name => 'orcl',
instantiation_scn => source_scn);
END;
/

Following Step are Downstream db;

1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;

2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
Grant connect, resource, dba,aq_Administrator_role to strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

3. Connect strmadmin/strm$1admin

4.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';
Create database link blue Connect to strmadmin identified by st$1$1admin Using 'blue';

5. Now Configure Streams Stepup in Downstrams (Capture and Propagation)
6.Create Streams Queue Stepup
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

7. Create Capture Stepup
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'streams_queue',
capture_name => 'stream1_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'orcl',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/


8. Add table to Capture.

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

9. Create Propagation Setup.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'ms.stream_heartbeat',
streams_name => 'stream1_propogation',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@blue',
include_dml => true,
include_ddl => false,
source_database => 'orcl',
inclusion_rule => true);
END;
/


Following Steps are Target DB.

1. Create New Tablespace for Streams.
create tablespace strm datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\strm01.dbf' size 1G;

2. Create New User and grant Streams Privileges.
Create user strmadmin Identified by st$1$1admin Default tablespace strm Quota unlimited on strm;
GRANT CONNECT, RESOURCE, DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

3.Connect strmadmin/strm$1admin

4.Create Streams Queue Stepup (Apply)
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

5.Create database link
Create database link orcl Connect to strmadmin identified by st$1$1admin Using 'orcl';

6. Create sample table.
create table ms.stream_heartbeat as (select * from ms.stream_heartbeat@orcl);
alter table ms.stream_heartbeat add primary key (global_name,last_Activity_Date);

7. Create Apply Processing.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'msr.stream_heartbeat',
streams_type => 'apply',
streams_name => 'apply1_stream',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => FALSE,
source_database => 'orcl',
inclusion_rule => TRUE );
END;
/

Now Streams Configuraction Compelete.

1. Start Capture.

2. Start Propagation.

3. Start Apply.

Oracle Apex AnyChart 5.1.2 Installing Steps

Oracle Apex AnyChart 5.1.2 Installing Steps

1.Download Apex AnyChart 5.1.2
http://anychart.apex-evangelists.com/download/apex_anychart_512.zip

2. Backup flashchart folder
cp -rf $IAS_ORACLE_HOME/Apache/Apache/images/flashchart $IAS_ORACLE_HOME/Apache/Apache/images/flashchart.bak

3. unzip apex_anychart_512.zip

4. copy unzipped all file to flashchart folder
cp *.* $IAS_ORACLE_HOME/Apache/Apache/images/flashchart

5. Backup imagelist.xml
cp $IAS_ORACLE_HOME/Apache/Apache/images/imagelist.xml $IAS_ORACLE_HOME/Apache/Apache/images/imagelist.xml.bak

6. Add the following line to $IAS_ORACLE_HOME/Apache/Apache/images/imagelist.xml (search for /flashchart/ and add it in this section)
/flashchart/AnyChart.swf (eg : {file}/flashchart/AnyChart.swf{file})

Note: this is a trial version of Anychart. You need to buy the full product let the Trial watermark disappear

7. Copy AnyChart.swf(license Version) to $IAS_ORACLE_HOME/Apache/Apache/images/flashchart

8. Now Check AnyChart

Oracle Apex (Middle Tier) port number Change 7777 to 80

Oracle Apex (Middle Tier) port number Change 7777 to 80

1. Login Oracle IAS Home.
cd $IAS_ORACLE_HOME/Apache/Apache/conf

2. Take a backup httpd.conf
cp httpd.conf httpd.conf.bak

3. Open httpd.conf and chage Port Number
change follow bellow entry
Port 7777
Listen 7777
to
Port 80
Listen 80

4. Save httpd.conf

5. Go into the directory $IAS_ORACLE_HOME/Apache/Apache/bin
cd $IAS_ORACLE_HOME/Apache/Apache/bin

6. Login root user
su root

7. Execute following line chown root .apachectl
$chown root .apachectl

8. Execute following line chmod 6750 .apachectl
$chmod 6750 .apachectl


9. Restart the OHS with the command $IAS_ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server

Before http://192.168.0.104:7777/pls/apex

After http://192.168.0.104/pls/apex

Oracle Application Express Upgrade 3.1.1 to 3.1.2

APEX Upgrade 3.1.1 to 3.1.2 Steps

SQL> select COMP_NAME,VERSION from dba_registry where COMP_NAME like 'Oracle%Application%Express%';
COMP_NAME VERSION
------------------------------ ------------------------------
Oracle Application Express 3.1.1.00.09

1.Download from metalink p7313609_11106_GENERIC.zip
2.Unzip and extract p7313609_11106_GENERIC.zip
3.Stop Oracle HTTP Server
ORACLE_BASE\ORACLE_HTTPSERVER_HOME\opmn\bin\opmnctl stopproc ias-component=HTTP_Server
4.Login Oracle Database
sqlplus "/as sysdba"
5.Run apxpatch.sql
SQL>@patch\apxpatch.sql
6.Copy image folder.
cp -rf patch/images ORACLE_HTTPSERVER_HOME/Apache/Apache
7.Start Oracle HTTP Server
ORACLE_BASE\ORACLE_HTTPSERVER_HOME\opmn\bin\opmnctl startproc ias-component=HTTP_Server


SQL> select COMP_NAME,VERSION from dba_registry where COMP_NAME like 'Oracle%Application%Express%';
COMP_NAME VERSION
------------------------------ ------------------------------
Oracle Application Express 3.1.2.00.02

Oracle 11g New Features for DBA (Database Administrators)

Oracle 11g New Features for Database Administrators (DBA)

1.Installation, Database Upgrades and Change Management
• Installation New Features Support
• Role and Privilege Changes
• Deprecated Components
• New Initialization Parameters Affecting Database Creation
• DBCA Enhancements
• Upgrading to Oracle Database 11g
• Database Replay
• The SQL Performance Analyzer
• Patching in Oracle Database Control

2.Database Diagnosis and Repair
• Introducing Automatic Diagnostic Repository (ADR)
• Configuring the ADR
• Using adrci Tool
• Using The Support Workbench in the OEM
• Database Health Monitor
• Data Recovery Advisor
• SQL Test Case Builder
• Data Block Corruption Parameters

3.Database Administration
• Automatic Memory Management
• Automatic Maintenance Tasks
• Oracle Flashback-Related New Features
• Flashback Data Archive
• Oracle Flashback Transaction Backout
• Virtual Columns
• New Data Partitioning Schemes
• DDL Lock Timeout
• Explicit Locking of Tables
• Invisible Indexes
• Read-Only Tables
• Shrinking Temporary Tablespaces and Tempfiles
• Creating an Initialization Parameter File from Memory
• Restore Point Enhancements
• Database Resident Connection Pooling
• Comparing and Synchronizing Database Objects
• SQL*Plus New Features
• Online Application Maintenance
• Oracle Advanced Compression Option
• Oracle Scheduler New Features
• Lightweight Jobs
• Remote External Jobs
• Finer-grained Dependency Management
• Enhancements in Oracle Database Resource Manager

4.Performance Tuning
• PL/SQL Native Compilation
• Server Result Cache
• Client Side Result Cache
• Enhanced Oracle Process Monitoring
• Subprogram Inlining
• SQL Tuning Automation
• SQL Access Advisor Enhancements
• Changing Statistics Preferences
• Enhanced Statistics Maintenance
• SQL Plan Management
• ADDM New Features
• AWR New Features
• Setting Metric Thresholds for Baselines
• Performance-Related Changes in Database Control
• Miscellaneous New Performance Tuning Features
• Real-Time SQL Monitoring

5.Database Security
• Stronger Password Hash Algorithm
• Security Out of the Box
• Anti Network Attacks Parameters
• Tablespace Encryption
• Fine-Grained Access Control for UTL* Packages
• Further Security New Features

6.Backup and Recovery New Features
• Enhanced Block Media Recovery
• RMAN Substitution Variables
• New RMAN Configuration Parameters
• The Multisection Backups
• Creating Archival Backups
• VALIDATE Command
• Configuring an Archived Redo Log Deletion Policy
• Active Database Duplication
• Importing and Moving Recovery Catalogs
• Virtual Private Catalogs
• Miscellaneous New Features in RMAN

7.Data Pump Utilities
• Compression Enhancement
• Encryption Enhancements
• Reusing a Dump File
• Remapping Data
• Renaming Tables During Export or Import
• Data Pump and Partitioned Tables
• Ignoring Nondeferred Constraints
• External Tables Based on Data Pump Driver
• Enhancement in the Transportable Parameter

8.Automatic Storage Management (ASM)
• SYSASM Privilege and OSASM Group
• Upgrading ASM using DBUA
• Upgrading ASM Manually
• ASM Restricted Mode
• Diskgroup Attributes
• Checking Diskgroup
• asmcmd Utility Commands
• Fast Rebalance
• Miscellaneous ASM New Features

9.PL/SQL New Features
• PL/SQL New Features

10.Data Warehousing
• SecureFiles
• Accessing a LOB Using SQL and PL/SQL
• Online Redefinition
• Partition Change Tracking (PCT)
• PIVOT Operator Enhancements
• Partitioning Improvements

Oracle 11g Deprecated Components

Oracle 11g Deprecated Components

Following are the components deprecated in Oracle 11g:
o iSQL*Plus
o Oracle Workflow
o Oracle Enterprise Manager Java Console
o Oracle Data Mining Scoring Engine
o Raw storage support (installer only)

Enabling frmservlet R12 direct login

Enabling f60cgi direct login
Enabling frmservlet R12.0.6 direct login

Enable Method:

java oracle.apps.fnd.security.AdminAppServer apps_username/apps_password AUTHENTICATION OFF DBC={dbc_file_name}

java oracle.apps.fnd.security.AdminAppServer apps/xxxxxx AUTHENTICATION OFF DBC=VIS.dbc

Disable Method:
1.To activate basic server security, from the command line, enter:
jre oracle.apps.fnd.security.AdminAppServer apps_username/apps_password AUTHENTICATION ON DBC={dbc file path}

2.To activate full server security (SECURE mode), from the command line, enter:
jre oracle.apps.fnd.security.AdminAppServer apps_username/apps_password AUTHENTICATION SECURE DBC={dbc file path}

Check the status:

java oracle.apps.fnd.security.AdminAppServer apps_username/apps_password STATUS DBC={dbc_file_name}

java oracle.apps.fnd.security.AdminAppServer apps/xxxxxx STATUS DBC=VIS.dbc

Database Server
---------------
DATABASE_ID: VIS
AUTHENTICATION: SECURE

Application Server
------------------
APPL_SERVER_STATUS: VALID
APPL_SERVER_ID: 61753FFF2F867C9CEE040A8C00B0000328F16873181892135982523005112239

APP-FND-01542

Error Message

APP-FND-01542 : This Application Server is not authorized to access this system. Please contact your System Administrator.





Solution:

1. Backup context file .

2. Update the context variable:
s_appserverid_authentication

By default in 11.5.10, this is set to SECURE.
In previous 11i versions, this was set to OFF.
For debug purposes, you can use ON or OFF.

Modes:
- ON : Partial
- SECURE : activates full server security (SECURE mode)
- OFF : deactivates server security

3. Run Autoconfig to instantiate the change.

Oracle Application Express 3.1 New Features

Oracle Application Express 3.1 New Features

1.Interactive Reports
2.Declarative BLOB Support
3.Enhanced Report Printing
4.Adding to the Theme Repository
5.Optional Runtime-Only Installation
6.Improved Security
7.Documented JavaScript libraries
8.Application Date Format
9.Application Builder refinements

Oracle Application Express 3.0 Features

Oracle Application Express 3.0 Features:

1.PDF Printing
2.Access Migration
3.Flash Charts
4.Drag and Drop Item Layout
5.Improved Web Services
6.New Item Types
7.Calendar Enhancement
8.Supporting Objects Enhancements
9.Page and Region Caching
10.Shared Components Enhancements
11.Item Finder Enhancements
12.Other Item Enhancements
13.New Multi-Delete Functions
14.Null Sort Order for Reports
15.Navigation Improvements
16.Application and Schema Comparison
17.Application Home Page - Additional Display Types
18.Application Builder Defaults
19.Developer Preferences
20.Developer Toolbar Additions
21.Friendly URL Syntax to Facilitate Bookmarks
22.Support of Locking with WAIT/NOWAIT in Automatic DML
23.SQL Workshop - Saved SQL and Scripts Change
24.SQL Workshop - Other Enhancements
25.APEX Views Exposed
26.New Password and Account Controls
27.Improved Workspace Management
28.New Look for Managing Users

Creating LVM(Logical Volume Manager) on Redhat Linux

Creating LVM(Logical Volume Manager) on Redhat Linux

LVM(Logical Volume Manager) is a method of allocating hard drive space into logical volumes that can be easily resized instead of partitions.



1.Create a partition Linux LVM type.
2.Inform the OS of partition table changes.
3.Create a Physical Volume (LMV).
4.Create a Volume Group using these Physical Volumes.
5.Create a Logical Volume from this Volume Group.
6.Format Volume Group.
7.Mount Volume Group.



1.Create a partition Linux LVM type.

[root@sys80 ~]# fdisk /dev/sda

The number of cylinders for this disk is set to 60801.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/sda: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 6374 51199123+ 83 Linux
/dev/sda2 6375 6948 4610655 82 Linux swap
/dev/sda3 6949 13028 48837600 83 Linux
/dev/sda4 13029 19108 48837600 5 Extended
/dev/sda5 13029 19108 48837568+ 8e Linux LVM

[root@sys80 ~]# fdisk /dev/sdb
The number of cylinders for this disk is set to 19457.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/sdb: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-19457, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-19457, default 19457): +50G

Command (m for help): p

Disk /dev/sdb: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 6080 48837568+ 83 Linux

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdb: 160.0 GB, 160041885696 bytes
255 heads, 63 sectors/track, 19457 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 6080 48837568+ 8e Linux LVM

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.


2.Inform the OS of partition table changes.
[root@sys80 ~]# partprobe
[root@sys80 ~]# cat /proc/partitions
major minor #blocks name

8 0 488386584 sda
8 1 51199123 sda1
8 2 4610655 sda2
8 3 48837600 sda3
8 4 0 sda4
8 5 48837568 sda5
8 16 156290904 sdb
8 17 48837568 sdb1


3.Create a Physical Volume (LMV).
[root@sys80 ~]# pvcreate /dev/sdb1
Physical volume "/dev/sdb1" successfully created

[root@sys80 ~]# pvcreate /dev/sda5
Physical volume "/dev/sda5" successfully created

4.Create a Volume Group using these Physical Volumes.
[root@sys80 ~]# vgcreate vg0 /dev/sdb1 /dev/sda5
Volume group "vg0" successfully created

5.Create a Logical Volume from this Volume Group.
[root@sys80 ~]# lvcreate -L 3048M -n data vg0
Logical volume "data" created

[root@sys80 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 49G 7.8G 38G 18% /
none 1.5G 0 1.5G 0% /dev/shm
/dev/sda3 46G 32M 43G 1% /db

6.Format Volume Group.
[root@sys80 ~]# mkfs.ext3 /dev/vg0/data
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
390144 inodes, 780288 blocks
39014 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=801112064
24 block groups
32768 blocks per group, 32768 fragments per group
16256 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

7.Mount Volume Group.
[root@sys80 ~]# mkdir /oracle
[root@sys80 ~]# mount /dev/vg0/data /oracle
[root@sys80 ~]# mount
/dev/sda1 on / type ext3 (rw)
none on /proc type proc (rw)
none on /sys type sysfs (rw)
none on /dev/pts type devpts (rw,gid=5,mode=620)
usbfs on /proc/bus/usb type usbfs (rw)
none on /dev/shm type tmpfs (rw)
/dev/sda3 on /db type ext3 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
/dev/mapper/vg0-data on /oracle type ext3 (rw)

[root@sys80 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 49G 7.8G 38G 18% /
none 1.5G 0 1.5G 0% /dev/shm
/dev/sda3 46G 32M 43G 1% /db
/dev/mapper/vg0-data 3.0G 37M 2.8G 2% /oracle