backup,restore,flash

最新推荐文章于 2023-08-25 19:27:52 发布

ORACLE_FANS

最新推荐文章于 2023-08-25 19:27:52 发布

阅读量603

点赞数

分类专栏： oracle 备份恢复文章标签： ORACLE

oracle 备份恢复专栏收录该内容

3 篇文章 0 订阅

订阅专栏

冷备：

shutdown immediate

select 'ho cp'||name||'/sda8/oraclebak/' from v$controlfile;
select 'ho cp'||name||'/sda8/oraclebak/' from v$datafile;
select 'ho cp'||name||'/sda8/oraclebak/' from v$logfile;
select 'ho cp'||name||'/sda8/oraclebak/' from v$

ho cp $ORACLE_HOME/dbs/spfilexxx(SID).ora

非关键文件恢复
非关键性损失是一种故障事件，掌握在最少时间恢复同时最少影响数据为操作的情况下

1.恢复临时表空间
a.
SQL> select username,temporary_tablespace from user_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SCOTT TEMP

b.
SQL> create temporary tablespace tempnew tempfile '/opt/oracle/database/temp1/tempnew.dbf' size 50m;

Tablespace created.

c.
SQL> alter database default temporary tablespace tempnew;

Database altered.

d.
SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

2.损坏联机日志的恢复

a.损坏的是当前联机日志

startup mount

alter database clear logfile group 1;

alter database clear unarchived logfile group 1;(上面命令不成功，则使用)

recover database until cancel;(上面命令不成功，则使用)

alter database open resetlogs;

b.损坏的是非当前联机日志

alter database clear logfile group xx;

3.索引表空间损坏
索引表空间是只包含索引的表空间
a.
drop tablespace index_tablespace

startup force

drop tablespace index_tablespace including contends and datafile;

b. recreate the tablespace

c. recreate all indexes in it

4.口令文件损坏

＄ORACLE＿HOME/dbs/orapwSID --->存放位置
orapwd file=$oracle_HOME/dbs/orapwdSID

5.控制文件损坏
alter database backup controlfile to '/opt/oracle/con.bak' ------->备份到文件

alter database backup controlfile to trace; ------->到跟踪文件

损坏单个控制文件：
可以直接复制其它控制文件并改名

所有的控制文件损，同时没有一个可用的备份的控制文件

必须有备份跟踪文件，否则无法恢复(所有的日志文件都被重新初始化)

1.
alter database backup controlfile to trace;
shutdown immediate

2.vim $ORACLE_BASE/admin/udump/xx.trc
CREATE CONTROLFILE REUSE DATABASE "HAO" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/opt/oracle/oradata/hao/redo01.log',
'/opt/oracle/oradata/redo01a.log') SIZE 50M,
GROUP 2 (
'/opt/oracle/oradata/hao/redo02.log',
'/opt/oracle/oradata/redo02a.log') SIZE 50M,
GROUP 3 '/opt/oracle/oradata/hao/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/hao/system01.dbf',
'/opt/oracle/oradata/hao/undotbs01.dbf',
'/opt/oracle/oradata/hao/sysaux01.dbf',
'/opt/oracle/oradata/hao/users01.dbf',
'/opt/oracle/oradata/hao/example01.dbf',
'/opt/oracle/oradata/hao/ts1.dbf',
'/opt/oracle/database/temp1/test1.dbf',
'/opt/oracle/database/temp1/undo1.dbf'
CHARACTER SET WE8ISO8859P1;

3.alter database open resetlogs;

4.SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

临时表空间损坏

SQL> select v$datafile.TS#,v$tablespace.NAME,dba_tablespaces.TABLESPACE_NAME
2 from v$datafile,v$tablespace,dba_tablespaces
3 where v$datafile.TS#=v$tablespace.ts# and
4 dba_tablespaces.TABLESPACE_NAME=v$tablespace.NAME;

       TS# NAME                           TABLESPACE_NAME
---------- ------------------------------ ------------------------------
         0 SYSTEM                         SYSTEM
         1 UNDOTBS1                       UNDOTBS1
         2 SYSAUX                         SYSAUX
         4 USERS                          USERS
         6 EXAMPLE                        EXAMPLE
         7 T1                             T1
        11 TEST1                          TEST1
        12 UNDO1                          UNDO1

8 rows selected.
1.
alter database datafile '/opt/oracle/ offline drop;

2.
alter database open

3.
create undo tablespace undo1 datafile '/opt/oracle/oradata/hao/undo1.dbf' size 50m

4.
alter system set undo_tablespace=undo1;

临时表空间

SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
UNDOTBS1                       UNDO
SYSAUX                         PERMANENT
USERS                          PERMANENT
EXAMPLE                        PERMANENT
T1                             PERMANENT
TEMP1                          TEMPORARY
TEMP2                          TEMPORARY
TEST1                          PERMANENT
UNDO1                          UNDO
TEMPNEW                        TEMPORARY

11 rows selected.

SQL> select TSNAME,FILENAME,FILE# from dba_hist_tempfile;

TSNAME                         FILENAME                            FILE#
------------------------------ ------------------------------ ----------
TEMP1                          /opt/oracle/database/temp1/tem          2
                               p1.dbf

TEMP2 /opt/oracle/database/temp1/tem 3
p2.dbf

TEMP /opt/oracle/oradata/hao/temp01 1
.dbf

TEMPNEW /opt/oracle/database/temp1/tem 4
pnew.dbf

TSNAME FILENAME FILE#
------------------------------ ------------------------------ ----------

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

flashback
9i:
闪回功能依赖回滚段中存储的数据前镜像，只要前镜像没有被覆盖，那么闪回就是可能的。

语法：as of scn(timestamp) expr

SQL> select count(*) from flash1;

COUNT(*)
----------
1580

SQL> delete from flash1 where rownum <30;

29 rows deleted.

SQL> select dbms_flashback.get_system_change_number scn from dual;(查询SCN号)

SCN
----------
1070335

SQL> select count(*) from flash1 as of scn 1070300;(查询scn在1070300的记录)

COUNT(*)
----------
1580

SQL> select count(*) from flash1 ;

COUNT(*)
----------
1551

SQL> create table flash2 as select * from flash1 as of scn 1070300;

Table created.

SQL> drop table flash1;

Table dropped.

SQL> select count(*) from flash2;

COUNT(*)
----------
1580

SQL> alter table flash2 rename to flash1;

Table altered.

10g

SMON_SCN_TIME最多保存144000条记录，平均3秒做一次数据更新
LGWR首先在SGA中记录SCN于时间的映射关系(由于LGWR至少每3秒就会被激活一次，所以现在SMON_SCN_TIME能够支持大于3秒的闪回)
闪回特性：启用闪回功能后，数据库将发生变化的数据在闪回日志文件中，进行内回，这些数据块可以被直接复制回来以洓滿意我们的恢复需求，同时redo可以被应用以辅助数据恢复到更精确的时间点，从而极大地缩短了恢复时间
由RVWR的新进程写入，要使用闪回技术，数据必须是归档模式

alter database flashback on;

SQL> startup mount
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size                  1220796 bytes
Variable Size             226496324 bytes
Database Buffers          369098752 bytes
Redo Buffers                7163904 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select dbid,name,flashback_on,current_scn from v$database;

DBID NAME FLASHBACK_ON CURRENT_SCN
---------- --------- ------------------ -----------
1164604165 HAO YES 1072930

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

db_flashback_retention_target (用于定义一个时间的上限，单位分钟，这是一个期望值，确切的闪回时间取决于闪回区的保留的闪回数据)

SQL> desc flash3;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> truncate table flash3 ;清除数据

Table truncated.

SQL> select to_char(OLDEST_FLASHBACK_TIME,'yyyy-mm-dd hh24:mi:ss')
2 from v$flashback_database_log;

TO_CHAR(OLDEST_FLAS
-------------------
2009-10-30 10:20:19

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

SQL> flashback database to timestamp
2 to_timestamp('2009-10-30 10:20:19','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> show parameter db_re%

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/flash_recovery_are
                                                 a
db_recovery_file_dest_size           big integer 2G
db_recycle_cache_size                big integer 0

flashback drop功能： ---------->所有用户删除的表对应的管理表：dba_recyclebin

不支持视图的恢复

SQL> show user
USER is "SCOTT"
SQL> drop table emp;(删除放到回收站，默认)

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$dx9GRE2MoV7gQAEKeAESQw==$0 TABLE 2009-10-30:11:03:38

SQL> flashback table emp to before drop;

Flashback complete.

flashback table emp to before drop rename to a; (如果库中现在有同名的表，则使用rename)

SQL>drop table emp purge;(直接删除)

SQL>purge dba_recyclebin;(清空回收站)

alter session set recyclebin=off|on (设置用户是否使用回收站，默认是on,sys用户不支持)

-------------------------------------
恢复表记录

alter table dept enable row movement

flashback table d1 to scn xxxxx

SQL>----具有flashback any

-------------------------------------------------------------------------------------------------
SQL> drop table tt;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T                BIN$dx9GRE2NoV7gQAEKeAESQw==$0 TABLE        2009-10-30:13:58:56
TT               BIN$dx9GRE2OoV7gQAEKeAESQw==$0 TABLE        2009-10-30:13:59:22
SQL> purge recyclebin
2 ;

Recyclebin purged.

SQL> show recyclebin
SQL> create table t1 (a int);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from t1 where a=1;

1 row deleted.

SQL> commit
2 ;

Commit complete.

SQL> select undo_sql from flashback_transaction_query
2 where table_name='T1';

UNDO_SQL
--------------------------------------------------------------------------------
delete from "SCOTT"."T1" where ROWID = 'AAAM5PAAEAAAAGIAAC';
delete from "SCOTT"."T1" where ROWID = 'AAAM5PAAEAAAAGIAAB';
delete from "SCOTT"."T1" where ROWID = 'AAAM5PAAEAAAAGIAAA';
insert into "SCOTT"."T1"("A") values ('1');

SQL> insert into "SCOTT"."T1"("A") values ('1');

1 row created.

以上实例为针对某个用户的某个表的某几条记录做恢复

热备

数据导入与导出(for 9i)

1.导出工具exp
＄RAOCLE_HOME/bin ------可执行文件

三种模式
a.用户模式导出用户所有对象以及对象中的数据
b.表模式导出用户所有的表或者指定的表
c.整个数据库导出数据库所有的对象
EXP：将对象导出到操作系统，一个二进制的压缩系统文件，可以在不同OS间迁移

普通用户导出
单张表：
[oracle@pi ~]$ exp scott/tiger tables=emp file=/opt/oracle/database/temp1/emp.dmp log=/opt/oracle/database/temp1/emp.log

Export: Release 10.2.0.1.0 - Production on Mon Nov 2 09:46:50 2009

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

多张表用逗号分隔
[oracle@pi ~]$ exp scott/tiger tables=emp,dept file=/opt/oracle/database/temp1/empdept.dmp log=/opt/oracle/database/temp1/emp.log

Export: Release 10.2.0.1.0 - Production on Mon Nov 2 09:50:27 2009

About to export specified tables via Conventional Path ...
. . exporting table EMP 13 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 5 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

使用sys导出
1.非交互模式
[oracle@pi ~]$ exp "'sys/oracle as sysdba'" tables=scott.emp file=/opt/oracle/database/temp1/empt1.dmp log=/opt/oracle/database/temp1/emp1.log

Export: Release 10.2.0.1.0 - Production on Mon Nov 2 09:55:30 2009

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table EMP 13 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
2.交互模式
[oracle@pi ~]$ exp tables=scott.emp file=/opt/oracle/database/temp1/empt1.dmp log=/opt/oracle/database/temp1/emp1.log

Export: Release 10.2.0.1.0 - Production on Mon Nov 2 09:57:40 2009

Username: sys as sysdba
Password:

2.owner(schema)导所属组即用户的所有表，表约束，存储过程，触发器
一般是sys导出用户的数据

exp owner=scott file=/opt/oracle/database/temp1/system*.dmp(让系统生成多个文件) log=/opt/oracle/database/temp1/system.log filesize=1g(指定导出文件的大小，便于系统管理经及系统对文件大小的限制，加快速度和刻盘)

[oracle@pi ~]$ exp system/oracle owner=scott file=/opt/oracle/database/temp1/system.dmp log=/opt/oracle/database/temp1/system.log

Export: Release 10.2.0.1.0 - Production on Mon Nov 2 10:01:55 2009

EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: sys as sysdba
Password:

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          5 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         13 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       SALGRADE          5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                   SYS_TEMP_FBT
. . exporting table                             T2         16 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

3.导出数据库中所有的即全库导出
[oracle@pi ~]$ exp system/oracle full=y file=/opt/oracle/database/temp1/all.dmp log=/opt/oracle/database/temp1/all.log

4.导出某个用户下的所有表
1.先VI一个文件
vim /opt/oracle/database/temp1/t2.txt

spool /opt/oracle/database/temp1/t1.txt
set heading off
set linesize 200
set trimspool on
set feedback off
select 'exp scott/tiger tables='||table_name||' file=/opt/oracle/database/temp1/
'||table_name||'.dmp ' from user_tables;
spool off

2.在SQL中执行t2.txt
SQL> @/opt/oracle/database/temp1/t1.txt

exp scott/tiger tables=T2 file=/opt/oracle/database/temp1/T2.dmp
exp scott/tiger tables=DEPT file=/opt/oracle/database/temp1/DEPT.dmp
exp scott/tiger tables=BONUS file=/opt/oracle/database/temp1/BONUS.dmp
exp scott/tiger tables=SALGRADE file=/opt/oracle/database/temp1/SALGRADE.dmp
exp scott/tiger tables=SYS_TEMP_FBT file=/opt/oracle/database/temp1/SYS_TEMP_FBT.dmp
exp scott/tiger tables=EMP file=/opt/oracle/database/temp1/EMP.dmp

5.远程导出
a.监听必须起来，tnsname必须设置好
b.
[oracle@pi temp1]$ exp scott/tiger@orcl1 tables=test file=/tmp/test.dmp

Export: Release 10.2.0.1.0 - Production on Mon Nov 2 11:26:30 2009

About to export specified tables via Conventional Path ...
. . exporting table TEST 14 rows exported
Export terminated successfully without warnings.

参数
exp imp
buffer=4096000 (数据缓冲区的大小，字节为单位，可自行调整)
direct=y (以direct方式导出，告诉exp直接读取数据，速度快)
feedback (每X行显示进度(0)数据量大的时候可以指定，查看状态)
compress=y (将在导出的同时合并碎块)
grants=y 权限
indexes=y 索引
rows=y 行，指定为N则是导出表结构
CONSTRAINTS=y

数据导入
imp
imp scott/tiger tables=emp file=/opt/oracle/database/temp1/emp.dmp log=/opt/oracle/database/temp1/xxx.log ignore=y

步骤:
1.create object 2.insert data 3.create index 4.create trigers,constraints

注意事项：
1.数据库对象已经存在，使用ignore=y
如果表中有唯一关键字约束，不合条件不补导入
如果表中没有唯一关键字约束，将引起重复记录

2.对象有主外键约束
解决方法：先导入主表，再导入从表
disable目标导入对象的主外键约束，导入后再enable
直接drop掉约束，在重建约束

3.权限不够
A用户去导出B用户的对象 A用户需要有exp_full_database权限
imp_full_database(导入)

4.导入大表(大于80M)时，存储分配失败
exp compress=y imp 记得compress=n

5.imp exp 版本，向下兼容性
imp 可以成功导入低版本exp生成的文件，不能导入高版本exp生成的文件

10g新工具
逻辑备份工具------数据泵
expdp只能用于服务器端，不能用于客户端

SQL> create directory expdp as '/tmp/';

Directory created.

SQL> ho /tmp/
/bin/bash: /tmp/: is a directory

SQL> grant read,write on directory expdp to scott;

Grant succeeded.

导出
[oracle@pi bin]$ expdp scott/tiger tables=emp directory=expdp dumpfile=empdp.dmp logfile=empdp.log

Export: Release 10.2.0.1.0 - Production on Monday, 02 November, 2009 14:45:31

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp directory=expdp dumpfile=empdp.dmp logfile=empdp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP" 7.781 KB 13 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/tmp/empdp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:45:43

导入
[oracle@pi bin]$ impdp scott/tiger directory=expdp dumpfile=empdp.dmp tables=emp

Import: Release 10.2.0.1.0 - Production on Monday, 02 November, 2009 15:07:50

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=expdp dumpfile=empdp.dmp tables=emp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 7.781 KB 13 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 15:07:53

impdp scott/tiger directory=expdp dumpfile=empdp.dmp tables=emp table_exists_action=replace

impdp scott/tiger directory=expdp dumpfile=empdp.dmp tables=emp schemas=scott

用户表：
expdp scott/tiger tables=emp directory=expdp dumpfile=empdp.dmp logfile=empdp.log schemas=scott

表空间：
expdp scott/tiger tables=emp directory=expdp dumpfile=empdp.dmp logfile=empdp.log tabespace=users

全库导出：
expdp scott/tiger tables=emp directory=expdp dumpfile=empdp%U.dmp logfile=empdp.log full=y parallel=4
同时有4个通道，文件取名为empdp_01(通道编号)

参数
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.

skip：如果表已经存在，则跳过并处理下一个对象
append:是表增加数据
truncate:截断表，然后增加新数据
replace:是删除表，重新建表并添加数据