Monthly Archives: January 2016

Exadata 12.1.2.2.0 Software is Released


Good new features announced, such as finding Flash Cache and Flash Log statistics on AWR, automatic ASM data redundancy check even shutting down a storage server by pressing the power button or through ILOM, preventing Flash Cache population in cell to cell rebalance, disabling SSH on Storage Servers and running CellCLI commands from Compute Nodes […]

How to Recompile all Invalid Database Objects


Cut and paste the sample code below into a file called compile.sql Log into SQL*Plus as a user with DBA privileges (required to access the DBA_OBJECTS view) Issue the following command from SQL*Plus to run the script: SQL> @”compile.sql” Sample Code REM ————————————————————————– REM REM compile.sql – Recompiles All Invalid Database Objects REM REM This […]

Recover Table using RMAN / Oracle Database 12c


[oracle@anar12cdb ~]$ rman target / Recovery Manager: Release 12.1.0.1.0 – Production on Wed Dec 4 20:41:25 2015 Copyright (c) 1982, 2015, Oracle and/or its affiliates. All rights reserved. connected to target database: KBAZERDB (DBID=2818129616) RMAN> backup database test; Starting backup at 04-DEC-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel […]

Create a startup trigger to start all PDBs after DB startup


Create a startup trigger to start all PDBs after DB startup SQL> create or replace trigger open_all_pdbs 2 after startup on database 3 begin 4 execute immediate ‘alter pluggable database all open’; 5 end; 6 / Trigger created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total […]

DBMS_UTILITY.compile_schema


The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus. EXEC DBMS_UTILITY.compile_schema(schema => ‘SCHEMAS_NAME’);

ORA-28595: Extproc agent : Invalid DLL Path


SYMPTOMS SQL> execute DEBUG_EXTPROC.STARTUP_EXTPROC_AGENT; BEGIN DEBUG_EXTPROC.STARTUP_EXTPROC_AGENT; END; * ERROR at line 1: ORA-28595: Extproc agent : Invalid DLL Path CAUSE A call DEBUG_EXTPROC.STARTUP_EXTPROC_AGENT simply loads the extproc.exe process and has not attempted to load any external libraries. As a result, the error can be a bit misleading. For this scenario, it was found that within […]

How to check SERIAL number of Exadata Hardware


root@bakuexa1dbadm01:~# cd /opt/ root@bakuexa1dbadm01:/opt# ls automic AVMRclnt AVMRclusclnt ipmitool MegaRAID oracle.cellos oracle.ExaWatcher oracle.SupportTools ORCLfmap sun-ssm root@bakuexa1dbadm01:/opt# cd oracle.cellos/ root@bakuexa1dbadm01:/opt/oracle.cellos# ls cell.conf HWFW_Config_Support_Info.xml image_functions imageLogger lib scripts_aura.sh CheckHWnFWProfile HWFWCheckUtil image.id ipconf ORACLE_CELL_OS_IS_SETUP sgutil.pm compmon ib_set_node_desc.sh imagehistory ipconf.pl patch validations gen_cellaffinity.sh igbrenumber.sh imageinfo iso rm_adds.pl root@bakuexa1dbadm01:/opt/oracle.cellos# root@bakuexa1dbadm01:/opt/oracle.cellos# ./CheckHWnFWProfile -S Server_Model=SUN_SERVER_X4-2 ====START SERIAL NUMBERS==== ==Motherboard, from smbios== –System […]

How to check Bundle Patch applies to Exadata Database


oracle@anarexadatatest:~$ echo $ORACLE_SID ANARLIVE1 oracle@anarexadatatest:~$ sqlplus “/ as sysdba”; SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 22 10:01:47 2016 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing […]

Move OCR to new diskgroup


[oracle@anardbrac1 trace]$ crsctl check crs CRS-4638: Oracle High Availability Services is online CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online [oracle@anardbrac1 trace]$ ocrcheck Status of Oracle Cluster Registry is as follows : Version : 3 Total space (kbytes) : 262120 Used space (kbytes) : 2920 Available […]

Create Public Synonym


SELECT ‘Create or replace public synonym  ‘||object_name || ‘ for ‘                                || OWNER || ‘.’ || object_name || ‘;’ FROM ALL_OBJECTS Where object_type  in (‘VIEW’,’PACKAGE’) and OWNER = ‘<USER_NAME>’;

Oracle Critical Patch Update Advisory – January 2016


To help you plan ahead, below is a roadmap of patch sets for Oracle Database major releases 11.1 and beyond, showing planned release dates and the duration of their support lives in relationship to the overall release life. The chart is by nature somewhat simplified so be sure to read the details below it to […]

How to Stop the scan listener and scan


root@anardb:~# srvctl stop scan_listener root@anardb:~# srvctl stop scan

ORA-00020: maximum number of processes (%s) exceeded


SQL> show parameter processes NAME TYPE VALUE ———————————— ———– —————————— processes integer 300 SQL> alter system set processes=600 scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. SQL> show parameter processes NAME TYPE VALUE ———————————— ———– —————————— processes integer 600

ORA-39181: Only partial table data may be exported due to fine grain access control


The below error appeared when trying to export the Schema using System User . . exported “OC_CON”.”DD_ALL_TABLE_COLUMNS” 299.0 KB 32693 rows ORA-39181: Only partial table data may be exported due to fine grain access control on “OC_CRD”.”CRD_CARD” . . exported “OC_CRD”.”CRD_CARD” 477.9 KB 5290 rows . . exported “OC_EOC”.”EOC_ACC_USED_LIMIT_BACKUP” 241.6 KB 41740 rows Solution: grant […]

Shared Memory Realm does not exist


ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Solution: $ export ORACLE_SID=orcl sqlplus “sys/password as sysdba” Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 574619648 bytes Fixed Size 1250236 bytes Variable Size 192941124 bytes Database Buffers 377487360 bytes Redo Buffers 2940928 bytes Database mounted. Database […]

ORA-19809: limit exceeded for recovery files


oracle@bakuexa1dbadm01:~$ sqlplus “/ as sysdba”; SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 18 10:32:46 2016 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select * […]

Recover lost of redo log


ash-3.2$ rm redo0* bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 17 07:21:29 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 3003176 bytes Variable Size 838864088 bytes Database Buffers 402653184 bytes Redo […]

SHUTDOWN: waiting for active calls to complete


Below message found in “alert.log” file when shutdown immediate hung for long time Thu DEC 19 21:08:00 2015 Active call for process 4448 user ‘SYSTEM’ program ‘ORACLE.EXE (J001)’ SHUTDOWN: waiting for active calls to complete. Suggestion : 1. connect to SYS user with new terminal window SQL> conn / sysdba Connected to idle instance. 2. […]

Lost redo+control files / Recover Database


bash-3.2$ rm redo0* bash-3.2$ rm control01.ctl bash-3.2$ rm “/u01/app/oracle/fast_recovery_area/ANARDB2/control02.ctl” bash-3.2$ ls -ltr total 5650988 -rw-r—– 1 oracle oinstall 62922752 Jun 17 07:24 temp01.dbf -rw-r—– 1 oracle oinstall 817897472 Jun 17 07:25 system01.dbf -rw-r—– 1 oracle oinstall 639639552 Jun 17 07:25 sysaux01.dbf -rw-r—– 1 oracle oinstall 83894272 Jun 17 07:25 undotbs01.dbf -rw-r—– 1 oracle oinstall 1340874752 […]

how can Expand OCRVOTE diskgroup size


[root@rac01 ~]# oracleasm createdisk VOTE2 /dev/sdd1 Writing disk header: done Instantiating disk: done SQL> select path, name, header_status from v$asm_disk; PATH NAME HEADER_STATU ——————– —————————— ———— ORCL:VOTE2 PROVISIONED ORCL:DATA1 DATA1 MEMBER ORCL:FRA1 FRA1 MEMBER ORCL:VOTE1 VOTE1 MEMBER SQL> alter diskgroup OCRVOTE add disk ‘ORCL:VOTE2’ drop disk VOTE1; Diskgroup altered. SQL> select path, name, header_status from […]