Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, December 29, 2014

Resetting unrecoverable_time in v$datafile

When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you run in ARCHIVELOG mode. If you cannot afford to lose tables or indexes created with NOLOGGING, then make a backup after the unrecoverable table or index is created. 

Thursday, December 25, 2014

Raw Device Setup for ASM


Today one of the DBA requested to have the ASM instance based on raw devices. I provided the below  as a quick demo.

Wednesday, December 24, 2014

Using OCLUMON to analyze Cluster Health

Cluster Health Monitor & OCLUMON

The Cluster Health Monitor (CHM) stores real-time operating system metrics in the CHM repository that you can use for later triage with the help of Oracle Support should you have cluster issues.

It consists of System Monitor Service, Cluster Logger Service, CHM Repository

Thursday, December 11, 2014

ASMCA tabs Volume and ASM Cluster File System are grayed out

DBA wanted to create the ACFS but got the Volume and Cluster File System grayed out.


ORA-01427 While running DBMS_HM.RUN_CHECK

  2      DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'HC1');
  3  END;
  4  /

Wednesday, December 10, 2014

Configuring ACFS (Standalone Server) 11gR2


Client has standalone GI installation  and wants to check the ACFS feature.


Oracle ACFS is designed as a general-purpose, single-node and clusterwide file system that delivers support for customer file system application data maintained outside of Oracle Database. Users and applications can access and manage Oracle ACFS using native operating system file system application programming interfaces (APIs) and command-line interface (CLI) tools. Users can also manage Oracle ACFS with Oracle Enterprise Manager.

Tuesday, December 09, 2014

Configuring Oracle Grid Infrastructure for a standalone server (11gR2)

1- One of our DBA already had installed Oracle (11gR2 RDBMS on Linux 5.8
2- After some times, client requested to use the ASM as test to know its capabilities so DBA was given the task to have the GI installed and configured without disturbing the current environment.

Wednesday, December 03, 2014

Exadata: Defining the Threshold for Exadata Cell

In Exadata an alert is automatically triggered when a predefined hardware or software issue is detected, or when a metric exceeds a threshold. By default, there are no thresholds defined but you can define your own if you want.

Tuesday, December 02, 2014

Exadata: Index Elimination


In some cases, Exadata Smart Scan may deliver better query performance than using an index. However, this may not always be the case. Even in cases where an index delivers better query performance you might choose to remove it if you determine that the un-indexed query performance is acceptable and the index is otherwise unnecessary. Removing unnecessary indexes saves space and improves DML performance by eliminating the maintenance operations associated with the index. 

Monday, December 01, 2014

Exadata: Using Hybrid Columnar Compression


We have a table TRANSACTION_LOG with more than 10 million rows , estimated size of table is 640MB. We want to  examine the performance of Exadata Hybrid Columnar Compression and want compare predicted and actual compression ratios using an example dataset, so that we can use HCC for our logging tables. We also want to examine how bulk data loading and query operations are affected using the different compression modes.

Thursday, November 27, 2014

Exadata: Using Integrated Lights Out Manager (ILOM) CLI

What is ILOM?
Oracle ILOM is integrated service processor hardware and software that is preinstalled on Oracle servers, including the storage and database servers in Oracle Exadata. The service processor runs its own embedded operating system and has a dedicated Ethernet port to provide out-of-band server monitoring and management capabilities. Oracle ILOM can be accessed via a browser-based web interface or a command-line interface, and it also provides an SNMP interface and IPMI support.

Monday, November 24, 2014

Exadata: Configuring SSH Equivalency to use dcli

Exadata comes with many storage servers, The Cell Administration can be done on the command line as user celladmin with CellCLI,but it would be an annoying task to do all commands many times. Therefore, dcli was introduced to enable us to control multiple Cells with a single command for which you have to configure the SSH.

Exadata: Physical Disks, LUNs, and Cell Disks Mapping

As an Exadata DMA, you should know to map the physical disks in an Exadata Storage Server to Logical Units (LUNs) and map LUNs to Exadata cell disks in order to understand how Exadata’s disks are presented as usable storage entities.

Exadata: Locating Oracle Cluster Registry and Voting Disks

On Exadata, Oracle has elected to store the Oracle Cluster Registry (OCR) and voting disks on Oracle ASM disk groups, mapped to Exadata storage server grid disks. The OCR and voting disks are typically stored in the DBFS_DG ASM disk group and associated grid disks.

Monday, October 20, 2014

How to load flat files containing Arabic (any non-English) characters using SQLLDR

When you need to load non-ASCII data from a flat file into an Oracle database, the primary tool of choice is SQL*Loader, but unfortunately it does not automatically recognize the character encoding scheme of non-ASCII text (Arabic) files, you have to specify the correct encoding for the flat file in order to ensure a successful load. For that purpose, NLS_LANG needs to be set before launching SQL*Loader. If encoding type of the flat file is unknown, unfortunately there is no 100% reliable way to determine what encoding is used in that text file.

ORA-01704: String Literal Too Long. Cause: String Literal is Longer Than 4000 Characters

A text literal can have a maximum length of 4000 bytes.  In order to update a column greater than 4K, bind variables must be used and in the case of lobs if the data set is greater than 32k, use DBMS_LOB for piece wise manipulation.

Tuesday, August 19, 2014

Exadata: Restoring OCR and Vote Disks

Oracle allows you to restore your Oracle Cluster Registry using the ocrconfig –restore command. This command accepts a backup OCR file as its argument, which you can choose by running ocrconfig –showbackup and selecting the appropriate backup copy that resides on your compute node file system.

Monday, August 04, 2014

Getting Flash Storage info

Exadata flash storage is provided by Sun Flash Accelerator F20 PCI flash cards. Each PCI flash card has a device partitioned per FDom, yielding 16 flash devices. These flash devices are manifested as ExadataStorage Server flash disks and used for Smart Flash Cache and Smart Flash Logging.

Getting Disk Storage Details on the Exadata Storage Servers

If you want to know how storage is allocated, presented, and used in the Exadata storage cell, use below commands to get the related information.

Getting Storage Server Architecture Details

Exadata Storage Servers are self-contained storage platforms that house disk storage for an Exadata Database Machine and run Oracle’s Cell Services (cellsrv) software. A single storage server is also commonly referred to as a cell.

Thursday, March 27, 2014

Restoring OCR and Vote (11gR2 Linux)

Restoring OCR from backup

Testing Scenario
OCR is located on +OCR_VOTE diskgroup which was created with external redundancy, 
OCRVOTE is corrupted or diskgroup where OCRVOTE is located has problem.

Tuesday, March 25, 2014

Fact Sheet - Hekaton - TimesTen

Upon the request of one of my friend, below is the quickly prepared fact sheet for Microsoft's Hekaton and Oracle's TimesTen, will be updated more soon.

Thursday, January 09, 2014

Setting up Data Guard 11gR2 (RAC to RAC)

Setting the Data Guard for RAC primary to RAC Standby.

1- Primary site has 2 Node RAC 11gR2 installation on Oracle Linux 5.6. Primary database name is PROD
2- Standby site has Oracle GI 11gR2 with RDBMS software only installation on Linux 5.6 on 2 node Linux 5.6. Standby database name will be STAN.