Help - Search - Members - Calendar
Full Version: standby not open read only
Oracle DBA Forums > Oracle > Oracle Forum
sabah
i, I've Two database one for primary(GISC) and one for standby (GISCST) when i start open standby database i appeare erro :

ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORCLSB\SYSTEM01.DBF'

I take the copy the datafiles from primary database and Pase them to the Standby database

and the Enviroment of two database is :

SPfile for primary(GISC) IS:
*.compatible='10.2.0.3'
*.control_files='C:\oracle\product\10.2.0\oradata\gisc\CONTROL01.CTL','C:\oracle\product\10.2.0\oradata\gisc\CONTROL02.CTL','C:\oracle\product\10.2.0\oradata\gisc\CONTROL03.CTL'
*.db_block_size=8192
*.db_cache_size=30M#DEMO
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\gisc','C:\oracle\product\10.2.0\oradata\giscst'
*.db_name='GISC'
*.fal_client='gisc'
*.fal_server='giscst'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG= (GISC, GISCST)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\product\10.2.0\flash_recovery_area\ VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=GISC'
*.LOG_ARCHIVE_DEST_2='SERVICE=GISCST VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=GISCST'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\gisc','C:\oracle\product\10.2.0\oradata\giscst'
*.optimizer_mode='choose'
*.parallel_threads_per_cpu=4#SMALL
*.pga_aggregate_target=30M#DEMO
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.shared_pool_size=50M#DEMO
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.star_transformation_enabled='true'
*db_unique_name='gisc'
*.service_name='gisc'
*.instance_name='gisc'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'

--------------------------------------------------------------------------------
listener.ora for primary(GISC):

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = gisc)
(ORACLE_HOME = C:\oracle\product\10.2.0)
(SID_NAME = GISC)
)
(SID_DESC =
(GLOBAL_DBNAME = GISC_DGMGRL)
(ORACLE_HOME = C:\oracle\product\10.2.0)
(SID_NAME = GISC)
)

--------------------------------------------------------------------------------
TNSNAME For (GISC):
GISC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GISC_DGMGRL)
)
)

GISCST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.3)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GISCST_DGMGRL)
)
)

--------------------------------------------------------------------------------
SPfile for Standby database (GISCST):
*.compatible='10.2.0.3'
*.control_files='C:\oracle\product\10.2.0\oradata\giscst\CONTROL01.CTL','C:\oracle\product\10.2.0\oradata\giscst\CONTROL02.CTL','C:\oracle\product\10.2.0\oradata\giscst\CONTROL03.CTL'
*.db_block_size=8192
*.db_cache_size=30M#DEMO
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\gisc','C:\oracle\product\10.2.0\oradata\giscst'
*.db_name='GISC'
*.fal_client='giscst'
*.fal_server='gisc'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG= (GISC, GISCST)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\product\10.2.0\flash_recovery_area\ VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=GISCST'
*.LOG_ARCHIVE_DEST_2='SERVICE=GISC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=GISC'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\gisc','C:\oracle\product\10.2.0\oradata\giscst'
*.optimizer_mode='choose'
*.parallel_threads_per_cpu=4#SMALL
*.pga_aggregate_target=30M#DEMO
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.shared_pool_size=50M#DEMO
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.star_transformation_enabled='true'
*db_unique_name='giscst'
*.service_name='giscst'
*.instance_name='giscst'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'

--------------------------------------------------------------------------------
listener.ora for standby (GISCST):

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.3)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = giscst)
(ORACLE_HOME = C:\oracle\product\10.2.0)
(SID_NAME = GISCST)
)
(SID_DESC =
(GLOBAL_DBNAME = GISCST_DGMGRL)
(ORACLE_HOME = C:\oracle\product\10.2.0)
(SID_NAME = GISCST)
)

--------------------------------------------------------------------------------
TNSNAME for standby (GISCST):

GISCST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.3)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GISCST_DGMGRL)
)
)

GISC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GISC_DGMGRL)
)
)

--------------------------------------------------------------------------------
then i do ping to service :

Microsoft Windows [Version 6.1.7601]
Copyright © 2009 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>tnsping gisc

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 12-FEB-2
012 09:49:17

Copyright © 1997, 2006, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.0.2)(PORT = 1521))) (CONNECT_DATA = (SID = GISC_DGMGRL)))
OK (0 msec)

C:\Users\Administrator>tnsping giscst

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 12-FEB-2
012 09:49:40

Copyright © 1997, 2006, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.0.3)(PORT = 1521))) (CONNECT_DATA = (SID = GISCST_DGMGRL)))
OK (10 msec)



--------------------------------------------------------------------------------
Microsoft Windows [Version 6.1.7600]
Copyright © 2009 Microsoft Corporation. All rights reserved.

C:\Users\gis>tnsping gisc

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 12-FEB-2
012 09:52:22

Copyright © 1997, 2006, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.0.2)(PORT = 1521))) (CONNECT_DATA = (SID = GISC_DGMGRL)))
OK (10 msec)

C:\Users\gis>tnsping giscst

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 12-FEB-2
012 09:52:30

Copyright © 1997, 2006, Oracle. All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.0.3)(PORT = 1521))) (CONNECT_DATA = (SID = GISCST_DGMGRL)))
OK (0 msec)




burleson
Hi Sabah,

You sound as if you have not had the required Oracle DBA training . . .

You may want to get the Easy Oracle Pack to get started:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy


***************************************
>> ORA-01152

You need to talk to your site DBA to resolve this error and do what is say, sufficiently recovery your standby database!

Please read:

http://www.dba-oracle.com/t_ora_01152_file..._old_backup.htm

Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery

It sounds like you skipped a step before opening the dtasndby database:

Get the checklist from book download here:

http://www.rampant-books.com/book_0402_streams.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.