一日风雨交加,晚上值班时,一业务的数据库空间不够, 报警 。
正常停库 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
然后cp -r /u01/app /db 再接着 mv /u01 /u02bak
[root@CP07_NV1_DB /]# umount /db [root@CP07_NV1_DB /]# mv db/ u01 [root@CP07_NV1_DB /]# mount //dev/mapper/data-db /u01
改权限
[root@CP07_NV1_DB app]# chown -R oracle:oinstall oracle [root@CP07_NV1_DB app]# chmod -R 775 oracle/
[root@CP07_NV1_DB app]# chown -R oracle:dba oraInventory/ [root@CP07_NV1_DB app]# chmod -R 774 oraInventory/ [root@CP07_NV1_DB app]# su - oracle [oracle@CP07_NV1_DB ~]$ cat .bash_profile # .bash_profile
# Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_UNQNAME=CP07MV1DB; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=CP07NV1DB; export ORACLE_SID PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH [oracle@CP07_NV1_DB ~]$ exit logout [root@CP07_NV1_DB app]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_redhat-lv_root 44G 42G 494M 99% / tmpfs 12G 224K 12G 1% /dev/shm /dev/sda1 485M 39M 421M 9% /boot /dev/mapper/data-db 296G 37G 244G 13% /u01 [root@CP07_NV1_DB app]# su - oracle [oracle@CP07_NV1_DB ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 12:33:15 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup ORACLE instance started.
Total System Global Area 4275781632 bytes Fixed Size 2235208 bytes Variable Size 822084792 bytes Database Buffers 3439329280 bytes Redo Buffers 12132352 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 381 Session ID: 191 Serial number: 3
可能由于昨晚数据库强制关闭,导致文件状态可能不一致,因为正常关闭数据库会同步校验各文件,使得重新启动的时候文件时间点一致。解决方案如下启动DB
SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@CP07_NV1_DB ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:48:35 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount ORACLE instance started.
Total System Global Area 4275781632 bytes Fixed Size 2235208 bytes Variable Size 822084792 bytes Database Buffers 3439329280 bytes Redo Buffers 12132352 bytes Database mounted. SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 1 1 88281 52428800 512 1 NO INACTIVE 1179936249 12-DEC-16 1179945253 12-DEC-16
4 1 88283 57671680 512 1 NO CURRENT 1179952814 12-DEC-16 2.8147E+14
3 1 88280 52428800 512 1 NO INACTIVE 1179929281 12-DEC-16 1179936249 12-DEC-16
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 2 1 88282 52428800 512 1 NO INACTIVE 1179945253 12-DEC-16 1179952814 12-DEC-16
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover database until time '2016-12-11'; Media recovery complete. SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from $database; select open_mode from $database * ERROR at line 1: ORA-00911: invalid character
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 1 1 1 52428800 512 1 NO CURRENT 1179956666 12-DEC-16 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------------- ------------- --------- ------------ --------- 4 1 0 57671680 512 1 YES UNUSED 0 0
重置日志的序列号
SQL> select member from v$logfile;
MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/CP07NV1D/redo03.log /u01/app/oracle/oradata/CP07NV1D/redo02.log /u01/app/oracle/oradata/CP07NV1D/redo01.log /u01/app/oracle/oradata/CP07NV1D/redo04.log
[oracle@CP07_NV1_DB ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:07:10 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance 2 SQL> select status from v$instance;
STATUS ------------ OPEN
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
SQL> select group#,sequence#,bytes,members,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS ---------- ---------- ---------- ---------- ---------------- 1 1 52428800 1 INACTIVE 2 2 52428800 1 CURRENT 3 0 52428800 1 UNUSED 4 0 57671680 1 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> SQL> select open_mode from v$database;
OPEN_MODE -------------------- READ WRITE