Oracle数据库开启归档
shu immedite
startup mount
alter database archivelog;
alter database open;
archive log list
mkdir -p /u01/backup
mkdir -p /u01/arch
alter system set log_archive_dest_1=‘location=/u01/arch’;
创建wallet和主密钥
mkdir -p /u01/app/oracle/admin/don/wallet
配置$ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION=
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY =/u01/app/oracle/admin/don/wallet)
)
)
配置自动打开钱夹
orapki wallet create -wallet /u01/app/oracle/admin/don/wallet -auto_login -pwd oracle1234
select * from v$encryption_wallet;
创建主密钥
alter system set encryption key identified by “Oracle234”;
alter system set encryption key identified by “Oracle234”;
手动打卡和关闭
alter system set wallet open identified by Oracle234;
alter system set wallet close identified by Oracle234;
用户和加密数据准备
create tablespace jm1 datafile ‘/u01/app/oracle/oradata/don/jm101.dbf’ size 50m
autoextend on maxsize 2g encryption default storage(encrypt);
create user t identified by t;
grant connect,dba to t;
alter user t default tablespace jm1;
conn t/t
创建表和插入数据
create table t1(id number,name varchar2(20));
insert into t1 values(1,‘ACE’);
insert into t1 values(2,‘OCM’);
insert into t1 values(3,‘OCP’);
insert into t1 values(4,‘OCA’);
insert into t1 select * from t1;
create table t2(id number,name varchar2(20));
insert into t2 values(1,‘ACE’);
insert into t2 values(2,‘OCM’);
insert into t2 values(3,‘OCP’);
insert into t2 values(4,‘OCA’);
insert into t1 select * from t2;
insert into t2 select * from t2;
查询
select table_name,tablespace_name from user_tables;
select tablespace_name,encrypted from dba_tablespaces;
select count(*) from t1;
模拟添加加密数据文件
alter tablespace jm1 add datafile ‘/u01/app/oracle/oradata/don/jm102.dbf’ size 16M autoextend on next 4m maxsize 2g;
select FILE_NAME,TABLESPACE_NAME from dba_data_files where tablespace_name=‘JM1’;
切换到t用户
conn t/t
创建测试表
CREATE TABLE “JM”
( “ID” VARCHAR2(8 CHAR) NOT NULL ENABLE,
“NAME” VARCHAR2(40 CHAR),
“DZ” VARCHAR2(50 CHAR),
“SFZ” VARCHAR2(18 CHAR),
“SJ1” VARCHAR2(20 CHAR),
“SJ2” VARCHAR2(50 CHAR),
“MAIL” VARCHAR2(30 CHAR),
“DATE1” DATE,
“DATE2” TIMESTAMP (6),
“SJ3” NUMBER,
“BANKID” NUMBER
) ;
Insert into JM (ID,NAME,DZ,SFZ,SJ1,SJ2,MAIL,DATE1,DATE2,SJ3,BANKID) values (‘00000598’,‘王红雷’,‘北京市海淀区’,‘11010419350918004x’,‘13678903423’,‘13911636883’,‘motianhaozi@hotmail.com’,to_date(‘2036-09-18’,‘yyyy-mm-dd’),null,13911636883,6281122334455667788);
Insert into JM (ID,NAME,DZ,SFZ,SJ1,SJ2,MAIL,DATE1,DATE2,SJ3,BANKID) values (‘00000599’,‘刘华预’,‘北京市海淀区田村中关村’,‘432721198002200029’,‘13678435232’,‘13910562628’,‘wangbin623@msn.com’,to_date(‘1981-02-20’,‘yyyy-mm-dd’),null,13910562628,6281122334455667788);
Insert into JM (ID,NAME,DZ,SFZ,SJ1,SJ2,MAIL,DATE1,DATE2,SJ3,BANKID) values (‘00000600’,‘于生力’,‘北京市海淀区田村中关村’,‘130223197801121416’,‘13534234232’,‘13810423376’,‘cs1657@sina.com.cn’,to_date(‘1979-01-12’,‘yyyy-mm-dd’),null,13810423376,6281122334455667788);
Insert into JM (ID,NAME,DZ,SFZ,SJ1,SJ2,MAIL,DATE1,DATE2,SJ3,BANKID) values (‘00000601’,‘柳梦璃’,‘北京市海淀区中关村’,‘130223198101121416’,‘13534234232’,‘13810423376’,‘zxdcclx@188.com’,to_date(‘1979-01-12’,‘yyyy-mm-dd’),null,13810423376,6281122334455667788);
Insert into JM (ID,NAME,DZ,SFZ,SJ1,SJ2,MAIL,DATE1,DATE2,SJ3,BANKID) values (‘00000602’,‘东方不败’,‘北京市海淀区田村中关村’,‘130223197801121416’,‘13534234232’,‘13810423376’,‘cs1657@sina.com.cn’,to_date(‘1979-01-12’,‘yyyy-mm-dd’),null,13810423376,628123456789066);
commit;
insert into jm select * from jm;
commit;
oracle数据库整库备份
create pfile from spfile;
开启rman参数
show all
configure backup optimization on;
configure controlfile autobackup on;
开始备份
backup format ‘/home/oracle/files/prod1_%U.bak’ database plus archivelog;
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database format ‘/u01/backup/db_%U.bak’
plus archivelog format ‘/u01/backup/ar_%U.bak’;
backup current controlfile format ‘/u01/backup/ctl_%U.bak’;
backup spfile format ‘/u01/backup/spfile_%U.bak’;
release channel c1;
release channel c2;
}
备份数据文件 归档日志文件 控制文件和参数文件
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup as compressed backupset database format ‘/u01/backup/db_%U.bak’
plus archivelog format ‘/u01/backup/ar_%U.bak’;
backup current controlfile format ‘/u01/backup/ctl_%U.bak’;
backup spfile format ‘/u01/backup/spfile_%U.bak’;
release channel c1;
release channel c2;
}
清理备份
delete backupset;
异机恢复
复制钱夹
cd /u01/app/oracle/admin/don
scp -r wallet oracle@192.168.81.49:/u01/app/oracle/admin/don
复制sqlnet.ora
cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
scp sqlnet.ora oracle@192.168.81.49:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
备份文件复制到备份机器
scp -r backup/ oracle@192.168.81.49:/u01/
拷贝参数文件到备份库dbs目录
scp spfiledon.ora initdon.ora oracle@192.168.81.49:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
scp spfiledon.ora oracle@192.168.81.49:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
注意：根据initdon.ora参数文件检查备份库服务器文件目录，如果没有需要创建并检查用户属组和权限
数据库启动到nomount
SQL> startup nomount
或者rman启动到nomount状态（如果备份了参数文件）
[oracle@otest2 dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 15 11:20:14 2021
Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdon.ora’
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 104859368 bytes
Database Buffers 46137344 bytes
Redo Buffers 5439488 bytes
RMAN>
—与源数据库的dbid一样
set dbid 309174946
恢复参数文件
restore spfile from ‘/u01/backup/spfile_0fvkmg8r_1_1.bak’;
RMAN> startup nomount force;
恢复控制文件
restore controlfile from ‘/u01/backup/ctl_07vkkcde_1_1.bak’;
控制文件恢复完成后，把数据库实例启动到mount状态
alter database mount;
查看wallet状态
SQL> select * from v$encryption_wallet;
WRL_TYPE
WRL_PARAMETER
STATUS
file
/u01/app/oracle/admin/don/wallet
CLOSED
如果钱夹没启动需要启动钱夹
alter system set wallet open identified by Oracle234;
确保钱夹正常
/u01/app/oracle/admin/don/wallet
OPEN
在新控制文件中注册数据文件备份和归档备份
RMAN> catalog start with ‘/u01/backup’;
恢复整个库
恢复数据文件
RMAN> restore database;
恢复数据库
RMAN> recover database;
select instance_name,status from v$instance;
resetlogs打开数据库，目的就是reset scn，这样两者的scn一致
SQL> alter database open resetlogs;
关闭数据库
SQL> shutdown immediate
SQL> startup
undo数据文件损坏 ORA-01157
重建undo文件
undo状态
从数据库中查询这些回滚段的状态：
select segment_name,tablespace_name,status from dba_rollback_segs;
查看 undo 段中区的状态
select segment_name,tablespace_name,status
from dba_undo_extents where status=‘EXPIRED’ order by status;
1g 缺省设置为 AUM(Auto Undo Management)
AUM 下 current undo retention 是自动调整的
select begin_time,tuned_undoretention from v$undostat;
使用dbv命令查看undo表空间的数据文件是否有加密块
单机环境
[oracle@otmt1 ~]$ dbv file=/u01/app/oracle/oradata/don/undotbs01.dbf
OracleRAC ASM环境
dbv file=+DATA/PRODCDB/DATAFILE/undotbs1.260.1048356521 userid=system/oracle
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Jan 21 17:47:14 2021
Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/don/undotbs01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 258488
Total Pages Processed (Seg) : 10
Total Pages Failing (Seg) : 0
Total Pages Empty : 3656
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1144204 (0.1144204)
