第十六章 非归档模式下的冷备份和恢复
<p>[TOC]</p>
<h1>第十六章 非归档模式下的冷备份和恢复</h1>
<ul>
<li>冷备份：脱机或关机备份，数据库在关闭状态下进行物理备份。</li>
</ul>
<h2>实验环境的搭建</h2>
<ul>
<li>确认表空间及用户存在</li>
</ul>
<pre><code>select * from dba_tablespaces;
PIONEER_DATA
PIONEER_INDX
select * from dba_users where lock_date is null ;
PJINLIAN
SH
select * from dba_role_privs where grantee='PJINLIAN';
select * from dba_sys_privs where grantee='PJINLIAN';
RESOURCE
CONNECT
SELECT ANY TABLE
grant select any table to pjinlian;
alter user sh identified by sh account unlock;
connect sh/sh
select count(*) from customers;--55500
select count(*) from sales; --918843</code></pre>
<ul>
<li>从sh用户复制数据到pjinlian用户
<pre><code>connect pjinlian/wuda
create table sales as select * from sh.sales ;
create table customers as select * from sh.customers;
create index sales_prod_id_idx on sales(prod_id) tablespace pioneer_indx;
create index sales_cust_id_idx on sales(cust_id) tablespace pioneer_indx;
create index sales_channel_id_idx on sales(channel_id) tablespace pioneer_indx;
create index customers_gender_idx on customers(cust_gender) tablespace pioneer_indx;
create index customers_city_idx on customers(cust_city) tablespace pioneer_indx;
-- 检查创建的数据是否正确
select table_name,tablespace_name from user_tables;
select index_name,table_name,tablespace_name,status from user_indexes;
-- 创建pjinlian用户时指定了表空间的使用权限，现设置其为无限
select username,
tablespace_name,
bytes / 1024 / 1024 MB,
max_bytes / 1024 / 1024 "Max MB"
from dba_ts_quotas
where username = 'PJINLIAN';
alter user pjinlian quota unlimited on PIONEER_INDX;
alter user pjinlian quota unlimited on PIONEER_DATA;</code></pre></li>
</ul>
<h2>数据库非归档模式和备份的术语</h2>
<ul>
<li>
<p>备份是针对物理崩溃进行，与是否归档模式关系重大，备份涉及重做日志文件</p>
</li>
<li>
<p>查看当前数据库运行模式</p>
<pre><code>SQL> connect sys/oracle as sysdba
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 34
Current log sequence 36</code></pre>
</li>
<li>
<p>设置数据库为非归档模式</p>
<pre><code>shutdown immediate
startup mount
alter database noarchivelog;
alter database open
SQL> archive log list</code></pre>
</li>
<li>备份中经常用到的几个术语:
1.数据库的全备份：备份数据库的所有数据文件和控制文件，这也是最常用的备份方法。非归档模式下数据库必须关闭进行全备份，归档模式下数据库可关闭可打开。
2.控制文件的备份：可以通过SQL命令来备份（第4章有学到）
3.表空间的备份：备份组成某一表空间的所有文件。在非归档模式下，只能单独地备份只读表空间或者正常脱机的表空间
4.数据文件的备份：备份单个的数据文件。在非归档模式下，只能单独地备份只读数据文件或正常脱机的数据文件。</li>
</ul>
<h2>冷备份(脱机备份)</h2>
<ul>
<li>
<p>关闭数据库复制数据文件、控制文件，其他文件相对较小也一并复制</p>
</li>
<li>
<p>快速恢复区的磁盘区
简化备份存储的管理
存储有归档日志、备份、闪回日志
存储有冗余的控制文件和冗余的重做日志文件
应该配置与数据文件，联机重做日志文件和控制文件不同的磁盘上，最好I/O控制器也不同</p>
</li>
<li>
<p>经验表明，快速恢复区至少是数据库大小的两倍</p>
</li>
<li>
<p>快速恢复区的磁盘空间由备份保留策略所控制，保留策略决定哪些文件什么时候可以废弃</p>
</li>
<li>快速恢复区两个参数
db_recovery_file_dest:快速恢复区的位置
db_recovery_file_dest_size:快速恢复区的大小
<code>show parameter db_recovery_file</code></li>
</ul>
<h3>冷备份的步骤</h3>
<p>1.使用<code>v$controlfile</code> 找到所有的控制文件。
使用<code>v$logfile</code> 找到所有的重做日志文件。
使用<code>dba_data_files</code> 找到所有的数据文件，以及与表空间的对应关系。
使用<code>v$tempfile</code> 和 <code>v$tablespace</code> 找到所有的临时文件，以及与临时表空间的对应关系。
使用<code>show parameter pfile</code> 找到正文参数文件或二进制参数文件。
2.正常关闭数据库(使用 <code>shutdown immediate|transactional|normal</code>)。
3.将所有的文件复制到备份硬盘或磁带上。
4.重新启动数据库，该操作也可能是在第2天上班时做的。</p>
<h2>冷恢复(脱机恢复)</h2>
<ul>
<li>从上次备份到系统崩溃这段时间内所有提交的数据会全部丢失</li>
</ul>
<h3>冷恢复的步骤</h3>
<p>1.如果数据库未关闭，需关闭数据库。
2.将所有的备份数据文件和备份控制文件复制到数据库中原来的位置。
3.也可以将所有的备份重做日志文件、参数文件和口令文件复制到数据库中原来的位置(该操作不是必须的)。
4.重新启动数据库。</p>
<ul>
<li>恢复成功之后，数据库即恢复到上一次备份，恢复所需的时间就是复制所有文件所需的时间。</li>
</ul>
<h2>脱机备份和脱机恢复的优缺点</h2>
<h3>脱机备份优缺点</h3>
<ul>
<li>
<p>脱机备份优点</p>
</li>
<li>脱机备份缺点</li>
</ul>
<h3>脱机恢复优缺点</h3>
<ul>
<li>
<p>脱机恢复优点</p>
</li>
<li>脱机恢复缺点</li>
</ul>
<h2>脱机备份的应用实例</h2>
<ul>
<li>查找出所需要备份的信息位置
<pre><code>SQL> connect sys/oracle as sysdba
--控制文件
set line 120
set pagesize 30
col name for a60
select name from v$controlfile ;</code></pre></li>
</ul>
<table>
<thead>
<tr>
<th>NAME</th>
</tr>
</thead>
<tbody>
<tr>
<td>/u01/app/oracle/oradata/orcl/control01.ctl</td>
</tr>
<tr>
<td>/u01/app/oracle/oradata/orcl/control02.ctl</td>
</tr>
</tbody>
</table>
<ul>
<li>重做日志文件
<pre><code>col member for a60
select member from v$logfile;</code></pre></li>
</ul>
<table>
<thead>
<tr>
<th>MEMBER</th>
</tr>
</thead>
<tbody>
<tr>
<td>/u01/app/oracle/oradata/orcl/redo03.log</td>
</tr>
<tr>
<td>/u01/app/oracle/oradata/orcl/redo02.log</td>
</tr>
<tr>
<td>/u01/app/oracle/oradata/orcl/redo01.log</td>
</tr>
</tbody>
</table>
<ul>
<li>数据文件
<pre><code>col file_name for a60
col tablespace_name for a60
select file_name,tablespace_name from dba_data_files;</code></pre></li>
</ul>
<table>
<thead>
<tr>
<th>number</th>
<th>file_name</th>
<th>tablespace_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>/home/oracle/data/disk1/sys/system01.dbf</td>
<td>SYSTEM</td>
</tr>
<tr>
<td>2</td>
<td>/home/oracle/data/disk1/sys/sysaux01.dbf</td>
<td>SYSAUX</td>
</tr>
<tr>
<td>3</td>
<td>/home/oracle/data/disk1/sys/undotbs01.dbf</td>
<td>UNDOTBS1</td>
</tr>
<tr>
<td>4</td>
<td>/home/oracle/data/disk1/use/users01.dbf</td>
<td>USERS</td>
</tr>
<tr>
<td>5</td>
<td>/home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer_<em>jg8x40jd</em>.dbf</td>
<td>PIONEER_DATA</td>
</tr>
<tr>
<td>6</td>
<td>/home/oracle/data/disk2/data/ORCL/datafile/o1_mf_pioneer_<em>jgrgzf2w</em>.dbf</td>
<td>PIONEER_INDX</td>
</tr>
<tr>
<td>7</td>
<td>/home/oracle/data/disk2/data/ORCL/datafile/o1_mf_example<em>jgy0yptr</em>.dbf</td>
<td>EXAMPLE</td>
</tr>
</tbody>
</table>
<ul>
<li>初始化文件</li>
</ul>
<pre><code>SQL> show parameter pfile
SQL> show parameter spfile</code></pre>
<table>
<thead>
<tr>
<th>NAME</th>
<th>TYPE</th>
<th>VALUE</th>
</tr>
</thead>
<tbody>
<tr>
<td>spfile</td>
<td>spfile</td>
<td>/u01/app/oracle/product/12.2.0/db_1/dbs/spfileorcl.ora</td>
</tr>
</tbody>
</table>
<ul>
<li>
<p>创建备份文件夹</p>
<pre><code>[oracle@oracle ~]$ pwd
/home/oracle
[oracle@oracle ~]$ mkdir backup
[oracle@oracle ~]$ ll
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:05 backup
[oracle@oracle ~]$ cd backup/
##创建路径，根据实际情况创建
[oracle@oracle backup]$ mkdir database
[oracle@oracle backup]$ mkdir dbs
[oracle@oracle backup]$ mkdir disk1
[oracle@oracle backup]$ mkdir disk2
[oracle@oracle backup]$ mkdir -p oradata/orcl
[oracle@oracle backup]$ ll
total 20
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:08 database
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:08 dbs
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:08 disk1
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:08 disk2
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:25 oradata
##进入oracle目录
[oracle@oracle oracle]$ cd /u01/app/oracle
##创建mgt目录
[oracle@oracle oracle]$ mkdir mgt
[oracle@oracle oracle]$ ll
drwxr-xr-x 2 oracle oinstall 4096 Jul 17 00:11 mgt</code></pre>
</li>
<li>切换回sqlplus，资源包有12c脱机备份视频</li>
</ul>
<h3>整理要备份的文件，执行复制</h3>
<table>
<thead>
<tr>
<th>数据库中路径</th>
<th>备份路径</th>
<th>文件类型</th>
</tr>
</thead>
<tbody>
<tr>
<td>/u01/app/oracle/oradata/orcl</td>
<td>/home/oracle/backup/oradata/orcl</td>
<td>控制文件、重做日志文件</td>
</tr>
<tr>
<td>/home/oracle/data/disk1</td>
<td>/home/oracle/backup/disk1</td>
<td>数据文件</td>
</tr>
<tr>
<td>/home/oracle/data/disk2</td>
<td>/home/oracle/backup/disk2</td>
<td>数据文件</td>
</tr>
<tr>
<td>/u01/app/oracle/product/12.2.0/db_1/dbs</td>
<td>/home/oracle/backup/dbs</td>
<td>初始化文件</td>
</tr>
</tbody>
</table>
<ul>
<li>66IP备份路径</li>
</ul>
<table>
<thead>
<tr>
<th>原始文件路径</th>
<th>备份路径</th>
<th>文件类型</th>
</tr>
</thead>
<tbody>
<tr>
<td>/u01/app/oracle/oradata/*</td>
<td>/home/oracle/coolbackup/oradata</td>
<td>数据文件、控制文件</td>
</tr>
<tr>
<td>/home/oracle/backup/*</td>
<td>/home/oracle/coolbackup/backup</td>
<td>数据文件</td>
</tr>
<tr>
<td>/u01/app/oracle/product/12.2.0/db_1/dbs/*.ora</td>
<td>/home/oracle/coolbackup/dbs</td>
<td>参数文件</td>
</tr>
</tbody>
</table>
<ul>
<li>
<p>关闭数据库，执行文件复制</p>
<pre><code>SQL> connect sys/oracle as sysdba
SQL> shutdown immediate
SQL> exit
cp /u01/app/oracle/oradata/orcl/*.* /home/oracle/backup/oradata/orcl
cp -rf /home/oracle/data/disk1/* /home/oracle/backup/disk1
cp -rf /home/oracle/data/disk2/* /home/oracle/backup/disk2
cp /u01/app/oracle/product/12.2.0/db_1/dbs/*.ora /home/oracle/backup/dbs
sql</code></pre>
</li>
<li>
<p>66IP操作</p>
<pre><code>[oracle@oracle script]$ chmod a+x coolbackup.sh
[oracle@oracle script]$ vim coolbackup.sh
#! /bin/sh
## coolbackup DB
rlwrap sqlplus / as sysdba <<EOF
shutdown immediate;
exit;
EOF
echo "Database stopped successfully!"
cp -r /u01/app/oracle/oradata/* /home/oracle/coolbackup/oradata
cp -r /home/oracle/backup/* /home/oracle/coolbackup/backup
cp /u01/app/oracle/product/12.2.0/db_1/dbs/*.ora /home/oracle/coolbackup/dbs
echo "---------- Copy action completed ----------"</code></pre>
</li>
<li>检查文件复制情况是否正常，冷备份完成，打开数据库</li>
</ul>
<h2>脱机恢复到原来位置的应用实例</h2>
<ul>
<li>
<p>文件原始位置（另一台电脑操作的，前面说的路径有所不同）</p>
<pre><code>select name from v$controlfile ;
/u01/app/oracle/oradata
select member from v$logfile;
/home/oracle/backup/disk3
/home/oracle/backup/disk6
/home/oracle/backup/disk9
select file_name,tablespace_name from dba_data_files;
/home/oracle/backup/disk1
/home/oracle/backup/disk2
/home/oracle/backup/disk4
/home/oracle/backup/disk7
/u01/app/oracle/oradata
spfile /u01/app/oracle/product/12.2.0/db_1/dbs/spfileorcl.ora</code></pre>
</li>
<li>脱机恢复
关闭数据库
替换文件
启动数据库</li>
</ul>
<pre><code>--丢失表
truncate table sales;
--查看表是否还存在
select * from cat;
--查看表中数据是否还存在
select count(*) from sales;
--关闭数据库
connect system/oracle as sysdba
shutdown immediate
exit
--执行复制操作，与备份时调换路径即可
cp -r /home/oracle/coolbackup/oradata/* /u01/app/oracle/oradata
cp -r /home/oracle/coolbackup/backup/* /home/oracle/backup
cp /home/oracle/coolbackup/dbs/spfileorcl.ora /u01/app/oracle/product/12.2.0/db_1/dbs
$ sql
SQL> startup
select count(*) from pjinlian.sales;
COUNT(*)
--------
918843</code></pre>
<h2>脱机恢复到非原来位置的应用实例</h2>
<h3>disk7磁盘坏了，恢复本该在disk7磁盘的数据文件到disk4</h3>
<table>
<thead>
<tr>
<th>表空间名</th>
<th>原来损坏位置</th>
<th>备份到新位置</th>
</tr>
</thead>
<tbody>
<tr>
<td>PIONEER_DATA</td>
<td>/home/oracle/backup/disk7/orcl/PIONEER_DATA01.dbf</td>
<td>/home/oracle/backup/disk4/orcl/PIONEER_DATA01.dbf</td>
</tr>
<tr>
<td>PIONEER_INDX</td>
<td>/home/oracle/backup/disk7/orcl/PIONEER_INDX01.dbf</td>
<td>/home/oracle/backup/disk4/orcl/PIONEER_INDX01.dbf</td>
</tr>
<tr>
<td>TS_DATA</td>
<td>/home/oracle/backup/disk7/orcl/ts_data01.dbf</td>
<td>/home/oracle/backup/disk4/orcl/ts_data01.dbf</td>
</tr>
</tbody>
</table>
<ul>
<li>模拟磁盘坏，删除两张表
<pre><code>select table_name,tablespace_name from user_tables ;
truncate table sales;
truncate table CUSTOMERS;
select * from cat;
select count(*) from sales;
select count(*) from CUSTOMERS;
SQL> connect system/oracle as sysdba
shutdown immediate
exit</code></pre></li>
</ul>
<table>
<thead>
<tr>
<th>table_name</th>
<th>tablespace_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>SALES</td>
<td>PIONEER_DATA</td>
</tr>
<tr>
<td>CUSTOMERS</td>
<td>PIONEER_DATA</td>
</tr>
</tbody>
</table>
<ul>
<li>
<p>以下两个复制不变</p>
<pre><code>$ cp -r /home/oracle/coolbackup/oradata/* /u01/app/oracle/oradata
$ cp /home/oracle/coolbackup/dbs/spfileorcl.ora /u01/app/oracle/product/12.2.0/db_1/dbs
## 以下复制disk7特殊处理
##重做日志文件的
$ cp -r /home/oracle/coolbackup/backup/disk3/* /home/oracle/backup/disk3
$ cp -r /home/oracle/coolbackup/backup/disk6/* /home/oracle/backup/disk6
$ cp -r /home/oracle/coolbackup/backup/disk9/* /home/oracle/backup/disk9
##数据文件的
$ cp -r /home/oracle/coolbackup/backup/disk1/* /home/oracle/backup/disk1
$ cp -r /home/oracle/coolbackup/backup/disk2/* /home/oracle/backup/disk2
$ cp -r /home/oracle/coolbackup/backup/disk4/* /home/oracle/backup/disk4
##损坏的磁盘的disk7，复制到disk4中
$ cp -r /home/oracle/coolbackup/backup/disk7/* /home/oracle/backup/disk4</code></pre>
</li>
<li>
<p>启动数据库</p>
<pre><code>SQL> startup mount
alter database rename file '/home/oracle/backup/disk7/orcl/PIONEER_DATA01.dbf' to '/home/oracle/backup/disk4/orcl/PIONEER_DATA01.dbf';
alter database rename file '/home/oracle/backup/disk7/orcl/PIONEER_INDX01.dbf' to '/home/oracle/backup/disk4/orcl/PIONEER_INDX01.dbf';
alter database rename file '/home/oracle/backup/disk7/orcl/ts_data01.dbf' to '/home/oracle/backup/disk4/orcl/ts_data01.dbf';
alter database open;</code></pre>
</li>
<li>检查是否正常
<pre><code>select file_name,tablespace_name from dba_data_files;
select tablespace_name,status from dba_tablespaces;
select count(*) from pjinlian.sales;
select count(*) from pjinlian.CUSTOMERS;</code></pre></li>
</ul>
<h2>冷备份示例</h2>
<ul>
<li>
<p>查看需要备份的文件</p>
<pre><code>select name from v$controlfile ;
select member from v$logfile;
select file_name,tablespace_name from dba_data_files;
SQL> show parameter spfile --有值是spfile开启的
select * from v$parameter where name='spfile';
select * from v$spparameter;</code></pre>
</li>
<li>创建对应的目录，脚本准备</li>
</ul>
<table>
<thead>
<tr>
<th>源数据位置</th>
<th>备份位置</th>
<th>文件类型</th>
</tr>
</thead>
<tbody>
<tr>
<td>/u01/app/oracle/oradata/*</td>
<td>/home/oracle/coolbackup/oradata</td>
<td>控制文件</td>
</tr>
<tr>
<td>/home/oracle/dbdata/disk3/*</td>
<td>/home/oracle/coolbackup/disk3</td>
<td>重做日志文件</td>
</tr>
<tr>
<td>/home/oracle/dbdata/disk1/*</td>
<td>/home/oracle/coolbackup/disk1</td>
<td>系统表空间数据文件</td>
</tr>
<tr>
<td>/home/oracle/dbdata/disk2/*</td>
<td>/home/oracle/coolbackup/disk2</td>
<td>用户表空间数据文件</td>
</tr>
</tbody>
</table>
<pre><code>[oracle@oracle script]$ vim /home/oracle/script/coolbackup.sh
#! /bin/sh
## CoolBacksCript
rlwrap sqlplus / as sysdba <<EOF
shutdown immediate;
host cp -rf /u01/app/oracle/oradata/* /home/oracle/coolbackup/oradata
host cp -rf /home/oracle/dbdata/disk3/* /home/oracle/coolbackup/disk3
host cp -rf /home/oracle/dbdata/disk1/* /home/oracle/coolbackup/disk1
host cp -rf /home/oracle/dbdata/disk2/* /home/oracle/coolbackup/disk2
exit;
EOF</code></pre>
<h2>冷恢复示例</h2>
<pre><code>SQL> shutdown immediate
[oracle@oracle script]$ vim /home/oracle/script/coolrecover.sh
#! /bin/sh
## CoolRecoverCript
cp -rf /home/oracle/coolbackup/oradata/* /u01/app/oracle/oradata
cp -rf /home/oracle/coolbackup/disk3/* /home/oracle/dbdata/disk3
cp -rf /home/oracle/coolbackup/disk1/* /home/oracle/dbdata/disk1
cp -rf /home/oracle/coolbackup/disk2/* /home/oracle/dbdata/disk2
echo "-----Copy action completed!-----"
SQL> startup</code></pre>
<h2>关机移动数据文件的位置</h2>
<pre><code>PIONEER_DATA /home/oracle/backup/disk7/orcl/PIONEER_DATA01.dbf /home/oracle/backup/disk4/orcl/PIONEER_DATA01.dbf
PIONEER_INDX /home/oracle/backup/disk7/orcl/PIONEER_INDX01.dbf /home/oracle/backup/disk4/orcl/PIONEER_INDX01.dbf
TS_DATA /home/oracle/backup/disk7/orcl/ts_data01.dbf /home/oracle/backup/disk4/orcl/ts_data01.dbf
$ cp -r /home/oracle/coolbackup/backup/disk7/* /home/oracle/backup/disk4
SQL> startup mount
alter database rename file '/home/oracle/backup/disk7/orcl/PIONEER_DATA01.dbf' to '/home/oracle/backup/disk4/orcl/PIONEER_DATA01.dbf';
alter database rename file '/home/oracle/backup/disk7/orcl/PIONEER_INDX01.dbf' to '/home/oracle/backup/disk4/orcl/PIONEER_INDX01.dbf';
alter database rename file '/home/oracle/backup/disk7/orcl/ts_data01.dbf' to '/home/oracle/backup/disk4/orcl/ts_data01.dbf';
alter database open;
-- 检查是否正常
select file_name,tablespace_name from dba_data_files;
select tablespace_name,status from dba_tablespaces;
select count(*) from pjinlian.sales;
select count(*) from pjinlian.CUSTOMERS;</code></pre>