This discussion is archived
12 Replies Latest reply: Apr 22, 2013 7:48 PM by 900455 RSS

ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits

1001175 Newbie
Currently Being Moderated
Hi guys, sorry by my english

this is my configuration:

Primary site:
O.S: Oracle Enterprise Linux 6.3 64 bits
Oracle Rac 11.2.0.3 64 bits (2 nodes)
owner RDBMS: "oracle"
owner ASM: "grid"
Database name: TCDB
instance names: TCDB1 and TCDB2
hostnames: rac3 and rac4


Standby site:
O.S: Oracle Enterprise Linux 6.3 32 bits
Oracle single instance 11.2.0.3 32 bits
owner RDBMS: "oracle"
owner ASM: "grid"
Database_name: TCDB
unique_name: STANDBY
hostname: DG

so, my oracle rac works fine, but i have a issue with orapwd file copied to standby site.

I copied my orapwTCDB1 to standby site and rename it to orapwSTANDBY. But when i try to connect using sqlplus

sqlplus sys/oracle@standby as sysdba

i get
ERROR:
ORA-01031: insufficient privileges

look my pfile's:

PRIMARY:

TCDB2.__db_cache_size=364904448
TCDB1.__db_cache_size=352321536
TCDB2.__java_pool_size=4194304
TCDB1.__java_pool_size=4194304
TCDB2.__large_pool_size=4194304
TCDB1.__large_pool_size=4194304
TCDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TCDB2.__pga_aggregate_target=209715200
TCDB1.__pga_aggregate_target=209715200
TCDB2.__sga_target=629145600
TCDB1.__sga_target=629145600
TCDB2.__shared_io_pool_size=0
TCDB1.__shared_io_pool_size=0
TCDB2.__shared_pool_size=247463936
TCDB1.__shared_pool_size=260046848
TCDB2.__streams_pool_size=0
TCDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TCDB/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DADOSDG/TCDB/control01.ctl','+DADOSDG/TCDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DADOSDG/STANDBY/','+DADOSDG/TCDB/','+FRADG/STANDBY/','+FRADG/TCDB/'
*.db_name='TCDB'
*.db_recovery_file_dest='+FRADG'
*.db_recovery_file_dest_size=3116367872
*.db_unique_name='TCDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TCDBXDB)'
*.fal_client='TCDB'
*.fal_server='STANDBY'
TCDB2.instance_number=2
TCDB1.instance_number=1
*.log_archive_config='dg_config=(TCDB,STANDBY)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=TCDB'
*.log_archive_dest_2='service=STANDBY LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STANDBY'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='+DADOSDG/STANDBY/','+DADOSDG/TCDB/','+FRADG/STANDBY/','+FRADG/TCDB/'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sga_target=629145600
*.STANDBY_FILE_MANAGEMENT='AUTO'
TCDB1.thread=1
TCDB2.thread=2
TCDB1.undo_tablespace='UNDOTBS2'
TCDB2.undo_tablespace='UNDOTBS1'

i will do rman active duplicate, so my standby pfile has just 2 parameter, and the database is on nomount state.
*.db_name=TCDB
*.remote_login_passwordfile='EXCLUSIVE'

so, why i can not connect to my standby site??

can it be because the primary site is 64 bits and the standby site is 32 bits,
so using the 64-bit executable orapwd, and copying the file to the standby, which is 32 bit, everything fails?

Edited by: 998172 on 04/04/2013 17:32
  • 1. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    mseberg Guru
    Currently Being Moderated
    Hello;

    Would review this :

    Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]

    Question

    After you renamed the password file on the Standby did you remember to stop apply, shutdown the database, restart the database ( mount ) on the new password file and start apply?

    You can test the connect from the primary to the by :

    SQL> connect sys/<password>@standby as sysdba

    Until that test works you will have an issue with Data Guard.

    Not sure if Oracle care but for DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT I alway use the ( )

    Example
    DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/RECOVER2', '/u01/app/oracle/oradata/RECLONE','/u01/oradata/RECOVER2','/u01/oradata/RECLONE')
    LOG_FILE_NAME_CONVERT=('/u01/app/oracle/flash_recovery_area/RECOVER2', '/u01/app/oracle/flash_recovery_area/RECLONE')
    The INIT looks fine otherwise, and like I said Oracle may not care about the ( )


    Best Regards

    mseberg
  • 2. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    1001175 Newbie
    Currently Being Moderated
    Hi mseberg , thanks for your reply.

    This is exactly my problem. When i try to connect to standby database using sqlplus

    SQL> connect sys/<password>@standby as sysdba

    i get the error
    ERROR:
    ORA-01031: insufficient privileges

    I do this test, because I know if this does not work, DataGuard also not work.

    By the way, I already tested it with the primary instance offline, then redos were not being applied.

    My problem is with password file (orapw).
  • 3. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    mseberg Guru
    Currently Being Moderated
    OK.

    That is the test. So did you restart the Standby database on the renamed password file?

    Other causes :

    OS user is not part of correct UNIX group(s)
    remote_login_passwordfile not set to EXCLUSIVE or SHARED on both systems
    Password file has incorrect name Ex. ( /u01/app/oracle/product/11.2.0.3/dbs/orapwSTANDBY )
    User not Granted SYSDBA
    Listener SID is incorrect case or mismatched case

    Best Regards

    mseberg
  • 4. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    1001175 Newbie
    Currently Being Moderated
    Hi mseberg,

    my user id and groups id are different

    for example:

    oracle in primary
    uid=500(oracle) gid=1301(oinstall) groups=1301(oinstall),1201(asmdba),1300(dba)

    grid in primary
    uid=1100(grid) gid=1301(oinstall) groups=1301(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper)

    oracle in standby
    uid=500(oracle) gid=54321(oinstall) groups=54321(oinstall),11111(asmdba),12345(dba)

    grid in standby
    uid=1100(grid) gid=54321(oinstall) groups=54321(oinstall),33333(asmadmin),11111(asmdba),44444(asmoper)

    can this be a problem???
  • 5. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Group ID has nothing to do with it.

    Generally if both sides have these groups its OK
    oinstall
    dba
    Best Regards

    mseberg
  • 6. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    saratpvv Journeyer
    Currently Being Moderated
    SQL> connect sys/<password>@standby as sysdba
    
    i get the error
    ERROR:
    ORA-01031: insufficient privileges
    
    I do this test, because I know if this does not work, DataGuard also not work.
    
    By the way, I already tested it with the primary instance offline, then redos were not being applied.
    
    My problem is with password file (orapw).
    Yes mostly this error caused because of passwordfile were not identical

    try creating password files on both the servers like this(execute it in $ORACLE_HOME/dbs):
    Example
    $ orapwd file=orapwORADR password=some_pass entries=5 force=y
    Reason:
    Every database in a Data Guard environment must use a password file. Additionally, the password used by SYS must be the same for all primary and standby databases.
  • 7. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    mseberg Guru
    Currently Being Moderated
    Hello;
    try creating password files on both the servers like this(execute it in $ORACLE_HOME/dbs):
    Unfortunately this will not work. On Oracle 11 you cannot create a password file on the Standby. You must copy the password file from the Primary and rename the password file it on the Standby and then restart the Standby to use the new password file.

    In versions prior to Oracle 11 you could create a new password like you state.

    Best Regards

    mseberg
  • 8. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    saratpvv Journeyer
    Currently Being Moderated
    Thank you mseberg

    Yes your correct in 11g it is not possible.

    I was in assumption of 10g and gave that solution.
  • 9. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    1001175 Newbie
    Currently Being Moderated
    Hi guys, sorry for the delay in response.


    So, the original problem was resolved,
    now i can connect from primary to standby, and from standby to primary.
    And because of this, my dataguard works!!!


    But i have another problem now.
    When i try make a switchover with databroker, the databroker can't initializing the instance.
    The error ORA-01031: insufficient privileges appears.

    When both databases (TCDB and STANDBY) are in nomount or mount or open,
    i can do this

    from primary
    sqlplus sys/***@standby as sysdba
    OK!!!
    sqlplus sys/***@tcdb as sysdba
    OK!!

    from standby:
    sqlplus sys/***@tcdb as sysdba
    OK!!
    sqlplus sys/***@standby as sysdba
    OK!!!

    but when the databases are off (shutdown/idle)

    from primary
    sqlplus sys/***@standby as sysdba
    ORA-01031: insufficient privileges
    sqlplus sys/***@tcdb as sysdba
    ORA-01031: insufficient privileges

    from standby:
    sqlplus sys/***@tcdb as sysdba
    ORA-01031: insufficient privileges
    sqlplus sys/***@standby as sysdba
    ORA-01031: insufficient privileges


    Because of this db_broker can't start the database in switchover!!!

    How can i start a remote instance using db_broker?? or
    How can i start a remote instance using sqlplus??
  • 10. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    hm360 Newbie
    Currently Being Moderated
    Hi,

    In the remote, to connect to the DB the no-mount/mount state, static registration to the listener is required.

    Step 1 :

    Insert a static entry for StandbyDB in the listener.ora file of the standby system.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = standby)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
    (SID_NAME = standby)
    )
    )

    LISTENER =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )


    Step 2 :

    Please reload the listener.

    $ lsnrctl reload

    or

    $ lsnrctl stop
    $ lsnrctl start
  • 11. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    1001175 Newbie
    Currently Being Moderated
    Hi hm360,


    again, i can connect remotely to standby instance when it in nomount or mount state

    my problem is, i want to connect to standby instance when instance is in idle state, when instace is down,
    because when i perform a switchover with data broker, the data broker will make a shutdown in standby instance and after switchover, he attempts start the instance.


    Take a look,


    LISTENER standby

    LISTENER2 =
    (DESCRIPTION=
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=dg)(PORT=1522))
    )
    )

    SID_LIST_LISTENER2=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=STANDBY)
    (ORACLE_HOME=/u01/app/grid/product/11.2.0/grid)
    (GLOBAL_DBNAME=STANDBY)
    )
    (SID_DESC=
    (SID_NAME=STANDBY)
    (ORACLE_HOME=/u01/app/grid/product/11.2.0/grid)
    (GLOBAL_DBNAME=STANDBY_DGMGRL)
    )
    )

    TNSNAMES primary

    STANDBY =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1522))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)(UR=A)
    (SERVICE_NAME = STANDBY)
    )
    )

    lsnrctl status listener2 in standby host when standby instance is down

    [grid@dg ~]$ lsnrctl status listener2

    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 12:49:10

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg)(PORT=1522)))
    STATUS of the LISTENER
    ------------------------
    Alias listener2
    Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date 20-APR-2013 12:37:05
    Uptime 0 days 0 hr. 12 min. 5 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
    Listener Log File /u01/app/grid/diag/tnslsnr/dg/listener2/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg)(PORT=1522)))
    Services Summary...
    Service "STANDBY" has 1 instance(s).
    Instance "STANDBY", status UNKNOWN, has 1 handler(s) for this service...
    Service "STANDBY_DGMGRL" has 1 instance(s).
    Instance "STANDBY", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully


    NOW  I WILL TRY TO CONNECT IN STANDBY FROM PRIMARY

    [oracle@rac3 ~]$ sqlplus sys/oracle@standby as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 20 12:51:42 2013

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

    ERROR:
    ORA-01031: insufficient privileges


    Enter user-name:


    NOW I WILL START THE STANDBY INSTANCE IN NOMOUNT STATE

    [oracle@dg ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 20 12:52:38 2013

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

    Connected to an idle instance.

    SQL> startup nomount
    ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
    ORACLE instance started.

    Total System Global Area 626327552 bytes
    Fixed Size 2230952 bytes
    Variable Size 184550744 bytes
    Database Buffers 436207616 bytes
    Redo Buffers 3338240 bytes
    SQL>


    lsnrctl status listener2 in standby host when standby instance is nomount state

    [oracle@dg ~]$ lsnrctl status listener2

    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 13:00:13

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg)(PORT=1522)))
    STATUS of the LISTENER
    ------------------------
    Alias listener2
    Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
    Start Date 20-APR-2013 12:37:05
    Uptime 0 days 0 hr. 23 min. 7 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
    Listener Log File /u01/app/grid/diag/tnslsnr/dg/listener2/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg)(PORT=1522)))
    Services Summary...
    Service "STANDBY" has 2 instance(s).
    Instance "STANDBY", status UNKNOWN, has 1 handler(s) for this service...
    Instance "STANDBY", status BLOCKED, has 1 handler(s) for this service...
    Service "STANDBY_DGMGRL" has 1 instance(s).
    Instance "STANDBY", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully



    NOW I WILL TRY TO CONNECT IN STANDY AGAIN FROM PRIMARY

    [oracle@rac3 ~]$ sqlplus sys/oracle@standby as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 20 12:53:32 2013

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


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    SQL>

    OK IT'S WORK but this is not my problem
    my probem is ORA-01031: insufficient privileges when instance is down
  • 12. Re: ORA-01031 + RAC 11.2.0.3 64 bits + dataguard single instance 32 bits
    900455 Newbie
    Currently Being Moderated
    Hi,

    Can you check if the oracle home location in the listener is given correctly. I see it is pointing to GRID_HOME where you have done the static registration.


    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points