oracle 模除,oracle非归档模（noarchive)式下冷备与恢复

生产中的数据库模式都是archivelog模式，noarchivelog 一定不能使用在生产数据库中，看了网上的资料做了一次关于noarchivelog模式下的冷备与恢复操作；实验的数据库对象为 undosql数据库；

用oracle用户连接数据库，

[oracle@data /]$ env |grep ORA

ORACLE_SID=undosql

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@data /]$ sqlplus / as sysdba

SQL> archive log list;

Database log modeNoArchive Mode      //非归档模式

Automatic archivalDisabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence1

Current log sequence3

SQL> select name from v$database;

NAME

---------

UNDOSQL                    //数据库名字为undosql

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

关闭数据库，原来的数据文件存放在/u01/app/oracle/oradata下

[oracle@dataoradata]$ pwd

/u01/app/oracle/oradata

[oracle@data oradata]$ ll

total 8

drwxr-x--- 2 oracle oinstall 4096 May 1520:11 orcl

drwxr-x--- 2 oracle oinstall 4096 May 16 08:57 undosql

一、我们在/u01/app/oracle下建立一个和backup目录(可以随便取名字，但是要拥有oracle权限)，先把oradata下undosql数据库的的所有数据拷贝到该目录下作为备份，用cp命令

[oracle@data backup]$ cp /u01/app/oracle/oradata/undosql/* .

undosql数据库的口令文件和spfiel文件 不需要拷贝，假设这些文件放在另外一个磁盘的目录中；

[oracle@data oracle]$ cd oradata///把undosql的数据库该个名字模拟磁盘损坏；

[oracle@data oradata]$ ll

total 8

drwxr-x--- 2 oracle oinstall 4096 May 1520:11 orcl

drwxr-x--- 2 oracle oinstall 4096 May 1608:57 undosql

[oracle@data oradata]$ mv undosql/undosql.bak

我们现在假设存放数据库文件的磁盘oradata损坏了，undosql数据库不能启动了，要把原先备份在backup中的数据库恢复到另外一个磁盘neworadata磁盘下(新加的磁盘)，

二、添加一块磁盘，挂在到系统中neworadata目录；

[root@data dev]# ll sd*

brw-r----- 1 root disk 8,0 May 16 09:08 sda

brw-r----- 1 root disk 8,1 May 16 09:09 sda1

brw-r----- 1 root disk 8,2 May 16 09:08 sda2

brw-r----- 1 root disk 8,3 May 16 09:09 sda3

brw-r----- 1 root disk 8, 16 May 16 09:08 sdb

[root@data dev]# fdisk sdb

Device contains neither a valid DOSpartition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes willremain in memory only,

until you decide to write them. After that,of course, the previous

content won't be recoverable.

Warning: invalid flag 0x0000 of partitiontable 4 will be corrected by w(rite)

Command (m for help): n

Command action

eextended

pprimary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-652, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK(1-652, default 652):

Using default value 652

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@data dev]# ll sd*

brw-r----- 1 root disk 8,0 May 16 09:08 sda

brw-r----- 1 root disk 8,1 May 16 09:09 sda1

brw-r----- 1 root disk 8,2 May 16 09:08 sda2

brw-r----- 1 root disk 8,3 May 16 09:09 sda3

brw-r----- 1 root disk 8, 16 May 16 10:00sdb

brw-r----- 1 root disk 8, 17 May 16 10:00sdb1

[root@data dev]# mkfs.ext3 sdb1

mke2fs 1.39 (29-May-2006)

Filesystem label=

OS type: Linux

Block size=4096 (log=2)

Fragment size=4096 (log=2)

655360 inodes, 1309289 blocks

65464 blocks (5.00%) reserved for the superuser

First data block=0

Maximum filesystem blocks=1342177280

40 block groups

32768 blocks per group, 32768 fragments pergroup

16384 inodes per group

Superblock backups stored on blocks:

32768, 98304, 163840, 229376, 294912, 819200, 884736

Writing inode tables: done

Creating journal (32768 blocks): done

Writing superblocks and filesystemaccounting information: done

This filesystem will be automaticallychecked every 30 mounts or

180 days, whichever comes first.Use tune2fs -c or -i to overrid

[root@data dev]# mount /dev/sdb1/u01/app/oracle/neworadata   //把新加的磁盘挂在到neworadata目录下；

[root@data dev]# chown -R oracle:oinstall /u01/app/oracle/neworadata    //修改权限

在/etc/fstab中追加一行

/dev/sdb1/u01/app/oracle/neworadataext3defaults0 0

~

[root@data dev]# df -Th

[root@data ~]# df -Th

Filesystem    Type    Size  Used Avail Use% Mounted on

/dev/sda3     ext3     27G   15G   11G  59% /

/dev/sda1     ext3     99M   22M   72M  24% /boot

tmpfs        tmpfs   1002M  480M  522M  48% /dev/shm

/dev/sdb1     ext3    5.0G  128M  3.1G  34% /u01/app/oracle/neworadata

三、从备份文件拷贝到neworadata目录下

[oracle@data oracle]$ cp -Rf/u01/app/oracle/backup/* /u01/app/oracle/neworadata

四、登录数据库，这时候肯定是不会启动成功，我们要修改参数文件和控制文件中指定的文件；

4.1、[oracle@data oracle]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production onThu May 16 10:50:59 201

Copyright (c) 1982, 2009, Oracle.All rights reserved.

SQL> conn /as sysdba

Connected to an idle instance.

SQL> create pfile from spfile;                  //创建pfile文件；

File created.

在$ORACLE_HOME/dbs目录下可以看到pfile文件

-rw-r--r-- 1 oracle oinstall946 May 16 13:05 initundosql.ora

4.2、编辑pfile文件(initundosql.ora)重新指定控制文件的新路径路径

*.control_files='/u01/app/oracle/neworadata/control01.ctl','/u01/app/oracle/flash_recovery_area/undosql/control02.ctl'

4.3、保存退出，在切换到oracle下，生成新的spfile文件，

SQL>create spfile from pfil

File created.

4.4、接下来修改稿控制文件里面的信息：把数据库加载到mount状态

SQL>startup mount;

ORACLE instance started.

Total System Global Area835104768 bytes

Fixed Size2217952 bytes

Variable Size499124256 bytes

Database Buffers331350016 bytes

Redo Buffers2412544 bytes

Database mounted.

SQL> desc v$datafile;

SQL> col name format a50

SQL> select file#,name from v$datafile;

FILE# NAME

------------------------------------------------------------

1/u01/app/oracle/oradata/undosql/system01.dbf

2 /u01/app/oracle/oradata/undosql/sysaux01.dbf

3/u01/app/oracle/oradata/undosql/undotbs01.dbf

4/u01/app/oracle/oradata/undosql/users01.dbf

5/u01/app/oracle/oradata/undosql/example01.dbf

修改这些文件的路径名称，指定到新的目录下面；

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/system01.dbf' to'/u01/app/oracle/neworadata/system01.dbf';

Database altered.

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/sysaux01.dbf' to'/u01/app/oracle/neworadata/sysaux01.dbf';

Database altered.

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/undotbs01.dbf' to'/u01/app/oracle/neworadata/undotbs01.dbf';

Database altered.

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/users01.dbf' to'/u01/app/oracle/neworadata/users01.dbf';

Database altered.

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/example01.dbf' to'/u01/app/oracle/neworadata/example01.dbf';

Database altered.

SQL> select file#,name from v$datafile;

FILE# NAME

------------------------------------------------------------

1 /u01/app/oracle/neworadata/system01.dbf

2 /u01/app/oracle/neworadata/sysaux01.dbf

3 /u01/app/oracle/neworadata/undotbs01.dbf

4 /u01/app/oracle/neworadata/users01.dbf

5 /u01/app/oracle/neworadata/example01.dbf

修改成功

4.5、修改临时文件：

SQL> desc v$tempfile;

NameNull?Type

------------------------------------------------- ----------------------------

FILE#NUMBER

CREATION_CHANGE#NUMBER

CREATION_TIMEDATE

TS#NUMBER

RFILE#NUMBER

STATUSVARCHAR2(7)

ENABLEDVARCHAR2(10)

BYTESNUMBER

BLOCKSNUMBER

CREATE_BYTESNUMBER

BLOCK_SIZENUMBER

NAMEVARCHAR2(513)

SQL> select file#,name from v$tempfile;

FILE# NAME

------------------------------------------------------------

1 /u01/app/oracle/oradata/undosql/temp01.dbf

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/temp01.dbf' to'/u01/app/oracle/neworadata/temp01.dbf';

Database altered.

4.6、修改联机重做日志文件：

SQL> desc v$logfile;

NameNull?Type

------------------------------------------------- ----------------------------

GROUP#NUMBER

STATUSVARCHAR2(7)

TYPEVARCHAR2(7)

MEMBERVARCHAR2(513)

IS_RECOVERY_DEST_FILEVARCHAR2(3)

SQL> col member format a50

SQL> select group#,member fromv$logfile;

GROUP# MEMBER

------------------------------------------------------------

3 /u01/app/oracle/oradata/undosql/redo03.log

2 /u01/app/oracle/oradata/undosql/redo02.log

1 /u01/app/oracle/oradata/undosql/redo01.log

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/redo03.log' to'/u01/app/oracle/neworadata/redo03.log';

Database altered.

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/redo02.log' to'/u01/app/oracle/neworadata/redo02.log';

Database altered.

SQL> alter database rename file'/u01/app/oracle/oradata/undosql/redo01.log' to'/u01/app/oracle/neworadata/redo01.log';

Database altered.

确认所有的文件都指定到新的文件路下；然后加载到open状态，如果能够加载成功，说明数据库已经恢复过来了；

SQL> alter database open;

Database altered.

总的思路来说，在Noarchive模型下，要全备数据库只能做冷备，要恢复的话只能是恢复到之前冷备的时间，之后的数据是不能够恢复的，所以在生产数据库中，是不用noarchive模式的；以上的实验结果是看了网上的资料做的；写下来做个记录；和大家分享一下；