During the recent Multi Node to Single Node R12 Clone, Encountered an strange error. When doing the database portion of the clone. Below command 'adclonectx.pl' creates the Context file
When running the same command, It dumped the below error,
file:/tmp/tmpCtxClone.xml: XML-20108: (Fatal Error) Start of root element expected.
AC-10006: Exception - org.xml.sax.SAXParseException: file:/tmp/tmpCtxClone.xml: XML-20108: (Fatal Error) Start of root element expected. thrown while creating OAVars object for file: /tmp/tmpCtxClone.xml
The new database context file has been created : /opt/oracle/product/11.1.0_SID/appsutil/SID_HostName.xml
At first site, I suspected that the issue is with format of the source xml file. Hence compared with the working XML file. Result is clean. Below portion of the error struck me
Thrown while creating OAVars object for file: /tmp//dummy.xml
Cause :
The /tmp is 100% full.
Fix:
Either remove the old files in /tmpdirectory OR export TEMP=/new/location where there is plenty of free space.
exec(): 0509-036 Cannot load program /opt/oraapps/SID_NAME/tech_st/10.1.2/bin/frmcmp_batch because of the following errors: rtld: 0712-001 Symbol deucxealert was referenced from module /opt/oraapps/I/tech_st/10.1.2/lib32/libig.so(), but a runtime definition of the symbol was not found. rtld: 0712-001 Symbol deucxget was referenced from module /opt/oraapps/SID_NAME/tech_st/10.1.2/lib32/libig.so(), but a runtime definition of the symbol was not found. rtld: 0712-001 Symbol depcxset was referenced from module /opt/oraapps/SID_NAME/tech_st/10.1.2/lib32/libig.so(), but a runtime definition of the symbol was not found.
These above errors show that the problem is not with the regenerating the PLL files instead the frmcmp.sh/frmcmp_batch.shitself failing to execute. After failed attempts to get some useful notes from metalink,I tried adrelink.sh.still no change. Also tried to change the PATH location by first putting $ORACLE_HOME/lib instead of $ORACLE_HOME/lib32.This time it threw new error. When comparing with the similar environment everything including seems to be same.
Then finally did a 'relink all' under 10.1.2 $ORACLE_HOME to generate the executables. This is has fixed the issue. The issue could have been due to the fact that 'adcfgclone.pl' was run using wrong path set.
I couldn't find much from Oracle Documentation.However there are very good bits and pieces on the net. Here are my takeaways from these readings.
1). HYBRID COLUMNAR COMPRESSION [HCC] is only for EXADATA.There seems to be some technical difficulties in implementing it for the traditional storage (Don't know what it is!).
2). Concept:
Compression is very effficient for a data which is quite repetetive. With repetitive values, we can replace the Values with the smaller values (eg Symbols/Special Characters) and create the metadata to map these values; this metadata can be used to decompress the data.In a nutshell ,Compression saves space at the cost of processing power (CPU Usage).
Below diagram shows a typical Oracle Data block Stores the Data.C1-5 are Columns.
Values with Lower Cardinality (more duplicated elements) are the potential candidates for the efficient compression. With this, Columns are set to have more duplicated values than the rows.
So with HCC, Columns are grouped together in a logical area called Compression Unit.Which is a logical grouping of multiple datablocks.
So the compression unit would look like.
3). There are 2 types of HCC
- Warehouse Compression - On Average, 10 Times Space Savings.
-Archive Compression - On Average, 15 Times Space Savings(70% at some customer sites)
4). Warehouse Compression
-Syntax:
CREATE TABLE X
COMPRESS FOR QUERY [LOW| HIGH]
AS
SELECT * FROM Y;
WhereHIGH | LOW - Specifies the Extent of Compression.
- Warehouse Compression is optimized for Query Performance.
5). Archive Compression
-Syntax:
CREATE TABLE X
COMPRESS FOR ARCHIVE [LOW| HIGH]
AS
SELECT * FROM Y;
WhereHIGH | LOW - Specifies the Extent of Compression.
- Archive Compression is optimised for Storage savings.
6). When a session places lock in the particular row HCC locks the whole Compression Unit Resulting in multiple row lock. Hence it may not be suitable for OLTP application where frequent update/insert occurs.
7). A Partition Table can have different type of HCC based on the Partition .For example. The latest partition can have COMPRESS BY QUERY whereas the older partition which hardly being queried can create using COMPRESS BY ARCHIVE.
8). The existing table can be converted to get benefit from HCC.However only newly added rows will be compressed .To compress the existing rows you need to do ALTER TABLE MOVE ;
9). You can check the characteristics of a row using DBMS_COMPRESSION package. To know more about the package click here
10)CPU Consumption for compression and decompression of the data is offset'd by less logical reads. Archive Compression consumes relatively more CPU compared to Warehouse Compression.
Lets consider an example to generate an SQL from select query to online datafile of a database.
The actual query is
select name from v$datafile;
From the above one we need to get the datafile online sql script.
ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.306.705691421' online ;
The final script would be,
set heading off
set lines 2000
select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;' from v$datafile;
Above command would give an output like
ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.306.705691421' online ;
ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.302.705691421' online ;
Two basic things to consider is
1) Whatever you need display as it is,should be given in ' ' quotes.
2) To get the value of a variable (in our case ‘NAME’ which gives Datafile file path and name) give it in Double Pipe. i.e, || ||
Taking our example, Lets break the SQL for better understanding.
SQL>select 'ALTER DATABASE DATAFILE''' || name || ''' online ;' from v$datafile;
Here “ALTER DATABASE DATAFILE” is the text we want to display at first. Hence the query“select 'ALTER DATABASE DATAFILE ' “. Remember there is a space between datafile and the ending single quote. Next we want datafile name with full location, which the actual query gets.
To get the value of the Name Column. We need to place column name in || ||
select 'ALTER DATABASE DATAFILE '|| name ||
This would command would give an something like
ALTER DATABASE DATAFILE/oradb01/TEST/data/apps ……
However we want 'to be included for the datafile name value.
Hence it should be
select 'ALTER DATABASE DATAFILE ' '' || name || ''
To add a single quote we need to add additional single quote to print single quote Hence we need to give 2 single quotes. Now we just need to add online; word to the end of it.
Which is'online ; ' . Note, you need to give space between the online and the starting single quote. 'online ; ' .Now adding it all would give
select 'ALTER DATABASE DATAFILE' ''|| name ||'' ' online ;' from v$datafile;
However the above need to be formatted as(remove the space between the quotes)
select 'ALTER DATABASE DATAFILE'''|| name ||''' online ;' from v$datafile;
Provides Highest Possible performance available - 40Gb/Sec each direction.
Uses ZDP Protocol (Zero Data Loss UDP Protocol) - Open Source Developed by Oracle Corp
Can also be used as RAC interconnect.
Each Infiband Link is able to carry the full data bandwidth of the entire Cell
2).Exadata comes to 2 DISK Type - SAS and SATA.
SAS - For Maximum Performance
SATA -For Maximum Capacity
3).Sun Database Machine doesn't include any software licence.
4).Sun Database Storage Server Details :
RAM - 72 GBP(18 x4 )
Disk Drives - 584 GB ( 4 x 2.5 146 GB)
Oracle Enterprise Linux
Oracle Database 11.2
5).A Full Rack Exadata can have 8 Database Servers,14 exadata cells,3 Infiband Switches
6).SQL Offloading is done by Database Server using iDB(Oracle Data Transfer Protocol)
7).CELLSRV component of Exadata storage server is used for Smart scan processing.CELLSERV Scans the blocks from storage and extract the relevant columns and send it back to the database server.
8).Database Server can break the SQL query and send the small portion of it to each exadata cells and upon retrieving the results ,Database Server constructs the results and required rows are returned to clients.
9).We can use the below query to find the list of functions which can be offloaded to the Exadata. Select * from v$sqlfn_metadata where offloadable='YES'
10).Exadata Storage server can perform Smart scans on encrypted tablespace and Columns.
11).With Performance of incremental Backup performance is significantly improved as the changes are tracked from individual block level rather than the large group of blocks.
Currently doing a R12 Single Instance Clone from PROD to TEST
I removed the Target ORACLE_HOME Direcories and untar'd the Source Oracle Home.I haven't touched the Oracle Inventory as it already had the Target Environment Details as its more of a refresh.
Though we know the ORACLE_HOME is already there.I tried adding it using the below command.
SEVERE:OUI-10197:Unable to create a new Oracle Home at /opt/oracle/product/11.1.0_IOFD. Oracle Home already exists at this location. Select another location.
Hence i went ahead and start doing the RMAN Restoration.While logging into 'sqlplus'.it throwed the bizzare error.
Enter user-name:
exec(): 0509-036 Cannot load program oracleIOFD because of the following errors:
0509-150 Dependent module libskgxp11.so could not be loaded.
0509-022 Cannot load module libskgxp11.so.
0509-026 System error: A file or directory in the path name does not exist.
ERROR:
ORA-12547: TNS:lost contact
Action Taken:
Did a relink.using relink all. After that i was able to login to sqlplus without any issue.However there were many errors with the relink .
Hence dettached the ORACLE_HOME from the Inventory and re-added as follow.
You may hit this when cleaning up Oracle Installation on Windows box.Sometimes it may happen that few files (eg.OCI.DLL) may not get deleted.When you try to delete it says "Access Denied."
As we don't know the process which has loaded this DLL.First we need to find the process and kill it.
Following command can be used to find the process which loads a particular dll.
We had a clear shutdown before the move.And upon starting the Cluster & Database, only one Node comes up.Which ever starts first will have full running CRS/CSS Stack ,the other node fails to start CSS and throws following error in the crsd.log.
20xx-xx-xx xx:xx:xx.xxx: [ COMMCRS][903]clsc_connect: (600000000033e030) no listener at (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_njzdb12_crs)) 20xx-xx-xx xx:xx:xx.xxx: [ CSSCLNT][1]clsssInitNative: connect failed, rc 9 20xx-xx-xx xx:xx:xx.xxx: [ CRSRTI][1] CSS is not ready. Received status 3 from CSS. Waiting for good.
Googling for the above error mostly points to the issue with /tmp/.oracle or /var/tmp/.oracle directory.The files under which has been deleted when the CRS was running.It was true that one of my colleague has deleted those log files.The easy fix told was to reboot both the machine.We tried but no luck.
Upon checking the cssd logs i could see the below error.
ocssd.log:[ CSSD]2010-03-05 17:48:21.908 [84704144] >TRACE: clssnmReadDskHeartbeat: node 3, vm-lnx-rds1173, has a disk HB, but no network HB, DHB has rcfg 0, wrtcnt, 2, LATS 1185024, lastSeqNo 2, timestamp 1267791501/1961474
Which just flashed to my brain to check the cluster interconnect.Yes.The Cluster Interconnect is not pinging between the servers.Informed to Sysadmins and got it fixed.Then it came out quite well.
Below PL/SQL block creates a file named 'sample.txt' with 'welcome' word in it under the Oracle Directory Named UTL_DIR [Can be created using CREATE DIRECTORY]
We can use utl_file_dir instead of CREATE DIRECTORY in that case we need to specify the whole path of the UNIX directory instead of the just the Directory name[i.e.., UTL_DIR]
set verify off set feedback off set lines 132 set pages 0 set heading off set space 0 set recsep off column text format a79 column line noprint
select DECODE(line,1,'create or replace ','')||text, line from dba_source where owner = upper('&&1') and type = upper('&&2') and name = upper('&&3') REFER MOS NOTE:SCRIPT:REGENERATING PACKAGE and PROCEDURE CODE from DBA_SOURCE [ID 1012473.7]
SELECT s.sid, s.serial#, st.sql_text sql_text FROM v$session s, v$sql st, v$process p WHERE s.sql_hash_value = st.hash_value AND s.sql_address = st.address AND s.paddr = p.addr and ( s.sid='&which_sid' )
To the full SQL Text:
select x.sql_text from v$session s,v$sqltext x where s.sql_address=x.address and s.sid = &which_sid order by sid, piece asc;
Find the Session details :
SELECT s.saddr, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.logon_time, s.status, p.program, p.spid FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid IN (&OS_PROCESS_ID);
To set the Time to in a required format:
alter session set nls_date_format='DD:MON:YYYY:HH24:MI:SS';
Then execute the required sql.
To Monitor the Progress of RMAN Backup:
SELECT sid, serial#, context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete" FROM v$session_longops WHERE opname LIKE 'RMAN:%' AND opname NOT LIKE 'RMAN: aggregate%';
