Before you start to troubleshoot you need to gather some information:
If your unaware of the current settings for undo you should check. One way is to use the show parameter command but you can also check the database initialization parameter file or spfile.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -------------
undo_management string AUTO
undo_retention integer 1200
undo_suppress_errors boolean FALSE
undo_tablespace string APPS_UNDOTS1
Determine which time the error occurred from the alert log, which will help you narrow down the time for a query we will use later. You alert log should have an entry similiar to:
Sat Dec 16 23:05:41 2008
ORA-01555 caused by SQL statement below (Query Duration=1506 sec, SCN: 0x0000.a8d6d835):
Sat Dec 16 23:05:41 2011
SELECT X FROM Y WHERE A=B
Now that you the automatic undo settings and the time of the error execute the following query (substitute the timestamp of the error in your alert log in place of the one below, with some buffer at either end):
SQL> select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNDOBLKS, UNXPSTEALCNT, EXPSTEALCNT ,
SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN
from v$undostat
where begin_time between to_date('08/16/2011 22:30:00','MM/DD/YYYY HH24:MI:SS')
and to_date('08/16/2011 23:30:00','MM/DD/YYYY HH24:MI:SS')
order by begin_time;
BEGIN_TIME UNDOBLKS UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT MAXQUERYLEN
---------------- -------- ------------ ----------- ----------- ------------- -----------
08/16/2011 22:32 10394 0 0 0 0 513
08/16/2011 22:42 5693 0 0 0 0 212
08/16/2011 22:52 10853 0 0 0 0 451
08/16/2011 23:02 8950 0 0 0 0 1653
08/16/2011 23:05 11284 0 0 1 0 1506
5 rows selected.
Based on this query we can determine a couple of things such as the number of transactions which required UNDO during the time period. How many undo blocks they required and whether there were any (successful) atempts to steal undo space from other transactions.
The query also shows which transactions received ora-1555 errors (SSOLDERRCNT) and whether or not there was enough space in the UNDO tablespace (NOSPACEERRCNT). If you look at the row above with a timestamp of 23:05, this is the transaction which produced my ora-1555 error. We know this because the SSOLDERRCNT column for this row has the value 1 and the timestamp is the same as the one in the log file. Since the MAXQUERYLEN for this transaction (1506) is greated than the undo_retention (1200) one solution would be to increase undo_retention.*
*There are other ways to solve this problem to try and avoid ora-1555 errors such as transaction tuning, etc. More detail can be found in Note:10630.1
For this particular case I will set it to 2000 and monitor the undo tablespace. By increasing undo_retention undo is kept for a longer period of time, so you should monitor the undo tablespace to make sure you have enough room. Oracle 9i+ has advisors which can help you determine the impact of raising undo_retention.
If the UNDO tablespace was too small, you would see a value in the NOSPACEERRCNT column. Take a look at the following row with a timestamp of 23:22:
BEGIN_TIME UNDOBLKS UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT MAXQUERYLEN
---------------- -------- ------------ ----------- ----------- ------------- -----------
08/16/2011 23:22 1034 10 0 1 1 1428
This row has a values for UNXPSTEALCNT and NOSPACEERRCNT. This means the transaction was not able to obtain space from the UNDO tablespace and attempted to steal 10 blocks (UNXPSTEALCNT) from other transactions. In this case we need to add space to the UNDO tablespace because it was not large enough to meet the undo_retention requirement.
Tuesday, 28 February 2012
Sunday, 19 February 2012
Oracle Enterprise Manager 12c
Total Cloud Control. Reduce downtime by up to 90%. Improve staff productivity by up to 75%. Reduce capital expenditures on servers by 20% or more. Increase IT agility while lowering the cost of managing IT by an order of magnitude.
Oracle Enterprise Manager is Oracle’s integrated enterprise IT management product line, which provides the industry’s only complete, integrated and business-driven enterprise cloud management solution. Oracle Enterprise Manager creates business value from IT by leveraging the built-in management capabilities of the Oracle stack for traditional and cloud environments, allowing customers to achieve unprecedented efficiency gains while dramatically increasing service levels.
The key capabilities of Enterprise Manager include:
A complete cloud lifecycle management solution allowing you to quickly set up, manage and support enterprise clouds and traditional Oracle IT environments from applications to disk.
Maximum return on IT management investment through the best solutions for intelligent management of the Oracle stack and engineered systems with real-time integration of Oracle’s knowledgebase with each customer environment
Best service levels for traditional and cloud applications through business-driven application management
Oracle Enterprise Manager is Oracle’s integrated enterprise IT management product line, which provides the industry’s only complete, integrated and business-driven enterprise cloud management solution. Oracle Enterprise Manager creates business value from IT by leveraging the built-in management capabilities of the Oracle stack for traditional and cloud environments, allowing customers to achieve unprecedented efficiency gains while dramatically increasing service levels.
The key capabilities of Enterprise Manager include:
A complete cloud lifecycle management solution allowing you to quickly set up, manage and support enterprise clouds and traditional Oracle IT environments from applications to disk.
Maximum return on IT management investment through the best solutions for intelligent management of the Oracle stack and engineered systems with real-time integration of Oracle’s knowledgebase with each customer environment
Best service levels for traditional and cloud applications through business-driven application management
Tuesday, 14 February 2012
How does one eliminate duplicates rows from a table?
Here's how you remove the duplicate rows before the primary key or unique indexes can be created:
DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;
Here column1, column2, column3 constitute the identifying key for each record.
Be sure to replace our_table with the table name from which you want to remove the duplicate rows. The GROUP BY is used on the columns that make the primary key for the table. This script deletes each row in the group after the first row.
DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;
Here column1, column2, column3 constitute the identifying key for each record.
Be sure to replace our_table with the table name from which you want to remove the duplicate rows. The GROUP BY is used on the columns that make the primary key for the table. This script deletes each row in the group after the first row.
Sunday, 12 February 2012
Killing Oracle Sessions
There are a number of ways to kill rogue sessions both within Oracle and externally.
• Identify the Session to be Killed
• ALTER SYSTEM KILL SESSION
• ALTER SYSTEM DISCONNECT SESSION
• The Windows Approach
• The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@nitishprod.com (TNS V1-V3)
1 23 287 3834 SYS sqlplus@nitishprod.com (TNS V1-V3)
1 40 387 4663 oracle@nitishprod.com (J000)
1 38 125 4665 oracle@nitishprod.com (J001)
SQL>
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
ALTER SYSTEM DISCONNECT SESSION
The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTIONclause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
• Identify the Session to be Killed
• ALTER SYSTEM KILL SESSION
• ALTER SYSTEM DISCONNECT SESSION
• The Windows Approach
• The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@nitishprod.com (TNS V1-V3)
1 23 287 3834 SYS sqlplus@nitishprod.com (TNS V1-V3)
1 40 387 4663 oracle@nitishprod.com (J000)
1 38 125 4665 oracle@nitishprod.com (J001)
SQL>
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
ALTER SYSTEM DISCONNECT SESSION
The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTIONclause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
Saturday, 11 February 2012
How to move Non-RAC Env To RAC Env
Step#1
######
Create password files for both RAC instances
--------------------------------------------
orapwd file=orapwnitiprd password=oranitiprd force=y
Step#2
######
Add RAC specific parameters in pfile
------------------------------------
Ex:
*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener='LISTENERS_PROD1’
prod1a.thread=1
prod1a.instance_number=1
prod1a.undo_tablespace='UNDOTBS1'
prod1b.thread=2
prod1b.instance_number=2
prod1b.undo_tablespace='UNDOTBS2'
init.ora FILE:
############################################################################################################################################
nitiprd1.__db_cache_size=1996488704
nitiprd2.__db_cache_size=1996488704
nitiprd1.__java_pool_size=16777216
nitiprd2.__java_pool_size=16777216
nitiprd1.__large_pool_size=16777216
nitiprd2.__large_pool_size=16777216
nitiprd1.__shared_pool_size=520093696
nitiprd2.__shared_pool_size=520093696
nitiprd1.__streams_pool_size=218103808
nitiprd2.__streams_pool_size=218103808
*.audit_file_dest='/oracle/admin/nitiprd/adump'
*.background_dump_dest='/oracle/admin/nitiprd/bdump'
*.cluster_database_instances=2
*.cluster_database=FALSE
*.compatible='10.2.0.3.0'
*.control_files='/oramxp/706/data/data00/control01.ctl','/oramxp/706/index/index00/control02.ctl','/oramxp/706/misc/miscdata/control03.ctl'
*.core_dump_dest='/oracle/admin/nitiprd/cdump'
*.db_16k_cache_size=109715200
*.db_block_size=8192
*.db_cache_size=409715200
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='nitiprd'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nitiprdXDB)'
nitiprd2.instance_number=2
nitiprd1.instance_number=1
*.job_queue_processes=10
*.log_archive_dest_1='location=/oramxp/706/archive/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=114572800
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=100
*.sga_max_size=2885760000
*.sga_target=2885760000
*.streams_pool_size=209715200
nitiprd2.thread=2
nitiprd1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
nitiprd2.undo_tablespace='UNDOTBS2'
nitiprd1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/nitiprd/udump'
#################################################################################################################################################
Step#3
######
Start up the instance if it is not already started and issue the following statement to create your spfile on your raw partition:
---------------------------------------------------------------------------------------------------------------------------------
create spfile='/oramxp/706/misc/miscdata/spfilenitiprd.ora' from pfile='/oracle/product/10g/dbs/initnitiprd.ora';
For easy manageability you can create an initSID.ora file in your local $ORACLE_HOME/dbs directory that points to your spfile:
------------------------------------------------------------------------------------------------------------------------------
initnitiprd1.ora
#################
spfile=/oramxp/706/misc/miscdata/spfilenitiprd.ora
initnitiprd2.ora
#################
spfile=/oramxp/706/misc/miscdata/spfilenitiprd.ora
Step#4
######
Create RAC Data Dictionary Views.
---------------------------------
Create the RAC data dictionary views on the first RAC instance.
SQL> !echo $ORACLE_SID
nitiprd1
SQL> spool /tmp/catclust.log
SQL> @$ORACLE_HOME/rdbms/admin/catclust
...
...
...
SQL> spool off
SQL> shutdown immediate;
Step#5
######
Register the RAC instances with CRS.
------------------------------------
srvctl add database -d nitiprd -o '/oracle/product/10g/'
srvctl add instance -d nitiprd -i nitiprd1 -n u060mxmd11
srvctl add instance -d nitiprd -i nitiprd2 -n u060mxmd12
srvctl start instance -d nitiprd -i nitiprd1
u060mxmd11:/home/oracle>srvctl start instance -d nitiprd -i nitiprd1
PRKP-1001 : Error starting instance nitiprd1 on node u060mxmd11
CRS-0215: Could not start resource 'ora.nitiprd.nitiprd1.inst'.
SQL> startup
ORACLE instance started.
Total System Global Area 2902458368 bytes
Fixed Size 2086904 bytes
Variable Size 771753992 bytes
Database Buffers 2113929216 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 nitiprd1
u060mxmd11
10.2.0.4.0 03-DEC-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
Step#6
######
Create redo logs for the second RAC instance.
---------------------------------------------
alter database add LOGFILE thread 2
GROUP 4 ('/oramxp/706/misc/redo/nitiprd/redo706_04a.log','/oramxp/706/misc/redo/nitiprd/redo706_04b.log') SIZE 500M,
GROUP 5 ('/oramxp/706/misc/redo/nitiprd/redo706_05a.log','/oramxp/706/misc/redo/nitiprd/redo706_05b.log') SIZE 500M,
GROUP 6 ('/oramxp/706/misc/redo/nitiprd/redo706_06a.log','/oramxp/706/misc/redo/nitiprd/redo706_06b.log') SIZE 500M;
Enable Thread No# 2
-------------------
SQL> alter database enable thread 2;
Database altered.
Rename Logfiles for Thread # 1
------------------------------
SQL> alter database rename file '/oramxp/706/data/redo01.log' to '/oramxp/706/misc/redo/nitiprd/redo706_01a.log';
Database altered.
SQL> alter database rename file '/oramxp/706/data/redo02.log' to '/oramxp/706/misc/redo/nitiprd/redo706_02a.log';
Database altered.
SQL> alter database rename file '/oramxp/706/data/redo03.log' to '/oramxp/706/misc/redo/nitiprd/redo706_03a.log';
Database altered.
Add Log Member to Log Groups
----------------------------
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_01b.log' TO GROUP 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_02b.log' TO GROUP 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_03b.log' TO GROUP 3;
Database altered.
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- -----------------------------------------------------------------
1 /oramxp/706/misc/redo/nitiprd/redo706_01a.log
2 /oramxp/706/misc/redo/nitiprd/redo706_02a.log
3 /oramxp/706/misc/redo/nitiprd/redo706_03a.log
4 /oramxp/706/misc/redo/nitiprd/redo706_04a.log
4 /oramxp/706/misc/redo/nitiprd/redo706_04b.log
5 /oramxp/706/misc/redo/nitiprd/redo706_05a.log
5 /oramxp/706/misc/redo/nitiprd/redo706_05b.log
6 /oramxp/706/misc/redo/nitiprd/redo706_06a.log
6 /oramxp/706/misc/redo/nitiprd/redo706_06b.log
1 /oramxp/706/misc/redo/nitiprd/redo706_01b.log
2 /oramxp/706/misc/redo/nitiprd/redo706_02b.log
3 /oramxp/706/misc/redo/nitiprd/redo706_03b.log
12 rows selected.
Run following command:
----------------------
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)
SQL> alter system set thread=2 scope=spfile sid='nitiprd2';
System altered.
Shutdown and startup the Node#1
-------------------------------
Step#7
######
Create undo tablespace for the second RAC instance
--------------------------------------------------
SQL> create undo tablespace UNDOTBS2 datafile '/oramxp/706/undo/undotbs02.dbf' size 20G;
Tablespace created.
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name=’UNDOTBS2’;
TABLESPACE FILE_NAME
--------------------- --------------------------------------
UNDOTBS2 /oramxp/706/misc/undotbs02.dbf
SQL> alter system set cluster_database=TRUE scope=spfile sid='*';
System altered.
Step#8
######
Start the second instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2902458368 bytes
Fixed Size 2086904 bytes
Variable Size 771753992 bytes
Database Buffers 2113929216 bytes
Redo Buffers 14688256 bytes
SQL> alter database mount;
Database altered.
SQL> alter system set instance_number=2 scope=spfile;
System altered.
SQL> alter database open;
Database altered.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 nitiprd2
u060mxmd12
10.2.0.4.0 03-DEC-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
######
Create password files for both RAC instances
--------------------------------------------
orapwd file=orapwnitiprd password=oranitiprd force=y
Step#2
######
Add RAC specific parameters in pfile
------------------------------------
Ex:
*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener='LISTENERS_PROD1’
prod1a.thread=1
prod1a.instance_number=1
prod1a.undo_tablespace='UNDOTBS1'
prod1b.thread=2
prod1b.instance_number=2
prod1b.undo_tablespace='UNDOTBS2'
init.ora FILE:
############################################################################################################################################
nitiprd1.__db_cache_size=1996488704
nitiprd2.__db_cache_size=1996488704
nitiprd1.__java_pool_size=16777216
nitiprd2.__java_pool_size=16777216
nitiprd1.__large_pool_size=16777216
nitiprd2.__large_pool_size=16777216
nitiprd1.__shared_pool_size=520093696
nitiprd2.__shared_pool_size=520093696
nitiprd1.__streams_pool_size=218103808
nitiprd2.__streams_pool_size=218103808
*.audit_file_dest='/oracle/admin/nitiprd/adump'
*.background_dump_dest='/oracle/admin/nitiprd/bdump'
*.cluster_database_instances=2
*.cluster_database=FALSE
*.compatible='10.2.0.3.0'
*.control_files='/oramxp/706/data/data00/control01.ctl','/oramxp/706/index/index00/control02.ctl','/oramxp/706/misc/miscdata/control03.ctl'
*.core_dump_dest='/oracle/admin/nitiprd/cdump'
*.db_16k_cache_size=109715200
*.db_block_size=8192
*.db_cache_size=409715200
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='nitiprd'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nitiprdXDB)'
nitiprd2.instance_number=2
nitiprd1.instance_number=1
*.job_queue_processes=10
*.log_archive_dest_1='location=/oramxp/706/archive/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=114572800
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=100
*.sga_max_size=2885760000
*.sga_target=2885760000
*.streams_pool_size=209715200
nitiprd2.thread=2
nitiprd1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
nitiprd2.undo_tablespace='UNDOTBS2'
nitiprd1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/nitiprd/udump'
#################################################################################################################################################
Step#3
######
Start up the instance if it is not already started and issue the following statement to create your spfile on your raw partition:
---------------------------------------------------------------------------------------------------------------------------------
create spfile='/oramxp/706/misc/miscdata/spfilenitiprd.ora' from pfile='/oracle/product/10g/dbs/initnitiprd.ora';
For easy manageability you can create an initSID.ora file in your local $ORACLE_HOME/dbs directory that points to your spfile:
------------------------------------------------------------------------------------------------------------------------------
initnitiprd1.ora
#################
spfile=/oramxp/706/misc/miscdata/spfilenitiprd.ora
initnitiprd2.ora
#################
spfile=/oramxp/706/misc/miscdata/spfilenitiprd.ora
Step#4
######
Create RAC Data Dictionary Views.
---------------------------------
Create the RAC data dictionary views on the first RAC instance.
SQL> !echo $ORACLE_SID
nitiprd1
SQL> spool /tmp/catclust.log
SQL> @$ORACLE_HOME/rdbms/admin/catclust
...
...
...
SQL> spool off
SQL> shutdown immediate;
Step#5
######
Register the RAC instances with CRS.
------------------------------------
srvctl add database -d nitiprd -o '/oracle/product/10g/'
srvctl add instance -d nitiprd -i nitiprd1 -n u060mxmd11
srvctl add instance -d nitiprd -i nitiprd2 -n u060mxmd12
srvctl start instance -d nitiprd -i nitiprd1
u060mxmd11:/home/oracle>srvctl start instance -d nitiprd -i nitiprd1
PRKP-1001 : Error starting instance nitiprd1 on node u060mxmd11
CRS-0215: Could not start resource 'ora.nitiprd.nitiprd1.inst'.
SQL> startup
ORACLE instance started.
Total System Global Area 2902458368 bytes
Fixed Size 2086904 bytes
Variable Size 771753992 bytes
Database Buffers 2113929216 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 nitiprd1
u060mxmd11
10.2.0.4.0 03-DEC-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
Step#6
######
Create redo logs for the second RAC instance.
---------------------------------------------
alter database add LOGFILE thread 2
GROUP 4 ('/oramxp/706/misc/redo/nitiprd/redo706_04a.log','/oramxp/706/misc/redo/nitiprd/redo706_04b.log') SIZE 500M,
GROUP 5 ('/oramxp/706/misc/redo/nitiprd/redo706_05a.log','/oramxp/706/misc/redo/nitiprd/redo706_05b.log') SIZE 500M,
GROUP 6 ('/oramxp/706/misc/redo/nitiprd/redo706_06a.log','/oramxp/706/misc/redo/nitiprd/redo706_06b.log') SIZE 500M;
Enable Thread No# 2
-------------------
SQL> alter database enable thread 2;
Database altered.
Rename Logfiles for Thread # 1
------------------------------
SQL> alter database rename file '/oramxp/706/data/redo01.log' to '/oramxp/706/misc/redo/nitiprd/redo706_01a.log';
Database altered.
SQL> alter database rename file '/oramxp/706/data/redo02.log' to '/oramxp/706/misc/redo/nitiprd/redo706_02a.log';
Database altered.
SQL> alter database rename file '/oramxp/706/data/redo03.log' to '/oramxp/706/misc/redo/nitiprd/redo706_03a.log';
Database altered.
Add Log Member to Log Groups
----------------------------
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_01b.log' TO GROUP 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_02b.log' TO GROUP 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oramxp/706/misc/redo/nitiprd/redo706_03b.log' TO GROUP 3;
Database altered.
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- -----------------------------------------------------------------
1 /oramxp/706/misc/redo/nitiprd/redo706_01a.log
2 /oramxp/706/misc/redo/nitiprd/redo706_02a.log
3 /oramxp/706/misc/redo/nitiprd/redo706_03a.log
4 /oramxp/706/misc/redo/nitiprd/redo706_04a.log
4 /oramxp/706/misc/redo/nitiprd/redo706_04b.log
5 /oramxp/706/misc/redo/nitiprd/redo706_05a.log
5 /oramxp/706/misc/redo/nitiprd/redo706_05b.log
6 /oramxp/706/misc/redo/nitiprd/redo706_06a.log
6 /oramxp/706/misc/redo/nitiprd/redo706_06b.log
1 /oramxp/706/misc/redo/nitiprd/redo706_01b.log
2 /oramxp/706/misc/redo/nitiprd/redo706_02b.log
3 /oramxp/706/misc/redo/nitiprd/redo706_03b.log
12 rows selected.
Run following command:
----------------------
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)
SQL> alter system set thread=2 scope=spfile sid='nitiprd2';
System altered.
Shutdown and startup the Node#1
-------------------------------
Step#7
######
Create undo tablespace for the second RAC instance
--------------------------------------------------
SQL> create undo tablespace UNDOTBS2 datafile '/oramxp/706/undo/undotbs02.dbf' size 20G;
Tablespace created.
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name=’UNDOTBS2’;
TABLESPACE FILE_NAME
--------------------- --------------------------------------
UNDOTBS2 /oramxp/706/misc/undotbs02.dbf
SQL> alter system set cluster_database=TRUE scope=spfile sid='*';
System altered.
Step#8
######
Start the second instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2902458368 bytes
Fixed Size 2086904 bytes
Variable Size 771753992 bytes
Database Buffers 2113929216 bytes
Redo Buffers 14688256 bytes
SQL> alter database mount;
Database altered.
SQL> alter system set instance_number=2 scope=spfile;
System altered.
SQL> alter database open;
Database altered.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 nitiprd2
u060mxmd12
10.2.0.4.0 03-DEC-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
RMAN-06059: expected archived log not found
RMAN attempted to backup an archive log file, but couldn't find it.
This can happen for a variety of reasons; the file has been manually moved or deleted, the archive log destination has recently been changed, the file has been compressed, etc.
Starting backup
current log archived
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 14:24:55
RMAN-06059: expected archived log not found, lost of archived log
compromises recoverability
ORA-19625: error identifying file /u02/oradata/scr9/archive/1_1160.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:
change archivelog all crosscheck;
It is advisable to perform a full backup of the database at this point.
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.
This can happen for a variety of reasons; the file has been manually moved or deleted, the archive log destination has recently been changed, the file has been compressed, etc.
Starting backup
current log archived
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 14:24:55
RMAN-06059: expected archived log not found, lost of archived log
compromises recoverability
ORA-19625: error identifying file /u02/oradata/scr9/archive/1_1160.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:
change archivelog all crosscheck;
It is advisable to perform a full backup of the database at this point.
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.
Friday, 10 February 2012
RPM Tip - Determining the architecture of an installed package.
As many of you are aware, one of the first steps prior to installing Oracle is to determine which operating system packages are required. On 64bit systems you are required to have both the 32bit and 64bit versions installed for some.
For example, one such required package is libstdc++-devel. If you execute rpm with the -qa options (q for query mode, a for all installed packages) you will see the following if it is installed:
[oravis@nitish.oracle.com~]$ rpm -qa | grep libstdc++-devel
libstdc++-devel-3.4.6-10
If your on a 64bit OS how do you know if this is the 32bit or 64bit version? You need specify a query format in the rpm command. In the following example I extract the name, version, release and architecture information for the libstdc++-devel package.
[oravis@nitish.oracle.com ~]$ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}.rpm \
%{INSTALLTIME:date}\n" | grep libstdc++
libstdc++-devel-3.4.6-10_x86_64.rpm Mon 06 Oct 2008 07:46:50 PM EDT
libstdc++-devel-3.4.6-10_i386.rpm Mon 06 Oct 2008 07:46:50 PM EDT
So based on this output I have both of the required packages and I can quickly identify which package I need to install if one is missing. Note: I always pipe rpm to grep because if you don't specify the package name properly you won't get any results. ie:
[oravis@nitish.oracle.com ~]$ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}.rpm \
%{INSTALLTIME:date}\n" libstdc
[oravis@nitish.oracle.com ~]$
For example, one such required package is libstdc++-devel. If you execute rpm with the -qa options (q for query mode, a for all installed packages) you will see the following if it is installed:
[oravis@nitish.oracle.com~]$ rpm -qa | grep libstdc++-devel
libstdc++-devel-3.4.6-10
If your on a 64bit OS how do you know if this is the 32bit or 64bit version? You need specify a query format in the rpm command. In the following example I extract the name, version, release and architecture information for the libstdc++-devel package.
[oravis@nitish.oracle.com ~]$ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}.rpm \
%{INSTALLTIME:date}\n" | grep libstdc++
libstdc++-devel-3.4.6-10_x86_64.rpm Mon 06 Oct 2008 07:46:50 PM EDT
libstdc++-devel-3.4.6-10_i386.rpm Mon 06 Oct 2008 07:46:50 PM EDT
So based on this output I have both of the required packages and I can quickly identify which package I need to install if one is missing. Note: I always pipe rpm to grep because if you don't specify the package name properly you won't get any results. ie:
[oravis@nitish.oracle.com ~]$ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}.rpm \
%{INSTALLTIME:date}\n" libstdc
[oravis@nitish.oracle.com ~]$
11i and Listener Passwords
A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.
There are some pre-reqs, specifically:
3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher
The note has detailed steps on how to set the password at the application and database tiers.
In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:
TNS-01169: The listener has not recognized the password
Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.
#!/usr/bin/perl -w
if ($#ARGV !=1) {
die "Usage: check_alert.pl ex. hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;
$interval=60; # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5; # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";
for (;;) {
@errors=("Subject: Listener Password Errors for $hostname\n");
$currTime = localtime(time);
push(@errors,"Here are some errors found at $currTime for $hostname.\n");
while () {
chop $_;
if (/TNS-01169/) {
push(@errors, "$_\n");
}
}
if ($#errors > $email_threshold) {
$rndExt = time;
$rndFile = "alert_errors_$rndExt";
open (TMPFILE, ">/tmp/$rndFile");
foreach $error (@errors) {
print TMPFILE $error;
}
close(TMPFILE);
system ("mail username\@mycompany.com < /tmp/$rndFile");
system ("rm /tmp/$rndFile");
}
sleep $interval;
seek filePtr, 0, 1;
}
The script takes two parameters:
A couple of key things you may want to customize in the above script are the $intervaland $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.
There are some pre-reqs, specifically:
3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher
The note has detailed steps on how to set the password at the application and database tiers.
In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:
TNS-01169: The listener has not recognized the password
Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.
#!/usr/bin/perl -w
if ($#ARGV !=1) {
die "Usage: check_alert.pl ex. hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;
$interval=60; # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5; # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";
for (;;) {
@errors=("Subject: Listener Password Errors for $hostname\n");
$currTime = localtime(time);
push(@errors,"Here are some errors found at $currTime for $hostname.\n");
while () {
chop $_;
if (/TNS-01169/) {
push(@errors, "$_\n");
}
}
if ($#errors > $email_threshold) {
$rndExt = time;
$rndFile = "alert_errors_$rndExt";
open (TMPFILE, ">/tmp/$rndFile");
foreach $error (@errors) {
print TMPFILE $error;
}
close(TMPFILE);
system ("mail username\@mycompany.com < /tmp/$rndFile");
system ("rm /tmp/$rndFile");
}
sleep $interval;
seek filePtr, 0, 1;
}
The script takes two parameters:
A couple of key things you may want to customize in the above script are the $intervaland $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.
R12: Sometimes cloning doesn’t remove all source references
I’m in the process of applying the latest round of security patches to our environment. While patching NITISH (I’ve already applied it to a sandbox and DEV environment), I hit the following error:
OPatch found the word "error" in stderr, please look into it.
You may have to run the make again.
Stderr Output (from /var/NITISH/apps/tech_st/10.1.2/.patch_storage/12828135/make_local.stderr):
/usr/bin/ld: cannot find –ljava
collect2: ld returned 1 exit status
make[1]: *** [/u01/NITISH/apps/tech_st/10.1.2/sysman/lib/libnmemso.so] Error 1
make: *** [libnmemso] Error 2
The issue was with the same file:
$ORACLE_HOME/sysman/lib/env_sysman.mk
The following line was still pointing to the source system:
JRE_LIB_DIR=/u01/PROD/apps/tech_st/10.1.2/jdk/jre/lib/i386
I changed it to NITISH, reran the patch and it completed successfully. Just a reminder that cloning doesn’t update all the necessary files.
OPatch found the word "error" in stderr, please look into it.
You may have to run the make again.
Stderr Output (from /var/NITISH/apps/tech_st/10.1.2/.patch_storage/12828135/make_local.stderr):
/usr/bin/ld: cannot find –ljava
collect2: ld returned 1 exit status
make[1]: *** [/u01/NITISH/apps/tech_st/10.1.2/sysman/lib/libnmemso.so] Error 1
make: *** [libnmemso] Error 2
The issue was with the same file:
$ORACLE_HOME/sysman/lib/env_sysman.mk
The following line was still pointing to the source system:
JRE_LIB_DIR=/u01/PROD/apps/tech_st/10.1.2/jdk/jre/lib/i386
I changed it to NITISH, reran the patch and it completed successfully. Just a reminder that cloning doesn’t update all the necessary files.
Thursday, 9 February 2012
ORA-29283: invalid file operation
Error starting at line 1 in command:
BEGIN
DECLARE
file_name VARCHAR2(50);
file_handle utl_file.file_type;
BEGIN
file_name := 'table.txt';
file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
SELECT column 1
||'~'||column 2
||'~'||column 3 out_line
FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;
Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
29283. 00000 - "invalid file operation"
Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.
Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.
We had migrated our servers from standalone to RAC and sometimes they worked sometimes not.
It was because we were not using the shared location for utl directory.
Once we put the files on shared location,it started working.
BEGIN
DECLARE
file_name VARCHAR2(50);
file_handle utl_file.file_type;
BEGIN
file_name := 'table.txt';
file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
SELECT column 1
||'~'||column 2
||'~'||column 3 out_line
FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;
Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
29283. 00000 - "invalid file operation"
Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.
Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.
We had migrated our servers from standalone to RAC and sometimes they worked sometimes not.
It was because we were not using the shared location for utl directory.
Once we put the files on shared location,it started working.
Subscribe to:
Posts (Atom)