Archive

Archive for November, 2009

how fast a Oracle table is imported

November 24, 2009 Leave a comment
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,rows_processed,
 round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes, 
 trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from   sys.v_$sqlarea;
Categories: Oracle

Resizing/recreating Online Redo Logs

November 23, 2009 Leave a comment

1. Find out the current online redo logs and their sizes

Set linesize 100

SQL> column member format a50

SQL> /

GROUP# MEMBER                                              BYTES

———- —————————————————–        —————-

1 /NROFPR/dbase/redo/set1/log1/redo01.log 104857600

2 /NROFPR/dbase/redo/set2/log2/redo02.log 104857600

3 /NROFPR/dbase/redo/set3/log3/redo03.log 104857600

 

      2. Backup the controlfile

      SQL> alter database backup controlfile to trace;

3. Make the last redo log group current

     SQL> select group#, status from v$log;

                 GROUP# STATUS
                  ———- —————-
                         1 CURRENT 
                         2 INACTIVE
                         3 INACTIVE

     SQL> alter system switch logfile ;

     SQL> select group#, status from v$log;

     SQL> select group#, status from v$log;

                 GROUP# STATUS
                  ———- —————-
                         1 INACTIVE 
                         2 CURRENT 
                         3 INACTIVE

4. Drop first redo log

     SQL> alter database drop logfile  group 1

     Database altered

     If you encounter the error message

     ALTER DATABASE DROP LOGFILE GROUP 1
     *
     ERROR at line 1:
    ORA-01624: log 1 needed for crash recovery of instance NESFPR (thread 1)
    ORA-00312: online log 1 thread 1: ‘<file_name>’

    Make sure the group is not current and then issue the command

    SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

   and then drop the the group

5. Recreate Drop Redo Log groups

   SQL> alter database add logfile group 1 (
    2  ‘/u03/app/oradata/NESFPR/redo01a.log’, 
    3  ‘/u04/app/oradata/NESFPR/redo01b.log’,
    4  ‘/u05/app/oradata/NESFPR/redo01c.log’) size 250m reuse;

6. Force another log switch

     SQL> select group#, status from v$log;

                 GROUP# STATUS
                  ———- —————-
                         1 INACTIVE
                         2 INACTIVE
                         3 CURRENT
Repeat the process for rebuilding the remainder of the redo logs.

7. Once all the redo logs have been built , it can be checked with the following sql

      SQL> SELECT a.group#, a.member, b.bytes
       2  FROM v$logfile a, v$log b WHERE a.group# = b.group#;

Categories: Oracle

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

November 20, 2009 Leave a comment

The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_NEXWPLTS’

Oracle only checks for listeners running on the default port (1521). So you have to tell it somehow to find the listener  and the way to do this is to enter the details in the tnsnames.ora. If you have mutliple oracle homes make sure the details are entered in the correct
tnsnames.ora file.

LISTENER_NEXWPLTS.ncs.local =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.52.51)(PORT = 50635))
  )

Test it with tnsping:
tnsping <listener_name>

Categories: Oracle

DBcontrol fails to start on 2nd RAC node

November 19, 2009 1 comment

1.1 Run $OH/bin/emctl stop dbconsole in the node oracle02

1.2 Run in the node oracle02
ps -ef |grep dbconsole
ps -ef |grep emagent

If find some processes per the above commands, kill them.

1.3 Delete the below files (not directories) from both the two nodes.

/opt/app/oracle/product/11.1.0EE/oracle02_testrac2/sysman/emd/state/*
/opt/app/oracle/product/11.1.0EE/oracle02_testrac2/sysman/emd/collection/*
/opt/app/oracle/product/11.1.0EE/oracle02_testrac2/sysman/emd/upload/*
/opt/app/oracle/product/11.1.0EE/oracle02_testrac2/sysman/emd/lastupld.xml
/opt/app/oracle/product/11.1.0EE/oracle02_testrac2/sysman/emd/agntstmp.txt
/opt/app/oracle/product/11.1.0EE/oracle02_testrac2/sysman/emd/protocol.ini

1.4 Start the dbconsole in the node o2gdbpr02 via ’emctl start dbconsole’, then verify if the status is running fine.

2.If the step1 doesn’t work, please run the below steps to reconfig the dbcontrol.

2.1 Stop the dbconsole in both two nodes

2.2 Run the below commands in both two nodes and make sure they are running successfully.

$ORA_CRS_HOME/bin/cluvfy comp crs
$ORA_CRS_HOME/bin/cluvfy comp nodecon -n all
$ORA_CRS_HOME//bin/cemutlo -n
$ORA_CRS_HOME/bin/crs_stat -t

2.3 Only in oracle02 node, run the below command:
$OH/bin/emca -config dbcontrol db -cluster

Categories: Oracle Rac

Using a non default listener

November 19, 2009 Leave a comment

When a non-default listener is used, then a listener.ora must be configured with the relevant listener address. For example

NESWPCTS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testoracle)(PORT = 50944))
  )

[oracle@testoracle admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 19-NOV-2009 12:22:27

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>set cu NESWPCTS

Current Listener is NESWPCTS

LSNRCTL> start

This would start the listener on port 50944

In order for PMON to be able to register the database service(s) with this listener, the init.ora parameter LOCAL_LISTENER must be set.

Eg, LOCAL_LISTENER=listener_NESWPCTS

PMON will attempt to resolve LOCAL_LISTENER using some naming method. For example, this may be resolved in tnsnames.ora, as follows:

 listener_NESWPCTS = (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=testoracle)(PORT=50944)) )

If a tnsnames.ora cannot be found or if LOCAL_LISTENER cannot be resolved, the alert.log will show:

PMON started with pid=2 Syntax error in listener string

If LOCAL_LISTENER can be resolved, but there is a syntax error in the tnsnames.ora specification, the alert log will show:

PMON started with pid=2 Syntax error in listener string (DESCRIPTION =)

SPECIFYING MULTIPLE LOCAL_LISTENERS

Multiple LOCAL_LISTENERs can be specified in one of two ways in the init.ora:

local_listener=listener_NESWPCTS, listener_NESWPCTS_ALIAS
local_listener=istener_NESWPCTS
local_listener=listener_NESWPCTS_ALIAS

In both cases, v$parameter will show: local_listener=listener_NESWPCTS, listener_NESWPCTS_ALIAS

PMON will register ONLY with the listener that appears first in the v$parameter value for local_listener

The correct method is to specify one local_listener in the init.ora,
and to specify multiple listener ADDRESSes in the connect descriptor.

For example,
init.ora:
   local_listener=all_listeners

tnsnames.ora:
   all_listeners.netwstore.ncs.local=
    (DESCRIPTION =
      (ADDRESS=(PROTOCOL=TCP)(HOST=nesdb1)(PORT=50634))
      (ADDRESS=(PROTOCOL=TCP)(HOST=nesdb2)(PORT=50611))
    )

Categories: Oracle

How to Recreate the Database Control Repository

November 18, 2009 1 comment

1) Configure your /etc/hosts on unix

192.168.0.211 nesdbts01  nesdbts01.domain.com

2) Configure your Listener.ora
Test connection.

3)
drop user SYSMAN cascade;
drop user MGMT_VIEW cascade;

4)
export ORACLE_HOME=<OH of the 10g database>
export ORACLE_SID=<SID of the 10g database>
export EMDROOT=$ORACLE_HOME
cd $ORACLE_HOME/bin
set ORACLE_SID=orcl
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

emctl start dbconsole

emctl status agent

Categories: Oracle

Upgrading to Oracle Database 11g

November 17, 2009 Leave a comment

Upgrade Path

You can directly upgrade to oracle 11g, if your curent database is 9.2.0.4 or newer. It supports direct upgrades to versions 9.2.0.4, 10.1 and 10.2

7.3.3 -> 7.3.4 -> 9.2.0.8 -> 11.1

8.0.5 -> 8.0.6 -> 9.2.0.8 -> 11.1

8.1.7 -> 8.1.7.4 -> 9.2.0.8 -> 11.1

9.0.1.3-> 9.0.1.4 -> 9.2.0.8 -> 11.1

9.2.0.3 (or lower) -> 9.2.0.8 -> 11.1

Oracle 11g client can access Oracle databases of versions 8i, 9i and 10g.

COMPATIBLE parameter

The default compatibilty value for Oracle 11g is 11.1 , You can , however upgrade to 11g with a minimum value of the COMPATIBLE parameter of 10.0.0 , but only a small number of features will be available if you keep the parameter to 10.0.0

Manual Upgrade Procedure

1.Invoke the Pre-Upgrade Information Tool in the database to upgrade.

This tool is simply the script $ORACLE_HOME/rdbms/admin/utlu111i.sql. So, you have to copy this script altogether with the scripts: utlu111s.sql and utlu111x.sql to a staging directory in the database to upgrade.

As with previous versions, this tool will examine the target database and display the warnings and recommendations that you should consider before you start the upgrade process such as removing obsolete initialization parameters, setting proper values to some parameters and adding space to key tablespaces.
Spool the output of running the script for later review.

SQL>spool upgrade11g.log
SQL>@utlu111i.sql

SQL>spool off

2.Backup the database.

3.Set the COMPATIBLE parameter to 11.1.0. You can do so by issuing the following command:
ALTER SYSTEM SET COMPATIBLE=’11.1.0′ SCOPE=SPFILE;

4.Modify the values of the initialization parameters and remove the obsolete parameters as recommended by the Pre-upgrade tool in the current initialization parameter file.

5.Copy the initialization parameter file to the new Oracle 11g home.

6.Shutdown cleanly the database.

7.If the database is installed in a Windows system, perform the following steps:

a)Stop the Oracle database service. Usually its name has the format OracleService<SID>.
b)Delete the service. You can use the oradim utility for this purpose.
oradim -delete –SID <sidname>
c)Use oradim utility in the Oracle 11g home to create a new Oracle Database 11g release instance. Of course, it should use the same SID.
oradim -NEW –SID <sidname>

8.If the database to upgrade is using a password file, move it to the new Oracle 11g home.

9.Change the environment variables ORACLE_HOME, PATH, and LD_LIBRARY_PATH so that they point to the new Oracle Database 11g directories.

10.In the Oracle 11g home, change to directory $ORACLE_HOME/rdbms/admin and then start the SQL*Plus

11.Start the database in upgrade mode and then run the upgrade script (it takes long time). When working in upgrade mode, Oracle allows only SYSDBA connections and sets some initialization parameters to specific values that are required to run the upgrade script.

SQL>login sys/password as sysdba

SQL>startup upgrade pfile=$ORACLE_HOME/dbs/initorcl.ora

SQL>spool upgrade11g.log

SQL>@ catupgrd.sql

SQL>spool off
12.After the upgrade script finishes, make sure no error occurred during the upgrade. Usually errors are raised
because of lack of shared memory or tablespace size. If there is any error, fix its cause and restart the upgrade script.

13.When the upgrade script successfully finishes, restart the database in OPEN mode.

14.Rum utlu111s.sql script (referred to as Post-Upgrade Status tool) to view the results of the upgrade. This tool will view the installed components and their status. If you see a component with invalid status, usually running the script in the next step will set it to valid.

15.Execute the script utlrp.sql to in parallel recompile any invalid PL/SQL program unit and Java code.
SQL>@utlrp.sql
SQL>SELECT COUNT(*) FROM DBA_INVALID_OBJECTS;

16.Because the upgraded database disables all tablespace alerts by setting the threshold to null, set the thresholds for tablespace alert to the values you desire.
If the upgrade process fails, restore the database from the backup.

Categories: Oracle