数据库的迁移救援

一日风雨交加，晚上值班时，一业务的数据库空间不够， 报警 。

正常停库
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