Oracle Database

Database Administration Tips: Upgrade from 11.2.0.3 to 19c Using GoldenGate [Coo...

2021-03-03T16:31:00.001+07:00

Database Administration Tips: Upgrade from 11.2.0.3 to 19c Using GoldenGate [Coo...: Introduction: In this post, I'll discuss the migration of 11.2.0.3 RAC DB on Oracle Linux 6 to 19.4 RAC on a different cluster on Ora...

"ORA-10567: Redo is inconsistent with data block" on Physical Standby 11g

2016-02-02T11:35:00.002+07:00

Today is a wonderful day. It's cool and sunny. We are in Spring. But I don't want to talk about the weather at this moment. I am sipping a cup of coffee and typing a note on my Oracle Database experience yesterday.
I have a Dataguard 11g using physical standby. After a long active time and some end of year batch jobs the size of UNDO tablespace so large. The result from checking show that the usage space is very little. So I decide resize the datafile of UNDO tablespace. The commands are success. And the file has a smaller size.
But on the Standby Database, the archivelogs are still recieved from Primary Database and cannot be applied. The alert log show the error:

Recovered data files to a consistent state at change 1443961551
Errors in file /oracle/app/diag/rdbms/proddr/PRODDR/trace/PRODDR_pr00_2930.trc:
ORA-00448: normal completion of background process
Errors in file /oracle/app/diag/rdbms/proddr/PRODDR/trace/PRODDR_mrp0_2918.trc:
ORA-00600: internal error code, arguments: [3020], [6], [11627], [25177451], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 6, block# 11627, file offset is 95248384 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 6: '/oradata/PRODDR/undotbs02.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
MRP0: Background Media Recovery process shutdown (PRODDR)

I have faced the lost of sync on Standby Database before. I have resolved by guide on http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html. But yesterday's error is different. The following are steps that I performed to fix the error.

Check the SCN on Primary:

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
1447116920 02-FEB-16 10.43.58.000000000 AM

Check the SCN on Standby:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1446590956

SQL> select scn_to_timestamp(1446590956) from dual;
SCN_TO_TIMESTAMP(1446590956)
---------------------------------------------------------------------------
02-FEB-16 09.26.47.000000000 AM

Check archivelog on Standby: logs are transfered but cannot be applied

SQL> select dest_id, sequence#, applied, deleted, to_char(first_time,'dd/mm/yyyy hh24:mi:ss') first_time
2 from (
3    select dest_id, first_time, sequence#, applied, deleted from v$archived_log order by first_time desc, dest_id
4 )
5 where rownum<=5
6 ;
   DEST_ID SEQUENCE# S APPLIED   DEL FIRST_TIME
---------- ---------- - --------- --- -------------------
         1      19220 A NO        NO 02/02/2016 10:30:18
         1      19219 A NO        NO 02/02/2016 10:13:53
         1      19218 A NO        NO 02/02/2016 09:56:23
         1      19217 A NO        NO 02/02/2016 09:39:35
         1      19216 A NO        NO 02/02/2016 09:30:16

Check process on Standby: MRP-apply process not running

SQL> select process, status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE

On Primary: backup datafile 6 as shown in the alertlog & copy to Standby host:

rman> backup format '/BACKUP/undofile2%U' datafile 6
# scp /BACKUP/undofile2d8qsrpml_1_1 oracle@standby-host:/BACKUP/

On Standby: catalog the new backuppiece; shutdown, mount DB and restore datafile 6:

rman> catalog start with '/BACKUP/undofile2d8qsrpml_1_1'
alter database recover managed standby database cancel
shutdown immediate;
startup mount;
RMAN> restore datafile 6; Starting restore at 01-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=577 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=768 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=959 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /oradata/PRODDR/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /BACKUP/undofile2d8qsrpml_1_1
channel ORA_DISK_1: piece handle=/BACKUP/undofile2d8qsrpml_1_1 tag=TAG20160201T181341
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 01-FEB-16

Starting apply process:

SQL> alter database recover managed standby database using current logfile disconnect from session

Now you can re-check the archivelog & MRP process on Standby as mentioned above.
Then taking a cup of coffe as the reward after a work hard.

Untimately, I want to say thank you to Bhavani and Arup Nanda. Yours' post help me to solve problems.

Configuration files for Oracle Business Intelligence system component

2016-01-06T16:43:00.001+07:00

BI Component	Configuration File	File Location
Oracle BI Server	NQSConfig.INI logconfig.xml	ORACLE_INSTANCE\config\OracleBIServerComponent\coreapplication_obisn For example: \instances\instance1\config\OracleBIServerComponent\coreapplication_obis1 Note: Although DBFeatures.ini is also located in this directory, do not edit this file directly. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about how to edit features for a database.
Oracle BI Presentation Services	instanceconfig.xml	ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn For example: \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 Note: Do not add elements to the instanceconfig.xml file unless you are overriding the stated default values. Override only those settings that are necessary for configuring the system to meet the needs of your organization.
Cluster Controller	ClusterConfig.xml ccslogging.xml	ORACLE_INSTANCE\config\OracleBIApplication\coreapplication For example: \instances\instance1\config\OracleBIApplication\coreapplication
Oracle BI Scheduler	instanceconfig.xml ccslogging.xml (for Cluster Controller)	ORACLE_INSTANCE\config\OracleBISchedulerComponent\coreapplication_obischn For example: \instances\instance1\config\OracleBISchedulerComponent\coreapplication_obisch1
JavaHost	config.xml logging_config.xml	ORACLE_INSTANCE\config\OracleBIJavaHostComponent\coreapplication_obijhn For example: \instances\instance1\config\OracleBIJavaHostComponent\coreapplication_obijh1
Oracle BI Presentation Services Plug-in	bridgeconfig.properties	MW_HOME\user_projects\domains\domain_name\config\fmwconfig\biinstances\coreapplication For example: mw_home\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication

Exporting data from Table to CSV file

2015-05-08T14:26:00.001+07:00

At a beautyful day, your manager said that: "give me the log of database". Of course, you can not tell your manager that the log is real large to select and read. I'm sure that you must perform the task cheerfully. And you'll think of exporting log data from tables to csv files. If that's what you're thinking about. Then the following is what you must do to finish your task.
I'm not the author. I only cache them here and retest.

CREATE OR REPLACE PACKAGE csv AS

-- --------------------------------------------------------------------------

-- Name : http://oracle-base.com/dba/miscellaneous/cvs.sql

-- Author : Tim Hall

-- Description : Basic CSV API. For usage notes see:

-- http://oracle-base.com/articles/9i/GeneratingCSVFiles.php

-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';

-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');

-- Requirements : UTL_FILE, DBMS_SQL

-- Ammedments :

-- When Who What

-- =========== ======== =================================================

-- 14-MAY-2005 Tim Hall Initial Creation

-- --------------------------------------------------------------------------

PROCEDURE generate (p_dir IN VARCHAR2,

p_file IN VARCHAR2,

p_query IN VARCHAR2);

END csv;

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY csv AS

-- --------------------------------------------------------------------------

-- Name : http://oracle-base.com/dba/miscellaneous/cvs.sql

-- Author : Tim Hall

-- Description : Basic CSV API. For usage notes see:

-- http://oracle-base.com/articles/9i/GeneratingCSVFiles.php

-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';

-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');

-- Requirements : UTL_FILE, DBMS_SQL

-- Ammedments :

-- When Who What

-- =========== ======== =================================================

-- 14-MAY-2005 Tim Hall Initial Creation

-- --------------------------------------------------------------------------

g_sep VARCHAR2(5) := ',';

PROCEDURE generate (p_dir IN VARCHAR2,

p_file IN VARCHAR2,

p_query IN VARCHAR2) AS

l_cursor PLS_INTEGER;

l_rows PLS_INTEGER;

l_col_cnt PLS_INTEGER;

l_desc_tab DBMS_SQL.desc_tab;

l_buffer VARCHAR2(32767);

l_file UTL_FILE.file_type;

BEGIN

l_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP

DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );

END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

-- Output the column names.

FOR i IN 1 .. l_col_cnt LOOP

IF i > 1 THEN

UTL_FILE.put(l_file, g_sep);

END IF;

UTL_FILE.put(l_file, l_desc_tab(i).col_name);

END LOOP;

UTL_FILE.new_line(l_file);

-- Output the data.

LOOP

EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

FOR i IN 1 .. l_col_cnt LOOP

IF i > 1 THEN

UTL_FILE.put(l_file, g_sep);

END IF;

DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);

UTL_FILE.put(l_file, l_buffer);

END LOOP;

UTL_FILE.new_line(l_file);

END LOOP;

UTL_FILE.fclose(l_file);

EXCEPTION

WHEN OTHERS THEN

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

IF DBMS_SQL.is_open(l_cursor) THEN

DBMS_SQL.close_cursor(l_cursor);

END IF;

RAISE;

END generate;

END csv;

SHOW ERRORS

exec sys.csv.generate('DUMP_DIR', 'testcsv.dat', 'select username, account_status from dba_users');

The result you'll get is the file as bellow:

If you want to split the output to many file:

CREATE OR REPLACE PACKAGE csv AS

-- --------------------------------------------------------------------------

-- Name : http://oracle-base.com/dba/miscellaneous/cvs.sql

-- Author : Tim Hall

-- Description : Basic CSV API. For usage notes see:

-- http://oracle-base.com/articles/9i/GeneratingCSVFiles.php

-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';

-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');

-- Requirements : UTL_FILE, DBMS_SQL

-- Ammedments :

-- When Who What

-- =========== ======== =================================================

-- 14-MAY-2005 Tim Hall Initial Creation

-- --------------------------------------------------------------------------

PROCEDURE generate (p_dir IN VARCHAR2,

p_file IN VARCHAR2,

p_query IN VARCHAR2,

p_row_per_file IN NUMBER :=0);

END csv;

CREATE OR REPLACE PACKAGE BODY csv AS

-- --------------------------------------------------------------------------

-- Name : http://oracle-base.com/dba/miscellaneous/cvs.sql

-- Author : Tim Hall

-- Description : Basic CSV API. For usage notes see:

-- http://oracle-base.com/articles/9i/GeneratingCSVFiles.php

-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';

-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');

-- Requirements : UTL_FILE, DBMS_SQL

-- Ammedments :

-- When Who What

-- =========== ======== =================================================

-- 14-MAY-2005 Tim Hall Initial Creation

-- --------------------------------------------------------------------------

g_sep VARCHAR2(5) := ',';

PROCEDURE generate (p_dir IN VARCHAR2,

p_file IN VARCHAR2,

p_query IN VARCHAR2,

p_row_per_file IN NUMBER :=0) AS

l_cursor PLS_INTEGER;

l_rows PLS_INTEGER;

l_col_cnt PLS_INTEGER;

l_desc_tab DBMS_SQL.desc_tab;

l_buffer VARCHAR2(32767);

l_file UTL_FILE.file_type;

i_row PLS_INTEGER;

i_page PLS_INTEGER;

BEGIN

l_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP

DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );

END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

i_page := 0;

l_file := UTL_FILE.fopen(p_dir, TO_CHAR(i_page)||p_file, 'w', 32767);

-- Output the column names.

FOR i IN 1 .. l_col_cnt LOOP

IF i > 1 THEN

UTL_FILE.put(l_file, g_sep);

END IF;

UTL_FILE.put(l_file, l_desc_tab(i).col_name);

END LOOP;

UTL_FILE.new_line(l_file);

-- Output the data.

i_row := 0;

LOOP

EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

FOR i IN 1 .. l_col_cnt LOOP

IF i > 1 THEN

UTL_FILE.put(l_file, g_sep);

END IF;

DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);

UTL_FILE.put(l_file, l_buffer);

END LOOP;

UTL_FILE.new_line(l_file);

i_row := i_row+1 ;

-- New file with column header

IF nvl(p_row_per_file,0) >= 10 THEN

IF MOD(i_row, p_row_per_file)=0 THEN

i_page := i_page + 1;

UTL_FILE.fclose(l_file);

l_file := UTL_FILE.fopen(p_dir, TO_CHAR(i_page)||p_file, 'w', 32767);

-- Output the column names.

FOR i IN 1 .. l_col_cnt LOOP

IF i > 1 THEN

UTL_FILE.put(l_file, g_sep);

END IF;

UTL_FILE.put(l_file, l_desc_tab(i).col_name);

END LOOP;

UTL_FILE.new_line(l_file);

END IF;

END LOOP;

UTL_FILE.fclose(l_file);

EXCEPTION

WHEN OTHERS THEN

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

IF DBMS_SQL.is_open(l_cursor) THEN

DBMS_SQL.close_cursor(l_cursor);

END IF;

RAISE;

END generate;

END csv;

Starting Weblogic Server & Managed Server without password

2014-06-18T00:08:00.002+07:00

You can use boot.properties file for starting without typing username password.

Please avoide blank lines in boot.properties file.

The clear text username and password will be encrypted the first time Weblogic server has been started.

Weblogic Server / Admin Server

cd ${DOMAIN_HOME}/servers/AdminServer/security

#pwd /u01/app/bihome/user_projects/domains/bifoundation_domain/servers/AdminServer/security

echo username=weblogic > boot.properties

echo password=******>>boot.properties

nohup ${DOMAIN_HOME}/bin/startWebLogic.sh >> Adminserver.log 2>&1 &

tail -f Adminserver.log

Managed Server

cd ${MW_HOME}/user_projects/domains/bifoundation_domain/servers/bi_server1/security

#pwd /u01/app/bihome/user_projects/domains/bifoundation_domain/servers/bi_server1/security

echo username=weblogic > boot.properties

echo password=******>>boot.properties

cd $DOMAIN_HOME

nohup ${DOMAIN_HOME}/bin/ startManagedWebLogic.sh >> ManagedServer.log 2>&1 &

tail -f ManagedServer.log

Moreover You can use environment variable:

export JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.management.username=weblogic -Dweblogic.management.password=******"

Start Oracle BI startNodeManager.sh has error "Unsupported cypher suite: TLS_RSA_EXPORT_WITH_RC4_40_MD5"

2014-06-17T18:12:00.004+07:00

Hi all,

Today after I have installed Oracle BI, I restart BI application server. To start BI Application server we have to perform 2 step as below:
1) source Environment variable:
. /u01/app/bihome/wlserver_10.3/common/bin/commEnv.sh
. /u01/app/bihome/user_projects/domains/bifoundation_domain/bin/setDomainEnv.sh
. /u01/app/bihome/user_projects/domains/bifoundation_domain/bin/setOBIDomainEnv.sh

2) start BI server:
i> nohup sh ${DOMAIN_HOME}/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=123456 > log_WL.out &
ii> nohup sh ${WL_HOME}/server/bin/startNodeManager.sh > log_NOD.out &
iii> nohup sh ${DOMAIN_HOME}/bin/startManagedWebLogic.sh bi_server1 t3://biapp.mylab.com:7001 > log_MWL.out &
iv> ${MW_HOME}/instances/instance1/opmnctl startall

But I have the error as below at startNodeManager.sh :

Jun 17, 2014 6:01:32 PM weblogic.nodemanager.server.AbstractServerManager log
INFO: Startup configuration properties loaded from "/u01/app/bihome/user_projects/domains/bifoundation_domain/servers/bi_server1/data/nodemanager/startup.properties"

java.io.IOException: Unsupported cypher suite: TLS_RSA_EXPORT_WITH_RC4_40_MD5
        at weblogic.nodemanager.server.SSLListener.init(SSLListener.java:82)
        at weblogic.nodemanager.server.NMServer.start(NMServer.java:206)
        at weblogic.nodemanager.server.NMServer.main(NMServer.java:377)
        at weblogic.NodeManager.main(NodeManager.java:31)

Jun 17, 2014 6:01:33 PM weblogic.nodemanager.server.NMServer main
SEVERE: Fatal error in node manager server
java.io.IOException: Unsupported cypher suite: TLS_RSA_EXPORT_WITH_RC4_40_MD5
        at weblogic.nodemanager.server.SSLListener.init(SSLListener.java:82)
        at weblogic.nodemanager.server.NMServer.start(NMServer.java:206)
        at weblogic.nodemanager.server.NMServer.main(NMServer.java:377)
        at weblogic.NodeManager.main(NodeManager.java:31)

To fix this
Add a new line to file /u01/app/bihome/wlserver_10.3/common/nodemanager/nodemanager.properties as below:

CipherSuite=SSL_RSA_EXPORT_WITH_RC4_40_MD5

Read more:

Metalink: WebLogic Server 10.3.x: Getting "Unsupported Cipher" Exceptions When Starting Node Manager(Doc ID 1307325.1)
http://docs.oracle.com/cd/E13222_01/wls/docs100/nodemgr/java_nodemgr.html

Unable to add disk using oracleasm and getting Instantiating disk: failed

2014-04-12T00:23:00.002+07:00

Unable to create the disk on RHEL6 using oracleasm

[root@racdr1 ~]# oracleasm createdisk ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done
[root@racdr1 ~]#

The reason behind this is under /etc/selinux/config file

make sure that it have following value

SELINUX=permissive

If not then set it and restart the system then issue the command again and there will be success.

[root@racdr1 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted

[root@racdr1 ~]# oracleasm createdisk ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@racdr1 ~]#

http://harvarinder.blogspot.com/2013/08/unable-to-add-disk-using-oracleasm-and.html

How To Change Java Heap Size In R12 To Avoid Java.Lang.OutOfMemoryError: Java Heap Space Error?

2013-11-05T22:16:00.002+07:00

Recently we had encountered a situation, wherein end users where not able to get the R12 login page and
it just hangs, but all the opmn processess were up and running – adopmnctl.sh status gives status as
alive for all the components viz., HTTP, oafm, oacore & forms. The same environment was available and
end users were able to access without any isuses 15 minutes before. This error happens only when
multiple people login to the same page and perform simillar activity like employee self service form
etc. So where and what exactly could be the problem.

This is how we approached and resolved the issue.

First we checked apache error log. The following error was reported in the error log.

==============================================================
[warn] [client IP Address ] oc4j_socket_recvfull timed out
[error] [client IP Address ] mod_oc4j: There is no oc4j process (for destination: application://oacore) available to service request.
[error] [client IP Address] mod_oc4j: request to OC4J [servername:OC4J AJP Port Range for Oacore]
failed: Connect failed
==============================================================

Above error message does give us a hint that the problem is with oacore, but as i said earlier the oacore is alive according to opmn.

Next, we verified the oacore log file.

($INST_TOP/logs/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err)

Error Message in the file

=======================================================
Exception in thread “OC4JMonitorThread” java.lang.OutOfMemoryError: Java heap space
=======================================================

While checking the error in file, parallely we opened another window to see the CPU and memory usage and we could see one java process was taking more than 100% CPU. This process was spawned by the opmn -d process and the process id didnt match with the oacore process id. Hint: adopmnctl.sh status will give the status as well the process id. It looked like a end client java process.

At this stage we had 3 options.

1. Kill the java process which is consuming high cpu.
2. Bounce oacore component
3. Adjust java (jvm) memory parameters

Each of the above options has its own implications and advantages. To minimize the downtime we decided to kill the java processes, and the moment we killed the java process, all the browsers which were hanging reported instantaneously Internal Server Error. This proved to be a bad decision.

So we moved to the next option, bouncing the oacore service which we are sure will resolve the issue (temporarily) and it did as expected. Basically when you bounce the services all the existing
connections and processess will be released which results in more available memory when you re-start the services.

Ok, now we tackled the problem and provided a temporary solution but we need to find a long term solution. This is option 3, adjusting java memory size.

Steps to change the heap size.

First, you need to identify how much is the maximum heap size that you can set. Click here.

Once you had identified the maximum heap size, we need to change the configuration files to make it
permanent.

Step 1: Edit opmn.xml file.

Location :$INST_TOP/ora/10.1.3/opmn/conf/

Open opmn.xml

Search for string Xms or Xmx or module-id=”OC4J”

This search should lead you to below location

‘<’process-type id=”oacore” module-id=”OC4J” status=”enabled” working-dir=”$ORACLE_HOME/j2ee/home”‘>’
‘<’category id=”start-parameters”‘>’
‘<’data id=”java-options” value=”-server -verbose:gc -Xmx512M -Xms128M ……]

The default value for Maximum (-Xmx) and Minimum (-Xms) heap sizes are 512M and 128M respectively.

Again here you have options, you can set both Xms and Xmx has the same value as Xmx if you feel all your sessoins require higher memory or set a lower value for Xms and the maximum value for Xmx. Dont forget to change the values under ‘<’category id=”stop-parameters”‘>’

opmn.xml also contains jvm configurations for other components – oafm & forms.

Step 2: Edit oc4j.properties file.

Location :($INST_TOP/ora/10.1.3/j2ee/oacore/config)

This step is optional since we had already made changes in opmn.xml but there is no harm in making the change here. This step will come handy for troubleshooting specific components of Oracle viz., configurator, iSupplier or any other option which heavily utilizes/consumes CPU/memory.

Search for string Xms or Xmx or wrapper.

Option 1: If you find any of the above parameters change the values corresponding to the value you had mentioned in opmn.xml or even more than that, as long as you dont exceed the maximum heap size limit.

Option 2: If you DO NOT find any of the above parameters, then make an entry like this, under the heading “# Java Object Cache Configuration Parameters”

wrapper.bin.parameters=-Xms[Value]M -Xmx[Value]M -XX:NewSize=256M -XX:MaxNewSize=256M

Step 3: Edit Applications Context file

vi $CONTEXT_FILE

Location:$INST_TOP/appl/admin/SID_hostname.xml

search for string s_oacore_jvm_start_options

Change Xms and Xmx value. Repeat the same step for parameter s_oacore_jvm_stop_options.

Changes made in Step 3 will take effect the next time you run autoconfig, whereas Step 1 & 2 changes will take effect the next time you bounce opmn services, but the values are not permanent in the sense these will be wiped off next time you run autoconfig. Yes you can preserve the changes by placing it inbetween Begin and End customizations.

You can also increase the Garbage Collection threads parameter (-XX:ParallelGCThreads) to a higher value if you have JDK 1.5 or more than 2 cpus or more memory. For more information on this you can refer to Metalink Note: 362851 – Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12.

http://oracamp.com/how-change-java-heap-size-r12-avoid-javalangoutofmemoryerror-java-heap-space-error

libXtst.so.6 cannot open shared object file error during Oracle Installation

2013-09-05T23:35:00.002+07:00

The "libXtst.so.6 cannot open shared object file" is quite common if you install any oracle product on 64bit system and misses something in the system
prerequisites. A quick resolution is installing the libXtst package for both 32 bit and 64 bit. But, last time while installing OID on 64bit Linux, I found that though libXtst package is already there, still this error is coming. See the error below and its solution.

The following error is coming during starting of installer.

$ ./runInstaller -ignoreSysPrereqs

-------------------------------------------------------------------------------------------------------------------
The OUI Screen may take around 5 to 30 seconds to come up depending upon system performance. Please Wait .......
-------------------------------------------------------------------------------------------------------------------

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-Red Hat Enterprise Linux AS release 4, redhat-2.1, redhat-3, SuSE-9 or UnitedLinux-1.0
Failed <<<<

>>> Ignoring required pre-requisite failures. Continuing...

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-02-11_01-46-40PM. Please wait ...[xxxxxx@Disk1]$ Oracle Universal Installer, Version 10.1.0.5.0 Production
Copyright (C) 1999, 2006, Oracle. All rights reserved.

Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2013-02-11_01-46-40PM/jre/1.4.2/lib/i386/libawt.so: libXtst.so.6: cannot open shared object file: No such file or directory occurred..
java.lang.UnsatisfiedLinkError: /tmp/OraInstall2013-02-11_01-46-40PM/jre/1.4.2/lib/i386/libawt.so: libXtst.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1560)
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1477)
at java.lang.Runtime.loadLibrary0(Runtime.java:788)
at java.lang.System.loadLibrary(System.java:834)
at sun.security.action.LoadLibraryAction.run(LoadLibraryAction.java:50)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(NativeLibLoader.java:38)

Solution

Although the following command is showing that libXtst package is installed for both 32bit and 64bit.
# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})
libXt-1.0.2-3.2.el5 (x86_64)
libXt-1.0.2-3.2.el5 (i386)

But oracle installer is trying to get file libXtst.so.6 from /usr/lib/libXtst.so.6 where libXtst.so.6 file doesn't exist. The 32bit version of the file is in /usr/X11R6/lib/libXtst.so.6 location.

So, I have created a softlink using following command and restarted the installation.
ln -s /usr/X11R6/lib/libXtst.so.6 /usr/lib/libXtst.so.6

cached from: http://oracle-latest-technology.blogspot.com/2013/02/libxtstso6-cannot-open-shared-object.html

Creating a Local Yum Repository Using an ISO Image

2013-04-15T22:20:00.001+07:00

Note

The system must have sufficient storage space to host a full Oracle Linux Media Pack DVD image (approximately 3.5 GB for Oracle Linux Release 6 Update 3).

To create a local yum repository (for example, if a system does not have Internet access):

On a system with Internet access, download a full Oracle Linux DVD image from the Oracle Software Delivery Cloud at http://edelivery.oracle.com/linux onto removable storage (such as a USB memory stick). For example,V33411-01.iso contains the Oracle Linux Release 6 Update 3 Media Pack for x86 (64 bit).
Transfer the removable storage to the system on which you want to create a local yum repository, and copy the DVD image to a directory in a local file system.
```
# cp /media/USB_stick/V33411-01.iso /ISOs
```

Create a suitable mount point, for example /var/OSimage/OL6.3_x86_64, and mount the DVD image on it.

# mkdir -p /var/OSimage/OL6.3_x86_64
# mount -o loop /ISOs/V33411-01.iso /var/OSimage/OL6.3_x86_64

Create an entry in /etc/fstab so that the system always mounts the DVD image after a reboot.
```
/ISOs/V33411-01.iso /var/OSimage/OL6.3_x86_64 loop defaults 0 0
```
In the /etc/yum.repos.d directory, edit the existing repository files, such as public-yum-ol6.repo or ULN-base.repo, and disable all entries by setting enabled=0.

Create the following entries in a new repository file (for example, /etc/yum.repos.d/OL63.repo).

[OL63]
name=Oracle Linux 6.3 x86_64
baseurl=file:///var/OSimage/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY
gpgcheck=1 
enabled=1

Clean up the yum cache.
```
# yum clean all
```

Test that you can use yum to access the repository.

# yum repolist
Loaded plugins: refresh-packagekit, security
...
repo id                    repo name                                status
OL63                       Oracle Linux 6.3 x86_64                  25,459
repolist: 25,459

Ref: http://docs.oracle.com/cd/E37670_01/E37355/html/ol_create_repo.html

Writing udev rules

2013-04-11T04:39:00.000+07:00

Built-in persistent naming schemes

udev provides persistent naming for some device types out of the box. This is a very useful feature, and in many circumstances means that your journey ends here: you do not have to write any rules.
udev provides out-of-the-box persistent naming for storage devices in the /dev/disk directory. To view the persistent names which have been created for your storage hardware, you can use the following command:

# ls -lR /dev/disk

This works for all storage types. As an example, udev has created /dev/disk/by-id/scsi-SATA_ST3120827AS_4MS1NDXZ-part3 which is a persistent-named symbolic link to my root partition. udev creates /dev/disk/by-id/usb-Prolific_Technology_Inc._USB_Mass_Storage_Device-part1 when I plug my USB flash disk in, which is also a persistent name.

Rule writing

Rule files and semantics

When deciding how to name a device and which additional actions to perform, udev reads a series of rules files. These files are kept in the /etc/udev/rules.d directory, and they all must have the .rules suffix.
Default udev rules are stored in /etc/udev/rules.d/50-udev.rules. You may find it interesting to look over this file - it includes a few examples, and then some default rules proving a devfs-style /dev layout. However, you should not write rules into this file directly.
Files in /etc/udev/rules.d/ are parsed in lexical order, and in some circumstances, the order in which rules are parsed is important. In general, you want your own rules to be parsed before the defaults, so I suggest you create a file at /etc/udev/rules.d/10-local.rules and write all your rules into this file.
In a rules file, lines starting with "#" are treated as comments. Every other non-blank line is a rule. Rules cannot span multiple lines.
One device can be matched by more than one rule. This has it's practical advantages, for example, we can write two rules which match the same device, where each one provides its own alternate name for the device. Both alternate names will be created, even if the rules are in separate files. It is important to understand that udev will not stop processing when it finds a matching rule, it will continue searching and attempt to apply every rule that it knows about.

Rule syntax

Each rule is constructed from a series of key-value pairs, which are separated by commas. match keys are conditions used to identify the device which the rule is acting upon. When all match keys in a rule correspond to the device being handled, then the rule is applied and the actions of the assignment keys are invoked. Every rule should consist of at least one match key and at least one assignment key.
Here is an example rule to illustrate the above:

KERNEL=="hdb", NAME="my_spare_disk"

The above rule includes one match key (KERNEL) and one assignment key (NAME). The semantics of these keys and their properties will be detailed later. It is important to note that the match key is related to its value through the equality operator (==), whereas the assignment key is related to its value through the assignment operator (=).
Be aware that udev does not support any form of line continuation. Do not insert any line breaks in your rules, as this will cause udev to see your one rule as multiple rules and will not work as expected.

Basic Rules

udev provides several different match keys which can be used to write rules which match devices very precisely. Some of the most common keys are introduced below, others will be introduced later in this document. For a complete list, see the udev man page.

KERNEL - match against the kernel name for the device
SUBSYSTEM - match against the subsystem of the device
DRIVER - match against the name of the driver backing the device

After you have used a series of match keys to precisely match a device, udev gives you fine control over what happens next, through a range of assignment keys. For a complete list of possible assignment keys, see the udev man page. The most basic assignment keys are introduced below. Others will be introduced later in this document.

NAME - the name that shall be used for the device node
SYMLINK - a list of symbolic links which act as alternative names for the device node

As hinted above, udev only creates one true device node for one device. If you wish to provide alternate names for this device node, you use the symbolic link functionality. With the SYMLINK assignment, you are actually maintaining a list of symbolic links, all of which will be pointed at the real device node. To manipulate these links, we introduce a new operator for appending to lists: +=. You can append multiple symlinks to the list from any one rule by separating each one with a space.

KERNEL=="hdb", NAME="my_spare_disk"

The above rule says: match a device which was named by the kernel as hdb, and instead of calling it hdb, name the device node as my_spare_disk. The device node appears at /dev/my_spare_disk.

KERNEL=="hdb", DRIVER=="ide-disk", SYMLINK+="sparedisk"

The above rule says: match a device which was named by the kernel as hdb AND where the driver is ide-disk. Name the device node with the default name and create a symbolic link to it named sparedisk. Note that we did not specify a device node name, so udev uses the default. In order to preserve the standard /dev layout, your own rules will typically leave the NAME alone but create some SYMLINKs and/or perform other assignments.

KERNEL=="hdc", SYMLINK+="cdrom cdrom0"

The above rule is probably more typical of the types of rules you might be writing. It creates two symbolic links at /dev/cdrom and /dev/cdrom0, both of which point at /dev/hdc. Again, no NAME assignment was specified, so the default kernel name (hdc) is used.

Matching sysfs attributes

The match keys introduced so far only provide limited matching capabilities. Realistically we require much finer control: we want to identify devices based on advanced properties such as vendor codes, exact product numbers, serial numbers, storage capacities, number of partitions, etc.
Many drivers export information like this into sysfs, and udev allows us to incorporate sysfs-matching into our rules, using the ATTR key with a slightly different syntax.
Here is an example rule which matches a single attribute from sysfs. Further detail will be provided later in this document which will aid you in writing rules based on sysfs attributes.

SUBSYSTEM=="block", ATTR{size}=="234441648", SYMLINK+="my_disk"

Device hierarchy

The Linux kernel actually represents devices in a tree-like structure, and this information is exposed through sysfs and useful when writing rules. For example, the device representation of my hard disk device is a child of the SCSI disk device, which is in turn a child of the Serial ATA controller device, which is in turn a child of the PCI bus device. It is likely that you will find yourself needing to refer to information from a parent of the device in question, for example the serial number of my hard disk device is not exposed at the device level, it is exposed by its direct parent at the SCSI disk level.
The four main match keys introduced so far (KERNEL/SUBSYSTEM/DRIVER/ATTR) only match against values corresponding to the device in question, and do not match values from parent devices. udev provides variants of the match keys that will search upwards through the tree:

KERNELS - match against the kernel name for the device, or the kernel name for any of the parent devices
SUBSYSTEMS - match against the subsystem of the device, or the subsystem of any of the parent devices
DRIVERS - match against the name of the driver backing the device, or the name of the driver backing any of the parent devices
ATTRS - match a sysfs attribute of the device, or a sysfs attribute of any of the parent devices

With hierarchy considerations in mind, you may feel that rule writing is becoming a little complicated. Rest assured that there are tools that help out here, which will be introduced later.

Heterogenous connection from Oracle database to Microsoft SQL server

2013-01-04T13:34:00.002+07:00

Using Oracle's heterogenous services also known as HS, we can make a connection to non-Oracle databases and access the data remotely. I used MS SQL server as the target non-Oracle database on Windows and used HS to access from Oracle database residing on Linux. Here, we'll see step by step instructions on how to set up a connection from Oracle database to Microsoft SQL server.

In this example, I've used the following:
Source Oracle database on Linux:test-oralin
Target SQL server on Windows:test-mssqlwin
DSN name:MSORA

Our objective is to make a connection from test-oralin to test-mssqlwin and access the SQL server database from Oracle. Here are the steps:

1. On test-mssqlwin, install Oracle Home. Example: E:\OraHome
Note: Only Oracle binaries are needed here so you need not create a database.

2. On test-mssqlwin, install ODBC drivers so that Oracle can access SQL server

3. On test-mssqlwin, configure ODBC by setting up a system DSN(Data Source Name)
Typically you can find this at Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC) however depending on the Windows version, this may differ slightly.
On System DSN tab, click Add button and then choose the driver you want to use to connect to SQL server. Continue with the ODBC configuration by entering the details of the SQL server you wish to connect to along with necessary credentials. For this test, I've chosen "MSORA" as my DSN name.

4. On test-mssqlwin, setup the heterogenous services init file.
Look for inithsodbc.ora in $ORACLE_HOME/hs/admin/ which in our case would be E:\OraHome\hs\admin. Make a copy of it and rename it to initMSORA.ora. Remember we had chosen "MSORA" as our DSN name. Open the file and enter the DSN name that you just created recently i.e "MSORA". This is how E:\OraHome\hs\admin\initMSORA.ora should look like:
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSORA
HS_FDS_TRACE_LEVEL = OFF

5. On test-mssqlwin, configure listener.ora. Take a sample copy from E:\OraHome\hs\admin\listener.ora.sample and copy it to E:\OraHome\network\admin\listener.ora
This is how your E:\OraHome\network\admin\listener.ora should look like:

LISTENER_MSORA =
(ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=test-mssqlwin)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER_MSORA=
(SID_LIST=
      (SID_DESC=
         (SID_NAME=MSORA)
         (ORACLE_HOME = E:\OraHome)
         (PROGRAM=hsodbc)
      )
)

Note: SID_NAME should be your DSN name. PROGRAM=hsodbc tells Oracle to use heterogenous services. Restart your listener to make sure the settings are in effect.

We have completed our configuration on the target MS SQL server i.e test-mssqlwin. Now let us move to our source Oracle database i.e test-oralin from where we wish to access test-mssqlwin

6. On test-oralin, configure your $ORACLE_HOME/network/admin/tnsnames.ora
MSORA =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-mssqlwin)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = MSORA)
    )
   (HS=OK)
)
Note:SID should be the DSN name created on test-mssqlwin. PORT should be same as what was configured for the listener on Oracle Home that was installed on test-mssqlwin. HS=OK means Oracle will use heterogenous services.

7. Verify the connection from test-oralin to test-mssqlwin by doing a tnsping MSORA from test-oralin.

8. We are all set now so let us access the SQL server database now from Oracle.
On test-oralin, connect to the Oracle database from where you want to access SQL server database located on test-mssqlwin
Make sure that global_names parameter is set to false
SQL> sho parameter global_names

NAME               TYPE             VALUE
------------------ ---------------- -------
global_names       boolean          FALSE

Create a database link:
CREATE DATABASE LINK <> CONNECT TO <> IDENTIFIED BY <> USING '<>';
Example:
SQL> CREATE DATABASE LINK MSORA CONNECT TO REPL IDENTIFIED BY REPL USING 'MSORA';

Database link created.

Note: REPL is a user on SQL server database i.e test-mssqlwin with REPL as it's password. 'MSORA' was given as a connection string in tnsnames.ora. Here, we have chosen MSORA as dblinkname, you could have any name.

We have completed our configuration. You may start accessing the data now.
Example:
SQL> desc emp@msora
Name          Null?      Type
------------- --------   --------------
EMPID         NOT NULL   NUMBER(1)
EMPNAME                  VARCHAR2(60)
CREATED                  DATE

reference: http://www.ritzyblogs.com/OraTalk/PostID/107/Heterogenous-connection-from-Oracle-database-to-Microsoft-SQL-server

http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

Differences and Similarities Between Index Coalesce and Shrink Space

2012-09-20T00:44:00.001+07:00

The first thing to point out is that each command has a slightly different purpose.

Coalesce is designed specifically to reduce fragmentation within an index but not to deallocate any freed up blocks which are placed on the freelist and recycled by subsequent block splits.

Shrink is designed specifically to reduce the overall size of an index segment, resetting the High Water Mark (HWM) and releasing any excess storage as necessary.

The key difference being that Shrink must reorganise the index leaf blocks in such a way that all the freed up, now empty blocks are all grouped together at “one end” of the index segment. All these blocks can then be deallocated and removed from the index segment. This means that specific leaf block entries must be removed from these specific blocks, in order to free up the leaf blocks in this manner.

Although Coalesce in 10g performs the operation in a similar manner to that of the Shrink Space, it can be more “lazy” in how it deals with the subsequent empty blocks and places then on the segment freelist as necessary.

COALESCE and SHRINK SPACE COMPACT are logically equivalent commands. Both options will “defragment” an index by “merging” index entries where possible thus reducing the number of blocks within the logical index structure. Both will result in the same number of leaf blocks within the index and both will result in the index height not being changed.

However, there are two key differences.

1) The SHRINK SPACE COMPACT option has the disadvantage of being more expensive to process as it has to concern itself with ensuring all necessary blocks can be emptied from the physical “end” of the index segment to be subsequently deallocated. This will result in more undo and redo being generated during the defragmentation of the index than would have been generated by the same corresponding COALESCE command.

2) The SHRINK SPACE COMPACT option has the advantage of being able to immediately deallocate the empty blocks, thereby reducing the actual size of the index segment by issuing a subsequent SHRINK SPACE option (although of course this can be performed in the one step by issuing SHRINK SPACE in the first place). However, the COALESCE option will not be able to just deallocate the free space. A subsequent Index SHRINK SPACE command on a previously coalesced index will require additional undo and redo than that of a previously “Shrunk” index as the necessary empty blocks are removed from the freelist and redistributed to allow for the de-allocation of blocks and the resetting of the High Water Mark of the index segment.

Note also that the Shrink option can only be used in Automatic Segment Space Management (ASSM) tablespaces.

Use Coalesce when the intent is to just defragment an index, knowing that the freed leaf blocks will be recycled by subsequent block splits, as it uses less resources than an equivalent Index Shrink Space.

Use Shrink Space when the intent is to reduce the actual storage allocated to an index, for example in the scenario where a table has permanently reduced its size and the index is unlikely to reuse the freed storage.

This demo highlights the Differences (and similarities) between an Index Coalesce and an Index Shrink Space.

Note however, that an index REBUILD might actually use substantially less resources than either a Coalesce or a Shrink Space and might reduce the height of an index as well.

But that’s a discussion for another day …

http://richardfoote.wordpress.com/2008/02/06/differences-and-similarities-between-index-coalesce-and-shrink-space/

Scripts

http://jonathanlewis.wordpress.com/2008/05/14/index-efficiency/

http://jonathanlewis.wordpress.com/2010/02/28/index-efficiency-2/
http://jonathanlewis.wordpress.com/2010/03/03/index-efficiency-3/

Who is using your UNDO space?

2012-06-12T10:37:00.001+07:00

Sure you've faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space... until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering 'what happened?' or 'who the User did this?', this post may be helpful.

There are some views that show information related to undo activity:

* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.

V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176    <-- Which stands for undo change vector size
ORDER BY a.value DESC

Good luck with your UNDO-eating monsters...

Who is using your UNDO space? Improved Script"

I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value > 0
ORDER BY a.value DESC

If you want to run this script on versions 10g1 and 10g2, just replace the statistic# with 176; 216 if your database is 11gR1... or use the following version independent script!!! (Hope we don't change the statistic name).

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND b.name = 'undo change vector size'
AND a.value > 0
ORDER BY a.value DESC

http://oracledisect.blogspot.com/2008/05/who-is-using-your-undo-space.html

Starting and Stopping Oracle Enterprise Manager 11g Grid Control

2012-06-04T09:56:00.003+07:00

This document will detail the steps required to stop and start Oracle Enterprise Manager 11g Grid Control and all its components. For information on installing Oracle Enterprise Manager 11g Grid Control see the post Install Oracle Enterprise Manager Grid Control 11gR1 on Linux.

In this document I will be making references to the OMS_HOME and AGENT_HOME. If you do not happen to know what those locations are you can find them in the /etc/oratab file.

[oracle@gc bin]$ grep -E 'oms|agent' /etc/oratab
*:/u02/app/oracle/product/weblogic/oms11g:N
*:/u02/app/oracle/product/weblogic/agent11g:N
[oracle@gc bin]$

So in this example the OMS_HOME would be /u02/app/oracle/product/weblogic/oms11g and AGENT_HOME would be/u02/app/oracle/product/weblogic/agent11g.

NOTE: The Oracle Enterprise Manger 11g Grid Control install process puts a script called gcstartup in /etc/init.dthat will stop and start the Oracle Management Service and Agent on OS startup/shutdown. It does not start or stop the repository database. If you would like to stop the automated startup/shutdown of Grid Control services place comments in front of the OMS and AGENT home directories in the /etc/oratab file or remove the script /etc/init.d/gcstartup.

Stopping Oracle Enterprise Manager 11g Grid Control

Stop the Oracle Management Service

From the OMS_HOME directory run the following to stop the OMS and WebTier services.

OMS_HOME/bin/emctl stop oms –all

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/oms11g/bin/emctl stop oms -all
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
[oracle@gc ~]$

Note if you do not include the –all flag the HTTP services for the WebLogic Server will not be shutdown.

Stop the Oracle Management Agent

From the AGENT_HOME directory run the following to stop the Agent.

AGENT_HOME/bin/emctl stop agent

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/agent11g/bin/emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.
[oracle@gc ~]$

Stop the repository database

[oracle@gc ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 11 11:41:21 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc ~]$

Stop the Listener

[oracle@gc ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2010 11:42:03

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gc)(PORT=1521)))
The command completed successfully
[oracle@gc ~]$

That is it. Oracle Enterprise Manager 11g Grid Control and all associated services are now shutdown.

Starting Oracle Enterprise Manager 11g Grid Control

Start the Listener

[oracle@gc ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2010 12:58:01

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u02/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/gc/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gc.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gc)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-MAY-2010 12:58:01
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u02/app/oracle/diag/tnslsnr/gc/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gc.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@gc ~]$

Start the repository database

[oracle@gc ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 11 12:58:49 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size                  1336260 bytes
Variable Size             805309500 bytes
Database Buffers          452984832 bytes
Redo Buffers               12582912 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc ~]$

Start the Oracle Management Service

From the OMS_HOME directory run the following to start the OMS and WebTier services

OMS_HOME/bin/emctl start oms

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/oms11g/bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Successfully Started
Oracle Management Server is Up
[oracle@gc ~]$

Start the Oracle Management Agent

From the AGENT_HOME directory run the following to start the Agent.

AGENT_HOME/bin/emctl start agent

[oracle@gc ~]$ /u02/app/oracle/product/weblogic/agent11g/bin/emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ........ started.
[oracle@gc ~]$

Enterprise Manager 11g Grid Control and all associated services and can be accessed at https://servername.domain:7799/em.

http://www.oracledistilled.com/grid-control/starting-and-stopping-oracle-enterprise-manager-11g-grid-control/

How to remove Agent/targets from Grid Control neatly

2012-05-06T20:17:00.000+07:00

I was given the task to remove the target host/agents and other listed targets of Server1 from Oracle grid control. At first I thought it was very easy and I was definitely correct. Here’s what happened:

From OEM Grid Control I clicked on targets and selected the Server1 from the list. Then from its homepage I clicked on the the targets and then I eventually select all the targets except of course from the agent. Note: (the management agent of server1 must be running to do this). The targets were successfully deleted and now the only thing left is the host and the agent of Server1.

To remove the agent and host form the list, I stop the management agent of server1 then I went back to my OEM Grid control to remove the host Server1. Now there comes the challenge, No matter how many times I tried to delete the host Server1 from oem grid control It seems like the page is just running and nothing happens. No matter how long I’ve waited the host Server1 still remains on the list.

Now to clean it manually, I connect to the OEM Grid Repository and perform the following:

SQL> select target_name from mgmt_targets where target_type=’oracle_emd’;

This command show me the list of all the registered targets from my OEM and I can see that Server1 is still there. Then to manually remove Server1 from the list, I then execute:

SQL> exec mgmt_admin.cleanup_agent(‘Server1:3872′);

It will take a while at least 3 to 5 mins the most. Once it was completed I quickly checked my OEM Grid Control Target List and voila!!! Server1 was successfully removed from the target list together with its agent.

Thanks to Metalink Note:454081.1 and to Luis Cardenas post (http://www.lazydba.com/oracle/0__146127.html) who gave me the idea to solve my problem.

http://dbamac.wordpress.com/2008/08/06/how-to-remove-agenttargets-from-grid-control-neatly/

Mount Windows folder on Linux

2012-04-20T11:20:00.000+07:00

Sometimes, we need to mount a Windows folder on Linux. In order to do it, let’s follow these steps:
1. Create a directory in Windows, share it and give full permission:

2. Create new directory in /tmp directory on Linux, name it to “test_dir” and mount shared Windows directory into that directory by running below code:
mount -t cifs -o username=#####,password=###### //192.168.##.##/test /tmp/test_dir

3. Now, by creating new file and folder in Windows directory, we can get them from Linux box. Let’s create a file and a directory in Windows folder

4. At last, let’s check it from Linux, whether we can see the file and the directory or not

As you can see, we can mount any Windows folder in Linux with very easy steps

http://kamranagayev.com/2009/04/06/mount-windows-folder-on-linux/

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

2012-03-22T08:53:00.001+07:00

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables .

Scenario

I was getting this error when I was trying to drop a schema.

Sql> drop user test cascade;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

Logged in as Test user and checked for queue tables .

SQL> select * from user_queue_tables;
no rows selected

SQL> select OWNER, NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED
from DBA_QUEUES where OWNER='TEST';
no rows selected

However I was able to locate few queue tables in the schema when I used

Sql> select table_name from user_tables;

Got few Tables starting with AQ$_ *******

Tried to delete these tables using the DBMS_AQADM.DROP_QUEUE_TABLE procedure . However ended with the following error message .

SQL> begin

2 DBMS_AQADM.DROP_QUEUE_TABLE(‘AQ$_test');

3 end;

5 /

begin

ERROR at line 1:

ORA-24002: QUEUE_TABLE SCOTT.AQ$_test does not exist

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084

ORA-06512: at "SYS.DBMS_AQADM", line 197

ORA-06512: at line 2

Working solution :

Logged in as sys and issued the following command :

alter session set events '10851 trace name context forever, level 2';

and then dropped all the AQ$.***** tables from sys successfully.

Finally dropped the particular schema .

Issue resolved

Linux shell script date formatting

2012-03-16T15:38:00.000+07:00

Linux date FAQ: How do I create a formatted date in Linux? (Most likely, "How do I create a formatted date I can use in a Linux shell script?")
I just ran into a case where I needed to create a formatted date in a Linux shell script, where the date format looks like this:

2010-07-11

To create this formatted date string, I just used the Linux date command, using the "+" sign to specify that I want to use the date formatting option, like this:

thedate=`date +"%Y-%m-%d"`

The Linux date command shown in this line of code creates the date string in the format I want, and then I save the date output to a Linux shell script variable named "thedate".
This just shows one possible way to format a date in a Linux shell script. Of course you can use '-', '.', and many other characters as date field separators, or use no characters at all.

More Linux date formatting information

For more information on other Linux date formatting options (there are many more date formatting options), take a look at the man page for the date command on your Linux system, like this:

man date

When I run that command on a CentOS Linux system, it shows these date formatting operators:

%%     a literal %
%a     locale’s abbreviated weekday name (e.g., Sun)
%A     locale’s full weekday name (e.g., Sunday)
%b     locale’s abbreviated month name (e.g., Jan)
%B     locale’s full month name (e.g., January)
%c     locale’s date and time (e.g., Thu Mar  3 23:05:25 2005)
%C     century; like %Y, except omit last two digits (e.g., 21)
%d     day of month (e.g, 01)
%D     date; same as %m/%d/%y
%e     day of month, space padded; same as %_d
%F     full date; same as %Y-%m-%d
%g     last two digits of year of ISO week number (see %G)
%G     year of ISO week number (see %V); normally useful only with %V
%h     same as %b
%H     hour (00..23)
%I     hour (01..12)
%j     day of year (001..366)
%k     hour ( 0..23)
%l     hour ( 1..12)
%m     month (01..12)
%M     minute (00..59)
%n     a newline
%N     nanoseconds (000000000..999999999)
%p     locale’s equivalent of either AM or PM; blank if not known
%P     like %p, but lower case
%r     locale’s 12-hour clock time (e.g., 11:11:04 PM)
%R     24-hour hour and minute; same as %H:%M
%s     seconds since 1970-01-01 00:00:00 UTC
%S     second (00..60)
%t     a tab
%T     time; same as %H:%M:%S
%u     day of week (1..7); 1 is Monday
%U     week number of year, with Sunday as first day of week (00..53)
%V     ISO week number, with Monday as first day of week (01..53)
%w     day of week (0..6); 0 is Sunday
%W     week number of year, with Monday as first day of week (00..53)
%x     locale’s date representation (e.g., 12/31/99)
%X     locale’s time representation (e.g., 23:13:48)
%y     last two digits of year (00..99)
%Y     year
%z     +hhmm numeric timezone (e.g., -0400)
%:z    +hh:mm numeric timezone (e.g., -04:00)
%::z   +hh:mm:ss numeric time zone (e.g., -04:00:00)
%:::z  numeric time zone with : to necessary precision (e.g., -04, +05:30)
%Z     alphabetic time zone abbreviation (e.g., EDT)

Because Unix and Linux systems are very consistent in this area, you should be able to write a date formatting command on one Unix or Linux system, and use that same command on another Unix system.

http://www.devdaily.com/linux-unix/date-format-formatted-linux-shell-script-command

Script to Check Physical Standby in Sync with the Primary

2012-02-27T16:50:00.000+07:00

http://shrikantrao.wordpress.com/2011/08/03/script-to-check-physical-standby-in-sync-with-the-primary/
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=861595.1

A simple script, on single click will provide us the status of DR sync with primary database.

Script 1) .bat file

sqlplus "sys/sys@prod as sysdba" @E:\DRSTATUS\onproduction.sql
sqlplus "sys/sys@standby as sysdba" @E:\DRSTATUS\onstandby.sql >> E:\cloverdba1_Clover_report\DRSTATUS\drsysn.out

I am appending the second output (for standby) to the same file. So that the output for both will be displayed in a single .out file.

Script 2) onproduction.sql file (On Primary)

set time on
set lines 200

spool E:\DRSTATUS\drsysn.out

prompt +++++++++++LAST PRODUCTION SEQUENCE GENERATED++++++++++++++++++

select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') Date from dual;
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

spool off
exit

Script 3) onstandby.sql file (On Physical Standby)

set time on
set lines 200

prompt ++++++++++++++++++++LAST SEQUENCE RECIEVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
;

prompt ++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
exit

Explanation:

1. To check availability of redo on standby. This can be done, by checking the output of “last Sequence Generated” in script 2(at primary) with “Last Sequence Received” in script 3(at standby). If the value differs that shows the archive is missing on standby.
2. To Check for redo applied on standby. The column “difference” in script 3(at standby) should be 0. This specifies that the primary is in sync with physical standby.
3. Second query of 3rd script will provide detail on archive gap, which redo is still missing at standby.

To demo the archive lag, I have cancelled managed recovery. This will show the number of lag in “difference” column (7 in my case). The Output file will be displayed as,

+++++++++++LAST PRODUCTION SEQUENCE GENERATED++++++++++++++++++

TO_CHAR(SYSDATE,'DD-MON-YYYYH
---------------------------------------------------------------------------
02-aug-2011 19:03:58

Elapsed: 00:00:00.00

Last Sequence Generated Thread
----------------------- ----------
582535 1

Elapsed: 00:00:00.09

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Aug 2 19:08:05 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

++++++++++++++++++++LAST SEQUENCE RECEIVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 582535 582528 7

Elapsed: 00:00:02.01
++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++

no rows selected

Elapsed: 00:00:34.06
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Ref: How To Check Whether Physical Standby is in Sync with the Primary? [ID 861595.1]

I found out a nice shell script while googling, would like to share,

#
# Script to check if standby database is in sync and in read only with apply mode
#
# Purpose script:
# This script checks if standby and primary are in sync and if standby is in mode "READ ONLY WITH APPLY"
# violations will be mailed to recepients defined in this script.
#
# Usage script:
# Place this script on the standby database server in /home/oracle/scripts
# The following changes will be necessary when implementing this script
# 1: CONNECTRSTR refers to the connectstring to connect to the primary db from the standby db
# 2: ORACLE_SID referts to the oracle SID of the standby database
# 3: ORACLE_HOME refers to the oracle HOME of the standby database
#

#!/usr/bin/ksh
. /etc/profile

DBSID=PROD # Database sid
CONNECTSTR=scott/tiger@PROD # Connect string prod
MAILADR=scott.tiger@everywhere.com # mail
NIETERG=2 # allowed difference between standby log and primary log sequence#

# set the environment to the standby database
export ORACLE_SID=PROD
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

# declare functions to query v$archived_log and other checks

runsqlstb()
{
sqlplus -S << TOTZOVER / as sysdba set heading off select max(sequence#) from v\$archived_log where applied='YES' and dest_id=2 ; exit TOTZOVER } runsqlprd() { sqlplus -S ${CONNECTSTR} <> mailtempl
echo logseq ${DBSID} standby database is $NUMBERSTB >> mailtempl
echo
mailx -s "Standby database out of sync " ${MAILADR} mailtempl
mailx -s "Fout in script " ${MAILADR} mailtempl
errtrap
fi

NUMBERPRD=`runsqlprd`
if [ $? -ne 0 ]
then
echo Something wrong with ${DBSID} > mailtempl
errtrap
fi

echo standby database is ${NUMBERSTB}
echo productie database is ${NUMBERPRD}

VERSCHIL=`expr ${NUMBERPRD} - ${NUMBERSTB}`
echo Standby is $VERSCHIL sequence achter
mailme

Advertisement

HOWTO reset root password in linux redhat

2011-12-07T22:19:00.003+07:00

Today when i trying to connect to our development database on linux server with root user, but i forget the correct password. :) because i was logging after a 10 months. after that i need to reset root password and this is the following step.

linux machine :
Red Hat Enterprise Linux AS release 4 (Nahant Update 3)
and grub loader.

step.
1. reboot machine
2. wait for GRUB splash screen appear
3. then click on "e" keyword in keyboard (hit enter)
4. after that another window will open again click on "e" keyword in keyword to select "KERNAL ... " from list of value. (hit enter)
5. then type "single" included space (hit enter)
6. then type "b" from keyboard to boot (hit enter)
7. system boot up as single user mode
8. you will see # prompt , here type "passwd" command to change root password
9. reboot
10. enjoy to logging with new user

reference: http://www.wallpaperama.com/forums/how-to-reset-linux-root-forgotten-passwords-get-retrieve-root-password-t956.html

How to Get the Locked Tables List In Oracle

2011-11-04T10:41:00.005+07:00

Get the Locked Tables List

SELECT l.inst_id,

    SUBSTR (L.ORACLE_USERNAME, 1, 8) ORA_USER,

    SUBSTR (L.SESSION_ID, 1, 3) SID, S.serial#,

    SUBSTR (O.OWNER || '.' || O.OBJECT_NAME, 1, 40) OBJECT,

    P.SPID OS_PID,

    DECODE (L.LOCKED_MODE,

           0, 'NONE',

           1, 'NULL',

           2, 'ROW SHARE',

           3, 'ROW EXCLUSIVE',

           4, 'SHARE',

           5, 'SHARE ROW EXCLUSIVE',

           6, 'EXCLUSIVE',

           NULL)

      LOCK_MODE

FROM sys.GV_$LOCKED_OBJECT L,

    DBA_OBJECTS O,

    sys.GV_$SESSION S,

    sys.GV_$PROCESS P

WHERE L.OBJECT_ID = O.OBJECT_ID

    AND l.inst_id = s.inst_id

    AND L.SESSION_ID = S.SID

    AND s.inst_id = p.inst_id

    AND S.PADDR = P.ADDR(+)

ORDER BY   l.inst_id;

And to get the details of a particular session given by the sid in the above query use this query

select STATUS , PROCESS , PROGRAM , LOGON_TIME from v$session where sid=

This will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.

SELECT s.username,

    s.sid,

    s.serial#,

    s.last_call_et / 60 mins_running,

    q.sql_text

FROM v$session s

JOIN v$sqltext_with_newlines q ON s.sql_address = q.address

WHERE status = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND last_call_et > 60
ORDER BY sid, serial#, q.piece

Setting ORACLE DB into noarchivelog mode

2011-11-01T22:03:00.001+07:00

Steps when the database state is down:
1. Login to sqlplus

  - sqlplus /nolog

  - connect / as sysdba

2. The db must be mounted EXCLUSIVE and not open for operation
- startup mount;
3. Check the log mode status of the database
- select log_mode from v$database;
4. Setting it to noarchivelog mode
- alter database noarchivelog;
5. Setting db open for user operation
- alter database open;
6. Reverting back to archivelog mode
- alter database archivelog;

Configuration NFS (Network File System)

2011-10-21T16:41:00.002+07:00

Server side 10.10.10.10
# cat /etc/exports
/d01/backup         10.10.10.20(ro)

# cat /etc/hosts.deny
#
# hosts.deny    This file describes the names of the hosts which are
#               *not* allowed to use the local INET services, as decided
#               by the '/usr/sbin/tcpd' server.
#
# The portmap line is redundant, but it is left to remind you that
# the new secure portmap uses hosts.deny and hosts.allow. In particular
# you should know that NFS uses portmap!

portmap:ALL
lockd:ALL
mountd:ALL
rquotad:ALL
statd:ALL

# cat /etc/hosts.allow
#
# hosts.allow   This file describes the names of the hosts which are
#               allowed to use the local INET services, as decided
#               by the '/usr/sbin/tcpd' server.
#
portmap: 10.10.10.20
lockd: 10.10.10.20
rquotad: 10.10.10.20
mountd: 10.10.10.20
statd: 10.10.10.20

# cd /sbin/
# portmap

# cd /usr/sbin/
# rpc.mountd
# rpc.nfsd
# rpc.statd
# rpc.lockd
# rpc.rquotad
# rpcinfo -p
   program vers proto   port
    100000    2   tcp    111 portmapper
    100000    2   udp    111 portmapper
    100005    1   udp    657 mountd
    100005    1   tcp    660 mountd
    100005    2   udp    657 mountd
    100005    2   tcp    660 mountd
    100005    3   udp    657 mountd
    100005    3   tcp    660 mountd
    100003    2   udp   2049 nfs
    100003    3   udp   2049 nfs
    100003    4   udp   2049 nfs
    100021    1   udp 35144 nlockmgr
    100021    3   udp 35144 nlockmgr
    100021    4   udp 35144 nlockmgr
    100003    2   tcp   2049 nfs
    100003    3   tcp   2049 nfs
    100003    4   tcp   2049 nfs
    100021    1   tcp 34377 nlockmgr
    100021    3   tcp 34377 nlockmgr
    100021    4   tcp 34377 nlockmgr
    100024    1   udp    801 status
    100024    1   tcp    804 status
    100011    1   udp    804 rquotad
    100011    2   udp    804 rquotad
    100011    1   tcp    807 rquotad
    100011    2   tcp    807 rquotad
# exportfs -ra

Client side 10.10.10.20
# cd /sbin/
# portmap
# service portmap status
# rpcinfo -p
   program vers proto   port
    100000    2   tcp    111 portmapper
    100000    2   udp    111 portmapper
    100021    1   udp 61494 nlockmgr
    100021    3   udp 61494 nlockmgr
    100021    4   udp 61494 nlockmgr
    100021    1   tcp 53241 nlockmgr
    100021    3   tcp 53241 nlockmgr
    100021    4   tcp 53241 nlockmgr
# mount 10.10.10.10:/d01/backup /d01/backup

Chuyển đổi file Excel xls sang file csv vẫn giữ font tiếng Việt unicode

2011-07-18T17:52:00.012+07:00

Mấy hôm nay gặp vấn đề về font tiếng Việt với file csv. Vấn đề chi tiết như sau.
Mình có file dữ liệu là excel *.xls . Vì nhu cầu công việc mình cần phải chuyển nó sang file có định dạng *.csv để import vào một ứng dụng của công ty. Mặc dù trong Excel có chức năng Save As, chọn type là *.csv; nhưng Excel lại không hỗ trợ Unicode. Do đó file *.csv sau khi tạo bằng chức năng Save As của Excel bị mất font tiếng Việt.
Lên net tìm một một buổi chẳng có chỉ thấy ai làm được. Chỉ có cách của Anhgolden's Blog! . Nhưng cách này thì rườm rà, không hay.
Thế là mình quyết định viết một macro ngay trên Excel để chuyển font Unicode sang UTF8. Rồi sau đó dùng chức năng Save As của Excel để lưu ra file *.csv. Vì đặc tính của UTF8 là dùng nhiều ký tự 1byte để biểu diễn cho một ký tự Unicode nhiều bytes. Nên file *.csv tạo ra vẫn có tiếng Việt. Và có thể import vào ứng dụng của công ty. Hiện nay, hầu hết các ứng dụng đều hỗ trợ UTF8(nhưng Excel không hiễn thị được tiếng Việt UTF8 nhé).
Giờ thì chỉ mỗi việc mở file excel có macro (Link download) run macro để chuyển font Unicode sang UTF8, và Save As là có file *.csv với font Tiếng Việt. Bài viết giúp được bạn comment cảm ơn 1 tiếng nhé.

Dưới đây là demo: