Monday, March 18, 2013

ASMCMD-08102: NO CONNECTION TO ASM; COMMAND REQUIRES ASM TO RUN


Platform         Microsoft Windows x64 (64-bit)
Product Version  11.2.0.2

DETAILED PROBLEM DESCRIPTION
+ASM instance is up/running and only accessible through EMGrid Control and cannot use 
sqlplus to connect to +ASM instance, but asmcmd fails with the following error:
 
ASMCMD-08102: no connection to ASM; command requires ASM to run
C:\Users\vmummadi>set oracle_home=C:\Oracle\Grid11gR2
C:\Users\vmummadi>SET ORACLE_SID=+ASM
C:\Users\vmummadi>asmcmd
Connected to an idle instance.
ASMCMD> lsdg
ASMCMD-08102: no connection to ASM; command requires ASM to run
 Enabled asmcmd tracing and the trace further shows:
C:\Users\vmummadi>set DBI_TRACE=1
C:\Users\vmummadi>set oracle_home=C:\Oracle\Grid11gR2
C:\Users\vmummadi>asmcmd
   DBI 1.602-ithread default trace level set to 0x0/1 (pid 7412) at DBI.pm line 273 via asmcmdshare.pm line 201
   -> DBI->connect(dbi:Oracle:, , ****, HASH(0x44ca968))
   -> DBI->install_driver(Oracle) for MSWin32 perl=5.010000 pid=7412 ruid=0 euid=0
      install_driver: DBD::Oracle version 1.20 loaded from C:/Oracle/Grid11gR2/perl/site/lib/DBD/Oracle.pm
            !! ERROR: '12560' 'ORA-12560: TNS:protocol adapter error (DBD ERROR: OCIServerAttach)' (err#0)
         DBI connect('','',...) failed: ORA-12560: TNS:protocol adapter error (DBDERROR: OCIServerAttach)
Connected to an idle instance.
ASMCMD>
check listener:
C:\Users\vmummadi>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 07-MAR-2013 08:49:35
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
---------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                22-FEB-2013 20:39:32
Uptime                    12 days 12 hr. 10 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\Grid11gR2\network\admin\listener.ora
Listener Log File         C:\Oracle\diag\tnslsnr\ATLAS\listener\alert\log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<omitted here>)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<omitted here>)(PORT=1521)))
Services Summary...
Service "+asm" has 1 instance(s).
 Instance "+asm", status READY, has 1 handler(s) for this service...
...
The command completed successfully
Errors:
The errors are misleading, including the ORA-12560 (TNS:protocol adapter error).

The clue to the underlying problem is coming from the ORA-1031 (insufficient privileges) 
during sqlplus "/ as sysasm"
C:\> set oracle_home=C:\Oracle\Grid11gR2
C:\> SET ORACLE_SID=+ASM
C:\> echo %ORACLE_SID%
+ASM
C:\> echo %ORACLE_HOME%
C:\Oracle\Grid11gR2
C:\> sqlplus "/ as sysasm"
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 13 12:03:09 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges 

Also, check if "SQLNET.AUTHENTICATION_SERVICES = (NTS)" is already set in the server's 
sqlnet.ora file

Solutions:
User is not part of the ORA_DBA OS group:
C:\Users\vmummadi>echo %username%
vmummadi
C:\Users\vmummadi>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
-------------------------------------------------------------------------------
.... <-- other users listed here except 'vmummadi'

NT AUTHORITY\SYSTEM
The command completed successfully.

The problem is to do with harden security in 11g that affects non-dba OS users with 
connections to Oracle instances.
If the user is not a member of the ORA_DBA OS group, then it either needs to connect to 
the ASM instance using the ASM orapw file (either using his/her own account or using the 
Oracle SYS account and as sysasm privilege) or be added to the OS ORA_DBA group.
Other workarounds include :
  • Add the non-dba OS user to the ASM's orapw file and grant it 'SYSASM' privileges:
  • Follow this -   http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba007.htm#ADMIN12478
  • Allow the non-dba user to connect to the ASM instance using the SYS Oracle user account
  • (for this, the non-dba user must know what is the password for SYS):

  • Example: sqlplus sys@<hostname or IP address of the host>:<port were the listener
  • lis tens>/+ASM as sysasm
  • Make the non-dba OS user a member of the ORA_DBA OS group so it can use bequeath connections
  • (ie, not using the listener) to the asm instance (ie, sqlplus "/ as sysasm")                Oracle Support -ID 1537484.1   Reference: Bug 16470328 

Friday, March 8, 2013

Using Snapshot Standby Database On Oracle 11g

A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use. 


Snapshot database has following characteristics
1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Data from the primary database is always protected as the archives are being received and stored in place.

4. All local updates will be discarded when snapshot database is converted back to physical standby database.

5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.

6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Once the snapshot standby is activated this database diverges from its primary database over time because redo data from the primary database is not applied.Again local updates to the snapshot standby database will cause additional divergence.

--> Steps to convert Physical Standby Database to the Snapshot Standby Database:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

1) If not already configured , configure flash recovery area as given below 

a) Set the size for recovery area. 

Alter system set db_recovery_file_dest_size=<size> 

b) Set Flash recovery area. 

Alter system set db_recovery_file_dest=<path> 

2) Bring the physical standby database to mount stage.  

3) Stop managed recovery if it is active.  

4) Convert physical standby database to snapshot standby database.  

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

The database is dismounted during conversion and must be restarted. 

Once the database is restarted  any transaction can be executed . 

SQL> select open_mode,database_role from v$database; 

OPEN_MODE                                  DATABASE_ROLE 
----------                                         ---------------- 
READ WRITE                                 SNAPSHOT STANDBY

Remember a guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

--> Steps to convert the Snapshot Standby Database to the Physical Standby Database


1. Shutdown the snapshot standby database. 

2. Bring the database to the mount stage. 

3. Issue the command 

ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

4. Shutdown the database and mount it

SQL> select open_mode,database_role from v$database; 

OPEN_MODE        DATABASE_ROLE 
----------              ---------------- 
MOUNTED          PHYSICAL STANDBY 

5. Start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted, Redo Apply can be started and all redo received by the snapshot standby database will be applied to the physical standby database.

Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.

If you are using the Data Guard Broker, you should use the below command to convert a physical standby database to a snapshot standby database and back.

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;



Thursday, February 21, 2013

Understand and Analyse AWR Report

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. 
Compare the AWR report for the period with bad performance with that of a one taken when performance was good, also a lot depends on type of problem you have, your system, your application, time of day etc.


SNAPSHOTS:

Creating Snapshots From Grid Control(automatically generated every hour), go to:

DB instance-Server tab-Automatic Workload Repository-Run AWR report- By Snapshot(Begin & End)


Creating Snapshots Manually:

You can manually create snapshots with the CREATE_SNAPSHOT procedure if you want to capture statistics at times different than those of the automatically generated snapshots. For example:
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/



Begin with:
1) Top 5 Timed Foreground Events
2) Operating System Statistics -- how much busy and wait ...
3) SQL statistics -- like top SQL's
4) Instance Activity 
5) I/O stats.
6) look out at advisors (memory-sga,pga, Undo & so on)
7) Buffer waits
8) Instance Efficiency Percentages (Target 100%)




Also check for SQL Statistics:

  • SQL ordered by Elapsed Time
  • SQL ordered by CPU Time
  • SQL ordered by User I/O Wait Time
  • SQL ordered by Gets
  • SQL ordered by Reads
  • SQL ordered by Physical Reads (UnOptimized)
  • SQL ordered by Executions
  • SQL ordered by Parse Calls
  • SQL ordered by Sharable Memory
  • SQL ordered by Version Count
  • Complete List of SQL Text



Reference:
NOTE:1086120.1 - Quick Instructions For Obtaining The Automatic Workload Repository (AWR) Report
FAQ: How to Use AWR reports to Diagnose Database Performance Issues [ID 1359094.1]
Using AWR/Statspack reports to help solve some Portal Performance Problems scenarios [ID 565812.1]

Thursday, February 14, 2013

vi cheat sheet


How To Give Grant Select On X$ Objects In Oracle 10g?


Metalink Note:453076.1

1) You cannot grant a select privilege on X$* tables to another user due to they are internally
protected.

- If you try grant select on x$ objects then you will receive the following error:
SQL> grant select on sys.x$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


2) Alternatively, you can create a view and then grant a select privilege on that new view to
another user as follow:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL> create user scott identified by tiger default tablespace users temporary tablespace temp;

User created.

SQL> grant resource to scott;

Grant succeeded.

SQL> grant connect to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> show user
USER is "SCOTT"

SQL> select * from sys.x$bh;
select * from sys.x$bh
*
ERROR at line 1:
ORA-00942: table or view does not exist

==================================================================

SQL> show user
USER is "SYS"
SQL> grant select on sys.x$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


SQL> create view vw_x$bh as select * from sys.x$bh; <(=== This works

View created.

SQL> grant select on sys.vw_x$bh to scott; <(=== This works

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> select * from sys.vw_x$bh; <(=== This works

External Tables Example From Oracle Documentation

test3>create directory ext_tab_dir as '/user1/oracle/test_vinay';

Directory created.

test3>grant read,write on directory ext_tab_dir to osvinay;

Grant succeeded.

test3>conn osvinay@test3
Enter password: *********
Connected.
test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                           ACCESS PARAMETERS (RECORDS FIXED 20 FIELDS (first_name CHAR(7),
  4                                                                      last_name CHAR(8),
  5                                                                      year_of_birth CHAR(4)))
  6                           LOCATION ('info.dat'));

Table created.

test3>
test3>select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963

test3>drop table emp_load;

Table dropped.

test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                           ACCESS PARAMETERS (RECORDS VARIABLE 2 FIELDS TERMINATED BY ','
  4                                               (first_name CHAR(7),
  5                                                last_name CHAR(8),
  6                                                year_of_birth CHAR(4)))
  7                           LOCATION ('info.dat'));

Table created.

test3>select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963

test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2   ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                          ACCESS PARAMETERS (RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ','
  4                                               (first_name CHAR(7),
  5                                                last_name CHAR(8),
  6                                                year_of_birth CHAR(4)))
  7                          LOCATION ('info.dat'));

Table created.

test3>select * from emp_load;
select * from emp_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

I got this error when i followed the example from Oracle documentation. After several attempts i finally figured out that it only works if you change the values in info.dat file as follows:


bash-2.05$ more info.dat
Alvin,Tolliver,1976,|Kenneth,Baer,1963,|Mary,Dube,1973,

test3>/

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973