Monthly Archives: July 2014

How to recovery table in Oracle 12c Release 12.1.0.1.0


bash> sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 13:31:36 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID […]

Archivelog Delete Commands


RMAN>delete archivelog all; RMAN>delete archivelog until time ‘SYSDATE-10′; RMAN>delete archivelog from time ‘SYSDATE-10′ RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′; RMAN>delete archivelog from sequence 1000; RMAN>delete archivelog until sequence 1500; RMAN>delete archivelog from sequence 1000 until sequence 1500; Note : Also, you can use noprompt statement for do not yes-no question. RMAN>delete noprompt archivelog […]

Archivelog List Commands


RMAN>list archivelog all; RMAN>list copy of archivelog until time ‘SYSDATE-10′; RMAN>list copy of archivelog from time ‘SYSDATE-10′ RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′; RMAN>list copy of archivelog from sequence 1000; RMAN>list copy of archivelog until sequence 1500; RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

ORA-16957: SQL Analyze time limit interrupt


Error “ORA-16957” happens when some SQL took more time to analyze during the Automatic Maintenance window.  This will not have any impact to database. You can ignore it if it is one time occurrence, but if this is happening daily then you can Manually tune the query to fix the issue. 1) You can use […]

oratop – monitoring tool


Just found out about the new oracle tool oratop, not used it live issues yet but it looks a great tool if your on linux and on 11.2.0.3 or > The oracle note on the product is – oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1) Sample […]

ORA-16957: SQL Analyze time limit interrupt


Cause: The ORA-16957 error is an internal error code used to indicate that SQL analyze has reached its time limit. Action: According to MOS Doc ID 1275248.1  ,  (This is an unpublished bug. The bug is fixes in version 12.1) The error ORA-16957: SQL Analyze time limit interrupt, can be ignored safely, as it does not indicate any […]

Applying Patches using OPatch utility


bash-3.2$ export ORACLE_SID=ABSTEST bash-3.2$ echo $ORACLE_SID ABSTEST bash-3.2$ sqll SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 17 23:53:05 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> shut […]

ORA-00027 cannot kill current session


ORA-00027 cannot kill current session Cause: An attempt was made to use ALTER SYSTEM KILL SESSION to kill the current session. Action: If it is necessary to kill the current session, do so from another session. ORA-00027 comes under “Oracle Database Server Messages”.  These messages are generated by the Oracle database server when running any […]

ORA-06502: PL/SQL: numeric or value error: character string buffer too small


Example:   declare myname varchar2(5); begin myname := ‘Anar Godjaev’; end; / ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4 Solution: declare myname varchar2(20); begin myname := ‘Nimish Garg’; end; /    

ORA-04031in alert.log


Error: ORA-04031: unable to allocate 67280 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”) Cause: An ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a memory chunk large enough to satisfy an internal allocation request on behalf of a user’s operation. Resolution: 1. Increased the […]

Data Recovery Advisor


rman target / Aşağıdaki komut ile bozulmaları görebiliriz RMAN>list failure; İstersek Failure ID numarası ile belirtilen bozulmanın detayını ögrenebiliriz. RMAN>list failure 246 detail; Şimdi ne yapacağımız konusundan RMAN’den danışmanlık hizmeti alalım. RMAN>advise failure; Bize bir öneride bulundu.Çıktının en sonunda önerdigi scripti bulabiliriz.İşletim sistemi üzerinden bu scriptin içerigine bakabiliriz. cat /u01/app/oracle/diag/rdbms/db11g/DB11G/hm/reco_62454.hm; Bu scriptin içerigini RMAN üzerinden […]

Solaris 10 sendmail stop/start


/etc/init.d/sendmail stop;sleep 10;/etc/init.d/sendmail start;

WARNING: db_create_file_dest is same as db_recovery_file_dest


Hi , I am getting above warning in alert log  .   Sat Jul 05 01:34:12 2014  WARNING: db_recovery_file_dest is same as db_create_file_dest   This is harmless bug 3837794 that has been fixed in 10.2. You’ll have to review Metalink for reference to the bug. However, the report essentially indicates that the error message can be […]

Warning: log write elapsed time


*** 2014-06-25 05:36:13.267 Warning: log write elapsed time 2026ms, size 1744KB *** 2014-06-25 05:36:16.072 Warning: log write elapsed time 2251ms, size 2948KB *** 2014-06-25 05:36:17.387 Warning: log write elapsed time 1315ms, size 1506KB *** 2014-06-25 05:36:19.555 Warning: log write elapsed time 1872ms, size 1608KB *** 2014-06-25 05:36:21.577 Warning: log write elapsed time 2021ms, size 2308KB […]

RMAN Crosscheck Backup and Delete Expired Backup


> rman target / Recovery Manager: Release 11.1.0.6.0 – Production on Thu Jun 2 14:06:15 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ANARDB (DBID=1270013502) RMAN> crosscheck backup; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=108 device type=DISK crosschecked backup piece: found to […]

Sequence-Based Recovery


UNTIL SEQUENCE kullanarak istediğimiz sıradaki Archivelog dosyasına kadar recovery yapabiliriz. 1 2 3 4 RMAN> STARTUP MOUNT; RMAN> RESTORE DATABASE UNTIL SEQUENCE 100; RMAN> RECOVER DATABASE UNTIL SEQUENCE 100; RMAN> ALTER DATABASE OPEN RESETLOGS;

Restore Point Recovery


Flashback Database özelliği aktif ise restore point(geri yükleme noktası) oluşturabiliriz.Aşagıdaki gibi SQL*Plus üzerindede oluşturabiliriz. 1 SQL> CREATE RESTORE POINT after_anar; Oluşturdugumuz geri yükleme noktasına aşağıdaki gibi dönebiliriz.(Varolan geri yükleme noktalarını V$RESTORE_POINT ile görebiliriz) 1 2 3 4 RMAN> STARTUP MOUNT; RMAN> RESTORE DATABASE UNTIL RESTORE POINT after_anar; RMAN> RECOVER DATABASE UNTIL RESTORE POINT after_anar; RMAN> […]

Oracle datapump Parameters


Oracle impdp parameters: Parameter Parameter description ATTACH Attach to existing job, e.g. ATTACH [=job name]. CONTENT Specifies data to load where the valid keywords are:(ALL), DATA_ONLY, and METADATA_ONLY. DIRECTORY Directory object to be used for dump, log, and sql files. DUMPFILE List of dumpfiles to import from (expdat.dmp),e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp. ENCRYPTION_PASSWORD Password key for […]