oracle回到还原前,oracle 学习笔记之RMAN 异地恢复 灾难恢复和flashback笔记

常用文件所在的位置

spfile

=>$ORACLE_SID=>$ORACLE_HOME/dbs/

v$parameter

v$controlfile

$datafile

dba_data_files

dba_temp_files

v$tempfile

v$log

v$logfile

select * from v$log

show parameter arch

log_archive_dest_1

log_archive_dest_state_1

select * from archived_log

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

rman target /

delete backup;

yes

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

所有文件丢失后的恢复

delete copy;

开启控制文件自动备份

run{

backup database plus archivelog;

sql 'alter system swith logfile';

sql 'alter system swith logfile';

sql 'alter system swith logfile';

backup archivelog all;(如果没有这个

数据文件备份SCN号不一样)

};

把归档日志备份到备份里面去了

select * from v$log;

select current_scn from v$database;

cd $ORACLE_HOME

vim initorcl.ora

database=orcl

startup nomount;

restore spfile from '备份的参数的文件';

shutdown immediate;

startup nomount

create pfile from memory;

rman target /

restore controlfile from '  ';

sql 'alter database mount';

restore database;  把备份的东西还原回来...

cd oradata/

cd orcl/

ls ll  using

backup controlfile;

alter database open;

alter database open resetlogs;  until

console;

控制文件 在备份前  备份  控制文件的背后后备份

alter system switch logfile;

alter system switch logfile;

alter system swtich logfile;

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

rman target /

delete backup;

delete copy;

show all;

configure controlfile autobackup off;

backup spfile format

'/u01/app/oracle/ganxinjian/spfile1118.bkp';

backup current controlfile;

---

connect hr/g3152647;

create table f1(id int);

insert into f1 values(1);

insert into f1 values(2);

---

alter system switch logfile;

alter system switch logfile;

----

run{

backup database plus archivelog;

sql 'alter system swtich logfile';

sql 'alter system switch logfile';

sql 'alter system switch logfile';

backup archivelog all；

}；

联机日志时不能备份的...

[SCN号的推进]

删除文件

cd $ORACLE_HOME/dbs

rm spfileorcl.ora;

rm initorcl.ora

cd $ORACLE_HOME/oradata/orcl

rm  控制文件  日志文件

数据文件  归档文件

sqlplus / as sysdba;

shutdown abort;

cd $ORACLE_HOME/dbs

orapwd file=orapworcl password=oracle

cd $ORACLE_HOME/dbs/

vim initorcl.ora;

db_name=orcl

startup nomount;

rman target /

restore spfile from '  ';

shutdown abort;

startup nomount;

create pfile from memory;

create pfile from spfile;

rman target /

shutdown abort;

cd $ORACLE_HOME/dbs;

rm spfileorcl.ora;

vim initorcl.ora

修改控制文件位置/

修改的目录要存在

su - root;

mkdir -p /u02/app/oracle/oradata/orcl

[rm -rf u02]

chown -R oracle:oinstall /u02;

startup nomount;

create spfile from pfile;

shutdown abort;

startup nomount;

rman target /

restore controlfile from '  ';

sql 'alter database mount';

report schema;

baidu.com

异地恢复

run{

set newname for datafile 1 to

'/u02/app/oracle/oradata/orcl/system01.dbf';

set newname for datafile 1

set newname for datafile 1

set newname for datafile 1

restore databse;

swtich datafile all;

}

要注册备份信息到控制文件

rman target /

catalog;

catalog start with '  备份集的位置  ';

run{

set newname for datafile 1 to

'/u02/app/oracle/oradata/orcl/system01.dbf';

set newname for datafile 2

set newname for datafile 3

set newname for datafile 4

restore databse;

swtich datafile all;

}

report schema;

run{

set newname for datafile 5 to '/u02/

restore datafile 5;

}

recover database;

sqlplus

sqlplus / as sysdba;

//alter database open;

alter database open resetlogs;

shutdown immediate;

startup mount;

还联机日志的位置

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

alter database rename file

'/u01/app/oracle/oradata/ocp/stdredoa.log' to

'/u01/app/oracle/oradata/log/stdredoa.log';

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

shutdown immeidate;

alter database noarchivelog;

archive log list;

select  log_mode from v$database;

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

restore point

Monitoring  and Tuning RMAN

select current_scn from v$database;

select s.sid,p.sid,s.client_info

from v$process p,v$session s

where p.addr = s.paddr

and client_info like 'rman%';

RMAN Multiplexing

show parameter backup_tape_io_slaves

Aschronous

select * from v$backup_async_io;

rman target /

backup datafile 5;

bakcup duration 00:10 datafile 5;

backup duration 00:10 minimize load datafile 5;

backup validate;

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

backup validate datafile 5;

backup datafile 5;

并行度

设置多个通道

parallelism  2

也可以run{}中手动指定多个通道

备份的速度与恢复的速度无关

Tuning

Summary  Tuning RMAN

1.并行度

必考题

你有2各备份命令同时在跑

A------------

B-------------

可以在备份命令中加上...

run{

set command id to 'sess1';

backup database;

}

run{

set command id to 'sess2';

backup database;

}

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

坏块....

9 Diagnosing the Database

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

ADR  (AutoMatic Diagnostic

Repository)自动诊断库

show parameter diag

alter system set diag_  ='/home/oracle/

tail -f alert_orcl.log

/bin/rm/ -rf  *.trc

/bin/rm -rf  * trm

cat /dev/null > alert_orcl.log

cd $ORACLE_BASE

cd diag/

cd rdbms/  cd tnslsnr/

alert ----------- log.xml

trace ----------------------alert_SID.log

ADRCI  接口

kaoshi 考试：ADRCI  时不依赖以实例启动的...

v$diag_flag

select * from v$diag_info;

show parameter core_dump;

show parameter diag

diagnostic_dest

alter system set diagnostic_dest = '/home/oracle';

alter system set diagnostic_dest= '$ORACLE_HOME/dbs';

/bin/rm -rf  *.audcd ..

bin /rm -rf adump

ORA-01578  块损坏错误

校验值

Set Parameter to Detect Corruption

RAC 集成

备份不能在块的级别... 只能在文件的备份

backup datafile 5

select * from dba_extents

whera owner='HR' and segment_name = 'DEPARTMENTS';

block ID=168

5 example  168

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

破坏block脚本

alter system flush

blockrecover datafile 5 block 168,169,170,171

块损坏修复

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

#!/bin/sh

FILE=${1:?'Parameter 1 should be set to file name'}

BLOCK=${2:?'Parameter 2 should be set to the block to be

corrupted'}

BLOCKSIZE=${3:?'Parameter 3 should be set to the database

block size'}

dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=$BLOCK

<

CORRUPT

EOF

dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 1 + $BLOCK`

<

CORRUPT

EOF

dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 2 + $BLOCK`

<

CORRUPT

EOF

dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 3 + $BLOCK`

<

CORRUPT

EOF

--chmod 755 gen_datafile_corruption.sh

select file_id, block_id from dba_extents where owner = 'HR'

and segment_name = 'DEPARTMENTS';

./gen_datafile_corruption.sh

/u01/app/oracle/oradata/orcl/example01.dbf

168 8192

dbv file=/u01/app/oracle/oradata/orcl/example01.dbf

blocksize=8192

RMAN>

blockrecover datafile 5 block 168,169,170,171;

<===> blockrecover corruption list

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

闪回

7中类型

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

1.drop

来源  recyclebin

命令  flashback table f1 to before drop

[rename to f1_1];

影响  you

局限性

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

select table_name

from user_tables;

drop table f1;

falshback table f1 to before drop;

grant all on f1 to scott;

select * from dba_table_privs

where gantee='SCOTT';

show recyclebin

select * from "  回收站内的表";

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

sys

select * from dba_recyclebin;

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

flashback table f1 to before drop;

falshback tabel "  " to before drop;

先进后出  后进先出的原则

flashback table f1 to before drop rename to f1_1;

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

表  是有附带对象的...  view

index  synonym

recyclebin;

create index idx_f1 on f1(id);

select * from dba_indexes

where table_name ='F1';

show recyclebin;

drop table f1;

show recyclebin;

select * from dba_recyclebin;

表删除后  索引也是一起删除的...

flashback table f1 before drop;

show recyclebin;

select * from dba_indexes

where table_name ='F1';

当你闪回一张的时候 表会变成原来的名称

索引名称和回收站里面的名字是一样的

2.将一个表闪回到过去的某一个时间点

来源  undo

flashback table e1 to timestamp to_timestamp(

);

3.闪回查询  某一个时刻的值

数据来源：undo

update e1 salary=0;

commit;

update e1 set salary =1;

host date;

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

create table e1_12 as

select salary from (e1 as of timestamp to timestamp(

'  host date')) a where a.;

对数据是没有影响的...

4:闪回版本查询

undo

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

create table e1

as select * from employees;

update e1 set salary=1000 where employee_id =120;

commit;

update e1 set salary=1100 where employee_id =120;

update e1 set salary=1500 where employee_id =120;

commit;

select

from e1

version between;

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

select versions_xid,

to_char(versions_starttime, 'yyyy-mm-dd

hh24:mi:ss') versions_starttime,

to_char(versions_endtime, 'yyyy-mm-dd

hh24:mi:ss') versions_endtime,

versions_startscn,

versions_endscn,

versions_operation,

salary

from hr.e1 versions between timestamp

minvalue and systimestamp

where employee_id = 120 order by

nvl(versions_startscn, -1);

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

select versions_xid,

to_char(versions_starttime, 'yyyy-mm-dd

hh24:mi:ss') versions_starttime,

versions_startscn,

versions_operation,

salary

from hr.e1 versions between timestamp

to_timestamp('2017-11-18 15:42:33', 'yyyy-mm-dd

hh24:mi:ss')

and systimestamp

where employee_id =120

order by nvl(versions_startscn,

-1);

5:

事务的闪回

sqlplus / as sysdba;

hostdate

undo 数据和追踪

打开追踪日志

alter database add supplemental log data;

alter database add supplemental log data (primary key)

columns;

6:闪回归档

create tablespace flash_arc datafile '

/oraclefiles/flash_arc_01.dbf'  '

size 100m autoextend on;

create flashback archive arc_1 year retention 1 year ;

grant flashback archive administer to hr;

connect hr/hr;

create flashback archive arc_1_day tablespace flash_arc_01

retention 1 day;

alter archive arc_1_day

modify retention 7 day;

alter table employees flashback archive arc_1_year;

alter tabel e1 flashback archive arc_1_day;

表的结构不能改变...

undo操作是不能跨越表的结构的

alter table e1 add(sex varchar2(20));

drop table e1;

describe ;

alter table e1 drop column sex;

select * from dba_tables

where tablespace_name ='FLASH_ARC';

select * from hr.sys_fba_ddl_colmap_7666;

CMP3$

CMP4$

alter table e1 no flashback archive;

alter table employees no flashback archive;

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

SQL> create tablespace flash_arc datafile

'/u02/app/oracle/oradata/orcl/flash_arc_01.dbf' size 100m

autoextend on;

Tablespace created.

SQL> create flashback archive arc_1_year tablespace

flash_arc;

create flashback archive arc_1_year tablespace flash_arc

*

ERROR at line 1:

ORA-01009: missing mandatory parameter

SQL> create flashback archive arc_1_year tablespace

flash_arc retention 1 year;

Flashback archive created.

SQL> show user

USER is "SYS"

SQL> grant flashback archive adminster to hr;

grant flashback archive adminster to hr

*

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant flashback archive administer to hr;

Grant succeeded.

SQL> conn hr/hr

Connected.

SQL> create flashback archive arc_1_day tablespace

flash_arc retention 1 day;

Flashback archive created.

SQL> alter table employees flashback archive

arc_1_year;

Table altered.

SQL> alter table e1 flashback archive arc_1_day;

Table altered.

create user hr1 identified by g3152647;

grant connect to hr1;

grant select on hr.employees;

connect hr1/g3152647;

select * from hr.employees;

update employees set salary=1;

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

7:flashback database

功能依赖于归档模式

alter database flashback on;

ps -ef|grev wr;

ora_rvwr_orcl

文件闪回归档

闪回日志

drop user hr1 cascade;

exit;

实施的时候只能在mount状态下

sqlplus / as sysdba;

shutdown immediate;

startup mount;

shutdown abort;

startup mount ;

flashback database to timestamp

to_timestamp('2017-11-18 16:56:00','yyyy-mm-dd

hh24:mi:ss');

相当于是做了不完全恢复

alter database open;

alter database open resetlogs;

connect hr1/g3152647;

9:00--------------------------9:10-------------------------------16:00

闪回日志 会回到9点  然后  archivelog

往前走一点...

物理上的删除  flashback database

那个数据文件能回去  但是不能使用

基于表空间的恢复

create restore point rp1;  恢复点

mount 模式下

flashback database to restore point rp1

flashback日志  位置不能变

create restore point rp2 guarantee flashback database;

flashback database to restore point rp2

drop restore point rp2;

drop restore point rp1;

主库  备库

gg