sqlplus /as sysdba
报错ORA-12162
切回系统
确认系统当前的ORACLE_HOME和ORACLE_SID环境变量
[oracle@asdlabdb01 ~]$ echo $ORACLE_HOME
空的
[oracle@asdlabdb01 ~]$ echo $ORACLE_SID
空的
查看环境变量
vi .bash_profile 没有问题
查看权限
ll .bash_profile
-rw--rw--- 1 oracle oinstall 529 10鏈?24 20:18 /home/oracle/.bash_profile
有问题。修改权限
chmod 775 /home/oracle/.bash_profile
进入ORLACE
sqlplus /as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
1 1 133 52428800 512 2 NO
INACTIVE 4087905 07-11月-14 4144262 08-11月-14
2 1 134 52428800 512 2 NO
CURRENT 4144262 08-11月-14 2.8147E+14
3 2 95 52428800 512 2 NO
INACTIVE 4127073 07-11月-14 4235424 09-11月-14
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
4 2 96 52428800 512 2 NO
CURRENT 4235424 09-11月-14 2.8147E+14
都不能归档
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE .31 0
1
REDO LOG 3.56 0
4
ARCHIVED LOG 95.7 0
200
空间使用率 95.7%
查看归档日志的空间只有5G左右
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FLASH_RECOVERY
db_recovery_file_dest_size big integer 5727M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
查看 +FLASH_RECOVERY 可使用空间还有150G
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 1536000 1531867 0 1531867 0 Y DATA/
MOUNTED EXTERN N 512 4096 1048576 151346 145534 0 145534 0 N FLASH_RECOVERY/
MOUNTED EXTERN N 512 4096 1048576 20480 20383 0 20383 0 N OCR_VOTING/
增大闪回日志文件的最大大小为50G
SQL> alter system set db_recovery_file_dest_size=50g;
系统已更改。
现在空间使用率为11.1%
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE .04 0
1
REDO LOG .4 0
4
ARCHIVED LOG 11.1 0
205
恢复正常
直接删除归档日志
asmcmd>
asmcmd>cd FLASH_RECOVERY
asmcmd>cd PROD
asmcmd>cd ARCHIVELOG
asmcmd>pwd
ASMCMD> pwd
+FLASH_RECOVERY/PROD/ARCHIVELOG
ASMCMD> ls
2014_10_24/
2014_10_25/
2014_10_26/
2014_10_27/
2014_10_28/
2014_10_29/
2014_10_30/
2014_10_31/
2014_11_01/
2014_11_02/
2014_11_03/
2014_11_04/
2014_11_05/
2014_11_06/
2014_11_07/
2014_11_10/
然后开始删除从最早的开始删除
检查一些无用的archivelog
RMAN> crosscheck archivelog all;
删除过期的归档
RMAN> delete expired archivelog all;
注：删除过期的归档
这样就把归档文件删除了。再进入sqlplus 查看ARCHIVELOG日志使用率！
第二种方法就是增大闪回日志文件的最大大小。如下：
alter system set DB_RECOVERY_FILE_DEST_SIZE=50g；
以上处理方法是当遇到出现日志写满报错时的处理，建议最好做个任务，定时删除日志，如下：
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; //删除七天前的归档
DELETE ARCHIVELOG FROM TIME 'SYSDATE-7'; //删除七天到现在的归档
有C++难题，加我！