71. oracle备份恢复¶
1. exp备份¶
1.1 查看服务端字符集¶
select userenv('language') from dual;
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 09:54:46 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL>
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL>exit
1.2 修改客户端字符集¶
#字符集非常重要！如果不设置，可能会乱码！！！
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
-bash-4.2$ echo $NLS_LANG
-bash-4.2$
-bash-4.2$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
-bash-4.2$
-bash-4.2$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
1.3 获取帮助¶
exp system/123 help=y
-bash-4.2$ exp system/123 help=y
Export: Release 11.2.0.2.0 - Production on Sun May 23 09:55:25 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
1.4 全库导出¶
#创建备份目录
mkdir /oradata/expbackup
#备份
exp system/123 file=/oradata/expbackup/full.dmp full=y
#查看
cd /oradata/expbackup && ll
-bash-4.2$ mkdir /oradata/expbackup
-bash-4.2$ exp system/123 file=/oradata/expbackup/full.dmp full=y
Export: Release 11.2.0.2.0 - Production on Sun May 23 09:57:29 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Pro duction
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
........#很多内容，很慢。
. . exporting table WWV_PURGE_WORKSPACES 0 rows exported
. . exporting table WWV_PURGE_WORKSPACE_RESPONSES 0 rows exported
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
-bash-4.2$ cd /oradata/expbackup/
-bash-4.2$ ls
full.dmp
-bash-4.2$
-bash-4.2$
-bash-4.2$ ll
total 185768
-rw-r--r-- 1 oracle dba 190226432 May 23 09:59 full.dmp
-bash-4.2$ ll -h
total 182M
-rw-r--r-- 1 oracle dba 182M May 23 09:59 full.dmp
1.5 按用户导出（常用）¶
sqlplus / as sysdba
#1.创建用户
create user chupeng identified by chupeng;
#2.授予权限
grant connect,resource to chupeng;
#3.在线切换用户
conn chupeng/chupeng
#4.测试
create table t1(id int,name varchar2(20));
insert into t1 values(1,'zs');
commit;
select * from t1;
select table_name from user_tables;
exit
#5.备份
exp system/123 file=/oradata/expbackup/chupeng.dmp owner=chupeng
#6.查看
cd /oradata/expbackup && ll
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:06:04 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create user chupeng identified by chupeng;
User created.
SQL>
SQL> grant connect,resource to chupeng;
Grant succeeded.
SQL>
SQL> conn chupeng/chupeng
Connected.
SQL>
SQL>
SQL> create table t1(id int,name varchar2(20));
Table created.
SQL>
SQL> insert into t1 values(1,'zs');
1 row created.
SQL>
SQL> select * from t1;
ID NAME
---------- --------------------
1 zs
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$ exp system/123 file=/oradata/expbackup/chupeng.dmp owner=chupeng
Export: Release 11.2.0.2.0 - Production on Sun May 23 10:08:53 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CHUPENG
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CHUPENG
About to export CHUPENG's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CHUPENG's tables via Conventional Path ...
. . exporting table T1 1 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 without warnings.
-bash-4.2$
-bash-4.2$ pwd
/oradata/expbackup
-bash-4.2$ ls
chupeng.dmp full.dmp
1.6 按表导出¶
#1.创建t2,t3表
sqlplus chupeng/chupeng
create table t2 as select * from t1;
create table t3 as select * from t1;
select * from t2;
select * from t3;
select table_name from user_tables;
#2.备份
exp chupeng/chupeng file=/oradata/expbackup/t2_t3.dmp tables=T2,T3 #已测试，表名不区分大小写！！
#3.查看
-bash-4.2$ sqlplus chupeng/chupeng
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:12:25 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create table t2 as select * from t1;
Table created.
SQL>
SQL> create table t3 as select * from t1;
Table created.
SQL>
SQL> select * from t2;
ID NAME
---------- --------------------
1 zs
SQL>
SQL> select * from t3;
ID NAME
---------- --------------------
1 zs
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
T2
T3
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ exp chupeng/chupeng file=/oradata/expbackup/t2_t3.dmp tables=T2,T3
Export: Release 11.2.0.2.0 - Production on Sun May 23 10:14:11 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T2 1 rows exported
. . exporting table T3 1 rows exported
Export terminated successfully without warnings.
-bash-4.2$
-bash-4.2$ pwd
/oradata/expbackup
-bash-4.2$
-bash-4.2$ ls
chupeng.dmp full.dmp t2_t3.dmp
2. imp恢复¶
2.1 全库导入¶
imp system/123 file=/oradata/expbackup/full.dmp full=y
#是不是首先得把数据库内容清空才能进行全库恢复？
2.2 按用户导入¶
#1.导入到原来的用户
#删表
sqlplus chupeng/chupeng
select table_name from user_tables;
drop table t1;
drop table t2;
drop table t3;
select table_name from user_tables;
exit
#恢复
imp system/123 file=/oradata/expbackup/chupeng.dmp fromuser=chupeng touser=chupeng
#查看
sqlplus chupeng/chupeng
select table_name from user_tables;
#2.导入到别的用户
#建用户
sqlplus / as sysdba
create user cp identified by cp;
grant connect,resource to cp;
exit
#恢复
imp system/123 file=/oradata/expbackup/chupeng.dmp fromuser=chupeng touser=cp
#查看
sqlplus cp/cp
select table_name from user_tables;
select * from t1;
exit
#为何只导入了一张表？？？其他2张呢？
#1.
-bash-4.2$ sqlplus chupeng/chupeng
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:27:23 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
T2
T3
SQL>
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> drop table t3;
Table dropped.
SQL>
SQL> select table_name from user_tables;
no rows selected
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ imp system/123 file=/oradata/expbackup/chupeng.dmp fromuser=chupeng touser=chupeng
Import: Release 11.2.0.2.0 - Production on Sun May 23 10:28:16 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing CHUPENG's objects into CHUPENG
. . importing table "T1" 1 rows imported
Import terminated successfully without warnings.
-bash-4.2$
-bash-4.2$ sqlplus chupeng/chupeng
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:28:27 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
SQL>
SQL> select * from t1;
ID NAME
---------- --------------------
1 zs
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
#2.
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:29:33 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create user cp identified by cp;
User created.
SQL> grant connect,resource to cp;
Grant succeeded.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ imp system/123 file=/oradata/expbackup/chupeng.dmp fromuser=chupeng touser=cp
Import: Release 11.2.0.2.0 - Production on Sun May 23 10:30:03 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing CHUPENG's objects into CP
. . importing table "T1" 1 rows imported
Import terminated successfully without warnings.
-bash-4.2$
-bash-4.2$ sqlplus cp/cp
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:30:10 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
SQL>
SQL> select * from t1;
ID NAME
---------- --------------------
1 zs
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
#为啥只导入了一张表？？？
2.3 按表导入¶
#1.删表
sqlplus chupeng/chupeng
drop table t2;
select table_name from user_tables;
exit
#2.测试是否可以只恢复1张表？(已测试，可以！)
imp chupeng/chupeng file=/oradata/expbackup/t2_t3.dmp tables=T2
#3.查看
sqlplus chupeng/chupeng
select table_name from user_tables;
#1.只恢复1张表
-bash-4.2$ imp chupeng/chupeng file=/oradata/expbackup/t2_t3.dmp tables=T2
Import: Release 11.2.0.2.0 - Production on Sun May 23 10:36:23 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing CHUPENG's objects into CHUPENG
. importing CHUPENG's objects into CHUPENG
. . importing table "T2" 1 rows imported
Import terminated successfully without warnings.
-bash-4.2$
#2.查看
-bash-4.2$ sqlplus chupeng/chupeng
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:36:30 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
T2
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
#3.删除所有表，恢复2张表测试
-bash-4.2$ sqlplus chupeng/chupeng
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:37:36 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
T2
T3
SQL>
SQL> drop table t2;
Table dropped.
SQL> drop table t3;
Table dropped.
SQL> drop table t1;
Table dropped.
SQL>
SQL> select table_name from user_tables;
no rows selected
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
#4.恢复
-bash-4.2$ imp chupeng/chupeng file=/oradata/expbackup/t2_t3.dmp tables=t2,t3
Import: Release 11.2.0.2.0 - Production on Sun May 23 10:38:29 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing CHUPENG's objects into CHUPENG
. importing CHUPENG's objects into CHUPENG
. . importing table "T2" 1 rows imported
. . importing table "T3" 1 rows imported
Import terminated successfully without warnings.
-bash-4.2$
#5.查看
-bash-4.2$ sqlplus chupeng/chupeng
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:38:34 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T2
T3
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
3. expdp备份¶
3.1 介绍¶
#逻辑泵备份是10g以后出现的，可以开多并发备份，效率提高！非常流行，只是只能在10g以上版本使用，而且没有字符集限制！！！
3.2 创建恢复目录，并授权用户¶
mkdir /oradata/expdpbackup
sqlplus / as sysdba
create directory dir as '/oradata/expdpbackup';
grant read,write on directory dir to system;
grant read,write on directory dir to chupeng;
exit
#如何查看恢复目录呢？？？
-bash-4.2$ mkdir /oradata/expdpbackup
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 10:58:14 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create directory dir as '/oradata/expdpbackup';
Directory created.
SQL>
SQL> grant read,write on directory dir to system;
Grant succeeded.
SQL>
SQL> grant read,write on directory dir to chupeng;
Grant succeeded.
SQL>
3.3 按用户导出（常用）¶
expdp system/123 schemas=chupeng dumpfile=chupeng.dmp DIRECTORY=dir;
-bash-4.2$ expdp system/123 schemas=chupeng dumpfile=chupeng.dmp DIRECTORY=dir;
Export: Release 11.2.0.2.0 - Production on Sun May 23 11:01:34 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Pro duction
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=chupeng dumpfile=chupeng.dmp DIRECTORY=dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "CHUPENG"."T2" 5.421 KB 1 rows
. . exported "CHUPENG"."T3" 5.421 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/oradata/expdpbackup/chupeng.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:01:56
-bash-4.2$ cd /oradata/expdpbackup/
-bash-4.2$
-bash-4.2$ ls
chupeng.dmp export.log
-bash-4.2$
-bash-4.2$ ll
total 188
-rw-r----- 1 oracle dba 188416 May 23 11:01 chupeng.dmp
-rw-r--r-- 1 oracle dba 1268 May 23 11:01 export.log
-bash-4.2$
-bash-4.2$ ll -h
total 188K
-rw-r----- 1 oracle dba 184K May 23 11:01 chupeng.dmp
-rw-r--r-- 1 oracle dba 1.3K May 23 11:01 export.log
3.4 并发进程¶
#11gxe貌似不支持！！！
expdp system/123 schemas=chupeng directory=dir dumpfile=expdp2.dmp parallel=4 job_name=expdp2
-bash-4.2$ expdp system/123 schemas=chupeng directory=dir dumpfile=expdp2.dmp parallel=4 job_name=expdp2
Export: Release 11.2.0.2.0 - Production on Sun May 23 11:05:13 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-39094: Parallel execution not supported in this database edition.
-bash-4.2$
#11gxe貌似不支持！！！
3.5 按表名导出¶
expdp chupeng/chupeng TABLES=t2,t3 dumpfile=expdp3.dmp DIRECTORY=dir;
-bash-4.2$ expdp chupeng/chupeng TABLES=t2,t3 dumpfile=expdp3.dmp DIRECTORY=dir;
Export: Release 11.2.0.2.0 - Production on Sun May 23 11:08:05 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "CHUPENG"."SYS_EXPORT_TABLE_01": chupeng/******** TABLES=t2,t3 dumpfile=expdp3.dmp DIRECTORY=dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CHUPENG"."T2" 5.421 KB 1 rows
. . exported "CHUPENG"."T3" 5.421 KB 1 rows
Master table "CHUPENG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHUPENG.SYS_EXPORT_TABLE_01 is:
/oradata/expdpbackup/expdp3.dmp
Job "CHUPENG"."SYS_EXPORT_TABLE_01" successfully completed at 11:08:09
-bash-4.2$
-bash-4.2$ pwd
/oradata/expdpbackup
-bash-4.2$ ls
chupeng.dmp expdp3.dmp export.log
3.6 按查询条件导出(了解)¶
expdp chupeng/chupeng directory=dir dumpfile=expdp4.dmp Tables=t1 query='where id=1';
3.7 按表空间导出（常用）¶
expdp system/123 DIRECTORY=dir dumpfile=tablespace.dmp tablespaces=users;
-bash-4.2$ expdp system/123 DIRECTORY=dir dumpfile=tablespace.dmp tablespaces=users;
Export: Release 11.2.0.2.0 - Production on Sun May 23 11:10:12 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** DIRECTORY=dir dumpfile=tablespace.dmp tablespaces=users
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/oradata/expdpbackup/tablespace.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 11:10:20
-bash-4.2$
-bash-4.2$ ll -h
total 676K
-rw-r----- 1 oracle dba 184K May 23 11:01 chupeng.dmp
-rw-r----- 1 oracle dba 100K May 23 11:08 expdp3.dmp
-rw-r--r-- 1 oracle dba 1.9K May 23 11:10 export.log
-rw-r----- 1 oracle dba 388K May 23 11:10 tablespace.dmp
-bash-4.2$
3.8 导出整个数据库¶
expdp system/123 directory=dir dumpfile=full.dmp full=y;
-bash-4.2$ expdp system/123 directory=dir dumpfile=full.dmp full=y;
Export: Release 11.2.0.2.0 - Production on Sun May 23 11:11:28 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=dir dumpfile=full.dmp full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 161.3 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
.................
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/oradata/expdpbackup/full.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 11:12:31
-bash-4.2$
4. impdp恢复¶
4.1 导入到指定用户¶
impdp system/123 directory=dir dumpfile=chupeng.dmp schemas=chupeng;
-bash-4.2$ impdp system/123 directory=dir dumpfile=chupeng.dmp schemas=chupeng;
Import: Release 11.2.0.2.0 - Production on Sun May 23 11:19:09 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=dir dumpfile=chupeng.dmp schemas=chupeng
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CHUPENG" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "CHUPENG"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "CHUPENG"."T3" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at 11:19:11
-bash-4.2$
4.2 改变表的owner¶
#目的：导入到别的用户去测试
#这个有问题,上课没有演示
impdp system/123 directory=dir dumpfile=expdp.dmp tables=chupeng.t1 remap_schema=hr:system;
4.3 导入表空间¶
impdp system/123 directory=dir dumpfile=tablespace.dmp tablespaces=users;
-bash-4.2$ impdp system/123 directory=dir dumpfile=tablespace.dmp tablespaces=users;
Import: Release 11.2.0.2.0 - Production on Sun May 23 11:21:34 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/******** directory=dir dumpfile=tablespace.dmp tablespaces=users
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."REGIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."LOCATIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."DEPARTMENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."JOBS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HR"."JOB_HISTORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" completed with 7 error(s) at 11:21:36
-bash-4.2$
4.4 导入整个数据库¶
impdp system/123 directory=dir dumpfile=full.dmp full=y;
-bash-4.2$ impdp system/123 directory=dir dumpfile=full.dmp full=y;
Import: Release 11.2.0.2.0 - Production on Sun May 23 11:22:23 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dir dumpfile=full.dmp full=y
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name
Failing sql is:
CREATE TABLESPACE "SYSAUX" DATAFILE SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M,'/u01/app/oracle/oradata/XE/sysaux.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"HR" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
.........
ORA-31684: Object type PACKAGE_BODY:"APEX_040000"."WWV_RENDER_CHART2" already exists
ORA-31684: Object type PACKAGE_BODY:"APEX_040000"."WWV_RENDER_REPORT3" already exists
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27477: "APEX_040000.ORACLE_APEX_PURGE_SESSIONS" already exists
Failing sql is:
BEGIN
dbms_scheduler.create_job('"ORACLE_APEX_PURGE_SESSIONS"',
job_type=>'STORED_PROCEDURE', job_action=>
'WWV_FLOW_CACHE.PURGE_SESSIONS'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('28-AUG-2011 10.24.00.261880000 PM -07:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=HOURLY;BYMINUTE=0'
, end_date=>NULL,
job_c
ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27477: "APEX_040000.ORACLE_APEX_MAIL_QUEUE" already exists
Failing sql is:
BEGIN
dbms_scheduler.create_job('"ORACLE_APEX_MAIL_QUEUE"',
job_type=>'STORED_PROCEDURE', job_action=>
'WWV_FLOW_MAIL.PUSH_QUEUE'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('28-AUG-2011 10.24.00.323920000 PM -07:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=MINUTELY;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55;BYS
ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27477: "APEX_040000.ORACLE_APEX_WS_NOTIFICATIONS" already exists
Failing sql is:
BEGIN
dbms_scheduler.create_job('"ORACLE_APEX_WS_NOTIFICATIONS"',
job_type=>'STORED_PROCEDURE', job_action=>
'WWV_FLOW_WORKSHEET_API.DO_NOTIFY'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('28-AUG-2011 10.24.00.356857000 PM -07:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=MINUTELY;BYMINUTE=0,30;BYSECOND=0'
ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27477: "APEX_040000.ORACLE_APEX_DAILY_MAINTENANCE" already exists
Failing sql is:
BEGIN
dbms_scheduler.create_job('"ORACLE_APEX_DAILY_MAINTENANCE"',
job_type=>'STORED_PROCEDURE', job_action=>
'WWV_FLOW_MAINT.DAILY_MAINTENANCE'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('28-AUG-2011 10.24.00.362619000 PM -07:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECON
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4808 error(s) at 11:22:54
-bash-4.2$
5. rman工具基本功能¶
5.1 介绍¶
rman：restore manager
#类似mysql中的extrabackup，是一种物理层面的备份工具
#rman是备份系统，更高级
5.2 登录rman¶
rman target system/123
-bash-4.2$ rman target system/123
Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 23 11:36:16 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2954413414)
RMAN>
RMAN> exit
Recovery Manager complete.
-bash-4.2$
5.3 列出备份信息¶
#1.列出详细备份信息：
list backup;
#2.列出备份集信息
list backupset;
#3.列出所有数据文件备份信息
list backup of database;
#4.列出特定表空间的所有数据文件备份集
list backup of tablespace users;
#5.列出指定文件的备份信息
list backup of datafile 1
#or
list backup of datafile '指定文件路径'
#6.按备份类型列出备份
list backup by file
#7.列出备份的影像副本信息
list copy
1.列出详细备份信息：¶
list backup;
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 473.21M DISK 00:00:02 23-MAY-21
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_9_1
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 357493 23-MAY-21 /u01/app/oracle/oradata/XE/sysaux.dbf
3 Full 357493 23-MAY-21 /u01/app/oracle/oradata/XE/undotbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 278.72M DISK 00:00:02 23-MAY-21
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_10_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/system.dbf
4 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/users.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 80.00K DISK 00:00:00 23-MAY-21
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_12_1
SPFILE Included: Modification time: 23-MAY-21
SPFILE db_unique_name: XE
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 2.50M DISK 00:00:00 23-MAY-21
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_13_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 353374 23-MAY-21 357513 23-MAY-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 3.00K DISK 00:00:00 23-MAY-21
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_14_1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 357513 23-MAY-21 357522 23-MAY-21
1 4 357522 23-MAY-21 357531 23-MAY-21
1 5 357531 23-MAY-21 357540 23-MAY-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13 2.00K DISK 00:00:00 23-MAY-21
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_15_1
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 357540 23-MAY-21 357548 23-MAY-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 9.33M DISK 00:00:01 23-MAY-21
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094307
Piece Name: /oradata/backup/ctl_XE_20210523_16_1
Control File Included: Ckp SCN: 357570 Ckp time: 23-MAY-21
RMAN>
2.列出备份集信息¶
list backupset;
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 473.21M DISK 00:00:02 23-MAY-21
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_9_1
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 357493 23-MAY-21 /u01/app/oracle/oradata/XE/sysaux.dbf
3 Full 357493 23-MAY-21 /u01/app/oracle/oradata/XE/undotbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 278.72M DISK 00:00:02 23-MAY-21
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_10_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/system.dbf
4 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/users.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 80.00K DISK 00:00:00 23-MAY-21
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_12_1
SPFILE Included: Modification time: 23-MAY-21
SPFILE db_unique_name: XE
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 2.50M DISK 00:00:00 23-MAY-21
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_13_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 353374 23-MAY-21 357513 23-MAY-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 3.00K DISK 00:00:00 23-MAY-21
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_14_1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 357513 23-MAY-21 357522 23-MAY-21
1 4 357522 23-MAY-21 357531 23-MAY-21
1 5 357531 23-MAY-21 357540 23-MAY-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13 2.00K DISK 00:00:00 23-MAY-21
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_15_1
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 357540 23-MAY-21 357548 23-MAY-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 9.33M DISK 00:00:01 23-MAY-21
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094307
Piece Name: /oradata/backup/ctl_XE_20210523_16_1
Control File Included: Ckp SCN: 357570 Ckp time: 23-MAY-21
RMAN>
3.列出所有数据文件备份信息¶
list backup of database;
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 473.21M DISK 00:00:02 23-MAY-21
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_9_1
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 357493 23-MAY-21 /u01/app/oracle/oradata/XE/sysaux.dbf
3 Full 357493 23-MAY-21 /u01/app/oracle/oradata/XE/undotbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 278.72M DISK 00:00:02 23-MAY-21
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_10_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/system.dbf
4 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/users.dbf
RMAN>
4.列出特定表空间的所有数据文件备份集¶
list backup of tablespace users;
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 278.72M DISK 00:00:02 23-MAY-21
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_10_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/users.dbf
RMAN>
5.列出指定文件的备份信息¶
#1.查看文件id
sqlplus / as sysdba
col file_name for a50;
select file_name,file_id from dba_data_files order by file_id;
exit
#2.查看备份信息
rman target system/123
list backup of datafile 1
#or
list backup of datafile '指定文件路径'
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 11:40:31 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL>
SQL> select file_name,file_id from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID
----------
/u01/app/oracle/oradata/XE/users.dbf
4
/u01/app/oracle/oradata/XE/sysaux.dbf
2
/u01/app/oracle/oradata/XE/undotbs1.dbf
3
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID
----------
/u01/app/oracle/oradata/XE/system.dbf
1
SQL>
SQL> col file_name for a50;
SQL> /
FILE_NAME FILE_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/XE/users.dbf 4
/u01/app/oracle/oradata/XE/sysaux.dbf 2
/u01/app/oracle/oradata/XE/undotbs1.dbf 3
/u01/app/oracle/oradata/XE/system.dbf 1
SQL>
SQL> select file_name,file_id from dba_data_files order by file_id;
FILE_NAME FILE_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/XE/system.dbf 1
/u01/app/oracle/oradata/XE/sysaux.dbf 2
/u01/app/oracle/oradata/XE/undotbs1.dbf 3
/u01/app/oracle/oradata/XE/users.dbf 4
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$
-bash-4.2$ rman target system/123
Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 23 11:45:13 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2954413414)
RMAN>
RMAN> list backup of datafile 1;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 278.72M DISK 00:00:02 23-MAY-21
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_10_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/system.dbf
RMAN>
RMAN> list backup of datafile '/u01/app/oracle/oradata/XE/users.dbf';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 278.72M DISK 00:00:02 23-MAY-21
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094244
Piece Name: /oradata/backup/full_XE_20210523_10_1
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 357495 23-MAY-21 /u01/app/oracle/oradata/XE/users.dbf
RMAN>
6.按备份类型列出备份¶
list backup by file;
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- --------- ------- ------- ---------- ---
1 8 B F A 357495 23-MAY-21 1 1 NO TAG20210523T094244
2 7 B F A 357493 23-MAY-21 1 1 NO TAG20210523T094244
3 7 B F A 357493 23-MAY-21 1 1 NO TAG20210523T094244
4 8 B F A 357495 23-MAY-21 1 1 NO TAG20210523T094244
List of Archived Log Backups
============================
Thrd Seq Low SCN Low Time BS Key S #Pieces #Copies Compressed Tag
---- ------- ---------- --------- ------- - ------- ------- ---------- ---
1 2 353374 23-MAY-21 11 A 1 1 NO TAG20210523T094303
1 3 357513 23-MAY-21 12 A 1 1 NO TAG20210523T094303
1 4 357522 23-MAY-21 12 A 1 1 NO TAG20210523T094303
1 5 357531 23-MAY-21 12 A 1 1 NO TAG20210523T094303
1 6 357540 23-MAY-21 13 A 1 1 NO TAG20210523T094303
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag
---------- --------- ------- - ------- ------- ---------- ---
357570 23-MAY-21 14 A 1 1 NO TAG20210523T094307
List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Compressed Tag
----------------- ------- - ------- ------- ---------- ---
23-MAY-21 10 A 1 1 NO TAG20210523T094244
RMAN>
7.列出备份的影像副本信息¶
list copy;
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name XE
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
6 1 7 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_7_jbmhbd6c_.arc
7 1 8 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_8_jbmhcbby_.arc
8 1 9 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_9_jbmkzky4_.arc
9 1 10 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_10_jbmlmk8m_.arc
RMAN>
8. 列出过期备份¶
list expired backup;
RMAN> list expired backup;
specification does not match any backup in the repository
RMAN>
9. 查看rman所有配置信息¶
show all;
RMAN> show all;
RMAN configuration parameters for database with db_unique_name XE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/xe/dbs/snapcf_XE.f'; # default
RMAN>
10. 概述备份¶
list backup summary;
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
7 B F A DISK 23-MAY-21 1 1 NO TAG20210523T094244
8 B F A DISK 23-MAY-21 1 1 NO TAG20210523T094244
10 B F A DISK 23-MAY-21 1 1 NO TAG20210523T094244
11 B A A DISK 23-MAY-21 1 1 NO TAG20210523T094303
12 B A A DISK 23-MAY-21 1 1 NO TAG20210523T094303
13 B A A DISK 23-MAY-21 1 1 NO TAG20210523T094303
14 B F A DISK 23-MAY-21 1 1 NO TAG20210523T094307
RMAN>
11. 概述过期备份(了解即可)¶
list expired backup summary;
RMAN> list expired backup summary;
specification does not match any backup in the repository
RMAN>
12. 列出某个备份集的信息¶
list backupset 59139;
13. 列出数据文件映像副本¶
list copy of datafile '数据文件路径'
14. 列出控制文件的备份信息¶
list backup of controlfile
15. 列出备份集中tage的备份片信息¶
list backupset tage 'tag号'
16. 查看已备份的归档日志信息¶
list backup of archivelog all;
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 2.50M DISK 00:00:00 23-MAY-21
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_13_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 353374 23-MAY-21 357513 23-MAY-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 3.00K DISK 00:00:00 23-MAY-21
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_14_1
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 357513 23-MAY-21 357522 23-MAY-21
1 4 357522 23-MAY-21 357531 23-MAY-21
1 5 357531 23-MAY-21 357540 23-MAY-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
13 2.00K DISK 00:00:00 23-MAY-21
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20210523T094303
Piece Name: /oradata/backup/arch_XE_20210523_15_1
List of Archived Logs in backup set 13
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 357540 23-MAY-21 357548 23-MAY-21
RMAN>
17. 列出所有的归档日志¶
list archivelog all;
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name XE
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
6 1 7 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_7_jbmhbd6c_.arc
7 1 8 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_8_jbmhcbby_.arc
8 1 9 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_9_jbmkzky4_.arc
9 1 10 A 23-MAY-21
Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_10_jbmlmk8m_.arc
RMAN>
18. 列出所有失效的归档日志¶
list expired archivelog all;
19. 列出指定表空间的备份信息¶
list copy of tablespace 'SYSTEM';
RMAN> list copy of tablespace 'system';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 05/23/2021 11:58:27
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "system"
RMAN>
RMAN>
RMAN> list copy of tablespace 'SYSTEM';
specification does not match any datafile copy in the repository
RMAN>
6. rman不完全恢复过程¶
#不完全恢复的过程
1.恢复参数文件，启动到nomount阶段
2.恢复控制文件，切换到mount阶段
3.恢复不一致数据文件
4.恢复归档
5.打开数据库到resetlogs模式（重置redo)
6.1 rman全备脚本说明¶
#脚本内容说明
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK #指定字符集可以删除！
export PATH=$ORACLE_HOME/bin:$PATH
#自己加的备份目录变量，改造一下
export BACKUP_DIR=/oradata/backup
rman target / log $BACKUP_DIR/rman_full.log append <<EOF #登录的命令，以eof追加的模式登录，然后以追加的模式记录日志
run #run模块，只能在脚本中使用
{
allocate channel c1 type disk; #分配几个备份的通道，实际就是开启并发，备份到磁盘上
allocate channel c2 type disk; #不指定disk也行，默认就是往磁盘备。只有2种方式，另一种是带库。服务器性能好的话可以多开几个。每个通道都会产生备份片，就是两个文件
backup database filesperset 4 format '$BACKUP_DIR/full_%d_%T_%s_%p'; #开启4个备份片，也就是生成4个备份文件
sql 'alter system archive log current'; #切换归档，把归档切换出来
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p' delete input; #备份归档
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p'; #备份控制文件，控制文件也有参数文件，最好放在最后备份！所有的备份信息都在控制文件中
#后面的命令就是整理备份
crosscheck backup; #整理备份信息
crosscheck archivelog all; #整理归档日志
delete noprompt obsolete; #删除过期失效的备份，删除的是控制文件中的信息，没有清理实际的
delete noprompt expired backup; #清除过期备份
delete noprompt expired archivelog all; #清除过期归档
}
EOF
6.2 执行全备脚本¶
su - root
mkdir /oradata
chown -R oracle.dba /oradata
su - oracle
cd /oradata
mkdir /oradata/backup
vim /oradata/full.sh
#写入如下内容
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
export BACKUP_DIR=/oradata/backup
rman target / log $BACKUP_DIR/rman_full.log append <<EOF
run
{allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p';
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;}
EOF
#执行脚本
bash /oradata/full.sh
6.3 查看备份结果¶
cd /oradata/backup && ls
cat /oradata/backup/rman_full.log
-bash-4.2$ cd /oradata/backup && ls
full_XE_20210523_3_1 full_XE_20210523_4_1 rman_full.log
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat rman_full.log
Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 23 09:28:02 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2954413414)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=33 device type=DISK
RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
RMAN-06909: WARNING: parallelism require Enterprise Edition
allocated channel: c2
channel c2: SID=34 device type=DISK
Starting backup at 23-MAY-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on c1 channel at 05/23/2021 09:28:03
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on c1 channel at 05/23/2021 09:28:03
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_3_1 tag=TAG20210523T092803 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_4_1 tag=TAG20210523T092803 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 05/23/2021 09:28:03
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
RMAN-03009: failure of backup command on c1 channel at 05/23/2021 09:28:03
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
RMAN>
Recovery Manager complete.
##############################################################################
#发现备份报错！！！百度找到此报错的原因是未归档模式，解决办法如下：
#1。关闭数据库
shutdown immediate;
#2。启动数据库到mount状态
startup mount;
#3。启用归档模式
alter database archivelog;
#4。查看修改后的数据库备份和恢复策略及归档文件的位置
archive log list;
#5.开启自动归档
archive log start
#使数据库启用自动归档，但是重启后数据库仍然处于手工归档模式。
#现在你可以用RMAN备份数据库了
补充
#开启数据库！
select open_mode from v$database;
alter database open;
select open_mode from v$database;
##############################################################################
#再次执行全备
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 09:38:23 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 272631024 bytes
Database Buffers 125829120 bytes
Redo Buffers 4333568 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>
SQL> archive log start
Statement processed.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ rm -rf /oradata/backup/*
-bash-4.2$
-bash-4.2$ ls /oradata/backup/
-bash-4.2$
-bash-4.2$ bash /oradata/full.sh
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> RMAN> -bash-4.2$
-bash-4.2$
-bash-4.2$ cd /oradata/backup/ && ls
full_XE_20210523_5_1 full_XE_20210523_7_1 rman_full.log
full_XE_20210523_6_1 full_XE_20210523_8_1
-bash-4.2$
-bash-4.2$ cat rman_full.log
Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 23 09:39:59 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2954413414, not open)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=18 device type=DISK
RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
RMAN-06909: WARNING: parallelism require Enterprise Edition
allocated channel: c2
channel c2: SID=20 device type=DISK
Starting backup at 23-MAY-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/XE/sysaux.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/XE/undotbs1.dbf
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_5_1 tag=TAG20210523T094000 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/XE/system.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/XE/users.dbf
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_6_1 tag=TAG20210523T094000 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_7_1 tag=TAG20210523T094000 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_8_1 tag=TAG20210523T094000 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-MAY-21
sql statement: alter system archive log current
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/23/2021 09:40:14
RMAN-11003: failure during parse/execution of SQL statement: alter system archive log current
ORA-01109: database not open
RMAN>
Recovery Manager complete.
########################################################################
#发现依旧报错。。。未开启数据库。。。开启数据库再次测试！
########################################################################
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 23 09:41:36 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ rm -rf /oradata/backup/*
-bash-4.2$
-bash-4.2$ bash /oradata/full.sh
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> RMAN> -bash-4.2$
-bash-4.2$
-bash-4.2$ pwd
/oradata/backup
-bash-4.2$
-bash-4.2$ ls
arch_XE_20210523_13_1 ctl_XE_20210523_16_1 full_XE_20210523_9_1
arch_XE_20210523_14_1 full_XE_20210523_10_1 rman_full.log
arch_XE_20210523_15_1 full_XE_20210523_12_1
-bash-4.2$
-bash-4.2$ cat rman_full.log
Recovery Manager: Release 11.2.0.2.0 - Production on Sun May 23 09:42:43 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2954413414)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=36 device type=DISK
RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
RMAN-06909: WARNING: parallelism require Enterprise Edition
allocated channel: c2
channel c2: SID=37 device type=DISK
Starting backup at 23-MAY-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/XE/sysaux.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/XE/undotbs1.dbf
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_9_1 tag=TAG20210523T094244 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/XE/system.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/XE/users.dbf
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_10_1 tag=TAG20210523T094244 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_11_1 tag=TAG20210523T094244 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/full_XE_20210523_12_1 tag=TAG20210523T094244 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-MAY-21
sql statement: alter system archive log current
sql statement: alter system archive log current
sql statement: alter system archive log current
sql statement: alter system archive log current
Starting backup at 23-MAY-21
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=1073295774
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/arch_XE_20210523_13_1 tag=TAG20210523T094303 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_2_jbmdryl2_.arc RECID=1 STAMP=1073295774
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=2 STAMP=1073295774
input archived log thread=1 sequence=4 RECID=3 STAMP=1073295777
input archived log thread=1 sequence=5 RECID=4 STAMP=1073295780
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/arch_XE_20210523_14_1 tag=TAG20210523T094303 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_3_jbmdrysg_.arc RECID=2 STAMP=1073295774
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_4_jbmds1vp_.arc RECID=3 STAMP=1073295777
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_5_jbmds4t8_.arc RECID=4 STAMP=1073295780
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=5 STAMP=1073295783
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/arch_XE_20210523_15_1 tag=TAG20210523T094303 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2021_05_23/o1_mf_1_6_jbmds7ss_.arc RECID=5 STAMP=1073295783
Finished backup at 23-MAY-21
Starting backup at 23-MAY-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 23-MAY-21
channel c1: finished piece 1 at 23-MAY-21
piece handle=/oradata/backup/ctl_XE_20210523_16_1 tag=TAG20210523T094307 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-MAY-21
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/full_XE_20210523_9_1 RECID=7 STAMP=1073295764
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/full_XE_20210523_10_1 RECID=8 STAMP=1073295767
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/full_XE_20210523_11_1 RECID=9 STAMP=1073295772
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/full_XE_20210523_12_1 RECID=10 STAMP=1073295773
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/arch_XE_20210523_13_1 RECID=11 STAMP=1073295783
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/arch_XE_20210523_14_1 RECID=12 STAMP=1073295785
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/arch_XE_20210523_15_1 RECID=13 STAMP=1073295786
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/ctl_XE_20210523_16_1 RECID=14 STAMP=1073295788
Crosschecked 8 objects
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oradata/backup/full_XE_20210523_3_1 RECID=1 STAMP=1073294884
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oradata/backup/full_XE_20210523_4_1 RECID=2 STAMP=1073294885
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oradata/backup/full_XE_20210523_5_1 RECID=3 STAMP=1073295600
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oradata/backup/full_XE_20210523_6_1 RECID=4 STAMP=1073295607
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oradata/backup/full_XE_20210523_7_1 RECID=5 STAMP=1073295611
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oradata/backup/full_XE_20210523_8_1 RECID=6 STAMP=1073295613
Crosschecked 6 objects
specification does not match any archived log in the repository
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 23-MAY-21
Backup Piece 1 23-MAY-21 /oradata/backup/full_XE_20210523_3_1
Backup Set 2 23-MAY-21
Backup Piece 2 23-MAY-21 /oradata/backup/full_XE_20210523_4_1
Backup Set 3 23-MAY-21
Backup Piece 3 23-MAY-21 /oradata/backup/full_XE_20210523_5_1
Backup Set 4 23-MAY-21
Backup Piece 4 23-MAY-21 /oradata/backup/full_XE_20210523_6_1
Backup Set 5 23-MAY-21
Backup Piece 5 23-MAY-21 /oradata/backup/full_XE_20210523_7_1
Backup Set 6 23-MAY-21
Backup Piece 6 23-MAY-21 /oradata/backup/full_XE_20210523_8_1
Backup Set 9 23-MAY-21
Backup Piece 9 23-MAY-21 /oradata/backup/full_XE_20210523_11_1
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_11_1 RECID=9 STAMP=1073295772
Deleted 1 objects
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_3_1 RECID=1 STAMP=1073294884
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_4_1 RECID=2 STAMP=1073294885
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_5_1 RECID=3 STAMP=1073295600
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_6_1 RECID=4 STAMP=1073295607
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_7_1 RECID=5 STAMP=1073295611
deleted backup piece
backup piece handle=/oradata/backup/full_XE_20210523_8_1 RECID=6 STAMP=1073295613
Deleted 6 objects
specification does not match any backup in the repository
specification does not match any archived log in the repository
released channel: c1
released channel: c2
RMAN>
Recovery Manager complete.
#貌似备份成功了，反正没有报错！！！
6.4 模拟故障¶
su - oracle
#删除数据目录
rm -rf /oradata/* #xe需要自己找到位置。
#重新创建数据目录
mkdir -p /oradata/xe
#强制关闭oracle数据库
sqlplus / as sysdba
shutdown abort
6.5 恢复参数文件，启动到nomount阶段¶
sqlplus / as sysdba
startup nomount
6.6 恢复控制文件，切换到mount阶段¶
#1.查看最新的控制文件
cd /oradata/backup && ls -ltr
#2.恢复最新的控制文件
rman target /
restore controlfile from '/oradata/backup/ctl_ORCL_日期';
alter database mount;
6.7 恢复不一致数据文件¶
rman target /
restore database;
6.8 恢复归档¶
rman target /
recover database;
6.9 打开数据库到resetlogs模式¶
rman target /
alter database open resetlogs;
#注意：一旦使用resetlogs之后，一定要立即进行全备！！！
7. 不完全恢复模拟¶
7.1 备份方案¶
#周日全备
#周一归档备份
7.2 全备¶
rm -rf /oradata/backup/*
sh /oradata/full.sh
7.3 确认是否全备成功¶
vim /oradata/backup/rman_full.log
7.4 查看数据库运行状态¶
sqlplus / as sysdba
select open_mode from v$database;
7.5 查看数据文件¶
select file_name,file_id from dba_data_files order by file_id;
7.6 插入数据¶
create table test as select * from tab;
create table test2 as select * from dba_users;
7.7 执行归档备份脚本¶
vim /oradata/arch.sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
export BACKUP_DIR=/oradata/backup
rman target / log $BACKUP_DIR/rman_arch.log append <<EOF
run
{allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p';
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;}
EOF
7.8 查看归档备份是否成功¶
vim /oradata/backup/rman_arch.log
7.9 搞破坏¶
#删除数据文件
rm -rf /oradata/*
mkdir -p /oradata/xe
7.10 强制关闭数据库¶
sqlplus / as sysdba
shutdown abort
7.11 恢复参数文件，启动到nomount阶段¶
sqlplus / as sysdba
startup nomount
7.12 查看备份信息，找到最新的控制文件¶
cd /oradata/backup && ls -ltr
7.13 恢复控制文件，切换到mount阶段¶
rman target /
restore controlfile from '上一步查到的控制文件路径';
alter database mount;
7.14 查看备份信息，并注册新的归档备份集¶
ls -ltr /oradata/backup
rman target /
list backup;
catalog backuppiece '上一步归档备份路径1';
catalog backuppiece '上一步归档备份路径2';
catalog backuppiece '上一步归档备份路径3';
list backup;
7.15 恢复不一致数据文件¶
restore database;
7.16 恢复归档¶
recover database;
7.17 打开数据库到resetlogs模式¶
alter database open resetlogs;
7.18 检查数据是否恢复¶
sqlplus chupeng/chupeng
select * from t1;
7.19 执行全备¶
rm -rf /oradata/backup/*
sh /oradata/full.sh
7.20 查看是否备份成功¶
vim /oradata/backup/rman_full.log
单独恢复部分数据案例¶
7.21 删除系统表¶
rm -rf system01.dbf
7.22 强制关闭数据库，启动到mount阶段¶
sqlplus / as sysdba
shutdown abort
7.23 恢复系统表¶
rman target /
restore datafile 1;
recover datafile 1;
7.24 启动数据库到open阶段¶
alter database open;
8. rman duplicate环境准备¶
8.1 从库清理历史数据¶
#删除归档文件和数据文件
rm -rf /u01/arch/*
rm -rf /oradata/* #xe数据目录不在这里
mkdir /oradata/xe
8.2 从库修改参数文件¶
cd $ORACLE_HOME/dbs
mv initorcl.ora initorcl_s.ora
vim initorcl_s.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl_s/adump'
mkdir -p /u01/app/oracle/admin/orcl_s/adump
8.3 从库修改环境变量¶
vim ~/.bash_profile
export ORACLE_SID=XE_s
source ~/.bash_profile
8.4 从库修改listener¶
cd /u01/app/oracle/product/11.2.0/db_1/network/admin
vim listener.ora
8.5 从库修改tnsname¶
vim tnsnames.ora
8.6 从库创建密码文件¶
cd $ORACLE_HOME/dbs
cp orapworcl orapworcl_s
8.7 测试tns连接性准备¶
#主库
sqlplus / as sysdba
startup
exit
lsnrctl start
lsnrctl status
#从库
sqlplus / as sysdba
startup nomount
create spfile from pfile;
exit
lsnrctl start
lsnrctl status
8.8 主库测试连通性¶
rman target system/123@orcl auxiliary system/123@orcl_s
duplicate target database to "ORCL" from active database nofilenamecheck;
8.9 从库测试¶
sqlplus / as sysdba
select open_mode from v$database;
最后更新:
2022-02-20 08:44:07