Oracle user cannot write to ASM DATA diskgroup after succesful installation of grid infra in 11.2

Problem:

While restoring controlfile to test environment, from filesystem or tape environment after installing grid infrastructure.

I got following errors from both RMAN  and DBCA, despite the fact that I could create directory alias in asmcmd.

RMAN> run {

2> allocate channel t1 device type 'SBT_TAPE'

3> parms 'ENV=(NSR_SERVER=bcksrv1, NSR_CLIENT=mydb, NSR_DATA_VOLUME_POOL=JB10)';

4> RESTORE controlfile to '+DATA' FROM '/tmp/ctl01.dbf';

5> release channel t1 ;

6> }

allocated channel: t1

channel t1: SID=386 device type=SBT_TAPE

channel t1: NMO v5.0.0.0

Starting restore at 29-MAY-12

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 05/29/2012 14:31:34

ORA-19504: failed to create file "+DATA"

ORA-15045: ASM file name '+DATA' is not in reference form

ORA-17502: ksfdcre:5 Failed to create file +DATA

ORA-15081: failed to submit an I/O operation to a disk

ORA-19600: input file is control file  (/tmp/ctl01.dbf)

ORA-19601: output file is control file  (+DATA)

Cause:

Access rights to oracle disk.

Solution:

I checked the asm disks. /dev/rdsk/disk * has grid:oinstall ownership.

Grid infra home and bin has grid:oinstall, while the oracle home and bin has oracle:oinstall, as told in the MOS.

In our case, access rights on disk device files, are not 77x. As the second 7 means access to group oinstall.

I changed the access rights to 777 and it worked.

Defining database link by using Oracle Gateway

For configuring a remote non-Oracle database connections on Oracle gateway,
I followed below steps, hope this helps.

1) Create below file in the gateway, with appropriate values like the example below:
  Beware of the value of X in the file name: init<X>.ora
  X value will later be used in listener.ora file SID_NAME parameter.
  Beware of connect string line: HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
  Ip,port and database name triple belongs to the remote sybase machine.

Server: Gateway
File name: /u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYSYDB1.ora
  
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
#HS_FDS_TRACE_LEVEL=OFF
HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING="EnableSPColumnTypes=2"
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /tmp/dg4sybs_mysydb.trc
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE

 

2) In the gateway machine (in my example host ip: 10.1.1.200), Enter lines in listener.ora file:
Server: Gateway
File name: /u01/app/oracle/product/11.1.0/network/admin/listener.ora

MYSYDB_1522 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200) (PORT = 1522))
     )
   )
  )
SID_LIST_MYSYDB_1522 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = MYSYDB1)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0)
      (PROGRAM = dg4sybs)
      (ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
    )
  )

3) Start the service by issueing below command:

lnsrctl start MYSYDB_1522

4) Enter TNS entry to Oracle server where you created dblink.
If it is RAC you must re-enter this information in all nodes.
10.1.1.200 ip belongs to gateway server.

Server: Local DB(s)
File name: /u01/app/product/DB/network/admin/tnsnames.ora

REMOTEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = 10.1.1.200)(PORT = 1522))
    (CONNECT_DATA =
      (SID = MYSYDB1)
    )
    (HS = OK)
  )
  
 
5) Create dblink in the database side, using the tns entry in previous step:

CREATE PUBLIC DATABASE LINK MYDBLINK
 CONNECT TO "user1"
 IDENTIFIED BY <PWD>
 USING 'REMOTEDB';
  
 
Troubleshooting :
1- 
 While testing with select * from dual@MYDBLINK;
 it was giving below error. This was because of a sybase side logon trigger.

 ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
 [Oracle][ODBC Sybase Wire Protocol driver]Socket closed. {08S01}[Oracle][ODBC Sybase Wire Protocol driver]Sybase Network Connection has terminated. If a transaction was in progress it has been aborted. {08S01}[Oracle][ODBC Sybase Wire Protocol driver][Sybase ASE]Execution of login script '"sp_adm_check_int_users"' failed with last error = 11039. See server errorlog for details.
 {HY000,NativeErr = 1638}[Oracle][ODBC Sybase Wire Protocol driver]File not found '.odbc.ini'. {HY000,NativeErr = 1509}
 ORA-02063: preceding 3 lines from MYDBLINK

  
 sp_adm_check_int_users is a logon checking procedure, which authenticates conenctions according to source host ip/hostname.
 
 
2- You can check for any error logs in the following directory.
 
  /u01/app/oracle/product/11.1.0/dg4sybs/log
  
  -rw-r--r--    1 oracle   dba           11012 May 08 11:37 MYSYDB1_agt_65733070.trc
  -rw-r--r--    1 oracle   dba           11012 May 08 11:39 MYSYDB1_agt_59441834.trc
  -rw-r--r--    1 oracle   dba           11012 May 08 15:38 MYSYDB1_agt_54329962.trc
  -rw-r--r--    1 oracle   dba         2113496 May 09 19:05 MYSYDB1_agt_22414304.trc
  -rw-r--r--    1 oracle   dba           18585 May 10 15:46 MYSYDB1_agt_5701974.trc

3- You may get TNS error: ORA-12154: TNS:could not resolve the connect identifier specified
  In my case, it was I forgot to enter the tns entry in other node of the RAC.

NID: renaming your database in a minute

NID Utility, "DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database".         - excerpt from Oracle documentation.

When I first learnt from a guy, fortunately not newbie dba, it is a real shame for me to hear about nid very late in my dba career.

The day before I heard about nid, we were discussing with another 16 year dba, why Oracle could not rename db in year 2012, even preparing for version 12. But it was a real disaster that we discovered that there was a tool since year 2001, version 9. (Oracle documentation about nid utiliy in 9i) This gave real pain that I could not write about "nid" for two weeks, and rethink about continueing my career in dba position :)

Before nid, we were producing controlfile by backup controlfile to trace, changing its contents, etc. No need to these disgusting and a bit lengthy process.

Here's what I did, while changing the name of database which I had restored for making a test and development environment. Changing database name from MYDB to MYDBTEST.

[oracle@myhostt01 ~]$ nid TARGET=SYS DBNAME=MYDBTEST logfile='/tmp/mydb_nid.log'
Password:
[oracle@myhostt01 ~]$

-- log file details
[oracle@myhostt01 ~]$ tail -f /tmp/mydb_nid.log
Instance shut down

Database name changed to MYDBTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database MYDBTEST changed to 1233471344.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

-- change dbname in init.ora

[oracle@myhostt01 ~]$ vi /tmp/init2.ora

MYDBTEST.__db_cache_size=4378853376
MYDBTEST.__java_pool_size=16777216
MYDBTEST.__large_pool_size=16777216
MYDBTEST.__pga_aggregate_target=1073741824
MYDBTEST.__sga_target=5368709120
MYDBTEST.__shared_io_pool_size=0
MYDBTEST.__shared_pool_size=922746880
MYDBTEST.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/MYDBTEST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/mydb/controlfile/current.270.782998169'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='MYDBTEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBTESTXDB)'
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5368709120
*.undo_tablespace='UNDOTBS1'

[oracle@myhostt01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 11 14:11:52 2012

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

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@myhostt01 ~]$ ps -ef|grep pmon
grid     15792     1  0 11:11 ?        00:00:00 asm_pmon_+ASM
oracle   29856 28284  0 14:12 pts/7    00:00:00 grep pmon