This discussion is archived
7 Replies Latest reply: Jan 23, 2013 4:13 AM by saurabh RSS

Error during open standby database in read only mode

650575 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    .

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

Legend

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