12 Replies Latest reply: Apr 22, 2013 9:48 PM by 900455 RSS

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

    1001175
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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