Foreign Key Constraint Error ORA-02298

While trying to create FK on cust_status got the following error.

ALTER TABLE CUST_STATUS ADD (CONSTRAINT CUST_STATUS_FK FOREIGN KEY (LOGIN) REFERENCES USERS(LOGIN))
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.CUST_STATUS_FK) - parent keys not found

SQL> desc cust_status
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID NOT NULL NUMBER(8)
TIMESTAMP NOT NULL DATE
STATUS NOT NULL VARCHAR2(35)
LOGIN VARCHAR2(20) ID NUMBER

SQL> desc users
Name Null? Type
----------------------------------------- -------- ----------------------------
LOGIN NOT NULL VARCHAR2(20) PASSWORD VARCHAR2(30)
LAST_NAME VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
EMAIL VARCHAR2(100)
ACTIVE CHAR(1)

To find out which rows are the problem rows,

SQL> select custid,login from cust_status a
where not exists (select 'x' from users where login = a.login);

You can delete the data using...

SQL> delete from cust_status
where login is null or login not in ( select login from users )

Now you should be able to create the FK constraint.

How to test Oracle Apps after a patch

How to test Oracle Applications after any maintenance, for example patch

Access the Application Home Page (http://host_name:port)
This verifies Oracle HTTP Server is up and running.

Login to Apps as "SYSADMIN"
If you can get to that page, it verifies JSERV is up and running as this page is served by JServ

Once you are able to login to the Apps, this verifies your connection to the database is working.

Click on Help button on the top right portion, if you can access the help page, it verifies you connection from the front end to the database is working as that page is served by the database.

Click on the Concurrent Manager, if you get to the page where it shows all the jobs, it verifies Forms is up and running. Try submitting a job and see if it runs successfully, it proves Concurrent Manager is up and running.

DG Failover Steps

Purpose : Failover to standby database in case the primary database server crashes.
Assumptions : Primary Database SID : CHDP1
Standby Database SID : CHDS1
flashback is on


Now the Primary datbase server crashes and no longer accessible.

Step by Step Instructions.

Login to standby database (CHDS1)
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;

In very urgent situations when you can not wait for some of the logs to be applied.
SQL> alter database activate standby database;

Now at this point in time your standby database (CHDS1) becomes primary databse.

After few hours, your original primary database server (which has CHDP1 database) comes back up and you wanted to make it (CHDP1) a standby database.

Login to CHDS1

SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
--------------------------------------------------
1234567

Login to CHDP1
SQL> startup mount;
SQL> flashback database to scn 1234567;
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup mount;

Check Kernel Parameters before installing software

To check Kernel parameter for Linux before installing oracle(you can add additional stuff as needed)
----- start check_OS_linux.sh -----------
echo "Checking kernel parameters..."
/sbin/sysctl -a 2>&1 | grep sem | grep -v error
/sbin/sysctl -a 2>&1 | grep shm | grep -v error
/sbin/sysctl -a 2>&1 | grep file-max | grep -v error
/sbin/sysctl -a 2>&1 | grep ip_local_port_range | grep -v error
/sbin/sysctl -a 2>&1 | grep rmem_ | grep -v error
/sbin/sysctl -a 2>&1 | grep wmem_ | grep -v error

echo "Checking OS components ..."
rpm -q binutils
rpm -q libaio
rpm -q gcc
rpm -q libstdc++
rpm -q libstdc++-devel
rpm -q gcc-c++
rpm -q glibc
rpm -q gnome-libs
rpm -q make
rpm -q pdksh
rpm -q sysstat
----------------- end script check_OS_linux.sh -----------------

To check kernel parameters for Solaris

---------------- start script check_OS_solaris.sh ----------------
/sbin/sysctl -a 2>&1 | grep sem | grep -v error

/sbin/sysctl -a 2>&1 | grep shm | grep -v error

/sbin/sysctl -a 2>&1 | grep file-max | grep -v error

/sbin/sysctl -a 2>&1 | grep ip_local_port_range | grep -v error

/sbin/sysctl -a 2>&1 | grep rmem_ | grep -v error

/sbin/sysctl -a 2>&1 | grep wmem_ | grep -v error
----------------- end script check_OS_solaris.sh ----------------

RMAN Backup types

Full
A backup of a datafile that includes every allocated block in the file being backed up. A full backup of a datafile can be an image copy, in which case every data block is backed up. It can also be stored in a backup set, in which case datafile blocks not in use may be skipped, according to certain rules.

A full backup cannot be part of an incremental backup strategy; that is, it cannot be the parent for a subsequent incremental backup.

Incremental
An incremental backup is either a level 0 backup, which includes every block in the file except blocks compressed out because they have never been used, or a level 1 backup, which includes only those blocks that have been changed since the parent backup was taken.

A level 0 incremental backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.