遭遇N个ORA报错--见证存储目录及数据库名称修改

今天拿到一个镜像还原了一台数据库服务器，实例名及文件存储都不是想要的。如此就动手改一改，坑一个个踩，让大家看看，还能有更多的ORA么

1. 部署环境：

2. 数据库初始状态

数据库base目录直接被重命名了，数据库只能启动到mount状态，若要进入open，遇到
第一个ORA

3.读取文件错误：ORA-01157 & ORA-01110

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select name from v$datafile ;
NAME
--------------------------------
/mnt/oracle/tj/system01.dbf
/mnt/oracle/tj/sysaux01.dbf
/mnt/oracle/tj/undotbs01.dbf
/mnt/oracle/tj/users01.dbf

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/mnt/oracle/tj/system01.dbf'

复制

既然知道前因后果，要解决也简单，找到存储上真实的文件位置，告诉DB它们在哪儿。
数据表空间 & 临时表空间 & undo表空间 一锅处理了

2.1 遭遇ORA-27037

第一次执行变更，拿一条试试，咦，

SQL> alter database rename file '/mnt/oracle/tj/system01.dbf'  to  '/mnt/vir/oracle/virdata/system01.dbf'   ;
alter database rename file '/mnt/oracle/tj/system01.dbf'  to  '/mnt/vir/oracle/virdata/system01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 1 - new file
'/mnt/vir/oracle/virdata/system01.dbf' not found
ORA-01110: data file 1: '/mnt/oracle/tj/system01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

复制

由报错特别是Linux-x86_64 Error可知，文件或者目录找不到，这里通常是目标的文件找不到，再次返回操作系统存储查看，还就是，目标目录 /mnt/vir/oracle/virdata/ 下无文件，原文件还在 tj目录下，手动移动文件到目标目录

[oracle@ZZDBA_B_10020 tj]$ ll
total 1526116
-rw-r-----. 1 oracle oinstall   9748480 Mar 20 23:02 control01.ctl
-rw-r-----. 1 oracle oinstall  52429312 Nov 22 01:22 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Nov 22 01:22 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Nov 22 01:31 redo03.log
-rw-r-----. 1 oracle oinstall 534781952 Nov 22 01:31 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 775954432 Nov 22 01:31 system01.dbf
-rw-r-----. 1 oracle oinstall  30416896 Aug 22  2023 temp01.dbf
-rw-r-----. 1 oracle oinstall  78651392 Nov 22 01:31 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Nov 22 01:31 users01.dbf
## 进入tj目录，执行
mv *.dbf ../virdata/
复制

2.2 再次执行变更（文件名重命名）

SQL> alter database rename file '/mnt/oracle/tj/system01.dbf'  to  '/mnt/vir/oracle/virdata/system01.dbf'   ;

Database altered.

SQL> alter database rename file '/mnt/oracle/tj/sysaux01.dbf'  to  '/mnt/vir/oracle/virdata/sysaux01.dbf'   ;
alter database rename file '/mnt/oracle/tj/undotbs01.dbf' to  '/mnt/vir/oracle/virdata/undotbs01.dbf'  ;
alter database rename file '/mnt/oracle/tj/users01.dbf'   to  '/mnt/vir/oracle/virdata/users01.dbf'    ;
alter database rename file '/mnt/oracle/tj/temp01.dbf'    to  '/mnt/vir/oracle/virdata/temp01.dbf'     ;

Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.

复制

2.4 修改完成后，尝试打开数据库，遭遇ORA-03113，暂放过

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 27143
Session ID: 1705 Serial number: 29383
复制

3. 修改初始化参数，修改相关存储目录及数据库名

-- 关闭数据库
SQL> shutdown immediate
ORA-01507: database not mounted
复制

3.1 由spfile创建pfile，并进行修改目录及dbname

再次之前，也要检查下环境变量文件 ~/.bash_profile

SQL> create pfile='/mnt/pfile20240321.ora' from spfile;

File created.
复制

复制一份通过vim修改完成后，保存文件名为vir_pfile20240321.ora，再次创建spfile

3.2 创建spfile二进制初始化参数文件

SQL> create spfile from pfile='/mnt/vir_pfile20240321.ora';

File created.
复制

3.3 mount数据库，遭遇ORA-09925

SQL> startup mount;
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
复制

咦，看这报错，好像是目录的问题，来查看下

SQL> show parameter au
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> exit
复制

3.4 重新登录sql*plus 遭遇报错 ORA-09925 & ORA-01075

[oracle@ZZDBA_B_10020 mnt]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 20 23:37:06 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-09925: ??????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-01075: ??????

Warning: You are no longer connected to ORACLE.
复制

怎么回事，sqlplus都进不去了，文件不存在，什么文件不存在呢？提示都是“???”，回头看看3.1说到的环境变量问题
好家伙，目录指向不对，修改环境变量相关目录

[oracle@ZZDBA_B_10020 ~]$ vi .bash_profile

export ORACLE_BASE=/mnt/vir/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=virdb
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export ORACLE_UNQNAME=virdb;
export JAVA_HOME=/usr/local/java;
export ORACLE_TERM=xterm;
export NLS_DATE_FORMAT="YYYY:MM:DD HH24:MI:SS";
export NLS_LANG=american_america.AL32UTF8;
export TNS_ADMIN=$ORACLE_HOME/network/admin;
export ORA_NLS11=$ORACLE_HOME/nls/data;
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export TEMP=/tmp
export TMPDIR=/tmp
 -- 使之立即生效
[oracle@ZZDBA_B_10020 ~]$ source .bash_profile

复制

再次进入sqlplus，吖~可以了

[oracle@ZZDBA_B_10020 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 20 23:38:25 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.
复制

好嘞，回头再聊看看3.3的问题，找不到audit trail file

SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/mnt/vir/oracle/product/11.2.0/dbhome_1/dbs/initvirdb.ora'
SQL>
SQL> create spfile from pfile='/mnt/vir_pfile20240321.ora';

File created.

SQL> startup nomount;
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL>
复制

检查初始化参数文件，大家是否发现了问题：目录不存在

[oracle@ZZDBA_B_10020 mnt]$ cat vir_pfile20240321.ora|grep au
*.audit_file_dest='/mnt/vir/oracle/admin/virdb/adump'
*.audit_trail='db'
[oracle@ZZDBA_B_10020 mnt]$ ll /mnt/vir/oracle/admin/virdb/adump
ls: cannot access /mnt/vir/oracle/admin/virdb/adump: No such file or directory
复制

创建相关目录

[oracle@ZZDBA_B_10020 mnt]$ mkdir -p /mnt/vir/oracle/admin/virdb/adump

复制

再来启动到nomount状态，可以了

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size            1040188576 bytes
Database Buffers         1392508928 bytes
Redo Buffers               20275200 bytes
SQL>
复制

4. 启动到monut状态，读取控制文件，遭遇ORA-00205

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

复制

解决：控制文件所在目录出错导致：找到原控制文件，移动到正确的路径

4.1 再次重启，遭遇ORA-01103

shuntdown immediate
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size            1040188576 bytes
Database Buffers         1392508928 bytes
Redo Buffers               20275200 bytes
ORA-01103: database name 'TJ' in control file is not 'virdb'
复制

控制文件的database name 与初始化参数的一致，导致启动失败
解决思路：重建控制文件，为此需要找到原有控制文件进行编辑，而目前在nomount下

4.2 重建控制文件

思路：关闭数据库，更改为原database name ，启动到mount ，并备份控制文件

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
复制

– 修改参数文件，重启

SQL> create spfile from pfile='/mnt/pfile20240321-tj.ora';

File created.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size            1040188576 bytes
Database Buffers         1392508928 bytes
Redo Buffers               20275200 bytes
Database mounted.
-- 再关闭数据库
复制

关键点，备份控制文件

SQL> alter database backup controlfile to trace ;

Database altered.
复制

在追踪日志文件里面，会看到一条记录，说明备份的控制文件所在的trace文件

Thu Mar 21 02:18:37 2024
alter database backup controlfile to trace
Backup controlfile written to trace file /mnt/vir/oracle/diag/rdbms/tj/virdb/trace/virdb_ora_6163.trc
Completed: alter database backup controlfile to trace

复制

找到文件，并获取及修改创建控制文件脚本

SQL> create spfile from pfile='/mnt/pfile20240321-sc.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size            1040188576 bytes
Database Buffers         1392508928 bytes
Redo Buffers               20275200 bytes

SQL>  CREATE CONTROLFILE SET DATABASE "virdb" RESETLOGS  FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/mnt/vir/oracle/virdata/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/mnt/vir/oracle/virdata/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/mnt/vir/oracle/virdata/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/mnt/vir/oracle/virdata/system01.dbf',
  '/mnt/vir/oracle/virdata/sysaux01.dbf',
  '/mnt/vir/oracle/virdata/undotbs01.dbf',
  '/mnt/vir/oracle/virdata/users01.dbf'
CHARACTER SET AL32UTF8
;
复制

其中，这一句很关键：
CREATE CONTROLFILE [SET|REUSE] DATABASE “virdb” [RESETLOGS|NORESETLOGS] [FORCE LOGGING ARCHIVELOG|NOARCHIVELOG]

未选好参数，你可能会遇到

CREATE CONTROLFILE REUSE DATABASE "virdb" RESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name TJ in file header does not match given name of virdb
ORA-01110: data file 1: '/mnt/vir/oracle/virdata/system01.dbf'

复制

也可能会遇到

CREATE CONTROLFILE DATABASE "VIRDB" RESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name TJ in file header does not match given name of VIRDB
ORA-01110: data file 1: '/mnt/vir/oracle/virdata/system01.dbf'

复制

再次调整创建控制文件的脚本

CREATE CONTROLFILE SET DATABASE "virdb" RESETLOGS  FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/mnt/vir/oracle/virdata/control01.ctl'
ORA-27038: created file already exists
Additional information: 1
复制

从报错可以看出，控制文件已存在，不允许创建。由此可见：创建控制文件的脚本应该是没有问题了
解决：进入操作系统，将控制文件备份，然后再次执行脚本,创建成功

.....
Control file created.

复制

5. 恢复控制文件（推荐步骤）,遭遇ORA-2703

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 987371 generated at 11/22/2023 01:31:49 needed for thread 1
ORA-00289: suggestion : /mnt/vir/oracle/fast_recovery_area/VIRDB/archivelog/2024_03_21/o1_mf_1_6_%u_.arc
ORA-00280: change 987371 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: ������������������������ '/mnt/vir/oracle/fast_recovery_area/VIRDB/archivelog/2024_03_21/o1_mf_1_6_%u_.arc' ORA-2703 7: ������������������������
Linux-x86_64
Error: 2: No such file or directory
Additional information: 3


ORA-00308: ������������������������ '/mnt/vir/oracle/fast_recovery_area/VIRDB/archivelog/2024_03_21/o1_mf_1_6_%u_.arc' ORA-2703 7: ������������������������
Linux-x86_64
Error: 2: No such file or directory
Additional information: 3
复制

查看对应目录，确实没有相关的归档文件，略过

6. open数据库，遭遇 ORA-01113 & ORA-01110，需要进行介质恢复

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/mnt/vir/oracle/virdata/system01.dbf'

复制

6.1 进入RMAN 进行数据库文件恢复，由于无归档，直接按默认方式

RMAN> recover database ;

Starting recover at 2024:03:21 02:55:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=572 device type=DISK

starting media recovery

unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/21/2024 02:55:25
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 987371

RMAN> exit


Recovery Manager complete.
``
### 6.2 恢复完成，再次open数据库
```sql
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED
复制

尝试1

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

复制

提示想要使用参数 RESETLOGS or NORESETLOGS

尝试2

SQL> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

复制

尝试3

SQL> alter database open RESETLOGS;

Database altered.

-- 查看打开状态
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
复制

7. 数据库open成功后，查看告警日志，遭遇ORA-25153: 临时表空间为空

Thu Mar 21 23:18:36 2024
Errors in file /mnt/vir/oracle/diag/rdbms/virdb/easdb/trace/virdb_m000_5669.trc:
ORA-25153: 临时表空间为空

复制

解决：增加临时表空间文件，若已存在，可重用

SQL> select * from dba_temp_files;

no rows selected

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/vir/oracle/virdata/temp01.dbf' REUSE;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------
/mnt/vir/oracle/virdata/temp01.dbf

复制

8. 简单操作测试

SQL> create table t (a int,b varchar2(12));

Table created.

SQL> insert into t values(1,'广州');

1 row created.
SQL> insert into t values(2,'上海');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         A B
---------- ------------
         1 广
         2 上海
``


基本可用，收工~

复制