单实例11.2.0.4迁移到11.2.0.4RAC

保命法则：先备份再操作，磁盘空间紧张无法备份就让满足，给自己留退路。

场景说明：

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 cpu：1核磁盘：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 cpu：1核磁盘：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	192.168.40.205

迁移前源库准备

构建测试数据(正式环境跳过)

--通过数据库主机连接
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);
begin
  for i in 1..10000 loop
    insert 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) || 'G'
  from 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 d
 WHERE d.OWNER   in ('TEST')
 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
 GROUP BY D.OWNER
 ORDER 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,owner
 union ALL
 select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg 
 where lob.segment_name=seg.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_size>10 ;

--查询库中记录数大于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
[oracle@dxj ~]$ lsnrctl status
[oracle@dxj ~]$ lsnrctl stop
[oracle@dxj ~]$ 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 'sid，serial#';


补充：
--源库杀用户连接 不建议 该步骤慎重 该文档未进行该操作  该方法迫不得已不用
ps -ef|grep LOCAL=NO|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 username='TEST';
alter user TEST account lock;
或
select 'alter user '||username||' account lock;' from dba_users where username in ('TEST') and ACCOUNT_STATU='OPEN';
执行以上输出结果

查看数据库信息

查看数据库参数

--数据库的创建日期和归档方式    
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	   UTF8

select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8


--查看当前数据库时区
select dbtimezone from dual;
补充：如果不是东八区  alter database set time_zone='+8: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				629M

7 rows selected.

查看源数据库DBID、控制文件、数据文件、redo日志文件的存储位置和归档：

--查看源数据库的DBID：
test@DXJ 2024-06-03 11:28:47> select dbid from v$database;

      DBID
----------
2712416469

--查看控制文件：
test@DXJ 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


--查看数据文件：
sys@DXJ 2024-06-03 11:29:39> set linesize 999
sys@DXJ 2024-06-03 11:30:37> col name for a69
sys@DXJ 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.dbf

5 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#;	 


--查看归档模式
sys@DXJ 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

大文件切片多开并行，优化速度，前提是存储牛逼，不然加了也没啥作用

评估：备份时间，传输时间，恢复时间，遇到问题

[oracle@dxj:/data/rman_bak]$ cat /data/rman_bak/rman_bak.sh 
#!/bin/bash
source /home/oracle/.bash_profile
export NLS_DATE_FORMAT='YYYYMMDD hh24:mi:ss'
rq=`date +%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; 
EOF

chmod +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）。
%d：Oracle数据库名称。
%T：当前时间的年月日格式（YYYYMMDD）。
%s：备份集号。 


rman备份有时间点，和scn 记录一下，恢复的时候按时间点和scn恢复就行了

通过rman进行一次全备：
vi rman_bak_L0.sh
#************************************************************************ 
#*** rman_bak_L0.sh *** 
#************************************************************************ 
#!/bin/bash
source /home/oracle/.bash_profile
rq=`date +%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 catalog

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: TAG20240125T180503
        Piece Name: /topsoft/rmanbak/autobackup/c-2492862311-20240125-06
  SPFILE Included: Modification time: 25-JAN-24
  SPFILE db_unique_name: dxj
  
b.控制文件备份如下：
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: TAG20240125T180503
        Piece Name: /topsoft/rmanbak/autobackup/c-2492862311-20240125-06
  Control File Included: Ckp SCN: 2895135      Ckp time: 25-JAN-24

  
c.数据文件备份如下：
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 oracle@192.168.40.200:/home/oracle/

--拷贝参数文件
scp  /home/oracle/initdxj20240603.ora oracle@192.168.40.200:/home/oracle/

--拷贝密码文件
scp  /home/oracle/orapwdxj oracle@192.168.40.200:/home/oracle/

目标数据库服务器

在RAC的orcl01机器上进行单机数据库的恢复

如果目标服务器已经新创建了dxj实例，需使用以下命令进行删除，删除后再进行如下恢复操作。

这里需要注意下，RAC和单点的静默删除实例命令是有区别的。

注意事项：

(1)RMAN 异机恢复的db_name 必须和备份的一致，如果说想改成其他名称，可以等还原之后，在用nid 命令修改。

(2)控制文件需要指定到共享设备上

(3)检查audit_file_dest，background_dump_dest， core_dump_dest，log_archive_dest_1，user_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_size=2113929216
dxj.__java_pool_size=16777216
dxj.__large_pool_size=16777216
dxj.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dxj.__pga_aggregate_target=671088640
dxj.__sga_target=2650800128
dxj.__shared_io_pool_size=0
dxj.__shared_pool_size=486539264
dxj.__streams_pool_size=0

*.undo_tablespace='UNDOTBS1'

增加参数

*.cluster_database=false

要先设置为flase，才能进行RMAN 恢复操作。恢复完成在改成true。

*.undo_management='AUTO'
*.cluster_database_instances=2
*.cluster_database=false
dxj2.thread=2
dxj1.thread=1
dxj2.undo_tablespace='UNDOTBS2'
dxj1.undo_tablespace='UNDOTBS1'
dxj2.instance_number=2
dxj1.instance_number=1

最终更改后的参数文件

[oracle@orcl01:/home/oracle]$ cat initdxj20240603.ora
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/dxj/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='+DATA/dxj/control01.ctl','+DATA/dxj/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_files=5000
*.db_name='dxj'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4322230272
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dxjXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.log_archive_dest_1='location=+ARCH/dxj'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=659554304
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=2638217216
*.sga_target=2638217216
*.undo_retention=10800
*.undo_management='AUTO'
*.cluster_database_instances=2
*.cluster_database=false
dxj2.thread=2
dxj1.thread=1
dxj2.undo_tablespace='UNDOTBS2'
dxj1.undo_tablespace='UNDOTBS1'
dxj2.instance_number=2
dxj1.instance_number=1

目标服务器上创建相应的目录

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状态

[oracle@orcl01:/home/oracle]$ export ORACLE_SID=dxj1
[oracle@orcl01:/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 spfile='+DATA/DXJ/spfiledxj.ora' from pfile='/home/oracle/initdxj20240603.ora';

所有节点本地生成spfilesid.ora文件的映射文件initsid.ora

本地生成spfilesid.ora文件的映射文件initsid.ora 该步骤必须操作不然再次启库还是pfile方式启库的

--节点1
[oracle@orcl01:/home/oracle]$ cd $ORACLE_HOME/dbs
[oracle@orcl01:/u01/app/oracle/product/11.2.0/db/dbs]$ 
echo "SPFILE='+DATA/DXJ/spfiledxj.ora'" > initdxj1.ora

--节点2
[oracle@orcl02:/home/oracle]$ cd $ORACLE_HOME/dbs
[oracle@orcl02:/u01/app/oracle/product/11.2.0/db/dbs]$ cat initdxj2.ora
echo "SPFILE='+DATA/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-15204&ORA-15001&ORA-17502

--问题描述
通过pfile生成spfile失败，提示DATA磁盘不存在
SQL> create spfile='+DATA/DXJ/spfiledxj.ora' from pfile='/home/oracle/initdxj20240603.ora';
create spfile='+DATA/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

查看集群状态
[grid@orcl01:/home/grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.DATA.dg
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.LISTENER.lsnr
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.OCR.dg
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.asm
               ONLINE  ONLINE       orcl01                   Started
               ONLINE  ONLINE       orcl02                   Started
......               

查看oracle文件权限  权限也对
[grid@orcl01:/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用户组也对
[oracle@orcl01:/home/oracle]$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54327(asmdba),54328(asmoper),54329(asmadmin)

查看参数文件
[oracle@orcl01:/home/oracle]$ cat initdxj20240603.ora
......
*.compatible='11.2.0.0.0'
......

--解决办法
将参数文件/home/oracle/initdxj20240603.ora中*.compatible='11.2.0.0.0'改成*.compatible='11.2.0.4.0'
重启库解决
su - oracle
sqlplus / as sysdba
shutdown abort
startup nomount pfile='/home/oracle/initdxj20240603.ora';
create spfile='+DATA/DXJ/spfiledxj.ora' from pfile='/home/oracle/initdxj20240603.ora';
成功

创建密码文件

在所有节点执行，注意密码要和原库一样

--节点1
[oracle@orcl01:/home/oracle]$ orapwd file=?/dbs/orapwdxj1 password=oracle

--节点2
[oracle@orcl02:/home/oracle]$ orapwd file=?/dbs/orapwdxj2 password=oracle

恢复控制文件

源库和目标库的控制文件路径虽然不一样，但是在更改参数文件的参数文件位置后，恢复控制文件过程中会把控制文件恢复到已更改的参数文件指定的控制文件路径。

[oracle@orcl01:/home/oracle]$ export ORACLE_SID=dxj1
[oracle@orcl01:/home/oracle]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jun 3 14:59:01 2024

Copyright (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: SID=32 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/dxj/control01.ctl
output file name=+DATA/dxj/control02.ctl
Finished restore at 03-JUN-24

RMAN> 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';

还原数据文件

加载备份目录下的备份集

--加载备份目录下的备份集
[oracle@orcl01:/home/oracle]$ export ORACLE_SID=dxj1
[oracle@orcl01:/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: SID=32 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 03-JUN-24

Starting implicit crosscheck copy at 03-JUN-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-JUN-24

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/autobackup/

List of Files Unknown to the Database
=====================================
File 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

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File 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;
}

详细过程如下：

RMAN>

run {
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 NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''/oradata/dxj/redo03.log'' to ''+DATA''

sql statement: ALTER DATABASE RENAME FILE ''/oradata/dxj/redo02.log'' to ''+DATA''

sql statement: ALTER DATABASE RENAME FILE ''/oradata/dxj/redo01.log'' to ''+DATA''

Starting restore at 03-JUN-24
using channel ORA_DISK_1

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 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 tag=TAG20240603T113405
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 tag=TAG20240603T113405
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 tag=TAG20240603T113405
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 03-JUN-24

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1170687869 file name=+DATA/dxj/datafile/system.271.1170687835
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1170687869 file name=+DATA/dxj/datafile/sysaux.273.1170687819
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1170687869 file name=+DATA/dxj/datafile/undotbs1.259.1170687819
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1170687869 file name=+DATA/dxj/datafile/users.262.1170687819
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1170687869 file name=+DATA/dxj/datafile/test.264.1170687819

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file

recover 数据库

在执行restore的节点执行，因为备份文件在该节点上。

RMAN> recover database;

详细过程如下：

RMAN> recover database;

Starting recover at 03-JUN-24
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
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 tag=TAG20240603T113442
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=+DATA/archivelog/1_6_1170010455.dbf thread=1 sequence=6
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
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 tag=TAG20240603T113442
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/archivelog/1_7_1170010455.dbf thread=1 sequence=7
unable to find archived log
archived log thread=1 sequence=8
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数据库到最后一个归档日志的sequence+1
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
YES

	 2			 ONLINE
+DATA
YES

	 1			 ONLINE
+DATA
YES

重置归档位置

如果在参数文件中已设置正确，该步骤可忽略

SQL> alter system set log_archive_dest_1='LOCATION=+ARCH' 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) || 'G'
  from dba_segments;
  
ROUND(SUM(BYTES)/1024/1024/1024,2)||'G'
-----------------------------------------
1.2G

--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
  FROM dba_objects d
 WHERE d.OWNER   in ('TEST')
 AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner) 
 GROUP BY D.OWNER
 ORDER 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_database=true scope=spfile;
alter system set cluster_database_instances=2 scope=spfile;
alter system set instance_number=1 scope=spfile sid='dxj1';
alter system set instance_number=2 scope=spfile sid='dxj2';
alter system set thread=1 scope=spfile sid='dxj1';
alter system set thread=2 scope=spfile sid='dxj2';

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_tablespace='undotbs1' scope=spfile sid='dxj1';
alter system set undo_tablespace='undotbs2' scope=spfile sid='dxj2';

创建在线日志组

在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_SID=dxj1
sqlplus / as sysdba
shutdown immediate

启动所有节点实例

在RAC的所有机器即orcl01、orcl02上都启动数据库，并在每台机器上检查是否能够查询到所有实例信息

--orcl01启动数据库  发现每台机器上都能够查询到所有实例信息  最后再关闭
export ORACLE_SID=dxj1
sqlplus / as sysdba
startup

SQL> 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	      orcl02

SQL> shutdown immediate


--orcl02启动数据库   发现每台机器上都能够查询到所有实例信息  最后再关闭
export ORACLE_SID=dxj2
sqlplus / as sysdba
startup

SQL> 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	      orcl02

SQL> shutdown immediate

把数据库加入集群

在RAC的随机一台机器如orcl01机器上把数据库加入集群(grid或oracle用户都可以)

srvctl add database -d dxj  -o $ORACLE_HOME -p +DATA/dxj/spfiledxj.ora

srvctl add instance -d dxj -i dxj1 -n orcl01

srvctl add instance -d dxj -i dxj2 -n orcl02

srvctl start database -d dxj

srvctl status database -d dxj

如下输出：

[oracle@orcl01:/home/oracle]$ srvctl start database -d dxj
[oracle@orcl01:/home/oracle]$ srvctl status database -d dxj
Instance dxj1 is running on node orcl01
Instance dxj2 is running on node orcl02

查看集群状态

[grid@orcl01:/home/grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.DATA.dg
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.LISTENER.lsnr
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.OCR.dg
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.asm
               ONLINE  ONLINE       orcl01                   Started
               ONLINE  ONLINE       orcl02                   Started
ora.gsd
               OFFLINE OFFLINE      orcl01
               OFFLINE OFFLINE      orcl02
ora.net1.network
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
ora.ons
               ONLINE  ONLINE       orcl01
               ONLINE  ONLINE       orcl02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       orcl01
ora.cvu
      1        ONLINE  ONLINE       orcl01
ora.dxj.db
      1        ONLINE  ONLINE       orcl01                   Open
      2        ONLINE  ONLINE       orcl02                   Open
ora.oc4j
      1        ONLINE  ONLINE       orcl02
ora.orcl01.vip
      1        ONLINE  ONLINE       orcl01
ora.orcl02.vip
      1        ONLINE  ONLINE       orcl02
ora.scan1.vip
      1        ONLINE  ONLINE       orcl01

重新配置初始化pfile

查看参数文件位置

SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 +DATA/dxj/spfiledxj.ora

2个节点，重新配置初始化pfile

--节点1
echo "spfile='+DATA/dxj/spfiledxj.ora'" > /u01/app/oracle/product/11.2.0/db/dbs/initdxj1.ora

--节点2
echo "spfile='+DATA/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_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
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 tag=ARCH
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 thread=1 sequence=16
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
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 tag=ARCH
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 thread=1 sequence=17
unable to find archived log
archived log thread=1 sequence=18
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: SID=2271 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished 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

最后小版本升级完成，且数据验证无问题。