Oracle

Oracle问题总结

浅时光 · 2月12日 · 2020年 8948次已读

问题1:OR来源(Source):https://www.dqzboy.comA-01589

问题描述:

must use RESETLOGS or NORESETLOGS option for database open

问题解决:

[[email protected] ~]$ rman target / 

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 12 14:14:30 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1554026542, not open)

RMAN> recover database;

Starting recover at 12-FEB-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=767 device type=DISK

starting media recovery

archived log for thread 1 with sequence 184 is already on disk as file /data/oracle/oradata/orcl/redo01.log
archived log file name=/data/oracle/oradata/orcl/redo01.log thread=1 sequence=184
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-FEB-20

RMAN> reset database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of reset database command on default channel at 02/12/2020 14:15:14
RMAN-20008: incarnation key must be specified in NOCATALOG mode

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    3273641984 bytes

Fixed Size                     2217792 bytes
Variable Size               2113931456 bytes
Database Buffers            1140850688 bytes
Redo Buffers                  16642048 bytes

RMAN> recover database;

Starting recover at 12-FEB-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 12-FEB-20

RMAN> alter database open resetlogs;

问题2:ORA-12514

问题描述:

#这样启动后远程连接会报错:
oracle ORA-12514:TNS:listener does not currently know of service requested in connect descriptor

#查看oracle监听状态
[[email protected] ~]# su - oracle
[[email protected] ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-FEB-2020 14:21:09

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bms-dev)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                12-FEB-2020 14:20:34
Uptime                    0 days 0 hr. 0 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/bms-dev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bms-dev)(PORT=1521)))
The listener supports no services    #提示服务没有监听
The command completed successfully
来源(Source):https://www.dqzboy.com

问题原因:

  • 数据库实例没注册到listener
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 12 14:29:33 2020

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

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

#这里出现报错:解决方案看问题3
SQL> alter system register;
alter system register
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

#解决上面的问题后,重新登入oracle数据库,然后启动数据库
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 12 14:32:11 2020

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3273641984 bytes
Fixed Size		    2217792 bytes
Variable Size		 2113931456 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		   16642048 bytes
Database mounted.
Database opened.

#再次查看orale监听状态
[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-FEB-2020 14:32:59

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bms-dev)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                12-FEB-2020 14:20:34
Uptime                    0 days 0 hr. 12 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/bms-dev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bms-dev)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
#以上表示数据库状态已经正常

问题3:ORA-01012

问题描述:

SQL> alter system register;
alter system register
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

问题原因:

  • 未完全关闭数据库导致ORA-01012: not logged来源(Source):https://www.dqzboy.com

问题解决:

#找到进程kill掉后,然后登入数据库启动

[[email protected] ~]$ ps -ef |grep ora_dbw0_
oracle   23736     1  0 14:17 ?        00:00:00 ora_dbw0_orcl
oracle   24886 24161  0 14:31 pts/3    00:00:00 grep --color=auto ora_dbw0_
[[email protected] ~]$ kill -9 23736
[[email protected] ~]$ ps -ef |grep ora_dbw0_
oracle   24902 24161  0 14:31 pts/3    00:00:00 grep --color=auto ora_dbw0_

[[email protected] ~]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area 3273641984 bytes
Fixed Size		    2217792 bytes
Variable Size		 2113931456 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		   16642048 bytes
Database mounted.
Database opened.

问题4:ORA-01113来源(Source):https://www.dqzboy.com和ORA-01110

问题描来源(Source):https://www.dqzboy.com述:

#oracle 打开数据库提示ORA-01113和ORA-01110
[[email protected] ~]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 7732424 bytes
Variable Size 535042342 bytes
Database Buffers 101234741824 bytes
Redo Buffers 104342576 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/app/oradata/phoneclient/system01.dbf'

问题解决:

[[email protected] ~]$ sqlplus / as sysdba
SQL> RECOVER DATAFILE '/oracle/app/oradata/phoneclient/system01.dbf '
Media recovery complete.

SQL> recover tablespace system;
Media recovery complete.

SQL> RECOVER DATABASE;
Media recovery complete.

SQL> ALTER DATABASE OPEN;
Database altered.

执行上面SQL遇到问题:

  • 问题:ORA-00283和ORA-01610
#如果执行上面的语句出现以下报错,尝试通过下面的方案进行解决
[[email protected] ~]$ sqlplus / as sysdba
SQL> RECOVER DATAFILE '/oracle/app/oradata/phoneclient/system01.dbf'

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

解决方案:

[[email protected] ~]$ rman target /
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    3273641984 bytes

Fixed Size                     2217792 bytes
Variable Size               2113931456 bytes
Database Buffers            1140850688 bytes
Redo Buffers                  16642048 bytes

RMAN> recover database;

Starting recover at 12-FEB-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 12-FEB-20

RMAN> alter database open resetlogs;

database opened
Oracle问题总结-浅时光博客
0 条回应
    本站已安全运行: | 耗时 0.367 秒 | 查询 75 次 | 内存 15.97 MB