记一次数据库无法登陆问题

最新推荐文章于 2024-05-04 09:38:05 发布

gengyaocool

最新推荐文章于 2024-05-04 09:38:05 发布

阅读量106

点赞数

分类专栏： oracle 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/gengyaocool/article/details/129096714

版权

oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

登陆发现提示用户名密码错误，意识到是数据库的问题，

查看数据库

用plsql登陆数据库提示ora-00025

经查阅资料，找到了问题排查方向，可能是archive log 日志已满。

（1）用sys用户登录

$ sqlplus / as sysdba

（2）查询归档日志空间

SQL> show parameter recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oracle/app/fast_recovery_area
db_recovery_file_dest_size           big integer 8G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
————————————————

（3）查询归档日志使用率

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE	     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE			      0 			0
	      0

REDO LOG			      0 			0
	      0

ARCHIVED LOG			  91.25 			0
	      4


FILE_TYPE	     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
BACKUP PIECE			      0 			0
	      0

IMAGE COPY			      0 			0
	      0

FLASHBACK LOG			      0 			0
	      0


FILE_TYPE	     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
FOREIGN ARCHIVED LOG		      0 			0
	      0


7 rows selected.

（4）扩展归档日志空间大小

alter system set db_recovery_file_dest_size=64G scope=spfile;

（5）重启数据库

SQL> shutdown immediate   
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup open
ORACLE instance started.

Total System Global Area 2.6857E+10 bytes
Fixed Size		    2265944 bytes
Variable Size		 1.2415E+10 bytes
Database Buffers	 1.4428E+10 bytes
Redo Buffers		   11501568 bytes
Database mounted.



^C
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01013: user requested cancel of current operation
Process ID: 11852
Session ID: 570 Serial number: 3

这里启动数据库遇到了问题，一直卡在Database mounted.

使用plsql连接提示ora-01033

等了有10分钟没反应

接下来新开一个ssh窗口

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.6857E+10 bytes
Fixed Size		    2265944 bytes
Variable Size		 1.2415E+10 bytes
Database Buffers	 1.4428E+10 bytes
Redo Buffers		   11501568 bytes
Database mounted.
Database opened.

至此，数据库可以正常访问了