Migration and Rescue of Database 02/25 Update SLTechnology News&Howtos

Migration and Rescue of Database

2025-02-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

A day of wind and rain, when on duty at night, a business database space is not enough, call the police.

Normal shutdown

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Then cp-r / u01/app / db

Then 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

Change the authority

[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

# 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

It is possible that the state of the files may be inconsistent due to the forced shutdown of the database last night, because the normal shutdown of the database synchronously verifies the files, so that the files are restarted at the same time. The solution is to start DB as follows

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.

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

Reset the serial number of the log

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

two

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

twenty

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

thirty

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