导航：首页 > 数据库 >

数据库的迁移救援

发表于：2025-01-21 作者：千家信息网编辑

千家信息网最后更新 2025年01月21日，一日风雨交加，晚上值班时，一业务的数据库空间不够，报警。正常停库SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE

千家信息网最后更新 2025年01月21日数据库的迁移救援

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

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

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

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

重置日志的序列号

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

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