|
生产中的数据库模式都是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目录；
[iyunv@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
[iyunv@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. [iyunv@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 [iyunv@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
[iyunv@data dev]# mount /dev/sdb1/u01/app/oracle/neworadata //把新加的磁盘挂在到neworadata目录下； [iyunv@data dev]# chown -R oracle:oinstall /u01/app/oracle/neworadata //修改权限 在/etc/fstab中追加一行 /dev/sdb1/u01/app/oracle/neworadataext3defaults0 0 ~
[iyunv@data dev]# df -Th [iyunv@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模式的；以上的实验结果是看了网上的资料做的；写下来做个记录；和大家分享一下；
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2013-5-17 08:56:41
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡