oracle第8章,循序渐进oracle，第8章笔记-备份与恢复

1)

archive log list;

查看归档情况

2)

log_archive_dest

log_archive_dest_n两种归档路径参数

修改路径

alter system set log_archive_dest_1='/opt/oracle/archive';

3)

alter database archivelog;开启归档模式，在mount状态下

alter database noarchivelog;关闭

4)

快速恢复区

show parameter db_recover

查看其大小和路径

可修改：

alter system set db_recovery_file_dest_size=65G scope=both;

5)

alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';

设置归档日志保存在快速恢复区

6)常用释放归档日志文件空间

RMAM:

crosscheck archivelog all;

delete expired archivelog all;

7)

backup archivelog all delete input

删除归档日志

8)

查询数据库端字符集

select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

9)

expdp/impdp -help 查看数据泵帮助

10)

数据泵导出例子

expdp eygle/eygle directory=dpata dumpfile=full.dmp schemas=EYGLE

job_name=expfull

stop_job 停止命令

select * from tab where tname='expfull';

再次链接

expdp eygle/eygle attach=expfull

或者直接重新启动

start_job

continue_client

11)

备份之前应该执行，确认数据库文件及存储路径

select name from v$datafile;

select member from v$logfile;

select nae from v$controlfile;

12)

热备份表空间

alter tablespace system begin backup;

host copy ....DBF ....DBF

alter tablespaces system end backup;

13)

查看备份情况

select * from v$backup;

14)

alter system switch logfile;更换日志文件组

select * rom V$log;查看日志文件组相关信息

select sid,serial#,username from v$session;

查看当前session信息

15)

alter database begin backup;并行开启

recover database；恢复数据库

16)

RMAN

configure controlfile autoback on;开启文件自动备份功能

查询设置状态：

show controlfile autobackup;

也可以在数据库中查询

select * rom v$rman_configuration;

17)

backup database;

18)

从自动备份中恢复spfile,控制文件，mount状态

restore spfile to '...ora' from autobackup;

restore controlfile to '...ctl' from autobackup;

19)如果不能mount 那么需要DBID

restore controlfile to '...ctl' from 'DBID号';

20) show all;查看所有RMAN备份参数

21)report obsolete显示废弃备份

22)

configure retention policy to redundancy 2;

配置保留策略为2

23)

delete noprompt obsolete 删除废弃备份 (noprompt不确认)

24)

keep until time 可指定备份集保留时间

keep forever 可永久保存

25)

backup tablespace users format '....bk'

keep until time 'sysdate + 365';

keep forever

26)

crosscheck backup

delete expired backup

查找删除过期备份

27)

手动删除备份

list backup of archivelog from sequence

140 thread 1;

delete noprompt archivelog

until sequence = 144;

delete noprompt archivelog

until time 'sysdate - 1';

28)

alter database backup controlfile to trace;

将控制文件的创建语句备份到跟踪文件中

29)

resetlogs 简历控制文件后 需要

recover database using backup contrilfile until cancel;

30)alter database open resetlogs;

1-执行不完全恢复后

2-试用了备份的控制文件进行回复后

3-使用带有resetlogs选项创建控制文件回复后

31)

alter session  SET EVENTS 'immediate trace name file_hdrs level 10';

将数据文件头信息转储出来

32)

recover database using backup controlfile;

通过文件头查找控制文件

33)

backup current controlfile foramat '....ctl';

引发控制文件自动备份;

34)

backup as copy current controlfile format '/path.ctl';

35)

run{

copy datafile 1 to '/opt/oracle/system01.dbf',

current controlfile to '/opt/oracle/control01.dbf';

}

36)

list copy；

查看数据库拷贝文件列表

37)

制定恢复文件到不同的位置

run{

set newname for datafile 8 to '/opt/oracle/oradata/eygle/trans01.dbf';

restore datafile 8;

switch datafile all;

recover datafile 8;

}

38)

restore validate controlfile;

restroe validate spfile;

restroe validate database；?

验证控制文件和参数文件，数据库备份的有效性

39)

拷贝数据文件号位41号

copy datafile 41 to '/mmsback/oralce/....313.1';

40)

list copy of datafile 41;

41)

restore datafile '+DATADG/mmsdb/datafile/eygle.313.1';

sql 'alter tablespace eygle online';

42)

crosscheck archivelig all;

backup as compressed backupset database plus archivelog;

43)增量备份

backup incremental lecel=0 database plus archivelog delete input;

backup incremental lecel=1 database plus archivelog delete input;

?

44)

alter database enable block change tracking using file '/data4/....log';

开启块改变跟踪

判断当前设置课查询：

SELECT  filename,status,bytes from v$block_change_tracking;

关闭跟踪

alter database disable block change tracking;

45)backup format '...bk' section size 200M tablespace system;

分段备份，最多256块

list backup of tablespace system;