网站工程前端,白银区住房和城乡建设局网站,网站服务器的费用,廊坊网站建设招聘保命法则#xff1a;先备份再操作#xff0c;磁盘空间紧张无法备份就让满足#xff0c;给自己留退路。
场景说明#xff1a;
1.本文档的环境为同平台、不同版本#xff08;操作系统版本可以不同#xff0c;数据库版本相同#xff09;#xff0c;源机器和目标机器部分…保命法则先备份再操作磁盘空间紧张无法备份就让满足给自己留退路。
场景说明
1.本文档的环境为同平台、不同版本操作系统版本可以不同数据库版本相同源机器和目标机器部分控制文件和ORACLE_BASE目录不一样。
2.目标机器只需要安装oracle只安装oracle数据库软件不创建数据库no netca dbca。
3.第一次利用备份恢复测试环境之后从源机器拷贝备份到目标机器并在控制文件中注册再进行恢复测试。 主要过程
将切换归档执行rman全库备份以及密码文件拷贝到目标主机进行rman恢复。 注意事项
当使用rman nocatalog恢复时数据库必须是处于“mount”状态的。而Oracle startup mount的前提条件是control必须存在。因此你必须在恢复datafile之前先恢复controlfile。 特别说明
源数据库和新数据库控制文件目录和ORACLE_BASE目录不同其他目录相同。解决办法更改参数文件中控制文件目录和ORACLE_BASE目录为新数据库位置。
源数据库和新数据库控制文件目录和ORACLE_BASE目录不同其他目录不相同。解决办法更改参数文件中控制文件目录和ORACLE_BASE目录为新数据库位置、更改控制文件中数据文件位置。
环境描述
源数据库环境
操作系统版本 : CentOS Linux release 7.9 内存4G cpu1核 磁盘40G 数据库版本 : Oracle 11.2.0.4 x64
数据库架构单点 数据库名 : dxj 数据库SID : dxj db_unique_name : dxj instance_name : dxj
归档模式已开启 IP : 192.168.40.52
目标数据库环境
操作系统版本 : CentOS Linux release 7.9 内存4G cpu1核 磁盘40G
数据库版本 : Oracle 11.2.0.4 x64 只安装grid、oracle数据库软件,no dbca
数据库架构RAC OS 规格 主机名 IP VIP private IP scanip redhat 7.9 1C2G orcl01 192.168.40.200 192.168.40.202 192.168.183.200 192.168.40.205 redhat 7.9 1C2G orcl02 192.168.40.201 192.168.40.203 192.168.183.201
迁移前源库准备
构建测试数据(正式环境跳过)
--通过数据库主机连接
su - oracle
sqlplus / as sysdba
select sysdate from dual;--查看数据文件存放目录
sqlplus / as sysdba
select file_name from dba_data_files;--创建表空间
--创建表空间大小5m开启自动扩展
create tablespace test datafile /oradata/dxj/test01.dbf size 5M autoextend on NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;--创建临时表空间
create temporary tablespace testtemp tempfile /oradata/dxj/testtemp01.dbf
size 50m autoextend on next 1m maxsize unlimited extent management local uniform size 4m;##创建数据库用户
--创建用户指定默认表空间和临时表空间
create user test identified by test default tablespace test temporary tablespace testtemp account unlock;
--赋予权限 最后的with admin option表示权限转授可以把本身获得的权限再授权给其他用户
grant dba,connect,resource to test with admin option;
注意密码最长30位
--切换用户
conn test/test##创建表create table test (id number primary key not null,name varchar2(100));
insert into test values (1,hm);
insert into test values (2,xk);
insert into test values (3,whm);
commit;create table t1 (id int not null);
beginfor i in 1..10000 loopinsert into t1 values(dbms_random.value()*10000);--如果列是主键insert into t1 values(i);end loop;
end;
/
统计数据量
--查看数据文件总大小
select sum(bytes)/1024/1024/1024 as size(G) from dba_data_files;size(G)
----------
1.30371094--查看总大小
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || Gfrom dba_segments;ROUND(SUM(BYTES)/1024/1024/1024,2)||G
-----------------------------------------
1.2G
统计业务数据
--查看用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in (SYS,SYSTEM,HR,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WMSYS,APPQOSSYS,APEX_030200,OWBSYS_AUDIT,ORDDATA,CTXSYS,ANONYMOUS,SYSMAN,XDB,ORDPLUGINS,OWBSYS,SI_INFORMTN_SCHEMA,OLAPSYS,SCOTT,ORACLE_OCM,XS$NULL,BI,PM,MDDATA,IX,SH,DIP,OE,APEX_PUBLIC_USER,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR);USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST TEST TESTTEMP--查看用户使用了哪些表空间
select distinct tablespace_name from dba_segments where owner in (TEST);
TABLESPACE_NAME
------------------------------
TEST--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)FROM dba_objects dWHERE d.OWNER in (TEST)AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_nameD.OBJECT_NAME AND D.OWNERB.owner) GROUP BY D.OWNERORDER BY D.OWNER ;OWNER COUNT(1)
------------------------------ ----------
TEST 3--每个业务用户下的各个对象类别的数量校验
select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in (TEST) group by OBJECT_TYPE,owner,status order by 1,3,2;OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
TEST INDEX VALID 1
TEST TABLE VALID 2
清理业务数据
需和开发 直属领导同意。对于占用空间大的表采用方案如下
1、删除表数据。
2、备份时加参数排除即不备份对业务来说不重要的表。
--查看表大小超过10G的大表
col owner for a40
col TABLE_NAME for a60
set linesize 999 pagesize 999
select * from (SELECT OWNER,TABLE_NAME,SUM(tabsize) table_size from (SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as tabsize FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN (SYS,SYSTEM,DBSNMP))GROUP BY SEGMENT_NAME,ownerunion ALLselect lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg where lob.segment_nameseg.segment_name and lob.OWNER NOT IN (SYS,SYSTEM,DBSNMP) group by lob.owner,lob.table_name) GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC)where table_size10 ;--查询库中记录数大于2千万的所有表当前用户拥有select any table权限
select table_name, num_rows from dba_tables t where t.owner upper(hr) and num_rows 20000000;
或
select table_name, num_rows from all_tables t where t.owner upper(hr) and num_rows 20000000;
停止监听
--停止监听
su - oracle
[oracledxj ~]$ lsnrctl status
[oracledxj ~]$ lsnrctl stop
[oracledxj ~]$ lsnrctl status
开启防火墙
--开启防火墙
systemctl start firewalld
systemctl status firewalld
firewall-cmd --list-ports
停止会话
查询会话如果有业务会话给干掉
--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;--查询用户会话
select username,serial#, sid ,status from v$session where username is not null;
select username,status,schemaname,osuser,process,machine,port,program,serial#, sid from v$session where username is not null;--删除相关用户会话
alter system kill session sidserial#;补充
--源库杀用户连接 不建议 该步骤慎重 该文档未进行该操作 该方法迫不得已不用
ps -ef|grep LOCALNO|awk {print $2}|xargs kill -9
锁定业务用户
--查看用户 和现场负责人确定哪些是业务用户哪些是需要迁的用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in (SYS,SYSTEM,HR,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WMSYS,APPQOSSYS,APEX_030200,OWBSYS_AUDIT,ORDDATA,CTXSYS,ANONYMOUS,SYSMAN,XDB,ORDPLUGINS,OWBSYS,SI_INFORMTN_SCHEMA,OLAPSYS,SCOTT,ORACLE_OCM,XS$NULL,BI,PM,MDDATA,IX,SH,DIP,OE,APEX_PUBLIC_USER,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR);USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST TEST TESTTEMP--锁定业务用户
select username,account_status,lock_date from dba_users where usernameTEST;
alter user TEST account lock;
或
select alter user ||username|| account lock; from dba_users where username in (TEST) and ACCOUNT_STATUOPEN;
执行以上输出结果 查看数据库信息
查看数据库参数
--数据库的创建日期和归档方式
SELECT to_char(created,yyyy-mm-dd), log_mode, log_mode FROM V$database; TO_CHAR(CR LOG_MODE LOG_MODE
---------- ------------ ------------
2024-05-26 ARCHIVELOG ARCHIVELOG--查数据库版本
SELECT version FROM product_component_version WHERE substr(product, 1, 6) Oracle; VERSION
--------------------------------------------------------------------------------
11.2.0.4.0--查看实例名
select instance_name from v$instance;INSTANCE_NAME
----------------
dxj-- 查看数据库字符集
set linesize 999
col value for a60
select * from nls_database_parameters where PARAMETER in (NLS_CHARACTERSET,NLS_NCHAR_CHARACTERSET);
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8select userenv(language) from dual;USERENV(LANGUAGE)
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8--查看当前数据库时区
select dbtimezone from dual;
补充如果不是东八区 alter database set time_zone8:00;-- 查看数据库配置参数
set lin 1000 pagesize 999
col NAME for a30
col value for a40
col DISPLAY_VALUE for a40
SELECT name,value,display_value FROM v$parameter
WHERE name IN(
processes,
sessions,
memory_target,
memory_max_target,
sga_max_size,
sga_target,
pga_aggregate_target
);NAME VALUE DISPLAY_VALUE
------------------------------ ---------------------------------------- ----------------------------------------
processes 2000 2000
sessions 3022 3022
sga_max_size 2650800128 2528M
sga_target 2650800128 2528M
memory_target 0 0
memory_max_target 0 0
pga_aggregate_target 659554304 629M7 rows selected.查看源数据库DBID、控制文件、数据文件、redo日志文件的存储位置和归档
--查看源数据库的DBID
testDXJ 2024-06-03 11:28:47 select dbid from v$database;DBID
----------
2712416469--查看控制文件
testDXJ 2024-06-03 11:28:57 select name from v$controlfile;NAME
--------------------------------------------------------------------------------
/oradata/dxj/control01.ctl
/u01/app/oracle/fast_recovery_area/dxj/control02.ctl--查看数据文件
sysDXJ 2024-06-03 11:29:39 set linesize 999
sysDXJ 2024-06-03 11:30:37 col name for a69
sysDXJ 2024-06-03 11:30:43 select status,name from v$datafile;STATUS NAME
------- ---------------------------------------------------------------------
SYSTEM /oradata/dxj/system01.dbf
ONLINE /oradata/dxj/sysaux01.dbf
ONLINE /oradata/dxj/undotbs01.dbf
ONLINE /oradata/dxj/users01.dbf
ONLINE /oradata/dxj/test01.dbf5 rows selected.--查看日志文件
--查看redo log大小和位置
set linesize 999
col member for a60
select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#b.group#; --查看归档模式
sysDXJ 2024-06-03 11:31:32 archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/archivelog
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6 源数据库服务器
rman备份源数据库
文件多的场景加section size
大文件切片 多开并行优化速度前提是存储牛逼不然加了也没啥作用
评估备份时间传输时间恢复时间遇到问题
[oracledxj:/data/rman_bak]$ cat /data/rman_bak/rman_bak.sh
#!/bin/bash
source /home/oracle/.bash_profile
export NLS_DATE_FORMATYYYYMMDD hh24:mi:ss
rqdate %Y%m%d
bakdir/data/rman_bak/${rq}
autobak/data/rman_bak/autobackup
if [ ! -d ${bakdir} ];
then mkdir -p ${bakdir}
fi
if [ ! -d ${autobak} ];
then mkdir -p ${autobak}
fi
cd $ORACLE_HOME/bin
./rman log $bakdir/rman${rq}.log target / EOF
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup incremental level 0 as compressed backupset section size 10g format${autobak}/emrdb_full_%U_%d_%T_%s database;
sql alter system switch logfile;
backup as compressed backupset section size 10g archivelog from time sysdate-1/12 format${autobak}/emrdb_arc_%U_%d_%T_%s;
BACKUP CURRENT CONTROLFILE format ${autobak}/emrdb_ctl_bk_%U_%d_%T ;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
exit;
EOFchmod x /data/rman_bak.sh
nohup sh /data/rman_bak.sh 参数说明sysdate-1/12 :2小时之前的归档。%U_%d_%T_%s
%U默认是%u_%p_%c的简写形式利用它可以为每一个备份片段即磁盘文件生成一个唯一名称这是最常用的命名方式执行不同备份操作时生成的规则也不同如下所示生成备份片段时%U%u_%p_%c
%u是一个由备份集编号和建立时间压缩后组成的8字符名称。利用%u可以为每个备份集生成一个唯一的名称。
%p备份集中备份片段的编号从1开始。
%c备份片段的复制数从1开始编号最大不超过256。
%dOracle数据库名称。
%T当前时间的年月日格式YYYYMMDD。
%s备份集号。 rman备份有时间点和scn 记录一下恢复的时候按时间点和scn恢复就行了 通过rman进行一次全备
vi rman_bak_L0.sh
#************************************************************************
#*** rman_bak_L0.sh ***
#************************************************************************
#!/bin/bash
source /home/oracle/.bash_profile
rqdate %Y%m%d
bakdir/topsoft/rmanbak/${rq}
autobak/topsoft/rmanbak/autobackup
if [ ! -d ${bakdir} ];
then mkdir -p ${bakdir}
fi
if [ ! -d ${autobak} ];
then mkdir -p ${autobak}
fi
cd $ORACLE_HOME/bin
./rman target / log$bakdir/rmanfull_${rq}.log EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ${autobak}/%F;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
sql alter system archive log current;
backup as compressed backupset incremental level 0 database tag dbfull format ${autobak}/backlv0_%d_%T_%t_%s_%p.bak;
sql alter system archive log current;
backup as compressed backupset archivelog all tag arch format ${autobak}/arch_%d_%T_%t_%s_%p.bak;
backup current controlfile format ${autobak}/ctl_%d_%T_%t_%s_%p.bak;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
report obsolete;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
list backup summary;
exit;
EOF【注意控制文件一定要最后备份由于没做归档目录数据库rman备份信息都放在控制文件里面要保证备份过程的信息内容都进入到控制文件保证数据文件和控制文件是一致备份】分别列出参数文件备份控制文件备份数据文件备份以及归档备份的名字
a.参数文件备份如下
RMAN list backup of spfile;using target database control file instead of recovery catalogList of Backup Sets
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 9.36M DISK 00:00:00 25-JAN-24 BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20240125T180503Piece Name: /topsoft/rmanbak/autobackup/c-2492862311-20240125-06SPFILE Included: Modification time: 25-JAN-24SPFILE db_unique_name: dxjb.控制文件备份如下
RMAN list backup of controlfile;List of Backup Sets
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 9.36M DISK 00:00:00 25-JAN-24 BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20240125T180503Piece Name: /topsoft/rmanbak/autobackup/c-2492862311-20240125-06Control File Included: Ckp SCN: 2895135 Ckp time: 25-JAN-24c.数据文件备份如下
RMAN list backup of database;
d.列出归档备份如下
RMAN list backup of archivelog all;
备份参数文件
create pfile/home/oracle/initdxj20240603.ora from spfile;
备份密码文件
密码文件位置$ORACLE_HOME/dbs
cp /u01/app/oracle/product/11.2.0/db/dbs/orapwdxj /home/oracle/
将rman备份文件、参数文件和密码文件copy至目标机器
如果端口不是默认的22需加-P port_value
--拷贝rman备份文件
scp -r /data/rman_bak/autobackup oracle192.168.40.200:/home/oracle/--拷贝参数文件
scp /home/oracle/initdxj20240603.ora oracle192.168.40.200:/home/oracle/--拷贝密码文件
scp /home/oracle/orapwdxj oracle192.168.40.200:/home/oracle/
目标数据库服务器
在RAC的orcl01机器上进行单机数据库的恢复
如果目标服务器已经新创建了dxj实例需使用以下命令进行删除删除后再进行如下恢复操作。
这里需要注意下RAC和单点的静默删除实例命令是有区别的。
注意事项
(1)RMAN 异机恢复的db_name 必须和备份的一致如果说想改成其他名称可以等还原之后在用nid 命令修改。
(2)控制文件需要指定到共享设备上
(3)检查audit_file_destbackground_dump_dest core_dump_destlog_archive_dest_1user_dump_dest等参数的位置。 如果2个节点和共享位置没有对应的目录先把目录建好。
--RAC静默删除实例
dbca -silent -deleteDatabase -sourceDB dxj -nodeinfo orcl01,orcl02--单点静默删除实例
dbca -silent -deleteDatabase -sourcedb dxj \
-sid dxj
修改参数文件
如果源服务器和目标服务器安装目录和数据目录都相同该步骤可忽略。不然按如下步骤更改。
修改目录
参数文件中
.audit_file_dest为$ORACLE_BASE目录/admin/dxj/adump
.control_files中control01.ctl 和control02.ctl 路径为DATA/dxj/
.db_recovery_file_dest为DATA
.diagnostic_dest为$ORACLE_BASE目录
--备份已备份的参数文件避免改的有问题重新从源库服务器传输到目标库服务器
cd /home/oracle
cp initdxj20240603.ora initdxj20240603.ora_bak--更改参数文件中的oracle_base目录为目标库的oracle_base目录 控制文件路径等路径
vi /home/oracle/initdxj20240603.ora语法说明
:%s#源目录#新目录#g
修改内存相关参数
如果源服务器和目标服务器配置都相同该步骤可忽略。不然按如下步骤更改。
由于目标服务器32G和源服务器内存128G差异大需要更改内存参数。
内存参数修改公式
--sga和pga配置的最佳实践
对于OLTP系统来说oracle建议是sga系统内存*0.8*0.8 单位字节
pga系统内存*0.8*0.2
对于DSS系统来说oracle建议是sga系统内存*0.8*0.5 单位字节
pga系统内存*0.8*0.5
去掉参数
以下参数可以去掉
dxj.__db_cache_size2113929216
dxj.__java_pool_size16777216
dxj.__large_pool_size16777216
dxj.__oracle_base/u01/app/oracle#ORACLE_BASE set from environment
dxj.__pga_aggregate_target671088640
dxj.__sga_target2650800128
dxj.__shared_io_pool_size0
dxj.__shared_pool_size486539264
dxj.__streams_pool_size0*.undo_tablespaceUNDOTBS1
增加参数
*.cluster_databasefalse
要先设置为flase才能进行RMAN 恢复操作。恢复完成在改成true。
*.undo_managementAUTO
*.cluster_database_instances2
*.cluster_databasefalse
dxj2.thread2
dxj1.thread1
dxj2.undo_tablespaceUNDOTBS2
dxj1.undo_tablespaceUNDOTBS1
dxj2.instance_number2
dxj1.instance_number1
最终更改后的参数文件
[oracleorcl01:/home/oracle]$ cat initdxj20240603.ora
*._b_tree_bitmap_plansFALSE
*._cleanup_rollback_entries2000
*._datafile_write_errors_crash_instanceFALSE
*._index_partition_large_extentsFALSE
*._memory_imm_mode_without_autosgaFALSE
*._optimizer_adaptive_cursor_sharingFALSE
*._optimizer_extended_cursor_sharingNONE
*._optimizer_extended_cursor_sharing_relNONE
*._optimizer_null_aware_antijoinFALSE
*._optimizer_use_feedbackFALSE
*._partition_large_extentsFALSE
*._PX_use_large_poolTRUE
*._undo_autotuneFALSE
*._use_adaptive_log_file_syncFALSE
*.audit_file_dest/u01/app/oracle/admin/dxj/adump
*.audit_trailNONE
*.compatible11.2.0.4.0
*.control_file_record_keep_time31
*.control_filesDATA/dxj/control01.ctl,DATA/dxj/control02.ctl
*.db_block_size8192
*.db_create_file_destDATA
*.db_domain
*.db_files5000
*.db_namedxj
*.db_recovery_file_destDATA
*.db_recovery_file_dest_size4322230272
*.deferred_segment_creationFALSE
*.diagnostic_dest/u01/app/oracle
*.dispatchers(PROTOCOLTCP) (SERVICEdxjXDB)
*.enable_ddl_loggingTRUE
*.event28401 trace name context forever,level 1,10949 trace name context forever,level 1
*.log_archive_dest_1locationARCH/dxj
*.log_archive_format%t_%s_%r.dbf
*.open_cursors1000
*.parallel_max_servers64
*.pga_aggregate_target659554304
*.processes2000
*.remote_login_passwordfileEXCLUSIVE
*.resource_limitTRUE
*.resource_manager_planforce:
*.sec_case_sensitive_logonFALSE
*.session_cached_cursors300
*.sga_max_size2638217216
*.sga_target2638217216
*.undo_retention10800
*.undo_managementAUTO
*.cluster_database_instances2
*.cluster_databasefalse
dxj2.thread2
dxj1.thread1
dxj2.undo_tablespaceUNDOTBS2
dxj1.undo_tablespaceUNDOTBS1
dxj2.instance_number2
dxj1.instance_number1
目标服务器上创建相应的目录
ORACLE_BASE目录不要创建因为数据库软件已安装。源库和目标库ORACLE_BASE目录不同采用更改目标库参数文件中ORACLE_BASE目录的方法。
窗口1在ASM共享磁盘DATA盘中建立DATA/dxj目录
--在ASM中建立DATA/DXJ目录
su - grid
asmcmd
cd DATA
mkdir DXJ--在ASM中建立ARCH/DXJ目录
su - grid
asmcmd
cd ARCH
mkdir DXJ
窗口2查看参数文件
cat /home/oracle/initdxj20240603.ora
窗口3创建参数文件中的目录
--所有节点都操作
mkdir -p /u01/app/oracle/admin/dxj/adump
通过pfile文件将数据库启动到nomount状态
[oracleorcl01:/home/oracle]$ export ORACLE_SIDdxj1
[oracleorcl01:/home/oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 20:39:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.SQL startup nomount pfile/home/oracle/initdxj20240603.ora;用pfile生成spfile文件(勿忘记)
用修改的pfile 来创建spfile注意放在共享设备上
数据库中用pfile生成spfile文件
--数据库中用pfile生成spfile文件
SQL create spfileDATA/DXJ/spfiledxj.ora from pfile/home/oracle/initdxj20240603.ora;
所有节点本地生成spfilesid.ora文件的映射文件initsid.ora
本地生成spfilesid.ora文件的映射文件initsid.ora 该步骤必须操作不然再次启库还是pfile方式启库的
--节点1
[oracleorcl01:/home/oracle]$ cd $ORACLE_HOME/dbs
[oracleorcl01:/u01/app/oracle/product/11.2.0/db/dbs]$
echo SPFILEDATA/DXJ/spfiledxj.ora initdxj1.ora--节点2
[oracleorcl02:/home/oracle]$ cd $ORACLE_HOME/dbs
[oracleorcl02:/u01/app/oracle/product/11.2.0/db/dbs]$ cat initdxj2.ora
echo SPFILEDATA/DXJ/spfiledxj.ora initdxj2.ora
重启库至nomount
--重启库至nomount
SQL shutdown abort;
SQL startup nomount; --启动到nomount状态
ORACLE instance started.Total System Global Area 2638954496 bytes
Fixed Size 2256152 bytes
Variable Size 1258291944 bytes
Database Buffers 1375731712 bytes
Redo Buffers 2674688 bytes验证启库是通过spfile文件方式
--验证启库是通过spfile文件方式
SQL set linesize 999
SQL show parameter pfile;NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
spfile string DATA/dxj/spfiledxj.ora
问题处理
通过pfile生成spfile失败提示DATA磁盘不存在ORA-15204ORA-15001ORA-17502
--问题描述
通过pfile生成spfile失败提示DATA磁盘不存在
SQL create spfileDATA/DXJ/spfiledxj.ora from pfile/home/oracle/initdxj20240603.ora;
create spfileDATA/DXJ/spfiledxj.ora from pfile/home/oracle/initdxj20240603.ora
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file DATA/DXJ/spfiledxj.ora
ORA-15001: diskgroup DATA does not exist or is not mounted
ORA-15204: database version 11.2.0.0.0 is incompatible with diskgroup DATA--分析过程
查看磁盘状态 data磁盘状态时mounted说明没问题
su - grid
sqlplus / as sysdba
SQL col name for a30
SQL select name,state from v$asm_diskgroup;NAME STATE
------------------------------ ---------------------------------
ARCH MOUNTED
DATA MOUNTED
OCR MOUNTED查看集群状态
[gridorcl01:/home/grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dgONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.DATA.dgONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.LISTENER.lsnrONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.OCR.dgONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.asmONLINE ONLINE orcl01 StartedONLINE ONLINE orcl02 Started
...... 查看oracle文件权限 权限也对
[gridorcl01:/u01/app/11.2.0/grid/bin]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 grid oinstall 209840352 Aug 18 2023 /u01/app/11.2.0/grid/bin/oracle查看oracle用户信息 oracle用户组也对
[oracleorcl01:/home/oracle]$ id oracle
uid54321(oracle) gid54321(oinstall) groups54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54327(asmdba),54328(asmoper),54329(asmadmin)查看参数文件
[oracleorcl01:/home/oracle]$ cat initdxj20240603.ora
......
*.compatible11.2.0.0.0
......--解决办法
将参数文件/home/oracle/initdxj20240603.ora中*.compatible11.2.0.0.0改成*.compatible11.2.0.4.0
重启库解决
su - oracle
sqlplus / as sysdba
shutdown abort
startup nomount pfile/home/oracle/initdxj20240603.ora;
create spfileDATA/DXJ/spfiledxj.ora from pfile/home/oracle/initdxj20240603.ora;
成功
创建密码文件
在所有节点执行注意密码要和原库一样
--节点1
[oracleorcl01:/home/oracle]$ orapwd file?/dbs/orapwdxj1 passwordoracle--节点2
[oracleorcl02:/home/oracle]$ orapwd file?/dbs/orapwdxj2 passwordoracle
恢复控制文件
源库和目标库的控制文件路径虽然不一样但是在更改参数文件的参数文件位置后恢复控制文件过程中会把控制文件恢复到已更改的参数文件指定的控制文件路径。
[oracleorcl01:/home/oracle]$ export ORACLE_SIDdxj1
[oracleorcl01:/home/oracle]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jun 3 14:59:01 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: DXJ (not mounted)RMAN restore controlfile from /home/oracle/autobackup/emrdb_ctl_bk_082se5im_1_1_DXJ_20240603;Starting restore at 03-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID32 device typeDISKchannel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file nameDATA/dxj/control01.ctl
output file nameDATA/dxj/control02.ctl
Finished restore at 03-JUN-24RMAN alter database mount;database mounted
released channel: ORA_DISK_1恢复数据文件
由于RAC下使用的是OMF路径所以先用脚本查询出源库中的数据文件名以及路径 sqlplus 执行
数据文件的转换由于目标 rac 库是 asm 存储的所以到新环境需要采用 set newname 来转换一下
可通过RMAN重命名数据文件、临时文件、日志文件进行还原
补充catalog的原理是查文件头里面 文件号 相对文件号 scn目的就是把信息取出来写到ctl的rman那一部分就是建个档 不明白
生成数据文件转换脚本
源库中执行
--数据文件转换脚本
set pagesize 200 linesize 200
select set newname for datafile || a.FILE# || to || DATA || ;
from v$datafile a
union all
select set newname for tempfile || a.FILE# || to || DATA || ;
from v$tempfile a
union all
SELECT SQL ALTER DATABASE RENAME FILE || a.MEMBER || to ||
DATA || ;
FROM v$logfile a;--输出结果
SETNEWNAMEFORDATAFILE||FILE_ID||TO||DATA||;
-----------------------------------------------------------------------------
set newname for datafile 4 to DATA;
set newname for datafile 3 to DATA;
set newname for datafile 2 to DATA;
set newname for datafile 1 to DATA;
set newname for datafile 5 to DATA;
set newname for datafile 6 to DATA;6 rows selected.--日志文件转换脚本
select set newname for tempfile ||file_id|| to ||DATA||; from dba_temp_files;输出结果
SETNEWNAMEFORTEMPFILE||FILE_ID||TO||DATA||;
-----------------------------------------------------------------------------
set newname for tempfile 1 to DATA;还原数据文件
加载备份目录下的备份集
--加载备份目录下的备份集
[oracleorcl01:/home/oracle]$ export ORACLE_SIDdxj1
[oracleorcl01:/home/oracle]$ rman target /
RMAN catalog start with /home/oracle/autobackup/;Starting implicit crosscheck backup at 03-JUN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID32 device typeDISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 03-JUN-24Starting implicit crosscheck copy at 03-JUN-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-JUN-24searching for all files in the recovery area
cataloging files...
no files catalogedsearching for all files that match the pattern /home/oracle/autobackup/List of Files Unknown to the DatabaseFile Name: /home/oracle/autobackup/emrdb_full_012se5hd_1_1_DXJ_20240603_1
File Name: /home/oracle/autobackup/emrdb_full_022se5hd_1_1_DXJ_20240603_2
File Name: /home/oracle/autobackup/emrdb_full_032se5hd_1_1_DXJ_20240603_3
File Name: /home/oracle/autobackup/emrdb_full_042se5hd_1_1_DXJ_20240603_4
File Name: /home/oracle/autobackup/emrdb_full_052se5hf_1_1_DXJ_20240603_5
File Name: /home/oracle/autobackup/emrdb_arc_062se5ii_1_1_DXJ_20240603_6
File Name: /home/oracle/autobackup/emrdb_arc_072se5ij_1_1_DXJ_20240603_7
File Name: /home/oracle/autobackup/emrdb_ctl_bk_082se5im_1_1_DXJ_20240603Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging doneList of Cataloged FilesFile Name: /home/oracle/autobackup/emrdb_full_012se5hd_1_1_DXJ_20240603_1
File Name: /home/oracle/autobackup/emrdb_full_022se5hd_1_1_DXJ_20240603_2
File Name: /home/oracle/autobackup/emrdb_full_032se5hd_1_1_DXJ_20240603_3
File Name: /home/oracle/autobackup/emrdb_full_042se5hd_1_1_DXJ_20240603_4
File Name: /home/oracle/autobackup/emrdb_full_052se5hf_1_1_DXJ_20240603_5
File Name: /home/oracle/autobackup/emrdb_arc_062se5ii_1_1_DXJ_20240603_6
File Name: /home/oracle/autobackup/emrdb_arc_072se5ij_1_1_DXJ_20240603_7
File Name: /home/oracle/autobackup/emrdb_ctl_bk_082se5im_1_1_DXJ_20240603
更改控制文件中数据文件和临时文件的名称并还原数据库
注意对switch的说明
对于nocatalog 模式下rman备份的信息是保存在控制文件里的包括文件的路径信息。 这里的switch的作用就是更新控制文件里的信息。
restore 的时候不会对temp 表空间进行restore。所以等restore 之后我们需要手工创建temp表空间。
不过在这个测试里我们还是对tempfile 进行了指定。 但是这个操作只更新控制文件不恢复数据文件。
--更改控制文件中数据文件和临时文件的名称还原数据库
RMAN
run {
set newname for datafile 4 to DATA;
set newname for datafile 3 to DATA;
set newname for datafile 2 to DATA;
set newname for datafile 1 to DATA;
set newname for datafile 5 to DATA;
set newname for datafile 6 to DATA;
set newname for tempfile 1 to DATA;
restore database;
switch datafile all;
switch tempfile all;
}
详细过程如下
RMANrun {
set newname for datafile 1 to DATA;
set newname for datafile 2 to DATA;
set newname for datafile 3 to DATA;
set newname for datafile 4 to DATA;
set newname for datafile 5 to DATA;
set newname for tempfile 1 to DATA;
set newname for tempfile 2 to DATA;
SQL ALTER DATABASE RENAME FILE /oradata/dxj/redo03.log to DATA ;
SQL ALTER DATABASE RENAME FILE /oradata/dxj/redo02.log to DATA ;
SQL ALTER DATABASE RENAME FILE /oradata/dxj/redo01.log to DATA ;
restore database;
switch datafile all;
switch tempfile all;
12 13 14 15 }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: ALTER DATABASE RENAME FILE /oradata/dxj/redo03.log to DATAsql statement: ALTER DATABASE RENAME FILE /oradata/dxj/redo02.log to DATAsql statement: ALTER DATABASE RENAME FILE /oradata/dxj/redo01.log to DATAStarting restore at 03-JUN-24
using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to DATA
channel ORA_DISK_1: restoring datafile 00004 to DATA
channel ORA_DISK_1: reading from backup piece /home/oracle/autobackup/emrdb_full_032se5hd_1_1_DXJ_20240603_3
channel ORA_DISK_1: piece handle/home/oracle/autobackup/emrdb_full_032se5hd_1_1_DXJ_20240603_3 tagTAG20240603T113405
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to DATA
channel ORA_DISK_1: restoring datafile 00005 to DATA
channel ORA_DISK_1: reading from backup piece /home/oracle/autobackup/emrdb_full_022se5hd_1_1_DXJ_20240603_2
channel ORA_DISK_1: piece handle/home/oracle/autobackup/emrdb_full_022se5hd_1_1_DXJ_20240603_2 tagTAG20240603T113405
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to DATA
channel ORA_DISK_1: reading from backup piece /home/oracle/autobackup/emrdb_full_012se5hd_1_1_DXJ_20240603_1
channel ORA_DISK_1: piece handle/home/oracle/autobackup/emrdb_full_012se5hd_1_1_DXJ_20240603_1 tagTAG20240603T113405
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 03-JUN-24datafile 1 switched to datafile copy
input datafile copy RECID6 STAMP1170687869 file nameDATA/dxj/datafile/system.271.1170687835
datafile 2 switched to datafile copy
input datafile copy RECID7 STAMP1170687869 file nameDATA/dxj/datafile/sysaux.273.1170687819
datafile 3 switched to datafile copy
input datafile copy RECID8 STAMP1170687869 file nameDATA/dxj/datafile/undotbs1.259.1170687819
datafile 4 switched to datafile copy
input datafile copy RECID9 STAMP1170687869 file nameDATA/dxj/datafile/users.262.1170687819
datafile 5 switched to datafile copy
input datafile copy RECID10 STAMP1170687869 file nameDATA/dxj/datafile/test.264.1170687819renamed tempfile 1 to DATA in control file
renamed tempfile 2 to DATA in control filerecover 数据库
在执行restore的节点执行因为备份文件在该节点上。
RMAN recover database;
详细过程如下
RMAN recover database;Starting recover at 03-JUN-24
using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread1 sequence6
channel ORA_DISK_1: reading from backup piece /home/oracle/autobackup/emrdb_arc_062se5ii_1_1_DXJ_20240603_6
channel ORA_DISK_1: piece handle/home/oracle/autobackup/emrdb_arc_062se5ii_1_1_DXJ_20240603_6 tagTAG20240603T113442
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file nameDATA/archivelog/1_6_1170010455.dbf thread1 sequence6
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread1 sequence7
channel ORA_DISK_1: reading from backup piece /home/oracle/autobackup/emrdb_arc_072se5ij_1_1_DXJ_20240603_7
channel ORA_DISK_1: piece handle/home/oracle/autobackup/emrdb_arc_072se5ij_1_1_DXJ_20240603_7 tagTAG20240603T113442
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file nameDATA/archivelog/1_7_1170010455.dbf thread1 sequence7
unable to find archived log
archived log thread1 sequence8
RMAN-00571:
RMAN-00569: ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
RMAN-03002: failure of recover command at 06/03/2024 15:09:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8 and starting SCN of 987464这里是提醒恢复到一个未知的scn号。 因为这部分SCN 还在online redo里可以在alter database mount之后通过set until scn或者set until time命令设置恢复到的scn号或时间
--在RAC的orcl01机器上recover数据库到最后一个归档日志的sequence1
RMAN recover database until sequence 8;
恢复完之后resetlogs打开数据库之前v$logfile中的member只显示DATA
SQL select * from v$logfile;GROUP# STATUS TYPE
---------- --------------------- ---------------------
MEMBER
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_RECOVE
---------3 ONLINE
DATA
YES2 ONLINE
DATA
YES1 ONLINE
DATA
YES重置归档位置
如果在参数文件中已设置正确该步骤可忽略
SQL alter system set log_archive_dest_1LOCATIONARCH SID*;
RESETLOGS打开数据库
SQL alter database open resetlogs;Database altered.
数据量查看
--查看数据库
SQL select sum(bytes)/1024/1024/1024 as size(G) from dba_data_files;size(G)
----------
1.30371094 --查看总大小
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || Gfrom dba_segments;ROUND(SUM(BYTES)/1024/1024/1024,2)||G
-----------------------------------------
1.2G--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)FROM dba_objects dWHERE d.OWNER in (TEST)AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_nameD.OBJECT_NAME AND D.OWNERB.owner) GROUP BY D.OWNERORDER BY D.OWNER ;OWNER COUNT(1)
------------------------------ ----------
TEST 3--每个业务用户下的各个对象类别的数量校验
SQL select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in (TEST) group by OBJECT_TYPE,owner,status order by 1,3,2;OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
TEST INDEX VALID 1
TEST TABLE VALID 2数据库配置更改
修改参数
--在RAC的orcl01机器上修改参数修改数据库的实例数目为每个实例命名并使每个实例拥有独立的thread
alter system set cluster_databasetrue scopespfile;
alter system set cluster_database_instances2 scopespfile;
alter system set instance_number1 scopespfile siddxj1;
alter system set instance_number2 scopespfile siddxj2;
alter system set thread1 scopespfile siddxj1;
alter system set thread2 scopespfile siddxj2;
undo tablespace
在RAC的orcl01机器上修改参数使每个实例有独立的undo tablespace
--查看undo tablespace保证只有一个undo tablespace
show parameter undo--在RAC的orcl01机器上创建第二个undo tablespace
create undo tablespace undotbs2 datafile DATA size 512m autoextend on maxsize unlimited;--在RAC的orcl01机器上修改参数使每个实例有独立的undo tablespace
alter system set undo_tablespaceundotbs1 scopespfile siddxj1;
alter system set undo_tablespaceundotbs2 scopespfile siddxj2;
创建在线日志组
在RAC的orcl01机器上为第二个thread创建在线日志组开了闪回区后默认1个日志组会有2个日志成员闪回区一个数据目录一个。
--查看日志文件
--查看redo log大小和位置
set linesize 999
col member for a60
select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#b.group#; --第二个thread创建在线日志组
alter database add logfile thread 2 group 4 (DATA,DATA) size 50m;
alter database add logfile thread 2 group 5 (DATA,DATA) size 50m;
alter database add logfile thread 2 group 6 (DATA,DATA) size 50m;
启动第二个thread
--在RAC的orcl01机器上启动第二个thread
alter database enable thread 2;
检查归档路径是否在ASM中
SQL archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ARCH
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1关闭节点1数据库
export ORACLE_SIDdxj1
sqlplus / as sysdba
shutdown immediate
启动所有节点实例
在RAC的所有机器即orcl01、orcl02上都启动数据库并在每台机器上检查是否能够查询到所有实例信息
--orcl01启动数据库 发现每台机器上都能够查询到所有实例信息 最后再关闭
export ORACLE_SIDdxj1
sqlplus / as sysdba
startupSQL set linesize 999
SQL select status,instance_number,instance_name,host_name from gv$instance; STATUS INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
------------ --------------- ---------------- ----------------------------------------------------------------
OPEN 1 dxj1 orcl01
OPEN 2 dxj2 orcl02SQL shutdown immediate--orcl02启动数据库 发现每台机器上都能够查询到所有实例信息 最后再关闭
export ORACLE_SIDdxj2
sqlplus / as sysdba
startupSQL set linesize 999
SQL select status,instance_number,instance_name,host_name from gv$instance; STATUS INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
------------ --------------- ---------------- ----------------------------------------------------------------
OPEN 1 dxj1 orcl01
OPEN 2 dxj2 orcl02SQL shutdown immediate把数据库加入集群
在RAC的随机一台机器如orcl01机器上把数据库加入集群(grid或oracle用户都可以)
srvctl add database -d dxj -o $ORACLE_HOME -p DATA/dxj/spfiledxj.orasrvctl add instance -d dxj -i dxj1 -n orcl01srvctl add instance -d dxj -i dxj2 -n orcl02srvctl start database -d dxjsrvctl status database -d dxj
如下输出
[oracleorcl01:/home/oracle]$ srvctl start database -d dxj
[oracleorcl01:/home/oracle]$ srvctl status database -d dxj
Instance dxj1 is running on node orcl01
Instance dxj2 is running on node orcl02
查看集群状态
[gridorcl01:/home/grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dgONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.DATA.dgONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.LISTENER.lsnrONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.OCR.dgONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.asmONLINE ONLINE orcl01 StartedONLINE ONLINE orcl02 Started
ora.gsdOFFLINE OFFLINE orcl01OFFLINE OFFLINE orcl02
ora.net1.networkONLINE ONLINE orcl01ONLINE ONLINE orcl02
ora.onsONLINE ONLINE orcl01ONLINE ONLINE orcl02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr1 ONLINE ONLINE orcl01
ora.cvu1 ONLINE ONLINE orcl01
ora.dxj.db1 ONLINE ONLINE orcl01 Open2 ONLINE ONLINE orcl02 Open
ora.oc4j1 ONLINE ONLINE orcl02
ora.orcl01.vip1 ONLINE ONLINE orcl01
ora.orcl02.vip1 ONLINE ONLINE orcl02
ora.scan1.vip1 ONLINE ONLINE orcl01
重新配置初始化pfile
查看参数文件位置
SQL show parameter spfile;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string DATA/dxj/spfiledxj.ora
2个节点重新配置初始化pfile
--节点1
echo spfileDATA/dxj/spfiledxj.ora /u01/app/oracle/product/11.2.0/db/dbs/initdxj1.ora--节点2
echo spfileDATA/dxj/spfiledxj.ora /u01/app/oracle/product/11.2.0/db/dbs/initdxj2.ora
问题处理
本实验文档未出现以下报错如果备份恢复中遇到以下报错可参考以下解决办法。
ORA-27102: out of memory
--问题描述
SQL startup nomount pfile/home/oracle/initdxj20240603.ora;
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: -939524096
Additional information: 1--问题原因
由于目标服务器32G和源服务器内存128G差异大需要更改内存参数。
参数文件中sga pga参数太大--解决办法
--sga和pga配置的最佳实践
对于OLTP系统来说oracle建议是sga系统内存*0.8*0.8 单位字节
pga系统内存*0.8*0.2
对于DSS系统来说oracle建议是sga系统内存*0.8*0.5 单位字节
pga系统内存*0.8*0.5更改参数文件中的关于内存的参数
recover database提示unable to find archived log
--问题描述
还原归档文件时间报错
RMAN recover database;Starting recover at 25-JAN-24
using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread1 sequence16
channel ORA_DISK_1: reading from backup piece /topsoft/newbak/autobackup/arch_dxj_20240125_1159207499_60_1.bak
channel ORA_DISK_1: piece handle/topsoft/newbak/autobackup/arch_dxj_20240125_1159207499_60_1.bak tagARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name/topsoft/oracle/oradata/archivelog/1_16_1155666473.dbf thread1 sequence16
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread1 sequence17
channel ORA_DISK_1: reading from backup piece /topsoft/newbak/autobackup/arch_dxj_20240125_1159207499_61_1.bak
channel ORA_DISK_1: piece handle/topsoft/newbak/autobackup/arch_dxj_20240125_1159207499_61_1.bak tagARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name/topsoft/oracle/oradata/archivelog/1_17_1155666473.dbf thread1 sequence17
unable to find archived log
archived log thread1 sequence18
RMAN-00571:
RMAN-00569: ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
RMAN-03002: failure of recover command at 01/25/2024 19:54:41
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 2895113--解决办法
--进行不完全恢复 归档序列号1
RMAN recover database until sequence 18;Starting recover at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID2271 device typeDISKstarting media recovery
media recovery complete, elapsed time: 00:00:00Finished recover at 25-JAN-24--恢复完成后将库启动到read only模式查询一下数据是否正常
SQL alter database open read only;Database altered.--对比数据量无问题后
SQL shutdown immediate;--resetlogs方式打开
SQL alter database open resetlogs;
Database altered.--查看数据库状态
SQL select status from v$instance;STATUS
------------
OPEN
最后小版本升级完成且数据验证无问题。
概念补充
完全恢复数据文件归档在线redo就能做实例恢复 这一级别就是完全恢复。ctl scn 、 datafile scn 、datafile head scn一致
不完全恢复数据文件归档 只能到 归档的最后一条redo不能实例恢复 就是不完全恢复 restore 解决 datafile
recover 解决实例恢复
catalog就是把三者注册到 ctl ,自动完成
如果你只注册前两个部分那么第三个部分就要手工指定
recover database until cancel时 把当前redo的路径写上就行了
最后会显示下一个redo sequence号这个号其实是不存在的因为还没产生oracle是机器它只会让你找最全的redo向前恢复 --实例恢复
老备份归档在线redo内存中的oracle
关机了 归档在线全都不需要
在线redo的存在解决数据文件和内存不一致
归档解决老备份和现有数据文件不一致 --文件恢复
recover
restore 小版本升级参考链接
oracle 单机 恢复 rac,单机11.2.0.1恢复到RAC 11.2.0.4的案例-CSDN博客
