10g

Full Database Export Failing With ORA-00980

While trying to do a full export for 10.2.0.3 database, we were getting following errors

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 980 encountered
ORA-00980: synonym translation is no longer valid
EXP-00000: Export terminated unsuccessfully

So as to debug the problem, we set errorstack for ORA-00980 as follows and tried exporting again

alter system set events '980 trace name errorstack level 3';

Export job again failed with error but this time it generated a trace file in user_dump_dest location. We got the following sql as failing statement in tracefile

ORA-00980: synonym translation is no longer valid
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM2) SYNNAM2,DBMS_JAVA.LONGNAME(SYNTAB) SYNTAB,TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID,
 TABOWNID, SYNOBJNO FROM   SYS.EXU9PTS      WHERE  TABOWNID = :1 ORDER  BY SYNTIME

A possible cause is that the package DBMS_JAVA is INVALID or absent in database so the public synonym DBMS_JAVA is no longer valid.Checking DBA_REGISTRY confirms that the JVM is in invalid state.

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
CATALOG  SYS      VALID        10.2.0.3.0 Oracle Database Catalog Views
CATPROC  SYS      VALID        10.2.0.3.0 Oracle Database Packages and Types
JAVAVM   SYS      INVALID      10.2.0.3.0 JServer JAVA Virtual Machine <--- Invalid state

We used Note 276554.1 – “How to Reload the JVM in 10.1.0.X and 10.2.0.X” in order to correct this problem.

Key things to look when diagnosing export/import issues

1)Check if you are using correct syntax. Use exp help=y or imp help=y to get the correct syntax.

2)Check if you are using correct exp/imp utility (version) against the database. Use following metalink note for compatibility matrix

Note 132904.1 – Compatibility Matrix for Export And Import Between Different Oracle Versions
3)Check DBA_REGISTRY view to see if the components are in valid state.
4) Try setting errorstack if you are facing “ORA-“ error.
5)Try setting trace=y parameter in export/import. This will generate a trace file (similar to sql trace) which can be used to diagnose the problems

e.g exp system/passwd full=y file=expdat.dmp log=exp.log trace=y

22 October, 2008 by Amit Bansal 10g

Question:Sizing ASM LUN

We received following question from Mr Orlando through our Contact Page

How about a discussion on how to size our LUNS to use with ASM. For instance we have a Dell MD3000 with 44x300GB HDDs (protected by RAID-10) and we’re planning to create LUNs of 2TB on the Storage, and deliver them to Windows 2003. for ASM to use what is common around the world? Just pass the 2TB Luns to Windows or maybe create several 500GB and give them to ASM. This is a RAC10g install, Oracle 10g Standard Edition.

Please find below our response to the question. We would also like to hear from others on their experiences and any other suggestions/opinions they may gave.

Our Response

As per 10gR2 ASM Best Practices document (available on ASM OTN Homepage), we can add Disk having size up to 2^32 Mb as ASM Disk (Minimum being 4 Mb). Therefore technically 2 Tb Disk/LUN can be added to the ASM Diskgroup but we would not recommend so.

Only advantage of having 2 Tb LUN would be reduced ASM Disks resulting in easier management. But there are some pitfalls too. We believe most of the problems will be seen during Disk Rebalance.

Suppose we have 3 TB of data on Two ASM Disks of 2 Tb each. Now we need to add new disk of 2Tb.In this case 1 Tb of data movement will happen and it will impact DB performance . Rebalance operation can also happen in case we are using Normal/High Redundancy and Disk failure occurs (due to Hardware problem or issues like header or block corruption), then failure group would be in Hung State. This needs to be corrected by adding a new Disk .

This would again require large amount of data movement (1.5 Tb in this example) and will take longer time to finish.

Lun size of 300-500 Gb should be fine for VLDB configuration. Having separate diskgroup for DATA and FRA (Flash Recover Area) is also recommended approach. One more thing which can be tried out in case of multi-terabyte database is to change the Allocation Unit size for ASM Diskgroup.Each Allocation Unit consumes some amount of memory in ASM SGA for storing the metadata information. Very Large Databases (VLDB) which can have sizes in TeraBytes will have too many AU’s allocated and also memory requirement for ASM instance will also increase. You can read more about this in our previous post Allocation Unit and Extents In ASM

8 September, 2008 by Amit Bansal 10g 0

How To Recover From Corrupted OCR Disk

It is very common where a DBA is left with corrupted OCR disk without having any good backup.
The same situation was experienced by me few days back. One node of RAC database shows the following:

NODE1:

<span style="font-family: arial,helvetica,sans-serif;"><strong>$ORA_CRS_HOME/bin/crs_stat -t
</strong>Name           Type           Target    State     Host
------------------------------------------------------------
ora.orcl.db    application    ONLINE    ONLINE    rac1
ora....11.inst application    ONLINE    ONLINE    rac1
ora....12.inst application    ONLINE    OFFLINE
ora....vice.cs application    OFFLINE   OFFLINE
ora....l11.srv application    ONLINE    OFFLINE
ora....l12.srv application    ONLINE    OFFLINE
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....DC.lsnr application    ONLINE    ONLINE    rac1
ora....abc.gsd application    ONLINE    ONLINE    rac1
ora....abc.ons application    ONLINE    ONLINE    rac1
ora....abc.vip application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora....bc2.gsd application    ONLINE    ONLINE    rac2
ora....bc2.ons application    ONLINE    ONLINE    rac2
ora....bc2.vip application    ONLINE    ONLINE    rac2</span>

The other node shows the following:
NODE2:

<span style="font-family: arial,helvetica,sans-serif;"><strong>/crs_stat -t</strong>
HA Resource                                   Target     State
-----------                                   ------     -----
ora.orcl.db                                   OFFLINE    OFFLINE
ora.orcl.orcl11.inst                          OFFLINE    OFFLINE
ora.orcl.orcl12.inst                          OFFLINE    OFFLINE
ora.orcl.test_service.cs                      ONLINE     OFFLINE
ora.orcl.test_service.orcl11.srv              OFFLINE    OFFLINE
ora.orcl.test_service.orcl12.srv              OFFLINE    OFFLINE
ora.rac1 .ASM1.asm                         OFFLINE    OFFLINE
ora.rac1 .LISTENER_RAC1 .lsnr           OFFLINE    OFFLINE
ora.rac1 .gsd                              OFFLINE    OFFLINE
ora.rac1 .ons                              OFFLINE    OFFLINE
ora.rac1 .vip                              OFFLINE    OFFLINE
ora.rac2.ASM2.asm                        OFFLINE    OFFLINE
ora.rac2.LISTENER_RAC2 2.lsnr         ONLINE     OFFLINE
ora.rac2.gsd                             ONLINE     OFFLINE
ora.rac2.ons                             ONLINE     OFFLINE
ora.rac2.vip                             ONLINE     OFFLINE</span>

We can see the inconsistent data across two node RAC. Every command for srvctl, crsctl was hanging on NODE 2.
Now the option is to restore the OCR backup, but if there is no backup available for OCR then we can use the following procedure to recover from corrupted OCR disk
(There will be complete downtime needed to perform these operations)

1. Check the status of CRS from node 1:

# ps -eaf |grep d.bin
root 12873 1 0 Aug11 ? 00:11:07 /u01/app/crs/bin/crsd.bin reboot
oracle 13105 12846 0 Aug11 ? 00:00:45 /u01/app/crs/bin/evmd.bin
oracle 13226 13200 0 Aug11 ? 00:13:13 /u01/app/crs/bin/ocssd.bin
root 21458 19986 0 20:34 pts/4 00:00:00 grep d.bin

2. Shutdown Oracle ClusterWare on all nodes:

<span style="font-family: arial,helvetica,sans-serif;">[root@rac1  bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.</span>

Check the status again:

[root@rac1 bin]# ps -eaf |grep d.bin
root 21927 19986 0 20:34 pts/4 00:00:00 grep d.bin

It shows that the cluster is stopped.

3. Execute rootdelete.sh from all nodes.

It is under directory $ORA_CRS_HOME/install/rootdelete.sh

NODE1:

<span style="font-family: arial,helvetica,sans-serif;">[root@rac1  install]# <strong>./rootdelete.sh</strong>
Shutting down Oracle Cluster Ready Services (CRS):
Stopping resources.
Error while stopping resources. Possible cause: CRSD is down.
Stopping CSSD.
Unable to communicate with the CSS daemon.
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down...
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in '/etc/oracle/scls_scr'</span>

NODE 2:

./rootdelete.sh Shutting down Oracle Cluster Ready Services (CRS): OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2] Shutdown has begun. The daemons should exit soon. Checking to see if Oracle CRS stack is down... Oracle CRS stack is not running. Oracle CRS stack is down now. Removing script for Oracle Cluster Ready services Updating ocr file for downgrade Cleaning up SCR settings in '/etc/oracle/scls_scr'

“OCR initialization failed accessing OCR device”, this error can occur due to folloing reasons:
1. ocrconfig_loc is not pointing to the correct ocr.
2. Problem of rights and owners on the ocr devices
3. Configuration problem on Oracle Cluster Synchronization Services

As the SCR entries are cleaned up so there is no need to worry about PROC-26 error.

If you have more than 2 nodes in a rac you need to run rootdelete.sh on all the other nodes also.

4. Run rootdeinstall.sh from the node where the RAC installation was done (usually it is the node1).
It will clear up the OCR disk contents.

./rootdeinstall.sh Removing contents from OCR device 2560+0 records in 2560+0 records out

5. Run root.sh from the same node:

./root.sh WARNING: directory '/u01' is not owned by root Checking to see if Oracle CRS stack is already configured Setting the permissions on OCR backup directory Setting up NS directories Oracle Cluster Registry configuration upgraded successfully WARNING: directory '/u01' is not owned by root assigning default hostname rac1 for node 1. assigning default hostname rac2 2 for node 2. Successfully accumulated necessary OCR keys. Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897. node : node 1: rac1 rac1-priv rac1 node 2: rac2 rac2-priv rac2 Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. Now formatting voting device: /dev/raw/raw1 Format of 1 voting devices complete. Startup will be queued to init within 90 seconds. Adding daemons to inittab Expecting the CRS daemons to be up within 600 seconds. CSS is active on these nodes. rac1 CSS is inactive on these nodes. rac2 2 Local node checking complete. Run root.sh on remaining nodes to start CRS daemons.

After its completion run root.sh on all remaining nodes.

 ./root.sh Checking to see if Oracle CRS stack is already configured Setting the permissions on OCR backup directory Setting up NS directories Oracle Cluster Registry configuration upgraded successfully clscfg: EXISTING configuration version 3 detected. clscfg: version 3 is 10G Release 2. assigning default hostname rac1 for node 1. assigning default hostname rac2 for node 2. Successfully accumulated necessary OCR keys. Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897. node : node 1: rac1 rac1-priv rac1 node 2: rac2 rac2-priv rac2 clscfg: Arguments check out successfully. NO KEYS WERE WRITTEN. Supply -force parameter to override. -force is destructive and will destroy any previous cluster configuration. Oracle Cluster Registry for cluster has already been initialized Startup will be queued to init within 90 seconds. Adding daemons to inittab Expecting the CRS daemons to be up within 600 seconds. CSS is active on these nodes. rac1 rac2 CSS is active on all nodes. Oracle CRS stack installed and running under init(1M) Running vipca(silent) for configuring nodeapps The given interface(s), "eth0" is not public. Public interfaces should be used to configure virtual IPs.

The silent mode VIPCA configuration will fail because of BUG 4437727 in 10.2.0.1. To solve this run the
VIPCA manually from root user from last node where this error has occured and follow the instructions.
# $ORA_CRS_HOME/bin/vipca

6. Now final step is to add the resources back to OCR with srvctl command.

Adding DATABASE to OCR:

$srvctl add database -d db_unique_name -o oracle_home [oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl add database -d orcl -o /u01/app/oracle/product/10.2.0/db_1

Adding INSTANCE to OCR:

srvctl add instance -d db_unique_name -i inst_name -n node_name [oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl add instance -d orcl -i orcl11 -n rac1 [oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl add instance -d orcl -i orcl12 -n rac2 2

Adding SERVICES to OCR:

$srvctl add service -d db_unique_name -s service_name -r preferred_list [oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl add service -d orcl -s test_service -r orcl11,orcl12

Adding NODEAPPS to OCR:

srvctl add nodeapps -n node_name -o oracle_home -A addr_str
Where addr_str= The node level VIP address
This command needs to be run from ROOT user otherwise you will get following error:

[oracle@rac1 ~]$ $ORA_CRS_HOME/bin/srvctl add nodeapps -n rac1 -o /u01/app/oracle/product/10.2.0/db_1 -A 10.167.21.89/255.255.255.0 PRKO-2117 : This command should be executed as the system privilege user. [oracle@rac1 ~]$ [oracle@rac1 ~]$ su - Password: [root@rac1 ~]# cd /u01/app/crs/bin [root@rac1 bin]# ./srvctl add nodeapps -n rac1 -o /u01/app/oracle/product/10.2.0/db_1 -A 10.167.21.87/255.255.255.0 [root@rac1 bin]#./srvctl add nodeapps -n rac2 2 -o /u01/app/oracle/product/10.2.0/db_1 -A 10.167.21.89/255.255.255.0

This will complete the OCR recreation, now you can test the status with cluvfy.

3 September, 2008 by Saurabh Sood 10g 19

Issues with CLUSTER_DATABASE parameter

Yesterday, I faced an interesting scenario while upgrading 2 Node RAC Database. I tried setting up CLUSTER_DATABASE=FALSE in spfile from Node 1,but it displayed value as TRUE after restarting database.Same was true for Database startup entries in Alert log. If I did the same setting in Node 2 and started database from node 2, it started in shared mode.I was using shared spfile (on OCFS) for both the systems.

CLUSTER_DATABASE is a Real Application Clusters parameter that specifies whether or not Real Application Clusters is enabled. It is mostly used for starting database in Exclusive mode during operations which will require updating dictionary. e.g Upgrading database,Enabling Archivelog and also for changing Database characterset.

I had ignored this error few times but today I wanted to find the cause and resolve it. To diagnose further, I set CLUSTER_DATABASE=FALSE from Node 1 with following command

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; 

Then I used srvctl to start the database

[oracle@blrraclnx1 bdump]$ srvctl start database -d orcl 

Node 1 Alert log had CLUSTER_DATABASE=TRUE

cluster_database = TRUE cluster_database_instances= 2 db_create_file_dest = +DATA db_recovery_file_dest = +FRA db_recovery_file_dest_size= 4294967296 thread = 1 instance_number = 1 

But on checking Alert Log from Node 2, I found CLUSTER_DATABASE=FALSE

cluster_database = FALSE cluster_database_instances= 1 db_create_file_dest = +DATA db_recovery_file_dest = +FRA db_recovery_file_dest_size= 4294967296 thread = 2 instance_number = 2 

Moreover Instance 2 had crashed with following errors

Tue Sep 2 21:10:14 2008 lmon registered with NM - instance id 2 (internal mem no 1) Warning: cluster_database_instances (1) is <= my node id (1) This instance wants to mount exclusive when instance 0 has mounted shared. Exiting! Tue Sep 2 21:10:15 2008 USER: terminating instance due to error 29707 Instance terminated by USER, pid = 2807 

Error says that Instance 0 (Node 1) has already mounted in SHARED Mode and this instance (Node 2) wants to start in Exclusive Mode.

To check the values I decided to use V$SPPARAMETER view. Till now I was using show parameter cluster_database command to check the values.

SQL> SELECT SID,NAME,VALUE,DISPLAY_VALUE FROM V$SPPARAMETER WHERE NAME='cluster_database'; SID NAME VALUE DISPLAY_VALUE ---------- ------------------------------ --------------- --------------- orcl1 cluster_database TRUE FALSE * cluster_database FALSE FALSE 

This is strange!! I had never specified any specific value for node 1. Anyways to resolve it, I used reset command to clear the orcl1 entry from spfile

SQL> alter system reset cluster_database sid='orcl1'; System altered. 

Then I re-checked the V$SPPARAMETER view

SQL> SELECT SID,NAME,VALUE FROM V$SPPARAMETER WHERE NAME='cluster_database'; SID NAME VALUE -------- ------------------ -------- * cluster_database FALSE 

Restarting the database next time allowed it to be started in Exclusive mode. Issue was resolved but question was “Why was CLUSTER_DATABASE variable different for Node 1”
I remember that as part of setup I had created Node 1 and added Node 2 later. There could be some missed steps there or it could be that this parameter was set explicitly with sid=’orcl1′ option.I really had no clue on why it was like that.If anyone has experienced this, then do let me know.

3 September, 2008 by Amit Bansal 10g 3

Using RDA As RDBMS Pre-Install Check Tool

Many of us would have come across RDA (Remote Diagnostic Agent) while working on a ticket with Oracle support. In case you have not heard about it, I would recommend to go through Metalink Note:314422.1 – Remote Diagnostic Agent (RDA) 4 – Getting Started

RDA captures System Information such as OS,Hardware Details (like number of CPU and amount of RAM),OS error log,OS Monitoring tool output (like vmstat,TOP,etc). This can be handy in case you do not know the command or the location of the OS logs.Similary you can find Database version,Database Patch inventory,Database Alert log and trace files.

This can help save lot of time as you need not remember all OS commands to capture the information.
Similarly RDA also collects Database Performance Statistics like OS Statistics (CPU,Memory and Disk I/O Stats) along with TOP SQL, Locking and Latch statistics. In case of 10g, it generates AWR Report (60 mins) and ADDM report based on captured Snapshots. All this information can be helpful for diagnosing a Performance Problem.

There is one more use of RDA which not many people are aware of. i.e RDA Health Check / Validation Engine (HCVE). HCVE Engine can be used to perform Pre-Install checks for Oracle Database and Oracle Application server on Unix system (At time of writing this article, this functionality is not available on windows)

To run this , you need to execute rda.sh -T hcve e.g I need to validate if I can install Oracle 10gR2 on my OEL4 (Linux x86).

$ ./rda.sh -T hcve Processing HCVE tests ... Available Pre-Installation Rule Sets: 1. Oracle Database 10g R1 (10.1.0) PreInstall (Linux-x86) 2. Oracle Database 10g R1 (10.1.0) PreInstall (Linux AMD64) 3. Oracle Database 10g R1 (10.1.0) PreInstall (IA-64 Linux) 4. Oracle Database 10g R2 (10.2.0) PreInstall (Linux AMD64) 5. Oracle Database 10g R2 (10.2.0) PreInstall (IA-64 Linux) 6. Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86) 7. Oracle Database 11g R1 (11.1.0) PreInstall (Linux AMD64) 8. Oracle Database 11g R1 (11.1.0) PreInstall (Linux-x86) 9. Oracle Application Server 10g (9.0.4) PreInstall (Linux) 10. Oracle Application Server 10g R2 (10.1.2) PreInstall (Linux) 11. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux AMD64) 12. Oracle Application Server 10g R3 (10.1.3) PreInstall (IA-64 Linux) 13. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux-x86) 14. Oracle Portal PreInstall (Generic) Available Post-Installation Rule Sets: 15. Oracle Portal PostInstall (generic) 16. RAC 10G DB and OS Best Practices (Linux) 17. Data Guard PostInstall (Generic) Enter the HCVE rule set number Hit 'Return' to accept the default (1) > 6 Enter value for < Planned ORACLE_HOME location or if set > Hit 'Return' to accept the default ($ORACLE_HOME) > /u01/app/oracle Test "Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86)" executed at Wed Aug 27 15:12:18 2008 Test Results ~~~~~~~~~~~~ ID NAME RESULT VALUE ===== ==================== ====== ======================================== 10 OS Certified? PASSED Adequate 20 User in /etc/passwd? PASSED userOK 30 Group in /etc/group? PASSED GroupOK 40 Input ORACLE_HOME RECORD /u01/app/oracle 50 ORACLE_HOME Valid? PASSED OHexists 60 O_H Permissions OK? PASSED CorrectPerms 70 Umask Set to 022? PASSED UmaskOK 80 LDLIBRARYPATH Unset? FAILED IsSet 100 Other O_Hs in PATH? FAILED OratabEntryInPath 110 oraInventory Permiss PASSED oraInventoryOK 120 /tmp Adequate? PASSED TempSpaceOK 130 Swap (in MB) RECORD 1051 140 RAM (in MB) FAILED 1001 150 Swap OK? FAILED InsufficientSwap 160 Disk Space OK? PASSED DiskSpaceOK 170 Kernel Parameters OK PASSED KernelOK 180 Got ld,nm,ar,make? PASSED ld_nm_ar_make_found 190 ulimits OK? FAILED StackTooSmall MaxLockMemTooSmall 200 EL4 RPMs OK? PASSED EL4rpmsOK 204 RHEL3 RPMs OK? PASSED NotRedHat 205 RHEL4 RPMs OK? PASSED NotRedHat 209 SUSE SLES9 RPMs OK? PASSED NotSuSE 212 Patch 3006854 Instal PASSED NotRHEL3 214 ip_local_port_range PASSED ip_local_port_rangeOK 220 Tainted Kernel? PASSED NotVerifiable 230 Other OUI Up? PASSED NoOtherOUI Result file: /home/oracle/rda/output/RDA_HCVE_A201DB10R2_lnx_res.htm

I also tried out option “RAC 10G DB and OS Best Practices (Linux)” which is part of Post Install but for some reason some of the components failed.

Enter the HCVE rule set number Hit 'Return' to accept the default (1) > 16 Enter the password for 'SYSTEM': Please re-enter it to confirm: Test "RAC 10G DB and OS Best Practices (Linux)" executed at Wed Aug 27 17:26:33 2008 Test Results ~~~~~~~~~~~~ ID NAME RESULT VALUE ===== ==================== ====== ======================================== 10 ORA_CRS_HOME RECORD /u01/app/crs 100 Database Name RECORD orcl 102 Database Version RECORD 10.2.0.4.0 104 Interconnect Network RECORD 106 DB Block Size RECORD 8192 108 DB File Multiblock R RECORD 16 120 Max Commit Propagati PASSED 0 130 SYS.AUDSES$ Cache Si PASSED 10000 132 SYS.IDGEN1$ Cache Si FAILED 20 140 Parallel Execution M FAILED 2148 150 Min Parallel Servers RECORD 1 152 Min Parallel Servers FAILED 0 200 $ORA_CRS_HOME Define PASSED Found 210 Remote Access PASSED All loaded 220 _USR_ORA_DEBUG / CRS FAILED blrraclnx1:? blrraclnx2:? 230 _USR_ORA_DEBUG / ORA FAILED blrraclnx1:? blrraclnx2:? 240 rmem_max PASSED OK 250 UDP Buffer Size PASSED OK 260 wmem_max PASSED OK 270 rmem_default PASSED OK 280 wmem_default PASSED OK 290 Sysrq Magic Keys PASSED OK 300 Oracle Executable Li PASSED linked 310 hangcheck-timer FAILED blrraclnx1:Unknown blrraclnx2:Unknown 320 aio-max-size Setting FAILED blrraclnx1:Unknown blrraclnx2:Unknown 330 Memory (32-bit) PASSED OK 340 Swap (32-bit) FAILED [blrraclnx1:]Swap<2RAM [blrraclnx2:]S..> 350 Swap (64-bit) PASSED OK 360 Patch List PASSED Complete Result file: /home/oracle/rda/output/RDA_HCVE_P400RAC_lnx_res.htm

We can find details about prescribed values at

https://metalink.oracle.com/metalink/plsql/docs/HCVE_P400RAC_lnx.htm

e.g To fix SYS.IDGEN1$ Cache Size, we need to set cache size for sequence SYS.IDGEN1$ greater than or equal to 10,000.

SQL> alter sequence SYS.IDGEN1$ cache 10200; Sequence altered.

Now we see that SYS.IDGEN1$ requirement is passed

132 SYS.IDGEN1$ Cache Si PASSED 10200

Refer Note:250262.1 – RDA 4 – Health Check / Validation Engine Guide for more information on HCVE.

27 August, 2008 by Amit Bansal 10g 0

Checking Database Feature Usage Stats

Today I came across view DBA_FEATURE_USAGE_STATISTICS (10g) which let’s us know whether particular Database Feature has been used till now or not. If yes, then it also let’s us know when it was used for the first time and also the last usage time. This can be helpful to check if anyone is using Database feature which is not licensed .

I have used filter DETECTED_USAGES >0 for checking the features which has been used in this Database.

SQL> SELECT NAME,DETECTED_USAGES AS "USAGE",CURRENTLY_USED,FIRST_USAGE_DATE,LAST_USAGE_DATE 2 FROM DBA_FEATURE_USAGE_STATISTICS WHERE DETECTED_USAGES >0 order by 1; NAME USAGE CURRE FIRST_USA LAST_USAG --------------------------------------------- ---------- ----- --------- --------- Automatic SQL Execution Memory 2 TRUE 14-AUG-08 21-AUG-08 Automatic SQL Execution Memory 1 TRUE 13-AUG-08 13-AUG-08 Automatic Segment Space Management (system) 2 TRUE 14-AUG-08 21-AUG-08 Automatic Segment Space Management (system) 1 TRUE 13-AUG-08 13-AUG-08 Automatic Segment Space Management (user) 1 TRUE 13-AUG-08 13-AUG-08 Automatic Storage Manager 2 TRUE 14-AUG-08 21-AUG-08 Automatic Storage Manager 1 TRUE 13-AUG-08 13-AUG-08 Automatic Undo Management 2 TRUE 14-AUG-08 21-AUG-08 Automatic Undo Management 1 TRUE 13-AUG-08 13-AUG-08 Character Set 2 TRUE 14-AUG-08 21-AUG-08 Character Set 1 TRUE 13-AUG-08 13-AUG-08 Dynamic SGA 1 TRUE 13-AUG-08 13-AUG-08 Internode Parallel Execution 2 TRUE 14-AUG-08 21-AUG-08 Locally Managed Tablespaces (system) 1 TRUE 13-AUG-08 13-AUG-08 Locally Managed Tablespaces (system) 2 TRUE 14-AUG-08 21-AUG-08 Locally Managed Tablespaces (user) 1 TRUE 13-AUG-08 13-AUG-08 Locally Managed Tablespaces (user) 2 TRUE 14-AUG-08 21-AUG-08 Parallel SQL Query Execution 2 TRUE 14-AUG-08 21-AUG-08 Partitioning (system) 1 TRUE 13-AUG-08 13-AUG-08 Partitioning (system) 2 TRUE 14-AUG-08 21-AUG-08 Protection Mode - Maximum Performance 1 TRUE 13-AUG-08 13-AUG-08 Protection Mode - Maximum Performance 2 TRUE 14-AUG-08 21-AUG-08 Real Application Clusters (RAC) 2 TRUE 14-AUG-08 21-AUG-08 Real Application Clusters (RAC) 1 TRUE 13-AUG-08 13-AUG-08 Recovery Area 1 TRUE 13-AUG-08 13-AUG-08 Recovery Area 2 TRUE 14-AUG-08 21-AUG-08 Segment Advisor 2 TRUE 14-AUG-08 21-AUG-08 Server Parameter File 2 TRUE 14-AUG-08 21-AUG-08 Server Parameter File 1 TRUE 13-AUG-08 13-AUG-08 Streams (system) 2 TRUE 14-AUG-08 21-AUG-08 Streams (system) 1 TRUE 13-AUG-08 13-AUG-08 Streams (user) 1 TRUE 13-AUG-08 13-AUG-08 Streams (user) 2 TRUE 14-AUG-08 21-AUG-08 Virtual Private Database (VPD) 2 TRUE 14-AUG-08 21-AUG-08 Virtual Private Database (VPD) 1 TRUE 13-AUG-08 13-AUG-08 XDB 2 TRUE 14-AUG-08 21-AUG-08 36 rows selected. 

So be careful when you use any Licensed feature (Unless you have already bought it) like Partitioning, AWR,Database Replay as this auditing is enabled by default 🙂

25 August, 2008 by Amit Bansal 10g 5

« Previous Page

Next Page »

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Recent Comments
Prabhav Pushkar on MGMTDB: Grid Infrastructure Management Repository
Azure BLOB Storage As Remote Backend for Terraform State File - AskDba.org Weblog on Azure Infrastructure Automation With Terraform: Configuration
tolu on 11g: Multiple failed login attempt can block New Application connections
venkatesh on Retrieving Database SID,Port information from Grid Control repository
Patrick on Solving ORA-1031 while connecting as “/ as sysdba” :
Trending
Resolving Shutdown Immediate Hang Situations
Identifying PostgreSQL Bloat and fixing it without downtime
Simplified Approach to Resolve ORA-4031
LGWR terminating instance due to error 338
Allocation Unit and Extents In ASM
How To Recover From Corrupted OCR Disk
Querying RDS PostgreSQL logs using Amazon Athena
Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Email Address

Join 188 other subscribers