Patching through OEM 12c in Offline mode

The below document is the step-by-step procedure to patch Oracle databases using OEM 12c in Offline mode.

SPU/CPU Patch Apply through OEM Cloud Control 12c (Offline Mode).pdf

Hope this helps

Cheers !!
Raheel Syed

Posted in OEM12c | Tagged , , , , , , , , | Leave a comment

Opatch Apply on Windows failed with CheckActiveFilesAndExecutables

While applying CPU Jan 2014 Patch on windows, opatch apply failed with error ‘CheckActiveFilesAndExecutables’

Following files are active :

D:\oracle\product\11.2.0\db_1\bin\oracommon11.dll
D:\oracle\product\11.2.0\db_1\bin\oraclient11.dll
D:\oracle\product\11.2.0\db_1\bin\orageneric11.dll
D:\oracle\product\11.2.0\db_1\bin\orapls11.dll
D:\oracle\product\11.2.0\db_1\bin\oran11.dll
D:\oracle\product\11.2.0\db_1\bin\oraxml11.dll
D:\oracle\product\11.2.0\db_1\bin\oci.dll
D:\oracle\product\11.2.0\db_1\bin\orahasgen11.dll
D:\oracle\product\11.2.0\db_1\bin\orahasgen11.dll
D:\oracle\product\11.2.0\db_1\bin\oraocr11.dll
D:\oracle\product\11.2.0\db_1\bin\oraocrb11.dll
D:\oracle\product\11.2.0\db_1\bin\oraocrutl11.dll
D:\oracle\product\11.2.0\db_1\bin\orannzsbb11.dll
D:\oracle\product\11.2.0\db_1\bin\orazt11.dll
D:\oracle\product\11.2.0\db_1\bin\oraasmclnt11.dll

Workaround:

1. Modify the Oracle services to Manual startup type.
2. Restart the server.
3. Rename the above .dll and try running opatch apply again.

Cheers !!!

Raheel Syed

Posted in Patch | Tagged , , , , , , , , , , , , , , , , | Leave a comment

scp – lost connection

Lost connection error when using scp

[oracle@server1 bkup]$ scp full_orcl.dmp myuser@server2:/app/oradump
myuser@server2’s password:
full_orcl  31% ********************************
lost connection

Resolution:
[oracle@server2 oradump]$ rsync –append –progress myuser@server1:/app/bkup/full_orcl.dmp .
full_orcl.dmp   5825256969   100%    1.78MB/s    1:22:47

Cheers !!!

Raheel

Posted in Linux | Tagged , | Leave a comment

ora-201 and Checker run found %n% new persistent data failures

Message from Alert.log:
Checker run found 3 new persistent data failures
ora-201 signalled during: alter database mount exclusive.

On Windows, when we stop and start the OracleService (services.msc) the above error reported.

Issue was with the permission on the controlfiles.

Moved Controlfiles to new location and issue got resolved.

Cheers !!!
Raheel Syed

Aside | Posted on by | Tagged , , , | Leave a comment

ORA-00001: unique constraint violated

IGNORE_ROW_ON_DUPKEY_INDEX hint is on rescue. This is the NEW feature in 11gR2 applied on INSERT statements, wherein duplicate values will be ignored rather causing ORA-00001 error.

Scenario: Development team wanted one of the STAGING table to be refreshed from PROD table without replacing the existing rows in the staging table.

Solution:

For understanding purpose I have created table EMP_BK as STAGING table and EMP as PROD table.

Existing table data:

Existing_table-data

SQL> insert into EMP_BK (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;

insert into EMP_BK (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
*
error at line 1:
ora-00001: unique constraint (scott.pk_emp_bk) violated

SQL> insert /*+ ignore_row_on_dupkey_index(emp_bk,pk_emp_bk) */  
into EMP_BK (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;

1 row created.

SQL> commit;

Commit complete.

Verify by querying the EMP_BK table


SQL> SELECT * FROM EMP_BK;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
1022 RAHEEL     DBA             7782 29-DEC-13       2000          0         30

15 rows selected.

Cheers,
Raheel

Posted in 11gR2, New Features | Tagged , , , | Leave a comment

Recovering from rm -rf on a datafile

Raheel Syed:

This should save me from embarrassment.

Originally posted on jarneil:

This blog posting is entirely the credit of Frits Hoogland, who pointed out to me that this was possible.

I managed to delete a datafile, that while it had been created in a completely stupid location, was very much in use. I’m sure we’ve all been there, I mean who hasn’t run rm -rf somewhere they shouldn’t have? When I subsequently checked the database and saw no flashback database, I realised this was going to mean the database being recreated. It was non-production of course, so no real big deal, but still a bit of work none the less. Thanks to Frits’ suggestion though, I could have saved myself some of that work!

First lets create a tablespace, in a really dumb location:

1
SQL> create tablespace temp_test datafile ‘/tmp/temp_test01.dbf’ size 10M;

Tablespace created.

SQL> conn temp_test/temp_test
Connected.
SQL> create table test_table (key number(1));

Table created.

SQL> insert into test_table…

View original 1,379 more words

Posted in Troubleshooting | Tagged , , | Leave a comment