당신은 주제를 찾고 있습니까 “ora 01110 ora 01157 – ORA-01110,ORA-01157 CANNOT IDENTIFY/LOCK DATAFILE“? 다음 카테고리의 웹사이트 https://chewathai27.com/you 에서 귀하의 모든 질문에 답변해 드립니다: https://chewathai27.com/you/blog. 바로 아래에서 답을 찾을 수 있습니다. 작성자 tech nizan 이(가) 작성한 기사에는 조회수 872회 및 좋아요 6개 개의 좋아요가 있습니다.
ora 01110 ora 01157 주제에 대한 동영상 보기
여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!
d여기에서 ORA-01110,ORA-01157 CANNOT IDENTIFY/LOCK DATAFILE – ora 01110 ora 01157 주제에 대한 세부정보를 참조하세요
This video shows how to get back lost datafile with backup and validate the database.
ora 01110 ora 01157 주제에 대한 자세한 내용은 여기를 참조하세요.
How to resolve ORA-01111 ORA-01110 ORA-01157 in a …
Oracle Database – Enterprise Edition – Version 7.3.2.2 to 11.2.0.3 [Release 7.3.2 to 11.2]: How to resolve ORA-01111 ORA-01110 ORA-01157 in …
Source: support.oracle.com
Date Published: 11/5/2022
View: 9365
Resolve ORA-01111, ORA-01110, ORA-01157 – Doyensys Blog
Resolve ORA-01111, ORA-01110, ORA-01157 … Mailn reasons for a file being created as UNNAMED or MISSING in the standby database, including …
Source: doyensys.com
Date Published: 8/26/2022
View: 7074
ORA-01157 tips – Burleson Consulting
As we see, the ORA-01157 is caused by a locking issue with the database writer (DBWR) background process. During a recovery, this can be caused by a unopened …
Source: www.dba-oracle.com
Date Published: 1/17/2022
View: 7149
Troubleshooting Common ORA-1157 Errors (cannot identify …
An ORA-01157 is issued whenever Oracle attempts to access a file but cannot find or lock the file. The background process was either unable to find one of …
Source: www.thegeekdiary.com
Date Published: 3/29/2021
View: 4155
ORA-01157: cannot identify/lock data file string – see DBWR …
ORA-01157: cannot entify/lock data file string – see DBWR trace file … Cause: The background process was either unable to find one of the data files or failed …
Source: nimishgarg.blogspot.com
Date Published: 12/21/2022
View: 6589
How to resolve ORA-01111, ORA-01110, ORA-01157 errors
How to resolve ORA-01111, ORA-01110, ORA-01157 errors · On stabdby; Check for the files needs to be recovered. SQL> select * from v$recover_file …
Source: heliosguneserol.com
Date Published: 6/9/2022
View: 9398
ORA-01157 ORA-01110 when adding resource – NetWeaver
When you are adding resource to cluster in Oracle Fail Safe environment, you are getting error ORA-01157 and ORA-01110: ORA-01157: cannot entify/lock data …
Source: userapps.support.sap.com
Date Published: 4/12/2022
View: 2009
ORA-01111 ORA-01110 ORA-01157 in a physical standby …
ORA-01111 ORA-01110 ORA-01157 in a physical standby database. After adding data files to the main library, the master-slave synchronization is suddenly …
Source: blog.actorsfit.com
Date Published: 1/18/2022
View: 4047
ORA-01157: cannot identify ORA-01110: data file … – dbaclass
SQL> select file_name from dba_temp_fileS; ERROR: ORA-01157: cannot entify/lock data file 205 – see DBWR trace file. ORA-01110: data file …
Source: dbaclass.com
Date Published: 8/6/2021
View: 5804
ORA-01157 and ORA-01110 cannot recognize/lock the data file
ORA-01157 and ORA-01110 cannot recognize/lock the data file … This error is caused by the fact that the lv containing the lv of /dev/rrpt001vg05 …
Source: www.soughttech.com
Date Published: 5/5/2022
View: 1220
주제와 관련된 이미지 ora 01110 ora 01157
주제와 관련된 더 많은 사진을 참조하십시오 ORA-01110,ORA-01157 CANNOT IDENTIFY/LOCK DATAFILE. 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.
주제에 대한 기사 평가 ora 01110 ora 01157
- Author: tech nizan
- Views: 조회수 872회
- Likes: 좋아요 6개
- Date Published: 2018. 11. 23.
- Video Url link: https://www.youtube.com/watch?v=I3OhY0IvLaI
How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database
How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database (Doc ID 1416554.1)
Last updated on APRIL 06, 2021
Applies to:
Goal
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Database Name: MMUGPROD
********
The physical standby recovery process was terminated with the error below:
Thu Feb 09 20:21:11 EAT 2012
Errors in file /
/MMUGPROD/bdump/mmugprod_mrp0_27482.trc: ORA-01111: name for data file 97 is unknown – rename to correct file
ORA-01110: data file 97: ‘/
/dbs/UNNAMED00097′ ORA-01157: cannot identify/lock data file 97 – see DBWR trace file
ORA-01111: name for data file 97 is unknown – rename to correct file
ORA-01110: data file 97: ‘/
/dbs/UNNAMED00097′ Thu Feb 09 20:21:11 EAT 2012
Errors in file /
/MMUGPROD/bdump/mmugprod_mrp0_27482.trc: ORA-01111: name for data file 97 is unknown – rename to correct file
ORA-01110: data file 97: ‘/
/dbs/UNNAMED00097′ ORA-01157: cannot identify/lock data file 97 – see DBWR trace file
ORA-01111: name for data file 97 is unknown – rename to correct file
ORA-01110: data file 97: ‘/
/dbs/UNNAMED00097′ Thu Feb 09 20:21:11 EAT 2012
MRP0: Background Media Recovery process shutdown (MMUGPROD)
Thu Feb 09 20:21:11 EAT 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Thu Feb 09 20:23:25 EAT 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[13]: Successfully opened standby log 8: ‘/
/MMUGPROD/redo8a.rdo’ ‘/
/dbs/UNNAMED00097 Thu Feb 09 20:21:11 EAT 2012
How to resolve the errors so that the recovery process could proceed so that the physical standby could be in sync with the primary database?
Solution
Sign In To view full details, sign in with your My Oracle Support account. Register Don’t have a My Oracle Support account? Click to get started!
Resolve ORA-01111, ORA-01110, ORA-01157
Mailn reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) Improper parameter settings related to file management.
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
For example if we add a data file on the Primary when parameter STANDBY_FILE_MANAGEMENT on standby set to MANUAL , While recovery process(MRP) is trying to apply archives, Due to that parameter setting it will create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP process and Errors will be as below.
Errors From Alert Log file:-
Errors in file /u01/app/oracle/admin/bdump/cc_mrp0_22294.trc:
ORA-01111: name for data file 247 is unknown – rename to correct file
ORA-01110: data file 247: ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’
ORA-01157: cannot identify/lock data file 247 – see DBWR trace file
ORA-01111: name for data file 247 is unknown – rename to correct file
ORA-01110: data file 247: ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’
Trace File:-
MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 247 is unknown – rename to correct file
ORA-01110: data file 247: ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’
ORA-01157: cannot identify/lock data file 247 – see DBWR trace file
ORA-01111: name for data file 247 is unknown – rename to correct file
ORA-01110: data file 247: ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’
ORA-01111: name for data file 247 is unknown – rename to correct file
ORA-01110: data file 247: ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’
ORA-01157: cannot identify/lock data file 247 – see DBWR trace file
ORA-01111: name for data file 247 is unknown – rename to correct file
ORA-01110: data file 247: ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’
ARCH: Connecting to console port…
Troubleshooting:-
Check for the files needs to be recovered.
SQL> select * from v$recover_file where error like ‘%FILE%’;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
———- ——- ——- ——————– ———- ——————–
247 ONLINE ONLINE FILE MISSING 0
SQL>
Identify on primary of data file 247(Primary Database)
SQL> select file#,name from v$datafile where file#=247;
FILE# NAME
———- ———————————————————————-
247 +DATA/c/datafile/undotbs9.595.750444337
SQL>
Identify dummy file name created in (Standby)
SQL> select file#,name from v$datafile where file#=247;
FILE# NAME
———- ———————————————————————-
247 /u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247
SQL>
Crosscheck that no MRP is running and STANDBY_FILE_MANAGEMENT can be enabled once after creating file on standby
SQL> alter database create datafile ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’ as ‘+DATA/oracle/datafile/undotbs9_245_81494317’;
Database altered.
SQL>
If You are managing File system ASM with OMF, you are not allowed to create data file, Because it will file system names and format maintained by ASM. If you try to Create datafile as above with ASM File, You will pass with below error.
SQL> alter database create datafile ‘/u01/oracle/orahome/dbs/UNNAMED00613’ as ‘+DATA/oracle/datafile/undotbs9_245_81494317’
* ERROR at line 1:
ORA-01276: Cannot add file
+DATA/oracle/datafile/undotbs9_245_81494317. File has an Oracle Managed Files file name.
Then Run above command as shown below
SQL> alter database create datafile ‘/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00247’ as new;
Database altered.
SQL>
Enable STANDBY_FILE_MANAGEMENT to AUTO & Start MRP.
SQL> show parameter standby_file_management
NAME TYPE VALUE
———————————— ———– ——————————
standby_file_management string MANUAL
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL>
SQL> show parameter standby_file_management
NAME TYPE VALUE
———————————— ———– ——————————
standby_file_management string AUTO
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
After creating the file, MRP will start applying archives on standby database.
Note:-
Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT so that existing standby files will not be accidentally overwritten.
ORA-01157 tips
ORA-01157 tips Oracle Database Tips by Donald BurlesonDecember 15, 2015 Question: I’m getting the ORA-01157 and ORA-01110 when trying to bring my tablespaces online: SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF’ How do I fix this ORA-01157 error? Answer: First, the Oracle docs note this on the ORA-01157 error: ORA-01157: cannot identify/lock data file string – see DBWR trace file Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified. Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES. As we see, the ORA-01157 is caused by a locking issue with the database writer (DBWR) background process. During a recovery, this can be caused by a unopened data files (i.e. database mounted but not open), a missing file, a permission problem in the file (e.g. no write permissions 770 on the files owned by Oracle). If the background process is unable to reach a data file, or is unable to lock it because it is in use , ORA-01157 is thrown because the database does not allow access. The other files will not be affected, but it is important to know that opening the database using the first instance will need to use online data files. there should be other errors which appear along with ORA-01157 to aid in resolving the problem.
Straightening out ORA-01157 consists of making the files of the database available, and either regularly opening the database, or use ALTER SYSTEM CHECK DATAFILES .
If you like Oracle tuning, you might enjoy my book “Oracle Tuning: The Definitive Reference”, with 950 pages of tuning tips and scripts. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum . Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.
Burleson Consulting
The Oracle of Database Support Oracle Performance Tuning Remote DBA Services
Copyright © 1996 – 2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.
Troubleshooting Common ORA-1157 Errors (cannot identify/lock data file) – The Geek Diary
Oracle Error: ORA-1157
An ORA-01157 is issued whenever Oracle attempts to access a file but cannot find or lock the file.
Error Explanation:
01157, 00000, “cannot identify/lock data file %s – see DBWR trace file” Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However, the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified. Action: Have the operating system make the file available to the database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.
ORA-01157 errors are usually followed by ORA-01110 and possibly an Oracle operating system layer error such as ORA-07360. A DBWR trace file is generated in the background_dump_dest directory. For Example, on Solaris platform, the following errors will appear:
ORA-01157: cannot identify/lock data file 19 – see DBWR trace file ORA-01110: data file 19: ‘/app/Oracle/oradata/users02.dbf’
From the DBWR trace file:
ORA-01157: cannot identify/lock data file 19 – see DBWR trace file ORA-01110: data file 19: ‘/app/Oracle/oradata/users02.dbf’ ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3
Common Causes and Solutions for ORA-1157
1. The datafile does exist, but Oracle cannot find it.
The datafile may have been renamed at the operating system level, moved to a different directory or disk drive either intentionally or unintentionally.
In this case, restore and recover the datafile or move the datafile to its original name.
2. The datafile does not exist or is unusable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore.
For example, the datafile might be truncated or overwritten, in which case ORA-27046 will accompany ORA-1157 error. For example:
ORA-27046: file size is not a multiple of logical block size
In this case, the user has two options:
1. Recreate the tablespace that the datafile belongs to
This option is best suited for USERS, INDEX, TEMPORARY tablespaces. It is also recommended for UNDO tablespaces if the database had been SHUTDOWN CLEANLY so that no active transactions are there in the rollback segments of this tablespace. If the tablespace is SYSTEM tablespace, then this amounts to recreating or rebuilding the database. This method is best suited for temporary tablespaces (since they do not contain important data), but can be used for USERS tablespaces and INDEXES tablespaces.
This method would be helpful wherein reasonably recent exports of the objects in the tablespace are available, or that the tables in the tablespace can be repopulated by running a script or program, loading the data through SQL*Loader, etc. The steps involved are:
1. If the database is down, mount it.
SQL> STARTUP MOUNT;
2. Offline drop the datafile.
SQ> ALTER DATABASE DATAFILE ‘full_path_file_name’ OFFLINE DROP;
3. If the database is at mount, open it.
SQL> ALTER DATABASE OPEN;
4. Drop the user tablespace.
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
Note: The users can stop with this step if they do not want the tablespace anymore in the database.
5. Recreate the tablespace.
SQL> CREATE TABLESPACE tablespace_name DATAFILE ‘datafile_full_path_name’ SIZE required_size;
6. Recreate all the previously existing objects in the tablespace. This can be done using the creation scripts for the objects in that tablespace or using the recent export dump available for that tablespace objects.
2. Recover the datafile using normal recovery procedures
This option is best suited for READ ONLY tablespaces and for USERS, INDEX tablespaces where recreating is not a feasible option. If the tablespace is of type UNDO, then this is the method to be used if the database was not SHUTDOWN CLEANLY. (that is if shutdown abort had been used or the database had crashed). If the tablespace is SYSTEM, then this is the recommended method, if there are backups and archivelogs are available. If the database is in NOARCHIVELOG mode, then you can recover only if the required changes are present in the ONLINE redologs.
In many situations, recreating the user tablespace is impossible or too laborious. The solution then is to restore the lost datafile from a backup and do media recovery on it. If the database is in NOARCHIVELOG mode, you will only succeed in recovering the datafile if the redo to be applied to the datafile is within the range of the online logs.
This method would be ideal for READ ONLY tablespaces. If the tablespace was not switched to READ-WRITE after backup was taken and if the tablespace was READ ONLY at the time of backup, then recovery is just restoring the backup of this tablespace. These are the steps:
1. Restore the lost file from a backup.
2. If the database is down, mount it.
SQL> STARTUP MOUNT;
3. Issue the following query:
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# FROM V$LOG V1, V$LOGFILE V2 WHERE V1.GROUP# = V2.GROUP#;
This will list all your online redolog files and their respective sequence and first change numbers.
4. If the database is in NOARCHIVELOG mode, issue the query:
SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. Just keep in mind that all the logs to applied will be online logs, and move on to step 5. If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered. Your options at this point would be to restore the most recent full backup (and thus lose all changes to the database since) or recreate the tablespace as explained in scenario 1.
5. Recover the datafile:
SQL> RECOVER DATAFILE ‘full_path_file_name’;
6. Confirm each of the logs that you are prompted for until you receive the message “Media Recovery Complete”. If you are prompted for a non-existing archived log, Oracle probably needs one or more of the online logs to proceed with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online logs. Then enter the full path name of one of the members of the redo group whose sequence number matches the one you are being asked for. Keep entering online logs as requested until you receive the message “Media Recovery Complete” .
7. If the database is at mount stage, open it.
Operating Systems Tempfiles missing
When using TEMPORARY tablespaces with tempfiles, the absence of the tempfile at the OS level can cause ORA-1157. Since Oracle does not checkpoint tempfiles, the database can be opened even with missing tempfiles.
The solution in this case would be to drop the logical tempfile and add a new one. For example:
select * from dba_objects order by object_name; select * from dba_objects order by object_name; * ERROR at line 1: ORA-01157: cannot identify/lock data file 1026 – see DBWR trace file ORA-01110: data file 1026: ‘/Oracle/oradata/temp2_01.tmp’
Solution:
How to resolve ORA-01111, ORA-01110, ORA-01157 errors
You may hit this error on your standby database. I have 12c standalone database with dataguard setup.
We got ORA-01111, ORA-01110, ORA-01157 errors on standby database and apply has been stoped.
There are many reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) Improper parameter settings related to file management.
On standby by site If Automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
Errors From Alert Log file on standby database:
Errors in file /XXX/bdump/cc_mrp0_22294.trc:
ORA-01111: name for data file 588 is unknown – rename to correct file
ORA-01110: data file 588: ‘/XXX/db_1/dbs/UNNAMED00588’
ORA-01157: cannot identify/lock data file 588 – see DBWR trace file
ORA-01111: name for data file 588 is unknown – rename to correct file
ORA-01110: data file 588: ‘/XXX/db_1/dbs/UNNAMED00588’
On standby Trace File:-
MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 588 is unknown – rename to correct file
ORA-01110: data file 588: ‘/XXX/db_1/dbs/UNNAMED00588’
ORA-01157: cannot identify/lock data file 588 – see DBWR trace file
ORA-01111: name for data file 588 is unknown – rename to correct file
ORA-01110: data file 588: ‘/XXX/db_1/dbs/UNNAMED00588’
ORA-01111: name for data file 588 is unknown – rename to correct file
ORA-01110: data file 588: ‘/XXX/db_1/dbs/UNNAMED00588’
ORA-01157: cannot identify/lock data file 588 – see DBWR trace file
ORA-01111: name for data file 588 is unknown – rename to correct file
ORA-01110: data file 588: ‘/XXX/db_1/dbs/UNNAMED00588’
ARCH: Connecting to console port…
So here is the our troubleshooting steps:
On stabdby; Check for the files needs to be recovered.
SQL> select * from v$recover_file where error like ‘%FILE%’;
SQL> select * from v$recover_file where error like ‘%FILE%’; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ———- ——- ——- ——————– ———- ——————– 588 ONLINE ONLINE FILE MISSING 0
On Primary; Identify data file 588
FILE# NAME ———- ———————————————————————- 588 +DATA/PROD/datafile/xxx.1223.23231241
On standby; Identify dummy file SQL> select file#,name from v$datafile where file#=588;
FILE# NAME ———- ———————————————————————- 588 /XXX/db_1/dbs/UNNAMED00588
On stabdby;
SQL> alter system set standby_file_management=MANUAL scope=both;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
NAME TYPE VALUE ———————————— ———– —————————— standby_file_management string MANUAL
SQL> alter database create datafile ‘/XXX/db_1/dbs/UNNAMED00588’ as ‘+DATA/PROD/datafile/xxx.1223.23231241’;
Database altered.
If You are managing File system ASM with OMF, you are not allowed to create data file, Because it will file system names and format maintained by ASM. If you try to Create datafile as above with ASM File, You will pass with below error.
SQL> alter database create datafile ‘/u01/oracle/orahome/dbs/UNNAMED00613’ as ‘+DATA/PROD/datafile/xxx.1223.23231241’;
*
ERROR at line 1:
ORA-01276: Cannot add file
‘+DATA/PROD/datafile/xxx.1223.23231241’ File has an Oracle Managed Files file name.
So Run above command:
SQL> alter database create datafile ‘/XXX/db_1/dbs/UNNAMED00588’ as new;
Database altered.
On stabdby; Enable STANDBY_FILE_MANAGEMENT to AUTO and start MRP
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
2858093 – ORA-01157 ORA-01110 when adding resource – NetWeaver
When you are adding resource to cluster in Oracle Fail Safe environment, you are getting error ORA-01157 and ORA-01110:
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘
‘ Then you check Oracle alert log, you found error:
ORA-51106: check failed to complete due to an error. See error below
ORA-48223: Interrupt Requested – Fetch Aborted – Return Code [448] [HM_FINDING]
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
ORA-01122: database file 246 failed verification check
ORA-01110: data file 246: ‘I:\ORACLE\
\SAPDATA1\SYSAUX_2\SYSAUX.DATA2′ ORA-01565: error in identifying file ‘I:\ORACLE\
\SAPDATA1\SYSAUX_2\SYSAUX.DATA2′ ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
ORA-01111 ORA-01110 ORA-01157 in a physical standby database
After adding data files to the main library, the master-slave synchronization is suddenly interrupted, and the following error appears in the alter log of the slave library:
ORA-10879: error signaled in parallel recovery slave ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 6 is unknown – rename to correct file ORA-01110: data file 6: ‘/data/uat/oracle/12.1.0.2/test/dbs/UNNAMED00006’ ORA-01157: cannot identify/lock data file 6 – see DBWR trace file ORA-01111: name for data file 6 is unknown – rename to correct file ORA-01110: data file 6: ‘/data/uat/oracle/12.1.0.2/test/dbs/UNNAMED00006’ ksvp2penabled: ep->flg = 0, rpr->slv_flg = 0 ksvp2penabled: ep = 0x7f0883d231e8, rpr = 0x184be0730 DDE: Problem Key ‘ORA 1110’ was flood controlled (0x1) (no incident) ORA-01110: data file 6: ‘/data/uat/oracle/12.1.0.2/test/dbs/UNNAMED00006’ ORA-10458: standby database requires recovery ORA-01157: cannot identify/lock data file 6 – see DBWR trace file ORA-01111: name for data file 6 is unknown – rename to correct file ORA-01110: data file 6: ‘/data/uat/oracle/12.1.0.2/test/dbs/UNNAMED00006’
Reason for error:
The query document found that the main library standby_file_management was not set to AUTO, and the slave library could not recognize it after adding the data file, which would cause the above error
Solution:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=manual; ALTER DATABASE CREATE DATAFILE ‘/data/uat/oracle/12.1.0.2/test/dbs/UNNAMED00006’ AS ‘/data/uat/oracle/12.1.0.2/test/dbs/test01.dbf’; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
reference:
How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database (document ID 1416554.1)
http://yunlongzheng.blog.51cto.com/blog/788996/1091944
ORA-01157: cannot identify ORA-01110: data file with dba_temp_file
Recently one of our database was migrated. After migration DB is up and running, But we found below errors in the alert log.
Errors in file /oracle/app/oracle/admin/test/diag/rdbms/b2btrn/TEST/trace/test_ora_20464.trc:
ORA-01157: cannot identify/lock data file 205 – see DBWR trace file
ORA-01110: data file 205: ‘/test/oradata/b2btrn/tempfile/temp1.304.921456819’
Now I queried dba_temp_files, and got the same error.
SQL> select file_name from dba_temp_fileS;
ERROR:
ORA-01157: cannot identify/lock data file 205 – see DBWR trace file
ORA-01110: data file 205: ‘/test/oradata/b2btrn/tempfile/temp1.304.921456819’
And the file is not present physically also:
ls -ltr /test/oradata/b2btrn/tempfile/temp1.304.921456819
/test/oradata/test/tempfile/temp1.304.921456819: No such file or directory
To fix it , I have dropped the temp file:
SQL>select file#,name from v$tempfile
FILE# NAME
———- ——————————————————————-
1 /test/oradata/temp03.dbf
3 /test/oradata/b2bsit1_iastemp.dbf
4 /test/oradata/sit1b2b_iastemp.dbf
2 /test/oradata/b2btrn/tempfile/temp1.304.
SQL> alter database tempfile ‘/test/oradata/b2btrn/tempfile/temp1.304.921456819’ drop;
Database altered.
SQL>select file_name from dba_temp_fileS
FILE_NAME
————————————————————–
/test/oradata/b2bsit1_iastemp.dbf
/test/oradata/sit1b2b_iastemp.dbf
/test/oradata/temp03.dbf
ORA-01157 and ORA-01110 cannot recognize/lock the data file-Tech Notes
Previously encountered the same error in production, the user queries the data in the client table, and the following error is reported:
Errors in file /oratrace/xxx/diag/rdbms/xxx/xxx2/trace/xxx2_dbw0_8454382.trc:
ORA-01157: cannot identify /lock data file 366-see DBWR trace file
ORA-01110: data file 366:’/dev/rrpt001vg05′
This error is caused by the fact that the lv containing the lv of /dev/rrpt001vg05 is not online after the 2 nodes are restarted. After varyonvg vg, still can’t access the lv under this vg. After executing the alter system check datafiles command in the database, all data files can be accessed.
This command is generally used in the rac environment.For example, one of the nodes cannot access a certain datafile (only this node is inaccessible, and the rest of the nodes can be accessed).On the inaccessible node, you can use the ALTER SYSTEM CHECK DATAFILES command to Update the sga information of the instance.The source of the updated information is the control file.
First do an experiment to repeat the failure again:
1.停止实例2
[[email protected]:/oracle] srvctl stop instance -d CQTEST -n testdb2 [[email protected]:/oracle] crsNAME TARGET STATE SERVER STATE_DETAILS
Local Resources
ora.LISTENER.lsnr
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.gsd
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2
ora.net1.network
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.ons
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.registry.acfs
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2
Cluster Resources
ora.test.db
1 ONLINE ONLINE testdb1 Open
2 OFFLINE OFFLINE Instance Shutdown
ora.testdb1.vip
1 ONLINE ONLINE testdb1
ora.testdb2.vip
1 ONLINE ONLINE testdb2
ora.cvu
1 ONLINE ONLINE testdb1
ora.oc4j
1 OFFLINE OFFLINE
2.offline oradata04
[[email protected]:/oracle] exittestdb2:/#varyoffvg oradata04
3.重启实例2
testdb2:/#su – oracle
[[email protected]:/oracle] srvctl start instance -d CQTEST -n cqtestdb2 [[email protected]:/oracle] crsNAME TARGET STATE SERVER STATE_DETAILS
Local Resources
ora.LISTENER.lsnr
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.gsd
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2
ora.net1.network
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.ons
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.registry.acfs
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2
Cluster Resources
ora.test.db
1 ONLINE ONLINE testdb1 Open
2 ONLINE ONLINE testdb2 Open
ora.testdb1.vip
1 ONLINE ONLINE testdb1
ora.testdb2.vip
1 ONLINE ONLINE testdb2
ora.cvu
1 ONLINE ONLINE testdb1
ora.oc4j
1 OFFLINE OFFLINE
4.Login data query the table on the file
[[email protected]:/oracle] sqlplus aa/aaSQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 9 16:27:24 2019
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
AA TABLE
B TABLE
SQL> select count() from aa;
select count() from aa
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01110: data file 8: ‘/dev/rtest001vg04’
SQL> select from b;
select from b
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01110: data file 8: ‘/dev/rtest001vg04’
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
5.online vg，查看lv状态为closed
[[email protected]:/oracle] lsvgrootvg
altinst_rootvg
hbvg
oraclevg
oradata01
oradata02
oradata03
archvg1
archvg2
oradata04
[[email protected]:/oracle] lsvg -l oradata040516-010 : Volume group must be varied on; use varyonvg command.
[[email protected]:/oracle] varyonvg -c oradata04ksh: varyonvg: cannot execute
[[email protected]:/oracle] exitcqtestdb2:/#varyonvg -c oradata04
cqtestdb2:/#lsvg -l oradata04
oradata04:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
test001vg04 jfs2 40 40 1 closed/syncd N/A
test002vg04 jfs2 40 40 1 closed/syncd N/A
test003vg04 jfs2 40 40 1 closed/syncd N/A
test004vg04 jfs2 40 40 1 closed/syncd N/A
6.After loading the vg, log in to the database to view, and found that the relevant data files are still not accessible.
cqtestdb2:/#su-oracle
[[email protected]:/oracle] sqlplus aa/aaSQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 9 16:28:50 2019
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select count() from aa;
select count() from aa
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01110: data file 8: ‘/dev/rtest001vg04’
7.执行alter system check datafiles;
SQL> conn / as sysdba
Connected.
SQL> alter system check datafiles;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
8.查看lv状态已经open
[[email protected]:/oracle] lsvg -l oradata04oradata04:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
test001vg04 jfs2 40 40 1 open/syncd N/A
test002vg04 jfs2 40 40 1 closed/syncd N/A
test003vg04 jfs2 40 40 1 closed/syncd N/A
test004vg04 jfs2 40 40 1 closed/syncd N/A
[[email protected]:/oracle] sqlplus aa/aaSQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 9 16:29:41 2019
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select count(*) from aa;
COUNT(*)
———-
1924
키워드에 대한 정보 ora 01110 ora 01157
다음은 Bing에서 ora 01110 ora 01157 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.
이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!
사람들이 주제에 대해 자주 검색하는 키워드 ORA-01110,ORA-01157 CANNOT IDENTIFY/LOCK DATAFILE
- 동영상
- 공유
- 카메라폰
- 동영상폰
- 무료
- 올리기
ORA-01110,ORA-01157 #CANNOT #IDENTIFY/LOCK #DATAFILE
YouTube에서 ora 01110 ora 01157 주제의 다른 동영상 보기
주제에 대한 기사를 시청해 주셔서 감사합니다 ORA-01110,ORA-01157 CANNOT IDENTIFY/LOCK DATAFILE | ora 01110 ora 01157, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.
