Recover Database From loss of Control file in Oracle RAC system

Time Out Oracle Form when idle for a long time

(1.) Download timeout.jar
(2.) Put it into ORACLE_HOME\forms\java folder.
(3.) Add entry of the jar to formsweb.cfg
-- archive_jini=frmall_jinit.jar,timeout.jar
-- archive=frmall.jar,timeout.jar
(4.) Open the form and in Form builder.
(5.) Create block called PJC 
(6.) Add Bean and name it TIMEOUT and make the Implementation Class oracle.forms.demos.TimeoutPJC
(7.) For the bean create a WHEN-CUSTOME-ITEM-EVEN trigger and put this code

DECLARE
     eventName                varchar2(30) := :system.custom_item_event;
     v_alert_answer      varchar2(1);
BEGIN
     IF (eventName='MAX_INACTIVITY_EXCEEDED') THEN
          --FORMS_DDL('ROLLBACK');
          message('Oracle Forms Session is timeout'); PAUSE;
          message('about to disconnect with DB'); pause;
          LOGOUT;
  END IF;
END;

DECLARE
      v_expiry_time      VARCHAR2(10) := '1'; --settintg timer this should be roud of value
     v_debug_yn                VARCHAR2(10) := 'true'; -- enabling debug message this u can set false also if u dont want debug message in java console
     args                               Varchar2(400);
     args_has_value      boolean := false;

BEGIN
          
     ----------------Timeout---
     IF v_expiry_time IS NOT NULL THEN
          args := args||'all'; -- this you change based on what event u want
          args_has_value:=true;

          IF (args_has_value) THEN
          -- Set custom Bean property
          set_custom_property('PJC.TIMEOUT',1,'RECORDING_EVENTS',args);
          END IF;

          set_custom_property('PJC.TIMEOUT',1,'ENABLE_DEBUGGING',v_debug_yn);
          set_custom_property('PJC.TIMEOUT',1,'TIMER_SLEEP_TIME','10');
          set_custom_property('PJC.TIMEOUT',1,'START_TIMER',v_expiry_time);
          
          -------
          END IF;

END;

FRM - 92091

Cause :- This is Due to some security issue in Client PC or Network Troubleness or time out

Action : - Add proper Entry in /etc/host file & proper host name in /etc/sysconfig/Network file

RMAN Consider snapcf_SID.f as obsolete

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f


Cause:
The snapshot controlfile is cataloged as a controlfile coy and is now obsolete. RMAN cannot delete this file as it is used by rman.

RMAN> show all;
RMAN configuration parameters for database with db_unique_name TRCPRD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/db/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f; # default


Solution:
•    Set new name (or location) for RMAN to use for snapshot controlfile:
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_ORACLE11G.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_ORACLE11G.f';
new RMAN configuration parameters are successfully stored

•    Crosscheck and delete the file from RMAN:
RMAN> crosscheck controlfilecopy "/u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1353 device type=DISK
validation failed for control file copy
control file copy file name=/u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f RECID=1 STAMP=828773553
Crosschecked 1 objects

RMAN> delete expired "controlfilecopy /u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f"

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1353 device type=DISK
List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
1       X 14-OCT-13       32121181196 14-OCT-13
        Name: /u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f
        Tag: TAG20131014T065231


Do you really want to delete the above objects (enter YES or NO)? YES
deleted control file copy
control file copy file name=/u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f

RECID=1 STAMP=828773553
Deleted 1 EXPIRED objects

•    Now try deleting the obsolete backups

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
using channel ORA_DISK_1
no obsolete backups found

•    Set the snapshot controlfile name (or location) to original:
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO "/u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f";

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_ORACLE11G.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/apps/oracle/product/11.2.0/db_1/dbs/snapcf_ORACLE11G.f';

Oracle 11g GoldenGate Install Step by Step on Oracle Linux 5.8(64 bit)

Pre-Requisities Software

Source Database

Target Database

GoldenGate Software

Source Host machine :- db(10.10.13.11) Oracle_SID=SRC

Target Host machine :- db2(10.10.13.12) Oracle_SID=TAR

The goal - replicate all changes in one scheme also support DDL from one Oracle DB host(db)  to another Oracle DB host(db2).

Assume :- Both Source and Target DB are ready And want to replicate Source schema(us01) on host DB to  Target Schema (us01)

Step 1:-Install Golden Gate software on both Source and Target Oracle DB host

[oracle@db]$ mkdir /u01/app/goldengate

Copy the download file to the new directory and unzip using:

[oracle@db]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

output is as :-

inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
The tar archive is around 230MB in size.

Extract the files from the tar archive using:

[oracle@db]$ tar xfv fbo_ggs_Linux_x64_ora11g_64bit.tar

Configure Environment Variables

The /home/oracle/.bash_profile should already contain the following environment variables.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

On the source server add the following entries to /home/oracle/.profile

export ORACLE_SID=SRC
export PATH=/home/oracle/goldengate:$PATH
export LD_LIBRARY_PATH=/u01/app/goldengate:$ORACLE_HOME/lib

Ensure the environment variables are set using

[oracle@db]$ source /home/oracle/.bash_profile

On the target server add the following entries to /home/oracle/.profile
export ORACLE_SID=TAR
export PATH=/u01/app/goldengate:$PATH
export LD_LIBRARY_PATH=/u01/app/goldengate:$ORACLE_HOME/lib

1.1:-Create Golden Gate Subdirectories

On Both(Source and Target) server create subdirectories for GoldenGate using the CREATE SUBDIRS command. For example:

[oracle@db]$ cd /u01/app/goldengate

[oracle@db goldengate]$ ./ggsci
GGSCI (db) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/app/goldengate

Output is as :-
Parameter files /u01/app/goldengate/dirprm: already exists
Report files /u01/app/goldengate/dirrpt: created
Checkpoint files /u01/app/goldengate/dirchk: created
Process status files /u01/app/goldengate/dirpcs: created
SQL script files /u01/app/goldengate/dirsql: created
Database definitions files /u01/app/goldengate/dirdef: created
Extract data files /u01/app/goldengate/dirdat: created
Temporary files /u01/app/goldengate/dirtmp: created
Stdout files /u01/app/goldengate/dirout: created

Now Your Golden gate software has been installed

2. Prepare the source database (host db) and Target for replication. Switch the database to archivelog mode: 

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;

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


Enable minimal supplemental logging: 
SQL> alter database add supplemental log data;

Database altered.


Forced logging should be enabled on the source database to ensure that all changes are written to the redo logs. DBCA does not enable force logging during database creation.

SQL> ALTER DATABASE FORCE LOGGING;





 
SQL>

Prepare the database for DDL replication. Turn off the recyclebin feature and be sure that it is empty. 
SQL> alter system set recyclebin=off scope=spfile;
SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


Create a schema that will contain the Oracle GoldenGate DDL objects. 

SQL>  create tablespace goldengate datafile '/u01/app/oracle/product/11.2.0/oradata/goldengate01.dbf' size 100m  autoextend on;


SQL>  create user gg01 identified by gg01 default tablespace goldengate quota unlimited on 
goldengate;

SQL>  grant create session, connect, resource to ggate;

Grant succeeded.

SQL> grant dba to ggate; -- just in case

Grant succeeded.

SQL>  grant execute on utl_file to ggate;

Grant succeeded.

SQL>

Change the directory on Golden Gate home directory and run scripts for creating all necessary objects for support ddl replication. 
[oracle@db ~]$ cd /u01/app/goldengate/
[oracle@db goldengate]$ sqlplus / as sysdba

SQL> @/u01/app/goldengate/marker_setup.sql
SQL> @/u01/app/goldengate/ddl_setup.sql
SQL> @/u01/app/goldengate/role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @/u01/app/goldengate/ddl_enable.sql


After that, add information about your Oracle GoldenGate DDL scheme into file GLOBALS. You should input this row into the file "GGSCHEMA ggate". 
[oracle@db gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (db.us.oracle.com) 1> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate

GGSCI (db.us.oracle.com) 2>



2.1. Prepare the target database (host db2) for replication. 

On the target server create a new tablespace for the GoldenGate objects. Again ensure that AUTOEXTEND is enabled.

SQL>  create tablespace goldengate datafile '/u01/app/oracle/product/11.2.0/oradata/goldengate01.dbf' size 100m  autoextend on;




SQL>  create user gg01 identified by gg01 default tablespace goldengate quota unlimited on 

goldengate;





3. Create the schemes for replication on both hosts (db, db2).

3.1. Source database (db):   
SQL> create user us01 identified by us01 default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to us01;

Grant succeeded.

SQL>

3.2. Target database(db2): 

SQL> create user us01 identified by us01 default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to us01;

Grant succeeded.

SQL> grant dba to us01; -- or particular grants

Grant succeeded.

SQL>


As these are test databases, security is not an issue and therefore DBA privilege has been granted to the new user.






4. Create the directory for trail files on both hosts and create directory for discard file on db2 host only. 
[oracle@db ~]  mkdir /u01/app/goldengate/dirdat/tr
[oracle@db2 ~] mkdir /u01/app/goldengate/dirdat/tr
[oracle@db2 ~] mkdir /u01/app/goldengate/discard



5. Listener Services Should be Running on both server


6.Configure GoldenGate Process like Manager,extract ,datapump on Source


Run ggcsi and configure the manager process. 
[oracle@db gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (db.us.oracle.com) 1> edit params mgr 
PORT 7809
GGSCI (db.us.oracle.com) 2> start manager

Manager started.

GGSCI (db.us.oracle.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

GGSCI (db.us.oracle.com) 4>


Login into database and add additional information about primary keys into log files. 
GGSCI (db.us.oracle.com) 4> dblogin userid gg01@tns_entry
Password:
Successfully logged into database.

GGSCI (db.us.oracle.com) 5> ADD SCHEMATRANDATA us01

2012-12-06 16:23:20  INFO    OGG-01788  SCHEMATRANDATA has been added on us01 source.

GGSCI (db.us.oracle.com) 6>


NOTE. This is a very important step, because if you don't do it you will not be able to replicate Update statements. You will get errors like the following: 
OCI Error ORA-01403: no data found, SQL 
Aborting transaction on /u01/app/oracle/product/gg/dirdat/tr beginning at seqno 1 rba 4413
                         error at seqno 1 rba 4413
Problem replicating SOURCE.T1 to TARGET.T1
Record not found
Mapping problem with compressed update record (target format)...
*
ID =
NAME = test3
*
...

As you know, when you write the update statement you usually don't change the primary key, so Oracle log files contain information about changing column values and don't contain information about primary key. For avoiding this situation you should add this information into log files using ADD SCHEMATRANDATA or ADD TRANDATA commands. Add extracts (regular and data pump). 
GGSCI (db.us.oracle.com) 6> add extract ex1, tranlog, begin now
EXTRACT added.

GGSCI (db.us.oracle.com) 7> add exttrail /u01/app/golgengate/dirdat/tr, extract ex1
EXTTRAIL added.

GGSCI (db.us.oracle.com) 8> edit params ex1
extract ex1
userid gg01@tns_entry, password gg01
exttrail /u01/app/golgengate/dirdat/tr 
ddl include mapped objname us01.*;
table us01.*;

GGSCI (db.us.oracle.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EX1        00:00:00      00:01:29

GGSCI (db.us.oracle.com) 10> add extract dp1, exttrailsource /u01/app/goldengate/dirdat/tr , begin now
EXTRACT added.

GGSCI (db.us.oracle.com) 11> add rmttrail /u01/app/goldengate/dirdat/tr, extract dp1
RMTTRAIL added.

GGSCI (db.us.oracle.com) 12> edit params dp1
EXTRACT pump1
USERID gg01, PASSWORD gg01
RMTHOST 10.10.13.12, MGRPORT 7809
RMTTRAIL /u01/app/goldengate/dirdat/tr 
PASSTHRU
table us01.*;

GGSCI (db.us.oracle.com) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EX1        00:00:00      00:02:33
EXTRACT     STOPPED     dp1       00:00:00      00:02:56

GGSCI (db.us.oracle.com) 14>

5.2. Configure the target database (db2). 
Configure the manager process 
[oracle@db2 goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


GGSCI (db2) 1> edit params mgr
PORT  7809

GGSCI (db2) 2> start manager

Manager started.

GGSCI (db2) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


Create the checkpoint table and change the GLOBAL file. 
GGSCI (db2) 4> EDIT PARAMS ./GLOBALS
GGSCHEMA gg01
CHECKPOINTTABLE gg01.checkpoint

GGSCI (db2) 5> dblogin userid gg01@tns_entry
Password:
Successfully logged into database.

GGSCI (db2) 6> add checkpointtable gg01.checkpoint

Successfully created checkpoint table target.checkpoint.

GGSCI (db2) 7>

Add replicat. 
GGSCI (db2) 8> add replicat rep1, exttrail /u01/app/goldengate/dirdat/tr, begin now
REPLICAT added.

GGSCI (db2) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID us01@tns_entry, PASSWORD us01
discardfile /u01/app/goldengate/discard/rep1_discard.txt, append, megabytes 10
DDL
HANDLECOLLISIONS
map us01.*, target us01.*,REPERROR(-1403,EXCEPTION);

GGSCI (db2) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:01:52

GGSCI (db2) 11>


5.3. Start extracts and replicat. 
GGSCI (db.us.oracle.com) 6> start extract ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (db.us.oracle.com) 7> start extract dp1

Sending START request to MANAGER ...
EXTRACT dp1 starting

GGSCI (db.us.oracle.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EX1        00:00:00      00:00:01
EXTRACT     RUNNING     dp1       00:00:00      00:01:01


GGSCI (db2) 6> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (db2) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:06

6. Check Host db.
[oracle@db goldengate]$ sqlplus us01/us01

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 20:09:17 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t1 (id number primary key, name varchar2(50));

Table created.

SQL> insert into t1 values (1,'test');

1 row created.

SQL> insert into t1 values (2,'test');

1 row created.

SQL> commit;

Commit complete.

SQL>

Check host db2: 
[oracle@db2 goldengate]$ sqlplus us01/us01

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 20:09:41 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         2 test

SQL>

As you can see, all works. Lets execute some SQL and Update statements of course. Host db: 
SQL> delete t1 where id =2;

1 row deleted.

SQL> insert into t1 values (3,'test');

1 row created.

SQL> update t1 set name='test3' where id = 3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         3 test3

SQL>


Let's check host db2: 
SQL>  select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         3 test3

SQL>





Command Prompt Output file Host DB(db)
GGSCI (gg1) 4> start manager

Manager started.


GGSCI (gg1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (gg1) 6> dbloging userid gg01@src
ERROR: Invalid command.

GGSCI (gg1) 7> dblogin userid gg01@src
Password:
Successfully logged into database.

GGSCI (gg1) 8> ADD SCHEMATRANDATA us01

2013-11-27 01:04:14  INFO    OGG-01788  SCHEMATRANDATA has been added on schema us01.

GGSCI (gg1) 9> add extract ex1 tranlog, begin now
EXTRACT added.


GGSCI (gg1) 10> add exttrail /u01/app/goldengate/dirdat/tr,extract ex1
EXTTRAIL added.


GGSCI (gg1) 11> edit params ex1



GGSCI (gg1) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EX1         00:00:00      00:03:28


GGSCI (gg1) 13> add extract dp1,exttrailsource /u01/app/goldengate/dirdat/tr ,begin now
EXTRACT added.


GGSCI (gg1) 14>

GGSCI (gg1) 14>

GGSCI (gg1) 14>

GGSCI (gg1) 14>

GGSCI (gg1) 14> add rmttrail /u01/app/goldengate/dirdat/tr,extract dp1
RMTTRAIL added.


GGSCI (gg1) 15> edit params dp1



GGSCI (gg1) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DP1         00:00:00      00:02:32
EXTRACT     STOPPED     EX1         00:00:00      00:10:00


GGSCI (gg1) 17>  start extract ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (gg1) 18> start extract dp1

Sending START request to MANAGER ...
EXTRACT DP1 starting


GGSCI (gg1) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:02
EXTRACT     RUNNING     EX1         00:00:00      00:00:09


GGSCI (gg1) 20> stop dp1

Sending STOP request to EXTRACT DP1 ...
Request processed.


GGSCI (gg1) 21> stop ex1

Sending STOP request to EXTRACT EX1 ...
Request processed.


GGSCI (gg1) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DP1         00:00:00      00:00:13
EXTRACT     STOPPED     EX1         00:00:00      00:00:06


GGSCI (gg1) 23> start ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (gg1) 24> start extract dp1

Sending START request to MANAGER ...
EXTRACT DP1 starting


GGSCI (gg1) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:03:49
EXTRACT     RUNNING     EX1         00:00:00      00:00:00


GGSCI (gg1) 26> start ex1
EXTRACT EX1 is already running.


GGSCI (gg1) 27> start dp1
EXTRACT DP1 is already running.


GGSCI (gg1) 28> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:09
EXTRACT     RUNNING     EX1         00:00:00      00:00:03


GGSCI (gg1) 29> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:08
EXTRACT     RUNNING     EX1         00:00:00      00:00:04


GGSCI (gg1) 30> info trandata us01
ERROR: No viable tables matched specification.

GGSCI (gg1) 31> info trandata us01.t1

Logging of supplemental redo log data is enabled for table US01.T1.

Columns supplementally logged for table US01.T1: OBJECT_ID.

GGSCI (gg1) 32> info trandata us01.t2

Logging of supplemental redo log data is disabled for table US01.T2.

GGSCI (gg1) 33> stop ex1

Sending STOP request to EXTRACT EX1 ...
Request processed.


GGSCI (gg1) 34> stop dp1

Sending STOP request to EXTRACT DP1 ...
Request processed.


GGSCI (gg1) 35> start ex1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (gg1) 36> start dp1

Sending START request to MANAGER ...
EXTRACT DP1 starting


GGSCI (gg1) 37> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:12
EXTRACT     RUNNING     EX1         00:00:00      00:00:05


GGSCI (gg1) 38> edit params ex1



GGSCI (gg1) 39> edit params dp1



GGSCI (gg1) 40>



Command prompt output for db2


Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:02:52


GGSCI (gg2) 29> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 30> info al
ERROR: Invalid command.

GGSCI (gg2) 31> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:05


GGSCI (gg2) 32> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        00:00:07      01:19:43


GGSCI (gg2) 33> start rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 34> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:19:56      00:00:03


GGSCI (gg2) 35> stop replicat rep1
REPLICAT REP1 is already stopped.


GGSCI (gg2) 36> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 37> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:24:20      00:00:02


GGSCI (gg2) 38> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 39> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:26:48      00:00:06


GGSCI (gg2) 40> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:26:48      00:05:58


GGSCI (gg2) 41> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 42> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:32:53      00:00:02


GGSCI (gg2) 43> edit replicat rep1
ERROR: Invalid command.

GGSCI (gg2) 44> edit replicat rep1
ERROR: Invalid command.

GGSCI (gg2) 45> edit rep1
ERROR: Invalid command.

GGSCI (gg2) 46> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:32:53      00:04:32


GGSCI (gg2) 47> edit params rep1



GGSCI (gg2) 48> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 49> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:33:15      00:05:28


GGSCI (gg2) 50> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 51> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:33:15      00:16:37


GGSCI (gg2) 52> edit params rep1



GGSCI (gg2) 53> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 54> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:33:15      00:23:18


GGSCI (gg2) 55> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:33:15      01:04:40


GGSCI (gg2) 56> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 57> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     REP1        01:33:15      01:04:52


GGSCI (gg2) 58>

GGSCI (gg2) 58> edit params rep1



GGSCI (gg2) 59> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg2) 60> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:05


GGSCI (gg2) 61> edit mgr
ERROR: Invalid command.

GGSCI (gg2) 62> edit params mgr



GGSCI (gg2) 63> edit params ./GLOBALS



GGSCI (gg2) 64> edit params rep1



GGSCI (gg2) 65>

The New Task Could not be created 0x80070005 Access is denied

To solve problem try command on DOS Prompt

C:\windows>CACLS TASKS /E /G builtin\administrators:F

Display ICON in Push Button (Run Form 10g with OC4J)

locata file C:\DevSuiteHome_2\j2ee\DevSuite\application-deployments\forms


Append Line::


virtual-directory virtual-path="/ICONS" real-path="C:\DevSuiteHome_2\j2ee\ICONS"
After That

Edit Registary.dat file

default.icons.iconpath=ICONS/

default.icons.iconextension=gif

Display Icons on Push Buttons in Application Server 10g

Forms 10g no longer supports ICO files as icons for the buttons. You will need to convert your icon files into GIF or JPG format.




Design time Configuration

To display the icons in your Forms builder, modify your windows registry Hkey_Local_Machine\Software\Oracle\\UI_ICON by entering the path of your GIF/JPG files for e.g. C:\MY_APP\ICONS.

Runtime Configuration

1) Creating Jar file

i) Once you are ready with your GIF files or JPG files, create a folder called ICONS. You can create this folder anywhere. For e.g. you can create ICONS folder on a C drive under root directory or as a sub-folder under any folder.
ii) Assuming you have created ICONS folder as a root folder on C drive, now copy all your GIF files in this folder.
iii) Go to the DOS prompt and enter cd\
iv) At C:\ prompt enter the following command to create Java Archive File (JAR) called my_icons

Jar –cvf my_icons.jar icons
v) Copy this file in \Forms\Java folder.
Please remember that having the name of the folder holding GIF/JPG files as ICONS is very important. Because if the folder name is something else, icons will not be displayed in your form at run time. This reason is not mentioned anywhere in Oracle’s documentation. I learnt about this through metalink (Service Request no. 6602276.993) after struggling for more than one day to display the icons.



2) Modify REGISTRY.DAT file
i) Open this file located in \forms\java\oracle\forms\registry folder.
ii) Append the existing parameter default.icons.iconpath as follows:

default.icons.iconpath=icons/
iii) If you are going to use GIF files as image files for icons, then leave the existing parameter default.icons.iconextension=gif as it is. If you want to use JPG files instead of GIF files, replace GIF with JPG as shown below:

default.icons.iconextension=jpg

3) Modify FORMSWEB.CFG file
This file exists under \Forms\Server folder.
Append the following existing parameters:

i) imagebase=codebase

ii) archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar,my_icons.jar

SQL Loader Concept in oracle

SQL Loader is utility to load external text file into oracle database ;
it has 3 files;
1.control file
2.data file
3.log file;

example
data file look like "Salary.txt"

1,2,3,4
11,22,sd,44
22,33,44,55

control file look like "Salary.CTL"

LOAD DATA
INFILE 'C:\SALARY.TXT'
INTO TABLE TEST1
FIELDS TERMINATED BY ','
(A CHAR,
B CHAR,
C CHAR,
D CHAR
)

AFTER THAT FIRE THE COMMAND

sqlldr user_name/pass@connect_string control=c:\salary.txt log=c:\salary.log

URL to Mail from Application Server 10g

http://ip_address/reports/rwservlet?server=Report_Server_Name+report=location_of_report+userid=user_name/pass@connect_string+desformat=pdf+DESTYPE=mail+DESNAME="jainarayan5484@rediffmail.com"+from="jainarayan@surya.co.in"+subject="hi"

This URL used to Mail a Report

Flashback Query

Select * from table as of SCN timestamp_to_scn (systimestamp-1/1440)

it will retreave before 1 min. data

RMAN Backup (Full and incremental level 0)

 {
sql "alter system archive log curreent";
backup spfile;
sql "alter database backup controlfile to trace";
backup incrmental level 0 cumulstive as COMPRESSED BACKUPSET tag
'%FULL_WEEKLY_LEVEL0' database include current controlfile;
}

FRM-92101 Forms Server Failure

IF Application Server refuse the connection then

do the following
1.go to services then click the property of  infrastructure-Process-Manager > Click Log-On Tab and then
Check Allow Service to intrect with desktop and then apply;
2.Change the Registry Value
HKEY_LOCAL_MACHINE/system/CurrentControlSet/Control/SessionManager/Subsystem edit Window Property Parameter
SharedSection from 1024,3072,512 to 1024,3072,1024
3.Reboot the system

Configuration of WebUtil to know client information

Configuring WebUtil at OS:

1.Download WebUtil http://www.oracle.com/technology/software/products/forms/files/webutil/webutil_106.zip
2. Download Java Com Library http://prdownloads.sourceforge.net/jacob-project/jacob_18.zip
3. Extract both zips to some directory.
4. Copy

File frmwebutil.jar

From folder: webutil_106\webutil_106\java To: $ORACLE_HOME/forms/java

File: jacob.jar

From folder: jacob_18 To: $ORACLE_HOME/forms/java

File: d2kwut60.dll, JNIsharedstubs.dll

From folder: webutil_106\webutil_106\webutil To: $ORACLE_HOME/forms/webutil

File: jacob.dll

From folder: jacob_18 To: $ORACLE_HOME/forms/webutil

File: forms_base_ie.js, forms_ie.js

- From folder: webutil_106\webutil_106\java To: $ORACLE_HOME/forms/java

File: webutil.olb, webutil.pll and create_webutil_db.sql

From folder: webutil_106\webutil_106 To: $ORACLE_HOME/forms
File: webutilbase.htm, webutiljini.htm, webutiljpi.htm and webutil.cfg

- From folder: webutil_106\webutil_106\server To: $ORACLE_HOME/forms/server

File: sign_webutil.bat
- From folder: webutil_106\webutil_106\webutil To: $ORACLE_HOME/forms/webutil

5. In $ORACLE_HOME/forms/server/formsweb.cfg file

Change the following entries


# Forms applet archive setting for JInitiator

archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar



[webutil]

WebUtilArchive=/forms/java/frmwebutil.jar,/forms/java/jacob.jar



6. In $ORACLE_HOME/forms/server/default.env file change the following entries





FORMS_PATH=C:\YourOracle_Home\forms;C:\YourOracle_home\forms\webutil



# webutil config file path

WEBUTIL_CONFIG=C:\YourOracle_Home\forms\server\webutil.cfg

Add frmwebutil.jar, jacob.jar into Classpath
CLASSPATH=C:\YourOracle_Home\forms\java\frmwebutil.jar;C:\YourOracle_Home\forms\java\jacob.jar

7. Add frmwebutil.jar and Jacob.jar path into Registry of window into FORMS_BUILDER_CLASSPATH Variable.

Configuring Database:

1. Connect as Scott or your User

2. Run the script $ORACLE_HOME/forms/create_webutil_db.sql using SQLPLUS or any tool.

Setting up Form Builder:

1. Open Form builder Connect with SCOTT and in PL/SQL libraries open Webutil.pll file

2. File > Save as and Rename the file to different name e.g WebUtil_lib

(Trust me it works to avoid Webutil not found error) :)

3. Compile the new PLL file and generate it

4. Attached the new named Webutil_lib.pll to your form with Remove Path option YES.

5. Open WebUtil.olb file Object group in builder and copy or subclass it into your form.

6. Now compile your form and run it.

Note: Browser will ask to certify these libraries first time Choose ALWAYS certify. Check Java Console icon on the task bar and see frmwebtul.jar and jacob.jar are loaded successfully.

7. Press Browse button on form and choose Datafile.txt and Press Open...Text file will load into the Data Block.

Feel free to comment on this if i missed something plz inform me

Manually upgrade Database server from 10.2.0 to 11.1.0 (10g to 11g)

How to upgrade R12 Database server from 10.2.0 to 11.1.0 (10g to 11g) manually ?


We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home

as a separate ORACLE_HOME in parallel to 10g Oracle Home.

We have to install the new database for upgrading the existing database.

But before installing check the software versions for.....

a)Oracle database version and check the appropriate upgrade path accordingly depending on the present version.

b)E-business suite version---minimum is 12.0.4 and apply interoperability patch for 11g on apps node using adpatch

c)Autoconfig has to be upgraded to latest version by applying 7207440.

I did not need to apply as my instance is on 12.0.6 and 6728000(12.0.6 upgrade patch) supersedes this autoconfig patch..

Now steps for database installation

1.Apply patch 6400501 to Apps 10.1.0.5 Oracle-home

2. Deregister the current database if you want to change database sid,host,port.And update autoconfig for database also before that.
$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps
contextfile=$CONTEXT_FILE -removeserver
3. Update applications context file with new database parameters....
s_dbhost
s_dbdomain
s_db_serv_sid
s_dbport
s_apps_jdbc_connect_descriptor
to new database values. Don't run auto config on apps tier now....
otherwise apps will not be able to connect to database at all.
Run autoconfig only after complete upgrade process is finished..
Database Installation
The 11.1.0 Oracle home must be installed on the database server node
in a different directory other than the current Oracle home
Log in to the database server node as the owner
of the Oracle RDBMS file system and database instance. Ensure
that environment settings, such as ORACLE_HOME, are set for the new
Oracle home you are about to create, and not for any existing
Oracle homes on the database server node
Choose to install software only (without any default database) in the new location.
After the installation,
Run utlu111i.sql (located in 11g_ORACLE_HOME/rdbms/admin) on source 10g database
and check the output.
SQL>11g_oracle_home/rdbms/admin/utlu111i.sql
It displays warnings and recommends steps to clear the issues.
Time Zone Issue
select * from v$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 3
If time zone file version is less than 4 then apply time zone patch 5632264
This can be done using opatch.
unzip the patch and run opatch going into the unzipped patch directory.
or manually copy the .dat files under 5632264/files/oracore/zoneinfo into
$ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version.
Do not forget to take a backup of old zoneinfo directory before this.
Re-run utlu111i.sql after patching the database to record
the new timezone file version.
This time it says something like Database contains stale optimizer statistics.
So..
Gather statistics

Steps to Cloning a Database

The following steps must be on Source -Production instance/Server
Step 1. Shutdown database in normal mode and start it up in restricted mode.
Step 2. Take the backup of control file
Step 3. Shutdown database again in normal mode.
Step 4. Copy /FTP init parameter file ,control file script and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.The following step must be on destination – Clone instance/server.
Step 5. Edit init parameter file and control file script.
Step 6. New Environment setup .
Step 7. Connect with svrmgrl and recreate control file.
Step 8. Open the database in resetlogs.
Step 9. Shutdown the database in normal mode
Step 10. Take the cold backup and start the database in archive/non archive mode.
Details :-

Step 1 – 3 on the Source Server ( Server A)
SQL> connect sys/pass
Connected.
sql> shutdown ;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 57124108 bytes
Fixed Size 70924 bytes
Variable Size 40198144 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace;

Statement processed.
SQL>
SQL> show parameter user_dump_dest
NAME TYPE           VALUE
----------------------------------- ------- -------------------------
user_dump_dest         string E:\ora816\admin\ora816\udump
Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora.trc . Rename this file to Ctrlprod.sql

Read Text File from Oracle PL/SQL Program

Read Text File from Oracle PL/SQL Program


PROCEDURE get_file_contents(filename varchar2,Nxt in out number ) IS in_file CLIENT_Text_IO.File_Type;
linebuf VARCHAR2(3000);
c number:=0;
Nxt number:=0;
pipe varchar2(35);
BEGIN

--opening the file in READ ONLY Mode 'r'

Select nvl(max(Record_no),0) into Nxt from Qapadmin.Labdata;
in_file := CLIENT_Text_IO.Fopen(filename, 'r');

--CLIENT_Text_IO.Get_Line(in_file, linebuf);--to skip the heading titles

--CLIENT_Text_IO.Get_Line(in_file, linebuf);--to skip the heading seperators

--navigating to data block

go_block('data');

---clear block if contents required to replaced

clear_block;
first_record;

LOOP

---this logic depends on the structure of ur Text file.....play with linebuf variable.

--I am reading the file as Comma seperated and no of column are known in my example.

Nxt:=Nxt+1;

/*:empid and empname is block's item*/

:empid := ltrim(rtrim(substr(linebuf, 1, instr(linebuf,' ',1,1)-1)));
:empname := ltrim(rtrim(substr(linebuf, instr(linebuf,' ',1,1)+1)));
End if;
C:=C+1;
next_record;
CLIENT_Text_IO.New_Line;--to move to next line in file
END LOOP;
EXCEPTION
WHEN no_data_found THEN ---when no line remains this exception will raise...
CLIENT_Text_IO.Fclose(in_file);
first_record;
END;

Steps to Apply Oracle Patch using Opatch

1.backup the whole database;
2.shutdown the databse as
   SQL>Shutdown immediate;
   SDL>exit
3.check any installed patch as
   oracle_home/Opatch Isinventory -detail
4.check for patch conflicts in advanced by running the following command
   Opatch apply -slient -no_bug_superset -report
5.install patch
   oracle_home/Opatch/opatch apply patch_location(like D:\patch\7631957