7 Replies Latest reply: Jan 23, 2013 6:13 AM by saurabh RSS

    Error during open standby database in read only mode

    650575
      hi,


      alter database open read only;
      alter database open read only
      *
      ERROR at line 1:
      ORA-10458: standby database requires recovery
      ORA-01152: file 1 was not restored from a sufficiently old backup
      ORA-01110: data file 1: '/u02/app/oracle/oradata/standby/system01.dbf'


      Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified


      what is the reason of this.

      Thanks in advance.
        • 1. Re: Error during open standby database in read only mode
          sb92075
          user647572 wrote:
          hi,


          alter database open read only;
          alter database open read only
          *
          ERROR at line 1:
          ORA-10458: standby database requires recovery
          ORA-01152: file 1 was not restored from a sufficiently old backup
          ORA-01110: data file 1: '/u02/app/oracle/oradata/standby/system01.dbf'


          Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified


          what is the reason of this.
          Error in tnsnames.ora file; error of omission?

          post content of spfile, listener.ora & sqlnet.ora files from Standby Server
          • 2. Re: Error during open standby database in read only mode
            650575
            thanks for your reply.


            [oracle@standby admin]$ cat listener.ora

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
            )
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.105.1.124)(PORT = 1521))
            )
            )
            )

            LISTENER_STANDBY =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.105.1.124)(PORT = 1521))
            )
            )


            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME =/opt/app/oracle/product/11.2.0/db_1)
            (PROGRAM = extproc)
            )
            )


            tnsfile






            [oracle@standby admin]$ cat tnsnames.ora
            standby =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST =10.105.1.124)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = standby)
            )
            )
            ora11g =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST =10.105.1.120)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ora11g)
            )
            )


            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.105.1.120)(PORT = 1521))
            )
            )

            EXTPROC_CONNECTION_DATA =
            (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            )
            (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
            )


            pfile



            [oracle@standby standby]$ cat initstandby.ora
            ora11g.__db_cache_size=130023424
            ora11g.__java_pool_size=4194304
            ora11g.__large_pool_size=4194304
            ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
            ora11g.__pga_aggregate_target=146800640
            ora11g.__sga_target=276824064
            ora11g.__shared_io_pool_size=0
            ora11g.__shared_pool_size=121634816
            ora11g.__streams_pool_size=8388608
            *.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
            *.audit_trail='db'
            *.compatible='11.2.0.1.0'
            #*.control_files='/u02/app/ora11g/oradata/ora11g/control01.ctl','/u01/app/oracle/flash_recovery_area/ora11g/control02.ctl'
            *.db_block_size=8192
            *.db_domain=''
            *.db_name='ora11g'
            *.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
            *.db_recovery_file_dest_size=4039114752
            *.diagnostic_dest='/opt/app/oracle'
            *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11g)'
            *.memory_target=2016M
            *.open_cursors=300
            *.processes=150
            *.remote_login_passwordfile='EXCLUSIVE'
            *.undo_tablespace='UNDOTBS1'

            *.audit_file_dest='/opt/app/oracle/admin/standby/adump'
            *.audit_trail=none
            #*.background_dump_dest='/opt/app/oracle/admin/standby/bdump'
            #*.compatible='10.2.0.2.0'
            #*.control_files='/opt/app/oracle/oradata/standby/control01.ctl'
            #,'/opt/app/oracle/oradata/standby/control02.ctl','/opt/app/oracle/or

            *.control_files='/u02/app/oracle/oradata/standby/control_sb01.ctl'
            #,'/u02/app/oracle/oradata/standby/control_02.ctl','/u02/app/oracle/oradata/standby/control_03.ctl'

            *.core_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/cdump'
            *.db_block_size=8192
            *.db_domain=''
            *.db_file_multiblock_read_count=16
            #*.db_name='standby'
            #*.dispatchers='(PROTOCOL=TCP) (SERVICE=standby)'
            *.job_queue_processes=10
            *.log_archive_dest_1='location=/opt/app/oracle/arch'
            *.log_archive_config='dg_config=(standby,ora11g)'
            *.log_archive_dest_1='LOCATION=/opt/app/oracle/oradata/standby/archivelog'
            *.log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role) db_unique_name=ora11g'
            *.log_archive_format='%t_%s_%r.dbf'
            *.standby_file_management=auto
            *.db_unique_name =standby
            *.fal_server='ora11g'
            *.fal_client='standby'
            *.service_names='standby'
            *.open_cursors=300
            *.pga_aggregate_target=525336576
            *.processes=1500
            *.remote_login_passwordfile='EXCLUSIVE'
            *.sga_target=1576009728
            *.undo_management='AUTO'
            *.undo_tablespace='undotbs02'
            #*.user_dump_dest='/opt/app/oracle/diag/rdbms/standby/standby/trace'
            *.standby_file_management ='manual'
            *.instance_name =standby
            #*.standby_archive_dest=/opt/app/oracle/oradata/standby/archivelog
            *.db_file_name_convert=(/u02/app/ora11g/oradata/ora11g,/u02/app/oracle/oradata/standby)
            *.log_file_name_convert='/u02/app/ora11g/oradata/ora11g','/u02/app/oracle/oradata/standby'
            #*.remote_listener=LISTENER_ora11g
            • 3. Re: Error during open standby database in read only mode
              CKPT
              alter database open read only;
              alter database open read only
              *
              ERROR at line 1:
              ORA-10458: standby database requires recovery
              ORA-01152: file 1 was not restored from a sufficiently old backup
              ORA-01110: data file 1: '/u02/app/oracle/oradata/standby/system01.dbf'

              Hi,

              Both primary & standby is in sync or not?

              post from primary:-
              select thread#,max(sequence#) from v$archived_log group by thread#;

              Post from Standby:-
              select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;


              Make it sync, if once it is sync, stop MRP then open database
              Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
              Fix this listener service issue, check is instance is registered with listener or not

              $lsnrctl services.
              • 4. Re: Error during open standby database in read only mode
                rodneyli
                The error "ORA-10458: standby database requires recovery" indicates that you need more archive logs to be applied before you can open the database.
                This could be due to a backup(using traditional method of putting tablespace in backup mode) running on the primary such that the database is inconsistent at that very moment.
                • 5. Re: Error during open standby database in read only mode
                  askraks
                  Hi,


                  standby is not in sync and Do the standby rollforward

                  http://asanga-pradeep.blogspot.com/2011/01/roll-forward-physical-standby-10gr2.html

                  open the DB in readonly.


                  Kind Regards,
                  Rakesh
                  • 6. Re: Error during open standby database in read only mode
                    985892
                    .

                    Edited by: 982889 on Jan 23, 2013 1:17 AM
                    • 7. Re: Error during open standby database in read only mode
                      saurabh
                      You can do is apply 4-5 archive logs of primary database and then try to open in read only mode.