Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ ORA-01157 & ORA-01110 Troubleshooting     - GCC library link order problems (Programming)

- Extended VMware Ubuntu root partition size (Linux)

- Oracle 11g can not export a variety of empty table solution (Database)

- Java string intern constant pool resolution Introduction (Programming)

- Analysis of Java keyword final (Programming)

- Getting Started Linux Shell Scripting (Programming)

- Sshuttle A VPN-based transparent proxy that uses ssh (Server)

- CentOS 6.4 under PXE + Kickstart unattended operating system installation (Programming)

- How to download GOG games in Linux command line (Linux)

- Calling Qt libraries to implement functional processes of some summary (Programming)

- Dynamic programming Android (Programming)

- AngularJS (Programming)

- Delegate in C # (Programming)

- Detailed Linux network security policies and protection measures (Linux)

- Struts2 dynamic call DMI and error Solution (Programming)

- Install Python 3.3.4 under CentOS 6.4 (Linux)

- Shuffle Process Arrangement in MapReduce (Server)

- The development environment to build Nodejs under Ubuntu 14.04 (Linux)

- CKEditor + SWFUpload achieve a more powerful editor (Linux)

- Linux network security strategy (Linux)

 
         
  ORA-01157 & ORA-01110 Troubleshooting
     
  Add Date : 2018-11-21      
         
         
         
  Today, when the virtual machine test databases that reported ORA-01157 and ORA-01110 error, suggesting no one data file. I saw the file name will know where the problem is another cause virtual machine does not start, because the database created by dnfs a "remote" table space. Because do not want to start that virtual machine (save memory), simply put the table space deletion, familiarize yourself with the process trouble-shooting.

1. Prepare the environment
We tested in Oracle11g in.

SQL>

SQL> select * from v $ version;

BANNER

-------------------------------------------------- ------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL / SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

2. Start the database error

In the database during startup, reported ORA-01157 and ORA-01110 error, suggesting no data files.

SYS @ HOEGH> startup
 
ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 524290820 bytes

Database Buffers 411041792 bytes

Redo Buffers 4919296 bytes

Database mounted.

ORA-01157: can not identify / lock data file 9 - see DBWR trace file

ORA-01110: data file 9: \ '/ u02 / oradata / HOEGH / test_tbs01.dbf \'

SYS @ HOEGH> select status from v $ instance;

STATUS

------------

MOUNTED

SYS @ HOEGH>
Due to another virtual machine does not start, the database during startup daemon can not find the data files can not be locked or the corresponding data file, the database will prohibit access to these data files and other data files are not affected. ORA-01157 and ORA-01110 errors General errors together.

 
3. Restart the database to mount state, delete data files
When you start the database, nomount state read the parameter file, mount the control status reading documents in the mount state can delete data files.

SYS @ HOEGH>
 
SYS @ HOEGH> startup nomount

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 524290820 bytes

Database Buffers 411041792 bytes

Redo Buffers 4919296 bytes

SYS @ HOEGH>

SYS @ HOEGH>

SYS @ HOEGH> alter database mount;

Database altered.

SYS @ HOEGH> alter database datafile \ '/ u02 / oradata / HOEGH / test_tbs01.dbf \' offline drop;

Database altered.
 
4. Open the database, delete the table space
Start the database to the open state, find the corresponding table space data file name; then, remove the table space.

SYS @ HOEGH> alter database open;
 

Database altered.

SYS @ HOEGH>

SYS @ HOEGH> select file_id, tablespace_name from dba_data_files;

  FILE_ID TABLESPACE_NAME

---------- ------------------------------

        4 USERS

        3 UNDOTBS1

        2 SYSAUX

        1 SYSTEM

        5 TEST1

        6 TEST2

        7 TEST3

        8 TEST

        9 TEST_TBS

9 rows selected.

SYS @ HOEGH> col file_name for a50

SYS @ HOEGH> col file_id for 99

SYS @ HOEGH> col tablespace_name for a10

SYS @ HOEGH> col status for a10

SYS @ HOEGH> col online_status for a20

SYS @ HOEGH> select file_name, file_id, TABLESPACE_NAME, STATUS, ONLINE_STATUS from dba_data_files;

FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS

-------------------------------------------------- ------- ---------- ---------- --------------------

/u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM

/u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE

/u02/oradata/HOEGH/test_tbs01.dbf 9 TEST_TBS AVAILABLE RECOVER

9 rows selected.

SYS @ HOEGH> drop tablespace TEST_TBS including contents;

Tablespace dropped.

SYS @ HOEGH>

SYS @ HOEGH> select file_name, file_id, TABLESPACE_NAME, STATUS, ONLINE_STATUS from dba_data_files;

FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS

-------------------------------------------------- ------- ---------- ---------- --------------------

/u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM

/u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE

/u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE

8 rows selected.

SYS @ HOEGH>
 
5. Restart the database
After performing the above operation, restart the database to ensure that the database can be opened normally.

SYS @ HOEGH>
 
SYS @ HOEGH> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS @ HOEGH>

SYS @ HOEGH>

SYS @ HOEGH> startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 511707908 bytes

Database Buffers 423624704 bytes

Redo Buffers 4919296 bytes

Database mounted.

Database opened.

SYS @ HOEGH>
     
         
         
         
  More:      
 
- Summary of Docker mounted directory (Server)
- Linux Log (Linux)
- Comparison of sorting algorithms (Programming)
- CentOS 6.4 RPM install MySQL-5.6.22-1 (Database)
- Use Epoll develop high-performance application server on Linux (Server)
- Oracle inline view updates problems encountered (Database)
- Construction CA certificate using OpenSSL command line (Server)
- Using Java arrays implement sequential stack (Programming)
- Security measures under Unix multi-user operating system (Linux)
- To share some very useful Vim command (Linux)
- Oracle Linux 5.5 (64bit) Install Oracle 11gR2 RAC detailed tutorial (Database)
- C / C ++ language usage summary of const (Programming)
- Linux top command to get started (Linux)
- Integrated security administrator Linux accident management (Linux)
- Oracle10g 64-bit installation solution in Windows 2008 Server R2 (Database)
- Python control multi-process and multi-threaded concurrency (Programming)
- CentOS 6.5 boot automatically mount the hard drive (Linux)
- Depth Java Singleton (Programming)
- PHP 7.0 Upgrade Notes (Linux)
- Json data with double backslashes to a single backslash Json data processing (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.