搜索到
22
篇与
的结果
-
Oracle 19c 单实例DG搭建最终版本 Oracle 19c 单实例DG搭建1.数据库配置1.1.关闭数据库sqlplus / as sysdba shutdown immediate1.2.启动到mount模式startup mount;1.3.修改archivelog#oracle用户下创建新的闪回路径（主库和备库同时创建） mkdir -p /u01/app/oracle/arch_risdb #sqlplus下修改archive log 日志路径 alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb'; alter database archivelog;1.4.开启闪回模式#创建闪回目录 mkdir -p /u01/app/oracle/flashback_area#修改闪回路径 show parameter db_recovery_file_dest; alter system set db_recovery_file_dest='/u01/app/oracle/flashback_area' scope=spfile; alter system set db_recovery_file_dest_size=16G; alter database flashback on;1.5.为备库创建日志文件set wrap off; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- --------------- 1 ONLINE /u01/app/oracle/oradata/RISDB/redo01.log 2 ONLINE /u01/app/oracle/oradata/RISDB/redo02.log 3 ONLINE /u01/app/oracle/oradata/RISDB/redo03.log # 当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组 alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SPECTRA/sredo04.log') size 200m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SPECTRA/sredo05.log') size 200m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SPECTRA/sredo06.log') size 200m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SPECTRA/sredo07.log') size 200m; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- ---------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/RISDB/redo01.log 2 ONLINE /u01/app/oracle/oradata/RISDB/redo02.log 3 ONLINE /u01/app/oracle/oradata/RISDB/redo03.log 4 STANDBY /u01/app/oracle/oradata/RISDB/redo04.log 5 STANDBY /u01/app/oracle/oradata/RISDB/redo05.log 6 STANDBY /u01/app/oracle/oradata/RISDB/redo06.log 7 STANDBY /u01/app/oracle/oradata/RISDB/redo07.log1.6.主库修改系统参数#设置db_unique_name alter system set db_unique_name='master' scope=spfile; #设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换 alter system set log_archive_config='DG_CONFIG=(master,slave)' scope=spfile; #设置日志归档路径1 alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master' scope=spfile; #设置日志归档路径2 alter system set log_archive_dest_2='SERVICE=slave LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=slave' scope=spfile; #启用归档路径1 alter system set log_archive_dest_state_1='enable' scope=spfile; #启用归档路径2 alter system set log_archive_dest_state_2='enable' scope=spfile; #设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换） alter system set fal_server='slave' scope=spfile; #设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换） alter system set fal_client='master' scope=spfile; #设置归档日志强制切换时间（s）1800表示30分钟 alter system set archive_lag_target=1800 scope=spfile; #设置归档日志文件名命名方式 alter system set log_archive_format='%t_%s_%r.arc'scope=spfile; #设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。 alter system set standby_file_management=auto scope=spfile; #设置数据文件名称的映射关系（注意顺序不能变） alter system set db_file_name_convert='slave','master' scope=spfile; #设置日志文件名称的映射关系（注意顺序不能变） alter system set log_file_name_convert='slave','master' scope=spfile;1.7.主库重启alter database open; shutdown immediate; startup;1.8.主库创建pfile文件create pfile from spfile; #Pfile文件位置：/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora1.9.主库配置网络监听#修改/u01/app/oracle/product/19c/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pacspri)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = pacspri) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = risdb) ) ) ## 修改 /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = risdb) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = risdb) ) ) RISDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = risdb) ) )1.10.主库测试监听#重启LISTENER服务 lsnrctl stop lsnrctl start tnsping primary tnsping standby tnsping risdb #在主库上tnsping测试都能ping通1.11.主库复制配置及参数文件到备库#关闭主数据库（备库） SQL> shutdown immediate #将主库pfile,orapwrisdb,listener.ora和tnsnames.ora文件拷贝到备库相应目录下。1.12.修改监听文件# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pacsstd)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = risdb) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = risdb) ) )1.13.备库启动监听并测试tnsping#重启LISTENER服务 lsnrctl stop lsnrctl start tnsping primary tnsping standby tnsping risdb #在备库上tnsping测试都能ping通1.14.修改参数文件risdb.__data_transfer_cache_size=0 risdb.__db_cache_size=241055039488 risdb.__inmemory_ext_roarea=0 risdb.__inmemory_ext_rwarea=0 risdb.__java_pool_size=0 risdb.__large_pool_size=2684354560 risdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment risdb.__pga_aggregate_target=17179869184 risdb.__sga_target=274877906944 risdb.__shared_io_pool_size=536870912 risdb.__shared_pool_size=30064771072 risdb.__streams_pool_size=0 risdb.__unified_pga_pool_size=0 *.archive_lag_target=1800 *.audit_file_dest='/u01/app/oracle/admin/risdb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/RISDB/control01.ctl','/u01/app/oracle/oradata/RISDB/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='primary','standby' #'standby','primary'交换位置 *.db_name='risdb' *.db_recovery_file_dest='/u01/app/oracle/flashback_area' *.db_recovery_file_dest_size=17179869184 *.db_unique_name='standby' #primary修改为standby *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=risdbXDB)' *.fal_client='standby' #primary修改为standby *.fal_server='primary' #standby修改为primary *.local_listener='LISTENER_RISDB' *.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' #primary修改为standby *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary' #standby两处，修改为primary *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.log_file_name_convert='primary','standby' #standby primary修改位置 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=16g *.processes=3000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=256g *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' #改完且检查路径是否都存在1.15.修改完成后通过pfile启动并创建spfilesqlplus / as sysdba SQL> startup pfile="/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora"; ORACLE instance started. Total System Global Area 2.7488E+11 bytes Fixed Size 30150760 bytes Variable Size 3.2749E+10 bytes Database Buffers 2.4159E+11 bytes Redo Buffers 506716160 bytes Database mounted. Database opened. SQL> create spfile from pfile; File created. SQL>2.恢复数据库2.1.连接备库进行数据恢复rman target sys/oracle19c@primary auxiliary sys/oracle19c@standby duplicate target database for standby from active database nofilenamecheck; duplicate target database for standby from active database dorecover nofilenamecheck;2.2.备库启动数据同步#完成后关闭数据库，启动到mount模式 startup mount; alter database recover managed standby database disconnect from session; #如果要停止同步 SQL> alter database recover managed standby database cancel; #注：如果想让备库比主库延迟几分钟可以启动为如下方式 SQL> alter database recover managed standby database delay 5 disconnect from session;2.3.恢复后数据测试#验证数据是否同步（在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成） select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc; select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #主库上切换日志文件再观察有没有正确同步到备库 alter system switch logfile; 2.4.验证数据库状态SQL> select switchover_status from v$database; #主库状态为 TO STANDBY表示可切换为备库 #备库状态为 NOT ALLOWED2.5.主备切换##执行切换命令，并将主库转换为备库角色 ##主库执行切换： SQL> alter database commit to switchover to physical standby with session shutdown; ##主库角色转换为备库： SQL> shutdown immediate; SQL> startup nomount; SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session; ##备库执行切换： SQL> alter database commit to switchover to primary; SQL> shutdown immediate; SQL> startup ##切换完成后可以alter system switch logfile来验证一下切换后的主备库数据同步是否正常。 ##注意： ##如果当前主库上活动的SESSION正在查询数据，则select switchover_status from v$database 命令会显示SESSIONS ACTIVE，可以通过如下命令进行切换。 alter database commit to switchover to physical standby with session shutdown; ##在备库上查询select switchover_status from v$database会显示 NOT ALLOWED,只有当主库上执行了alter database commit to switchover to physical standby后，备库上才会显示TO PRIMARY 2.6.故障切换##当主库发生无法修复的故障时，需要紧急将备库提升为主库来接管服务。这种情况下，需要预先开启主库的flashback，当主库修复后还可以通过flashback闪回到一个时间点，并切换为备库，否则就只能重建备库。 SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database finish force; SQL> alter database commit to switchover to primary; SQL> alter database open; SQL> select database_role from v$database; DATABASE_ROLE -------------------------------- PRIMARY2.7.通过flashback重建备库##当原来的主库经过维修后还可以启动数据库，则可以通过闪回到指定的SCN来进行standby重建 ##A). 首先在当前主库，也就是failover过后的新的主库上查询到自己变成主库的那一刻的scn号： SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ----------------------------------------------------------------------- 1400381 ##B). 对到原来的主库（已修好的机器）上闪回数据，并进行切换 SQL> shutdown immediate; SQL> startup mount; SQL> flashback database to scn 1400381; 闪回完成。 SQL> alter database convert to physical standby; SQL> shutdown immediate; SQL> startup mount; ##C). 在当前主库重新启用log_archive_dest_state_2并切换归档日志（或者直接重启数据库） SQL> alter system set log_archive_dest_state_2=enable; SQL> alter system switch logfile; ##D). 回到原主库（当前备库）开启日志应用进程 SQL> alter database recover managed standby database using current logfile disconnect; ##此时数据库已经完成切换，重新回到DG组中，并以备库的方式加入。可以通过3.13.5中的方法验证数据同步状态。备注##Oracle Dtataguard有三种保护模式： Maximum Availability Maximum Performance Maximum Protection AFFIRM NOAFFIRM AFFIRM SYNC ASYNC SYNC ##Oracle Dataguard配置过程中指定的log_archive_dest_2参数的值为SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby 其中ASYNC参数表示异步传输日志，这种日志传输方式只能配置为最佳性能模式，如果要修改为最高可用或者最大保护的话需要将该参数修改为AFFIRM。 ##通过select protection_mode from v$database查询保护模式 SQL> select protection_mode from v$database; PROTECTION_MODE ---------------------------------------- MAXIMUM PERFORMANCE ##备注2： ##当主备同步有问题时候可以通过查询错误输出来初步诊断 SQL> select error from v$archive_dest where target='STANDBY'; ##备注： ##当备库由于错误的操作（错误的提升角色或者错误的打开到OPEN状态并且被写入了数据，导致了主备不一致，这时只能抛弃被错误写入的数据）可以将备库闪回到一个正常状态下再重新进行数据同步。比如今天下午2点发现主库有问题，无法启动。这时将备库提升为了主库并开启为OPEN状态，客户端写入了一小部分数据时才发现，原来备库的数据没有即时同步到最新状态。这种情况下只有等主库修复完成正常启动，然后再从主库重新同步才能保证数据丢失更少。 ##查询某一个时间点的scn号： timestamp_to_scn('23-10月-19 11.00.00.000000000 上午') from dual;
-
Oracle 19c 集群环境RU补丁安装 1 补丁背景此环境为新装的Oracle19c 版本，未安装补丁。提前将所需的软件包上传到各节点的opt目录下包功能p35642822_190000_Linux-x86-64.zipGrid Updatesp35643107_190000_Linux-x86-64.zipDatabase Updatesp35648110_190000_Linux-x86-64.zipJAVAVMp6880880_190000_Linux-x86-64.zipOPatch1.1 解压补丁包# root用户执行 cd /opt/ unzip p35642822_190000_Linux-x86-64.zip -d gi/ unzip p35643107_190000_Linux-x86-64.zip -d db/ unzip p35648110_190000_Linux-x86-64.zip -d javavm/ chown -R grid:oinstall /opt/*1.2 打补丁注意事项为保障业务的连续性，先取其中一个节点安装gi，db，javavm补丁。安装完成后，启动节点接管业务后再执行其他节点的安装。2 GI补丁注：GI 补丁在grid用户下执行检查。更新补丁在root用户下执行2.1 生产库GI版本-双节点[grid@rac01:/home/grid]$ crsctl query crs releaseversion Oracle High Availability Services release version on the local node is [19.0.0.0.0] [grid@rac01:/home/grid]$ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [19.0.0.0.0]2.2 生产库GI OPATH版本-双节点[grid@rac01:/home/grid]$ opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded.2.3 grid opatch更新-双节点# root用户执行 mv /u01/app/19.3.0.0/grid/OPatch /u01/app/19.3.0/grid/OPatch.bak unzip -q /opt/p6880880_190000_Linux-x86-64.zip -d /u01/app/19.3.0/grid/ chmod -R 755 /u01/app/19.3.0/grid/OPatch chown -R grid:oinstall /u01/app/19.3.0/grid/OPatch su - grid opatch version2.4 GI补丁检查-双节点2.4.1 安装前补丁版本检查[grid@rac01:/u01/app/19.3.0/grid]$ opatch lspatches 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399) 29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247) 29517242;Database Release Update : 19.3.0.0.190416 (29517242) 29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763) OPatch succeeded.2.4.2 补丁冲突检查$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/gi/35642822/33575402/ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/gi/35642822/35553096/ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/gi/35642822/35643107/ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/gi/35642822/35652062/ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/gi/35642822/35655527/2.4.3 检查节点空间是否足够打补丁$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/gi/35642822/33575402/ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/gi/35642822/35553096/ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/gi/35642822/35643107/ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/gi/35642822/35652062/ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/gi/35642822/35655527/2.4.4 GI 补丁自动应用注：root用户执行# root用户执行以下安装补丁命令 # DBWLM 预估15m /u01/app/19.3.0/grid/OPatch/opatchauto apply /opt/gi/35642822/33575402/ # TOMCAT 预估10m /u01/app/19.3.0/grid/OPatch/opatchauto apply /opt/gi/35642822/35553096/ # Database 预估50m /u01/app/19.3.0/grid/OPatch/opatchauto apply /opt/gi/35642822/35643107/ # ACFS 预估13m /u01/app/19.3.0/grid/OPatch/opatchauto apply /opt/gi/35642822/35652062/ # OCW 预估15m /u01/app/19.3.0/grid/OPatch/opatchauto apply /opt/gi/35642822/35655527/2.5 GI补丁安装完成验证[grid@rac02:/home/grid]$ opatch lspatches 35655527;OCW RELEASE UPDATE 19.21.0.0.0 (35655527) 35652062;ACFS RELEASE UPDATE 19.21.0.0.0 (35652062) 35643107;Database Release Update : 19.21.0.0.231017 (35643107) 35553096;TOMCAT RELEASE UPDATE 19.0.0.0.0 (35553096) 33575402;DBWLM RELEASE UPDATE 19.0.0.0.0 (33575402) OPatch succeeded.3 Database补丁3.1 oracle opatch更新-双节点# root用户执行 mv /u01/app/oracle/product/19.3.0/dbhome_1/OPatch/ /u01/app/oracle/product/19.3.0/dbhome_1/OPatch_bak/ unzip -q /opt/p6880880_190000_Linux-x86-64.zip -d /u01/app/oracle/product/19.3.0/dbhome_1/ chmod -R 755 /u01/app/oracle/product/19.3.0/dbhome_1/OPatch chown -R oracle:oinstall /u01/app/oracle/product/19.3.0/dbhome_1/OPatch su - oracle opatch version3.2 Database 补丁检查-双节点注：database补丁在oracle用户下执行安装3.2.1 安装前补丁版本检查[oracle@rac02:/home/oracle]$ opatch lspatches 35655527;OCW RELEASE UPDATE 19.21.0.0.0 (35655527) 29517242;Database Release Update : 19.3.0.0.190416 (29517242) OPatch succeeded.3.2.2 补丁冲突检查$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/db/35643107/3.2.3 检查节点空间是否足够打补丁$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /opt/db/35643107/3.2.4 Database 补丁# 预估1h /u01/app/oracle/product/19.3.0/dbhome_1/OPatch/opatchauto apply /opt/db/35643107/ -oh /u01/app/oracle/product/19.3.0/dbhome_1/3.2.5 Database 安装完成验证[oracle@rac02:/home/oracle]$ opatch lspatches 35643107;Database Release Update : 19.21.0.0.231017 (35643107) 35655527;OCW RELEASE UPDATE 19.21.0.0.0 (35655527) OPatch succeeded.4 JAVAVM 补丁4.1 补丁安装-双节点注：安装JAVAVM补丁在Oracle 用户下执行# oracle用户执行命令 su - oracle $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /opt/javavm/35648110/ # 关闭当前节点实例 sqlplus / as sysdba shutdown immediate; exit # 应用补丁 $ORACLE_HOME/OPatch/opatch apply /opt/javavm/35648110/ # 补丁安装完成后启动数据库 sqlplus / as sysdba startup; exit5 完结注意事项 本次补丁更新需要注意严格按照以上文档描述的用户执行相关命令，更新完一个节点，待节点启动成功后迁移scan到更新好的节点后再进行另一节点的更新。过程无业务中断。
-
OracleLinux_R7_U9安装Oracle19c集群 1 安装计划1.1 测试环境规划模块软件虚拟化VMware® Workstation 17 Pro 17.0.0 build-20800274操作系统OracleLinux-R7-U9-Server-x86_64-dvd.isoOracle GILINUX.X64_193000_grid_home.zipOracle DBLINUX.X64_193000_db_home.zip1.2 虚拟机规划硬件规划模块配置处理器2 Core内存4 GB磁盘40 GB网卡两个网卡镜像-1.3 网络规划节点名Public IPPrivate IPVirtual IPScan IPrac-01192.168.110.22110.10.10.221192.168.110.223192.168.110.220rac-02192.168.110.22210.10.10.222192.168.110.224192.168.110.2201.4 系统分区规划分区大小功能/boot1 G引导分区/boot/efi1 GEFI 分区swap4 G虚拟内存(对标内存大小)/剩下的空间根分区1.5 共享存储规划虚拟磁盘大小OCR11 GOCR21 GOCR31 GFRA10 GDATA40 G1.6 用户,组规划GroupNameGroupID说明oinstall54421Oracle清单和软件所有者dba54322数据库管理员oper54323DBA操作员组backupdba54324备份管理员dgdba54325DG管理员kmdba54326KM管理员asmdba54327ASM数据库管理员组asmoper54328ASM操作员组asmadmin54329Oracle自动存储管理组racdba54330RAC管理员1.7 软件目录规划目录名称路径说明ORACLE_BASE （oracle）/u01/app/oracleoracle基目录ORACLE_HOME （oracle）/u01/app/oracle/product/19.3.0/dbhome_1oracle用户HOME目录ORACLE_BASE （grid）/u01/app/gridgrid基目录ORACLE_HOME （grid）/u01/app/19.3.0/gridgrid用户HOME目录2 系统安装注：安装步骤省略3 共享存储配置3.1 创建虚拟磁盘(Windows宿主机)# VMware 安装目录运行CMD 执行下边的命令创建vmdk文件 vmware-vdiskmanager.exe -c -s 1g -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\ocr1.vmdk" vmware-vdiskmanager.exe -c -s 1g -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\ocr2.vmdk" vmware-vdiskmanager.exe -c -s 1g -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\ocr3.vmdk" vmware-vdiskmanager.exe -c -s 10GB -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\fra.vmdk" vmware-vdiskmanager.exe -c -s 40GB -a lsilogic -t 2 "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\data.vmdk"3.2 修改VMX文件(Windows宿主机)注：关闭两个虚拟机后，打开两个虚拟机目录的vmx文件，将下边的内容添加到文件中，将下边的路径改为实际创建的路径和名称。修改完成后重新打开虚拟机#shared disks configure diskLib.dataCacheMaxSize=0 diskLib.dataCacheMaxReadAheadSize=0 diskLib.dataCacheMinReadAheadSize=0 diskLib.dataCachePageSize=4096 diskLib.maxUnsyncedWrites = "0" disk.locking = "FALSE" scsi1.sharedBus = "virtual" scsi1.present = "TRUE" scsi1.virtualDev = "lsilogic" scsi1:0.mode = "independent-persistent" scsi1:0.deviceType = "disk" scsi1:0.present = "TRUE" scsi1:0.fileName = "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\ocr1.vmdk" scsi1:0.redo = "" scsi1:1.mode = "independent-persistent" scsi1:1.deviceType = "disk" scsi1:1.present = "TRUE" scsi1:1.fileName = "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\ocr2.vmdk" scsi1:1.redo = "" scsi1:2.mode = "independent-persistent" scsi1:2.deviceType = "disk" scsi1:2.present = "TRUE" scsi1:2.fileName = "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\ocr3.vmdk" scsi1:2.redo = "" scsi1:3.mode = "independent-persistent" scsi1:3.deviceType = "disk" scsi1:3.present = "TRUE" scsi1:3.fileName = "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\fra.vmdk" scsi1:3.redo = "" scsi1:4.mode = "independent-persistent" scsi1:4.deviceType = "disk" scsi1:4.present = "TRUE" scsi1:4.fileName = "C:\Users\syspn\Documents\Virtual Machines\ShareDisk\data.vmdk" scsi1:4.redo = ""3.3 磁盘绑定磁盘绑定有两种方式： multipath + Udev：多路径必须使用该方式，才能正确识别磁盘，multipath将scsi_id 一样的设备绑定为一个相同的dm设备。再通过Udev绑定asm磁盘。* 单路径也能使用该方式。 udev：直接将/dev/sd* 设备绑定为asm磁盘具体方式参见4 安装前系统准备工作4.1 设置主机名-双节点# 节点1 hostnamectl set-hostname rac01 # 节点2 hostnamectl set-hostname rac024.2 调整主机Hosts-双节点echo "#public ip 192.168.110.221 rac01 192.168.110.222 rac02 #priv ip 10.10.10.221 rac01-pri 10.10.10.222 rac02-pri #vip ip 192.168.110.223 rac01-vip 192.168.110.224 rac02-vip #scan ip 192.168.110.220 rac-scan" >> /etc/hosts4.3 网卡配置-双节点注：检查双网卡配置网卡1： 配置public IP 需要网关网卡2： 配置priv IP 不要网关网卡都关闭IPv64.4 测试连通性-双节点ping -c 1 rac01; ping -c 1 rac02; ping -c 1 rac01-pri; ping -c 1 rac02-pri;如果测试有不通的检查原因并解决4.5 调整Network-双节点echo "NOZEROCONF=yes" >>/etc/sysconfig/network && cat /etc/sysconfig/network4.6 调整/dev/shm -双节点# 修改shm大小 echo "tmpfs /dev/shm tmpfs rw,exec,size=4G 0 0">>/etc/fstab # 重新挂载 mount -o remount /dev/shm # 检测shm大小是否修改 df -h4.7 关闭防火墙-双节点systemctl stop firewalld systemctl disable firewalld4.8 关闭selinux-双节点sed -i 's/enforcing/disabled/g' /etc/selinux/config && grep "SELINUX=" /etc/selinux/config setenforce 04.9 关闭透明大页-双节点cp /etc/default/grub /etc/default/grub.bak sed -i 's/quiet/quiet\ transparent_hugepage=never/g' /etc/default/grub #执行命令 grub2-mkconfig -o /boot/grub2/grub.cfg #不重启生效 echo never > /sys/kernel/mm/transparent_hugepage/enabled4.10 修改本地源-双节点注：内网环境必改，外网可选mv /etc/yum.repos.d/* /tmp/ echo "[local_yum]" >> /etc/yum.repos.d/henry.repo echo "name = henry_repo" >> /etc/yum.repos.d/henry.repo echo "baseurl = file:///mnt/" >> /etc/yum.repos.d/henry.repo echo "enabled = 1" >> /etc/yum.repos.d/henry.repo echo "gpgcheck = 0" >> /etc/yum.repos.d/henry.repo mount /dev/cdrom /mnt/4.11 安装软件和工具包-双节点4.11.1 软件包安装注：tigervnc 可选是否安装，如果使用x11转发则不需要安装tigervnc。yum install -y bc* ntp* binutils* compat-libcap1* compat-libstdc++* dtrace-modules* dtrace-modules-headers* dtrace-modules-provider-headers* dtrace-utils* elfutils-libelf* elfutils-libelf-devel* fontconfig-devel* glibc* glibc-devel* ksh* libaio* libaio-devel* libdtrace-ctf-devel* libXrender* libXrender-devel* libX11* libXau* libXi* libXtst* libgcc* librdmacm-devel* libstdc++* libstdc++-devel* libxcb* make* net-tools* nfs-utils* python* python-configshell* python-rtslib* python-six* targetcli* smartmontools* sysstat* gcc* nscd* unixODBC* unzip readline xauth* nano net-tools wget curl tigervnc*4.11.2 其他包安装注：将 compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm 上传到root根目录rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm4.12 配置核心参数-双节点直接复制执行下边代码块memTotal=$(grep MemTotal /proc/meminfo | awk '{print $2}') totalMemory=$((memTotal / 2048)) shmall=$((memTotal / 4)) if [ $shmall -lt 2097152 ]; then shmall=2097152 fi shmmax=$((memTotal * 1024 - 1)) if [ "$shmmax" -lt 4294967295 ]; then shmmax=4294967295 fi cat <<EOF>>/etc/sysctl.conf fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = $shmall kernel.shmmax = $shmmax kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 16777216 net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 net.core.wmem_default = 16777216 fs.aio-max-nr = 6194304 vm.dirty_ratio=20 vm.dirty_background_ratio=3 vm.dirty_writeback_centisecs=100 vm.dirty_expire_centisecs=500 vm.swappiness=10 vm.min_free_kbytes=524288 net.core.netdev_max_backlog = 30000 net.core.netdev_budget = 600 #vm.nr_hugepages = net.ipv4.conf.all.rp_filter = 2 net.ipv4.conf.default.rp_filter = 2 net.ipv4.ipfrag_time = 60 net.ipv4.ipfrag_low_thresh=6291456 net.ipv4.ipfrag_high_thresh = 8388608 EOF sysctl -p4.13 关闭avahi服务-双节点systemctl stop avahi-deamon systemctl disable avahi-deamon systemctl stop avahi-chsconfd systemctl disable avahi-chsconfd4.14 关闭其他服务-双节点--禁用开机启动 systemctl disable accounts-daemon.service systemctl disable atd.service systemctl disable avahi-daemon.service systemctl disable avahi-daemon.socket systemctl disable bluetooth.service systemctl disable brltty.service systemctl disable chronyd.service systemctl disable colord.service systemctl disable cups.service systemctl disable debug-shell.service systemctl disable firewalld.service systemctl disable gdm.service systemctl disable ksmtuned.service systemctl disable ktune.service systemctl disable libstoragemgmt.service systemctl disable mcelog.service systemctl disable ModemManager.service systemctl disable ntpd.service systemctl disable postfix.service systemctl disable postfix.service systemctl disable rhsmcertd.service systemctl disable rngd.service systemctl disable rpcbind.service systemctl disable rtkit-daemon.service systemctl disable tuned.service systemctl disable upower.service systemctl disable wpa_supplicant.service --停止服务 systemctl stop accounts-daemon.service systemctl stop atd.service systemctl stop avahi-daemon.service systemctl stop avahi-daemon.socket systemctl stop bluetooth.service systemctl stop brltty.service systemctl stop chronyd.service systemctl stop colord.service systemctl stop cups.service systemctl stop debug-shell.service systemctl stop firewalld.service systemctl stop gdm.service systemctl stop ksmtuned.service systemctl stop ktune.service systemctl stop libstoragemgmt.service systemctl stop mcelog.service systemctl stop ModemManager.service systemctl stop ntpd.service systemctl stop postfix.service systemctl stop postfix.service systemctl stop rhsmcertd.service systemctl stop rngd.service systemctl stop rpcbind.service systemctl stop rtkit-daemon.service systemctl stop tuned.service systemctl stop upower.service systemctl stop wpa_supplicant.service 4.15 配置ssh服务-双节点# 配置LoginGraceTime参数为0, 将timeout wait设置为无限制 sed -i '/#LoginGraceTime 2m/ s/#LoginGraceTime 2m/LoginGraceTime 0/' /etc/ssh/sshd_config && grep LoginGraceTime /etc/ssh/sshd_config # 加快SSH登录 禁用DNS sed -i '/#UseDNS yes/ s/#UseDNS yes/UseDNS no/' /etc/ssh/sshd_config && grep UseDNS /etc/ssh/sshd_config4.16 修改Login配置-双节点cat >> /etc/pam.d/login <<EOF session required pam_limits.so EOF4.17 配置用户限制-双节点echo "#ORACLE SETTING grid soft nproc 16384 grid hard nproc 16384 grid soft nofile 16384 grid hard nofile 65536 grid soft stack 1638 grid hard stack 32768 grid hard memlock 8192000 grid soft memlock 8192000 oracle soft nproc 16384 oracle hard nproc 16384 oracle soft nofile 16384 oracle hard nofile 65536 oracle soft stack 10240 oracle hard stack 32768 oracle hard memlock 8192000 oracle soft memlock 8192000 " >> /etc/security/limits.conf ulimit -a4.18 时间同步配置-双节点注：如不配置时间同步，时间差明显了会出现集群脑裂现象，建议使用稳定的本地时间服务器同步可选时间服务ntpchony# 检查时间是否一致 date4.19 创建用户和组-双节点 groupadd -g 54321 oinstall groupadd -g 54322 dba groupadd -g 54323 oper groupadd -g 54324 backupdba groupadd -g 54325 dgdba groupadd -g 54326 kmdba groupadd -g 54327 asmdba groupadd -g 54328 asmoper groupadd -g 54329 asmadmin groupadd -g 54330 racdba useradd -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,racdba -u 10000 oracle useradd -g oinstall -G dba,asmdba,asmoper,asmadmin,racdba -u 10001 grid echo "oracle" | passwd --stdin oracle echo "grid" | passwd --stdin grid4.20 创建目录-双节点mkdir -p /u01/app/19.3.0/grid mkdir -p /u01/app/grid mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1 chown -R grid:oinstall /u01 chown -R oracle:oinstall /u01/app/oracle chmod -R 775 /u01/4.21 配置用户环境变量-双节点注：直接使用root用户执行以下代码块，如目录有改变，修改变动的目录即可4.21.1 Grid#节点1 cat >> /home/grid/.bash_profile << "EOF" umask 022 export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/19.3.0/grid export TNS_ADMIN=$ORACLE_HOME/network/admin export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_SID=+ASM1 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH alias sas='sqlplus / as sysasm' export PS1="[\`whoami\`@\`hostname\`:"'$PWD]\$ ' EOF #节点2 cat >> /home/grid/.bash_profile << "EOF" umask 022 export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/19.3.0/grid export TNS_ADMIN=$ORACLE_HOME/network/admin export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_SID=+ASM2 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH alias sas='sqlplus / as sysasm' export PS1="[\`whoami\`@\`hostname\`:"'$PWD]\$ ' EOF4.21.2 oracle用户# 节点1 cat >> /home/oracle/.bash_profile << "EOF" umask 022 export TMP=/tmp export TMPDIR=$TMP export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1 export ORACLE_HOSTNAME=oracle19c-rac1 export TNS_ADMIN=\$ORACLE_HOME/network/admin export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_SID=orcl1 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH alias sas='sqlplus / as sysdba' export PS1="[\`whoami\`@\`hostname\`:"'$PWD]\$ ' EOF #节点2 cat >> /home/oracle/.bash_profile << "EOF" umask 022 export TMP=/tmp export TMPDIR=$TMP export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1 export ORACLE_HOSTNAME=oracle19c-rac1 export TNS_ADMIN=\$ORACLE_HOME/network/admin export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_SID=orcl2 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH alias sas='sqlplus / as sysdba' export PS1="[\`whoami\`@\`hostname\`:"'$PWD]\$ ' EOF4.22 连接iscsi存储-双节点（可选）注：如未使用本地创建的磁盘，使用iscsi共享磁盘，需要先连接到共享磁盘yum install -y iscsi-initiator-utils systemctl start iscsi systemctl enable iscsi #发现target(discovery) iscsiadm -m discovery -t st -p 192.168.110.199:3260 #连接target(discovery)存储 iscsiadm -m node -T iqn.2005-10.org.freenas.ctl:oracle19c-rac -p 192.168.110.199 -l #查看登录的session 相当于iscsiadm -m session -P 0 iscsiadm -m session #扫描所有关联的target/session iscsiadm -m node -R iscsiadm -m session -R #自动登录 iscsiadm -m node -T iqn.2005-10.org.freenas.ctl:oracle19c-rac -p 192.168.110.199,3260 --op update -n node.startup -v automatic #扫描新添加的Iscsi存储设备 /usr/bin/scsi-rescan #查看已经建立的iscsi连接 iscsiadm -m session -P 3 #断开iscsi连接 iscsiadm --mode node --targetname <target_name> --portal <target_portal> --logout #断开所有iscsi连接 iscsiadm --mode node --logoutall=all #重启iscsi systemctl restart iscsid #取消掉自动登录 iscsiadm --mode node --targetname iqn.2005-10.org.freenas.ctl:oracle19c-rac --portal 192.168.110.199:3260 --op delete4.23 配置共享存储-双节点4.23.1 multipath + udevmultipath配置##安装multipath yum install -y device-mapper* mpathconf --enable --with_multipathd y ##查看共享盘的scsi_id /usr/lib/udev/scsi_id -g -u /dev/sdb /usr/lib/udev/scsi_id -g -u /dev/sdc /usr/lib/udev/scsi_id -g -u /dev/sdd /usr/lib/udev/scsi_id -g -u /dev/sde /usr/lib/udev/scsi_id -g -u /dev/sdf mv /etc/multipath.conf /etc/multipath.conf_bak # 将iscsi_id 替换到下边的代码块中 cat <<EOF>> /etc/multipath.conf defaults { user_friendly_names yes } blacklist { devnode "^sda" } multipaths { multipath { wwid "36589cfc00000044b6fe5709b1454743d" alias asm_ocr01 } multipath { wwid "36589cfc000000bc28e3caeba02722b8b" alias asm_ocr02 } multipath { wwid "36589cfc00000007e32793d447621f10a" alias asm_ocr03 } multipath { wwid "36589cfc0000000b3db671f80dbce5e2f" alias asm_fra } multipath { wwid "36589cfc000000e0e55b50afdddf54d4b" alias asm_data } } EOF #加载内核模块 modprobe dm_multipath #删除所有不使用的多路径设备 multipath -F #查看生成的多路径盘符 multipath -v2 #显示多路径配置状态 multipath -llUdev配置cd /dev/mapper for i in asm_*; do printf "%s %s\n" "$i" "$(udevadm info --query=all --name=/dev/mapper/"$i" | grep -i dm_uuid)" >>/dev/mapper/udev_info done while read -r line; do dm_uuid=$(echo "$line" | awk -F'=' '{print $2}') disk_name=$(echo "$line" | awk '{print $1}') echo "KERNEL==\"dm-*\",ENV{DM_UUID}==\"${dm_uuid}\",SYMLINK+=\"${disk_name}\",OWNER=\"grid\",GROUP=\"asmadmin\",MODE=\"0660\"" >>/etc/udev/rules.d/99-oracle-asmdevices.rules done < /dev/mapper/udev_info ##重载udev udevadm control --reload-rules udevadm trigger --type=devices ll /dev/asm*4.23.2 Udev(非multipath)注：/dev/sd[b-f] 对应下边的 b c d e f 如有更多的盘添加到代码中for i in b c d e f; do echo "KERNEL==\"sd*\", ENV{DEVTYPE}==\"disk\", SUBSYSTEM==\"block\", PROGRAM==\"/lib/udev/scsi_id -g -u -d \$devnode\", RESULT==\"`/lib/udev/scsi_id -g -u -d /dev/sd$i`\", SYMLINK+=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules done # 加载rules文件,重新加载udev rule /sbin/udevadm control --reload # 检查新的设备名称 /sbin/udevadm trigger --type=devices --action=change4.24 用户互信注：所有节点(rac01,rac02)的所有用户(oracle,grid,root)执行生成密钥对#生成密钥对 rm -rf ~/.ssh mkdir ~/.ssh ssh-keygen -t rsa ssh-keygen -t dsa注：节点1(rac01)的所有用户(oracle,grud,root)执行追加互信cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys ssh rac02 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ssh rac02 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys scp ~/.ssh/authorized_keys rac02:~/.ssh/authorized_keys注：所有节点(rac01,rac02)的所有用户(oracle,grid,root)执行互信验证，都不需要密码则互信添加完成ssh rac01 date; ssh rac02 date; ssh rac01-pri date; ssh rac02-pri date;4.25 准备工作完成，重启系统-双节点5 安装Oracle GI5.1 安装Grid-节点1注：将LINUX.X64_193000_grid_home.zip 上传到/opt/目录中。# root用户下修改软件包权限 chown grid:oinstall /opt/LINUX.X64_193000_grid_home.zip # 切换到grid用户解压GI包 su - grid unzip /opt/LINUX.X64_193000_grid_home.zip -d $ORACLE_HOME5.2 安装cvuqdisk软件-双节点# root 用户下执行 export CVUQDISK_GRP=oinstall rpm -ivh /u01/app/19.3.0/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm # 将cvuqdisk传到节点2 scp /u01/app/19.3.0/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm rac02: # 节点2执行安装 export CVUQDISK_GRP=oinstall rpm -ivh /root/cvuqdisk-1.0.10-1.rpm5.3 安装前检查-节点1# 在grid用户下执行 # 使用 CVU 验证硬件和操作系统设置 cd $ORACLE_HOME # 检测最后如有需要执行脚本，root登录所有节点（rac01,rac02）执行提示的脚本后按回车检测 ./runcluvfy.sh stage -pre crsinst -n rac01,rac02 -fixup -verbose ./runcluvfy.sh stage -pre crsinst -n rac01,rac02 -verbose ./runcluvfy.sh stage -post hwos -n rac01,rac02 -verbose5.4 执行安装-节点1su - grid cd $ORACLE_HOME ./gridSetup.sh安装过程选项Configuration Option > Configure Oracle Grid Infrastructure for a New Cluster Cluster Configuration > Configure an Oracle Standalone Cluster Grid Plug and Play > Cluster Name: rac SCAN Name: rac-scan Cluster Node Information > add 添加节点2 , 检查并修改列表中的Public Hostname 和 Virtual Hostname 与 Hosts 文件中设置的主机名一致 Network Interface Usage > 将private 选择为 ASM & private Storage Option > Use Oracle Flex ASM for storage Create Grid Infrastructure > No Create ASM Disk Group > Disk Group Name : OCR ,下边选择asm_ocr1,asm_ocr2,asm_ocr3 ASM Password > Use same passwords for these accounts: 设置密码 Failure Isolation > Do not use IPMI Managenent Option > 不勾选 Operation System Groups > 默认 Installtion Location > 默认 Create Inventory > 默认 Root Script execution > 如需要自动执行安装后脚本就勾选Automatically run ... 并填写root密码 Prerequisite Checks > 等待检查完成 Summary > 默认 Install Product > 等待安装完成，过程中如出现INS-20802 错误，点击OK Finish > close5.5 GI安装完成检测集群状态-双节点crsctl stat res -t [grid@rac01:/home/grid]$ crsctl status res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE rac01 STABLE ONLINE ONLINE rac02 STABLE ora.chad ONLINE ONLINE rac01 STABLE ONLINE ONLINE rac02 STABLE ora.net1.network ONLINE ONLINE rac01 STABLE ONLINE ONLINE rac02 STABLE ora.ons ONLINE ONLINE rac01 STABLE ONLINE ONLINE rac02 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE rac01 STABLE 2 ONLINE ONLINE rac02 STABLE 3 OFFLINE OFFLINE STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE rac01 STABLE ora.OCR.dg(ora.asmgroup) 1 ONLINE ONLINE rac01 STABLE 2 ONLINE ONLINE rac02 STABLE 3 OFFLINE OFFLINE STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE rac01 Started,STABLE 2 ONLINE ONLINE rac02 Started,STABLE 3 OFFLINE OFFLINE STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE rac01 STABLE 2 ONLINE ONLINE rac02 STABLE 3 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE rac01 STABLE ora.qosmserver 1 ONLINE ONLINE rac01 STABLE ora.rac01.vip 1 ONLINE ONLINE rac01 STABLE ora.rac02.vip 1 ONLINE ONLINE rac02 STABLE ora.scan1.vip 1 ONLINE ONLINE rac01 STABLE --------------------------------------------------------------------------------5.6 GI创建ASM磁盘组-任意节点# grid用户执行 asmca # 按照下边的过程创建其他磁盘组 右键点击 Disk Groups > Create... > 填写Fra - 选择 External - 下边勾选 /dev/asm_fra > ok 右键点击 Disk Groups > Create... > 填写Data - 选择 External - 下边勾选 /dev/asm_data > ok 等待创建完成后看状态，如果是MOUNTED(2 of 2) 就没问题，点击 exit 退出6 安装Oracle DB6.1 安装db -节点1注：将LINUX.X64_193000_db_home.zip 上传到/opt/目录中。# root用户下修改软件包权限 chown oracle:oinstall /opt/LINUX.X64_193000_db_home.zip # 切换到oracle用户解压GI包 su - oracle unzip /opt/LINUX.X64_193000_db_home.zip -d $ORACLE_HOME6.2 执行安装-节点1su - oracle cd $ORACLE_HOME ./runInstaller安装过程选项Configuration Option > set Up Software Only Database Installation Option > Oracle Real Application Clusters Database installation Nodes Selection > 选择所有节点 Database Edition > Enterprise Edition Installation Location > 默认 Operating System Groups > 默认 Root Script execution > 如需要自动执行安装后脚本就勾选Automatically run ... 并填写root密码 Prerequisite Checks > 等待检查完成，判断是否需要修复，勾选Igone All Summary > Install Install Product > 等待安装完成 Finish > close6.3 软件安装完成验证-任意节点执行命令，检查是否能登录，检查版本信息[oracle@rac01:/home/oracle]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 8 19:58:16 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL>7 创建数据库7.1 数据库规划项目描述内存规划PGA SGAprocesses1000字符集AL32UTF8归档模式打开redo5组undo2 G最大扩展4 Gtemp4 G闪回配置4 G7.2 DBCA创建数据库oracle用户下执行dbca命令Database Operation > Create a database Creation Mode > Advanced configuration Deployment Type > Database type : Oracle Real Application Cluster (RAC) database Configuration Type : Admin Managed select a template for your database.选择合适的安装 Nodes Selection > 选择所有节点 Database Identification > 填写实例信息，根据情况选择是否使用PDB Storage Option > 使用ASM存储，检查Database file location 是否正确 Fast Recovery Option > 是否启用闪回和归档，根据需求选择 Data Vault Option > 默认，不配置 Configuration Options > 根据环境配置，本测试配置Use Automatic Shared Memory Management : 1488 Sizing页 Processes修改为1000 Character sets 设备为 AL32UTF8 Sample schemas 勾选 Add sample schemas to the database Management Options > EM 配置，保持默认即可 User Credentials > Use the same administrative password for all accounts:设置密码 Creation Option > 保持默认 Prerequisite Checks > 等待检查完成,Igonre All Summary > 保持默认，Finish Progress Page > 等待安装完成 Finish > close 7.3 创建完成7.3.1 检查实例状态-任意节点grid 用户检查实例su - grid srvctl status database -d orcl # 查看实例启动情况Oracle 用户检查实例su - oracle # 检查监听情况 lsnrctl status # 登录实例 sqlplus / as sysdba # 检查实例启动状态 select open_mode from v$database;8 Rac集群日常管理命令8.1 集群资源状态su - grid crsctl status res -t8.2 集群服务状态su - oracle crsctl check cluster -all8.3 数据库状态su - oracle srvctl status database -d orcl8.4 GI 监听状态su - grid lsnrctl status srvctl status listener8.5 SCAN 状态su - grid srvctl status scan srvctl status scan_listener lsnrctl status LISTENER_SCAN18.6 nodeapps状态su - grid srvctl status nodeapps8.7 VIP 状态su - grid srvctl status vip -node rac01 srvctl status vip -node rac028.8 数据库配置su - grid srvctl config database -d orcl crsctl status res ora.orcl.db -p |grep -i auto8.9 OCRsu - grid ocrcheck8.10 VOTEDISKsu - grid crsctl query css votedisk8.11 GI版本su - grid crsctl query crs releaseversion crsctl query crs activeversion8.12 ASMsu - grid asmcmd > lsdg > lsof > lsdsk8.13 启动/关闭Racsu - grid # 关闭\启动单个实例 srvctl stop\start instance -d racdb -i rac01 # 关闭\启动所有实例 srvctl stop\start database -d orcl # 关闭\启动CRS crsctl stop\start crs # 关闭\启动集群服务 crsctl stop\start cluster -all crsctl start\stop crs # 是单节管理 crsctl start\stop cluster # [-all 所有节点] 可以管理多个节点 crsctl start\stop crs # 管理crs 包含进程 OHASD crsctl start\stop cluster # 不包含OHASD进程 要先启动 OHASD进程才可以使用 srvctl stop\start database # 启动\停止所有实例及其启用的服务8.14 切换Scansu - grid srvctl relocate scan_listener -i 1 -n rac028.15 切换VIPsu - grid srvctl config network > srvctl relocate vip -vip oracle19c-rac2-vip -node rac02
-
Oracle 19c 单实例DG搭建 Oracle 19c 单实例DG搭建1.数据库配置1.1.关闭数据库sqlplus / as sysdba shutdown immediate1.2.启动到mount模式startup mount;1.3.修改archivelog#oracle用户下创建新的闪回路径（主库和备库同时创建） mkdir -p /u01/app/oracle/arch_risdb #sqlplus下修改archive log 日志路径 alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb'; alter database archivelog;1.4.开启闪回模式#创建闪回目录 mkdir -p /u01/app/oracle/flashback_area#修改闪回路径 show parameter db_recovery_file_dest; alter system set db_recovery_file_dest='/u01/app/oracle/flashback_area' scope=spfile; alter system set db_recovery_file_dest_size=16G; alter database flashback on;1.5.为备库创建日志文件set wrap off; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- --------------- 1 ONLINE /u01/app/oracle/oradata/RISDB/redo01.log 2 ONLINE /u01/app/oracle/oradata/RISDB/redo02.log 3 ONLINE /u01/app/oracle/oradata/RISDB/redo03.log # 当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组 alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SPECTRA/sredo04.log') size 200m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SPECTRA/sredo05.log') size 200m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SPECTRA/sredo06.log') size 200m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SPECTRA/sredo07.log') size 200m; select * from v$logfile order by group#; #查询日志组 GROUP# STATUS TYPE MEMBER ---------- -------------- -------------------- ---------------------------------------------------- 1 ONLINE /u01/app/oracle/oradata/RISDB/redo01.log 2 ONLINE /u01/app/oracle/oradata/RISDB/redo02.log 3 ONLINE /u01/app/oracle/oradata/RISDB/redo03.log 4 STANDBY /u01/app/oracle/oradata/RISDB/redo04.log 5 STANDBY /u01/app/oracle/oradata/RISDB/redo05.log 6 STANDBY /u01/app/oracle/oradata/RISDB/redo06.log 7 STANDBY /u01/app/oracle/oradata/RISDB/redo07.log1.6.主库修改系统参数#设置db_unique_name alter system set db_unique_name='master' scope=spfile; #设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换 alter system set log_archive_config='DG_CONFIG=(master,slave)' scope=spfile; #设置日志归档路径1 alter system set log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master' scope=spfile; #设置日志归档路径2 alter system set log_archive_dest_2='SERVICE=slave LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=slave' scope=spfile; #启用归档路径1 alter system set log_archive_dest_state_1='enable' scope=spfile; #启用归档路径2 alter system set log_archive_dest_state_2='enable' scope=spfile; #设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换） alter system set fal_server='slave' scope=spfile; #设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换） alter system set fal_client='master' scope=spfile; #设置归档日志强制切换时间（s）1800表示30分钟 alter system set archive_lag_target=1800 scope=spfile; #设置归档日志文件名命名方式 alter system set log_archive_format='%t_%s_%r.arc'scope=spfile; #设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。 alter system set standby_file_management=auto scope=spfile; #设置数据文件名称的映射关系（注意顺序不能变） alter system set db_file_name_convert='slave','master' scope=spfile; #设置日志文件名称的映射关系（注意顺序不能变） alter system set log_file_name_convert='slave','master' scope=spfile;1.7.主库重启alter database open; shutdown immediate; startup;1.8.主库创建pfile文件create pfile from spfile; #Pfile文件位置：/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora1.9.主库配置网络监听#修改/u01/app/oracle/product/19c/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pacspri)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = pacspri) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = risdb) ) ) ## 修改 /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = risdb) ) ) standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = risdb) ) ) RISDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = risdb) ) )1.10.主库测试监听#重启LISTENER服务 lsnrctl stop lsnrctl start tnsping primary tnsping standby tnsping risdb #在主库上tnsping测试都能ping通1.11.主库复制配置及参数文件到备库#关闭主数据库（备库） SQL> shutdown immediate #将主库pfile,orapwrisdb,listener.ora和tnsnames.ora文件拷贝到备库相应目录下。1.12.修改监听文件# listener.ora Network Configuration File: /u01/app/oracle/product/19c/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pacsstd)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = risdb) (ORACLE_HOME = /u01/app/oracle/product/19c/db_1) (SID_NAME = risdb) ) )1.13.备库启动监听并测试tnsping#重启LISTENER服务 lsnrctl stop lsnrctl start tnsping primary tnsping standby tnsping risdb #在备库上tnsping测试都能ping通1.14.修改参数文件risdb.__data_transfer_cache_size=0 risdb.__db_cache_size=241055039488 risdb.__inmemory_ext_roarea=0 risdb.__inmemory_ext_rwarea=0 risdb.__java_pool_size=0 risdb.__large_pool_size=2684354560 risdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment risdb.__pga_aggregate_target=17179869184 risdb.__sga_target=274877906944 risdb.__shared_io_pool_size=536870912 risdb.__shared_pool_size=30064771072 risdb.__streams_pool_size=0 risdb.__unified_pga_pool_size=0 *.archive_lag_target=1800 *.audit_file_dest='/u01/app/oracle/admin/risdb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/RISDB/control01.ctl','/u01/app/oracle/oradata/RISDB/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='primary','standby' #'standby','primary'交换位置 *.db_name='risdb' *.db_recovery_file_dest='/u01/app/oracle/flashback_area' *.db_recovery_file_dest_size=17179869184 *.db_unique_name='standby' #primary修改为standby *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=risdbXDB)' *.fal_client='standby' #primary修改为standby *.fal_server='primary' #standby修改为primary *.local_listener='LISTENER_RISDB' *.log_archive_config='DG_CONFIG=(primary,standby)' *.log_archive_dest_1='location=/u01/app/oracle/arch_risdb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' #primary修改为standby *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary' #standby两处，修改为primary *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.log_file_name_convert='primary','standby' #standby primary修改位置 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=16g *.processes=3000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=256g *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' #改完且检查路径是否都存在1.15.修改完成后通过pfile启动并创建spfilesqlplus / as sysdba SQL> startup pfile="/u01/app/oracle/product/19c/db_1/dbs/initrisdb.ora"; ORACLE instance started. Total System Global Area 2.7488E+11 bytes Fixed Size 30150760 bytes Variable Size 3.2749E+10 bytes Database Buffers 2.4159E+11 bytes Redo Buffers 506716160 bytes Database mounted. Database opened. SQL> create spfile from pfile; File created. SQL>2.恢复数据库2.1.连接备库进行数据恢复rman target sys/oracle19c@primary auxiliary sys/oracle19c@standby duplicate target database for standby from active database nofilenamecheck; duplicate target database for standby from active database dorecover nofilenamecheck;2.2.备库启动数据同步#完成后关闭数据库，启动到mount模式 startup mount; alter database recover managed standby database disconnect from session; #如果要停止同步 SQL> alter database recover managed standby database cancel; #注：如果想让备库比主库延迟几分钟可以启动为如下方式 SQL> alter database recover managed standby database delay 5 disconnect from session;2.3.恢复后数据测试#验证数据是否同步（在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成） select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc; select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a where a.COMPLETION_TIME>sysdate -1/24 order by sequence#; #主库上切换日志文件再观察有没有正确同步到备库 alter system switch logfile;
-
zabbix通过ODBC驱动监控Oracle zabbix ODBC 驱动监控Oracle本文档参考位置Zabbix官网odbc文档 Oracle monitoring and integration with ZabbixOracle官网odbc文档 Oracle Instant Client ODBC 安装说明 | Oracle 中国目标数据库创建用于监视Oracle DB的用户以及赋权CREATE USER zabbix IDENTIFIED BY password; -- Grant access to the zabbix user. GRANT CONNECT, CREATE SESSION TO zabbix; GRANT SELECT ON v$instance TO zabbix; GRANT SELECT ON v$database TO zabbix; GRANT SELECT ON v$sysmetric TO zabbix; GRANT SELECT ON v$system_parameter TO zabbix; GRANT SELECT ON v$session TO zabbix; GRANT SELECT ON v$recovery_file_dest TO zabbix; GRANT SELECT ON v$active_session_history TO zabbix; GRANT SELECT ON v$osstat TO zabbix; GRANT SELECT ON v$restore_point TO zabbix; GRANT SELECT ON v$process TO zabbix; GRANT SELECT ON v$datafile TO zabbix; GRANT SELECT ON v$pgastat TO zabbix; GRANT SELECT ON v$sgastat TO zabbix; GRANT SELECT ON v$log TO zabbix; GRANT SELECT ON v$archive_dest TO zabbix; GRANT SELECT ON v$asm_diskgroup TO zabbix; GRANT SELECT ON sys.dba_data_files TO zabbix; GRANT SELECT ON DBA_TABLESPACES TO zabbix; GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix; GRANT SELECT ON DBA_USERS TO zabbix;lynx监控系统安装odbc包软件包对应选择数据库版本的yum -y install unixODBC unixODBC-devel rpm -ivh oracle-instantclient19.14-basic-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-devel-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-odbc-19.14.0.0.0-1.x86_64.rpm rpm -ivh oracle-instantclient19.14-sqlplus-19.14.0.0.0-1.x86_64.rpm修改配置文件## 修改Oracle tnsname配置文件 ## 编辑配置文件，在安装的版本路径下修改以下文件 ## vi /usr/lib/oracle/<version>/client64/network/admin/tnsnames.ora orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <IPAddress>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) oracle = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <IPAddress>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle) ) )修改系统环境变量增加以下内容 ## vi /etc/profile export ORACLE_HOME=/usr/lib/oracle/<version>/client64 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATH export TNS_ADMIN=$ORACLE_HOME/network/admin export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin ## 修改 libsqora.so.11.1文件权限（若安装的版本存在，本测试使用的 Oralce 11g 版本） chmod +x /usr/lib/oracle/<version>/client64/lib/libsqora.so.11.1 ln -s /usr/lib/oracle/<version>/client64/lib/libsqora.so.11.1 libodbcinst.so.1创建文件并写入内容## vi /etc/odbcinst.ini [Oracle] Description = [描述自填] Driver = /usr/lib/oracle/[version]/client64/lib/libsqora.so.11.1创建文件并写入内容##vi /etc/odbc.ini [orcl] Description = [描述自填] Driver = Oracle ##上边odbcinst.ini创建的驱动名称Oracle ServerName = [Address]:[Port]/[SID] UserID = zabbix Password = <password> [oracle] Description = [描述自填] Driver = Oracle ##上边odbcinst.ini创建的驱动名称Oracle ServerName = [Address]:[Port]/[SID] UserID = zabbix Password = <password>新建文件/etc/sysconfig/zabbix-serverORACLE_HOME=/usr/lib/oracle/[version]/client64 LD_LIBRARY_PATH=/usr/lib/oracle/[version]/client64/lib:/usr/lib64 TNS_ADMIN=/usr/lib/oracle/[version]/client64/network/admin PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/[version]/client64/lib测试## 检测驱动是否正常 ldd /usr/lib/oracle/[version]/client64/lib/libsqora.so.11.1 ## 未出现not found 字样为正常 ##测试配置是否成功 isql -v orcl ## 此处的ORCL为odbc.ini中配置的名称 ## 返回以下内容为成功 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
-
Oracle 备份脚本 完整备份run { #### 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup as compressed backupset database format 'D:\rmanbackup\db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }增量备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 as compressed backupset database format 'D:\rmanbackup\inc1_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }差异备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 differential as compressed backupset database format 'D:\rmanbackup\diff_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }
-
RMAN详解 RMAN详解一、备份与RMAN备份的概念数据库完全备份：按归档模式分为归档和非归档归档模式 打开状态，属于非一致性备份 关闭状态，可以分为一致性和非一致性非归档模式打开状态，非一致性备份无效 关闭状态，一致性备份，非一致性备份不被推荐RMAN备份RMAN使用会话来完成备份操作，从RMAN客户端连接到服务器将产生一个服务器会话RMAN备份内容包括：整个数据库,表空间,数据文件,指定的数据文件,控制文件,归档日志文件,参数文件等RMAN备份的类型完整备份(full)或增量备份(incremental)一致性备份(consistent)或不一致性备份(inconsistent)热备(open)或冷备(closed)，冷备时数据库必须处于mount状态，冷备可以为一致性备份或非一致性备份完整备份一个或多个数据文件的一个完整副本,包含从备份开始处所有的数据块.完整备份不能作为增量的基础增量备份包含从最近一次备份以来被修改或添加的数据块.可以分为差异增量备份和累计增量备份差异增量备份仅仅包含n级或n级以下被修改过的数据块。备份数据量小，时间长。累计增量备份仅仅包含n-1级或n-1级以下被修改过的数据块。备份数据量大，恢复时间短。0级增量备份相当于一个完整备份,该备份包含所有已用的数据块文件,与完整备份的差异是完整备份不能用作级增量备份的基础一致性备份备份所包含的各个文件中的所有修改都具备相同的系统变化编号(system change number，SCN)。也就是说，备份所包含的各个文件中的所有数据均来自同一时间点。一致性数据库完全备份(consis-tent whole backup)进行还原(restore)后，不需要执行恢复操作(recovery)非一致性备份在数据库处于打开(open)状态时，或数据库异常关闭(shut down abnormally)后，对一个或多个数据库文件进行的备份。非一致性备份需要在还原之后进行恢复操作备份集与镜像副本备份集是包含一个或多个数据文件,归档日志文件的二进制文件的集合.备份集由备份片组成,一个备份集中可以包含一个或多个备份片可以通过filesperset参数来设置备份集中可包含的备份片数，也可以设定参数maxpiecesize来制定每个备份片的大小。备份集中空闲的数据块将不会被备份，因此备份集可以支持压缩。备份集支持增量备份，可以备份到磁盘或磁带。镜像副本是数据文件或归档日志文件等的完整拷贝,未经过任何压缩等处理,不能备份到磁带,也不支持增量备份。恢复时可以立即使用实现快速恢复。等同于操作系统的复制命令。可以作为级增量备份。备份路径可以备份到磁盘目录可以备份到磁带闪回区备份限制数据库必须处于mount或open状态不能备份联机日志在非归档模式下仅仅能作干净备份，即在干净关闭且启动到mount状态下备份在归档模式下，current状态下数据文件可以备份二、使用RMAN进行备份1、备份数据库查看数据库当前工作模式,非ARCHIVELOG模式更改为ARCHIVELOG模式SQL> select dbid,name,log_mode from v$database;------查看当前数据库是不是archivelog模式 DBID NAME LOG_MODE ---------- --------------------------- ------------------------------------ 2816063020 ORADB NOARCHIVELOG SQL> shutdown immediate------关闭immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount------启动到nomount模式 ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes SQL> alter database mount;------挂载数据库 Database altered. SQL> archive log list------归档日志列表 Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19c/dbs/arch Oldest online log sequence 16 Current log sequence 18 SQL> alter database archivelog;------更改数据库为归档日志模式 Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/19c/dbs/arch Oldest online log sequence 16 Next log sequence to archive 18 Current log sequence 18 SQL>备份数据库创建RMAN路径[oracle@oradb ~]$ mkdir /u01/app/oracle/rmanbak/ [oracle@oradb ~]$开始备份[oracle@oradb ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 11 22:48:36 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORADB (DBID=2816063020, not open) RMAN> backup database format '/u01/app/oracle/rmanbak/whole_%d_%U';------备份整个数据库 Starting backup at 11-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=273 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/whole_ORADB_02vs2eb4_1_1 tag=TAG20210411T224908 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-00 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup as compressed backupset database format '/u01/app/oracle/rmanbak/whole_%d_%U';------备份整个数据库并压缩备份集 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup s·et channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/whole_ORADB_04vs2efb_1_1 tag=TAG20210411T225123 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-01 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>2、备份数据文件RMAN> backup as copy datafile 4 format '/u01/app/oracle/rmanbak/df_%d_%U';------备份类型为镜像备份 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf output file name=/u01/app/oracle/rmanbak/df_ORADB_data_D-ORADB_I-2816063020_TS-UNDOTBS1_FNO-4_0avs2fem tag=TAG20210411T230806 RECID=1 STAMP=1069628896 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-03 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> list copy; specification does not match any control file copy in the repository specification does not match any archived log in the repository List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 1 4 A 11-APR-21 2942179 11-APR-21 NO Name: /u01/app/oracle/rmanbak/df_ORADB_data_D-ORADB_I-2816063020_TS-UNDOTBS1_FNO-4_0avs2fem Tag: TAG20210411T230806 RMAN> backup datafile 4,5 format '/u01/app/oracle/rmanbak/df_%d_%U';------备份类型为备份集 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/df_ORADB_0cvs2fj3_1_1 tag=TAG20210411T231027 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-04 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>3、备份表空间RMAN> backup tablespace users format '/u01/app/oracle/rmanbak/tb_%d_%U'; Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/tb_ORADB_08vs2fbf_1_1 tag=TAG20210411T230623 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-02 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup tablespace temp;------零时表空间不需要备份 Starting backup at 11-APR-21 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 04/11/2021 23:11:22 RMAN-20202: Tablespace not found in the recovery catalog RMAN-06019: could not translate tablespace name "TEMP" RMAN>4、备份控制文件RMAN> configure controlfile autobackup on;------自动备份控制文件置为on状态,将自动备份控制文件和参数文件 new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> backup current controlfile;------单独备份控制文件及参数文件 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0evs2fqn_1_1 tag=TAG20210411T231431 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-05 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup datafile 4 include current controlfile;------备份数据文件时包含控制文件 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0gvs2frf_1_1 tag=TAG20210411T231455 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0hvs2fri_1_1 tag=TAG20210411T231455 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-06 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup spfile format '/u01/app/oracle/rmanbak/sp_%d_%U';------单独备份SPFile Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/rmanbak/sp_ORADB_0jvs2fv6_1_1 tag=TAG20210411T231654 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-07 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN> backup copies 2 device type disk spfile;------ Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 with 2 copies and tag TAG20210411T231716 piece handle=/u01/app/oracle/product/19c/dbs/0lvs2fvs_1_1 comment=NONE piece handle=/u01/app/oracle/product/19c/dbs/0lvs2fvs_1_2 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-08 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>注：在备份system01.dbf或system表空间时将会自动备份控制文件和参数文件，即使自动备份控制文件参数为off5、备份归档日志文件备份归档日志时仅仅备份归档过的数据文件(不备份联机重做日志文件)备份归档日志时总是对归档日志做完整备份RMAN对归档日志备份前会自动做一次日志切换，且从一组归档日志中备份未损坏的归档日志RMAN会自动判断哪些归档日志需要进行备份归档日志的备份集不能包含其它类型的文件RMAN> backup 2> format '/u01/app/oracle/rmanbak/lf_%d_%U' 3> archivelog all delete input;------delete input删除所有已经备份过的归档日志 Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup archivelog all delete input format '/u01/app/oracle/rmanbak/lf_%d_%U';------此种写法实现了上述相同的功能 Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup archivelog sequence between 50 and 120 thread 1 delete input;------ Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup archivelog from time "sysdate-15" until time "sysdate-7";------- Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 RMAN> backup format'/u01/app/oracle/rmanbak/lf_%d_%U' archivelog from sequence =80 delete input;------ Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 ------执行下面的命令,并观察备份列出的信息,可以看到使用plus archivelog时使用了上面描述的步骤来进行备份 RMAN> backup database plus archivelog format'/u01/app/oracle/rmanbak/lg_%d_%U'delete input; Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 Starting backup at 11-APR-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB/undotbs01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF input datafile file number=00005 name=/u01/app/oracle/product/19coradatahr.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORADB/users01.dbf channel ORA_DISK_1: starting piece 1 at 11-APR-21 channel ORA_DISK_1: finished piece 1 at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/0nvs2goa_1_1 tag=TAG20210411T233018 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 Finished backup at 11-APR-21 Starting backup at 11-APR-21 using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 11-APR-21 Starting Control File and SPFILE Autobackup at 11-APR-21 piece handle=/u01/app/oracle/product/19c/dbs/c-2816063020-20210411-09 comment=NONE Finished Control File and SPFILE Autobackup at 11-APR-21 RMAN>使用plus archivelog时备份数据库完成的动作(backup database plus archivelog)1.首先执行alter system archive log current命令(对当前日志归档)2.执行backup archivelog all命令(对所有归档日志进行备份)3.执行backup database命令中指定的数据文件、表空间等4.再次执行alter system archive log current5.备份在备份操作期间产生的新的归档日志三、总结数据文件的备份集对于未使用的块可以执行增量备份，可以跳过未使用过的数据块来进行压缩备份对于控制文件、归档日志文件、spfile文件则是简单的拷贝，并对其进行打包压缩而已四、RMAN异机恢复恢复前准备恢复首先配置跟源库相同的Oracle环境测试环境待恢复数据库的ORACLE_SID为orcl查询源库ORCL的DBID并记录select name,dbid from v$database;[oracle@localhost ~]$ sqlplus / as sysdba------以sysdba身份进入sqlplus SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 14 16:29:22 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name,dbid from v$database;------执行查询DBID语句 NAME DBID --------------------------- ---------- ORCL 1597556564------记录这个需要恢复的库的DBID SQL>开始恢复[oracle@localhost ~]$ export ORACLE_SID=orcl------设置ORACLE_SID为即将恢复的数据库的SID [oracle@localhost ~]$ rman target /------启动rman从当前ORACLE_SID Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 14 15:54:58 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, 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/19c/db_1/dbs/initorcl.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073737800 bytes Fixed Size 8904776 bytes Variable Size 276824064 bytes Database Buffers 780140544 bytes Redo Buffers 7868416 bytes RMAN> set dbid=1597556564;------设置DBID为源库的DBID executing command: SET DBID RMAN> restore spfile to pfile '/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora' from '/rmanbak/orcl/spfile_bak/spf_4_1';------恢复pfile文件 从备份的spfile文件 Starting restore at 14-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=11 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /rmanbak/orcl/spfile_bak/spf_4_1 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 14-APR-21 RMAN> shutdown immediate;------正常关闭immediate Oracle instance shut down RMAN> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora'; ------启动数据库到nomount状态 使用刚刚恢复出来的pfile文件 '此处启动命令可能会报错，最下边给出解决方案' Oracle instance started Total System Global Area 2466249672 bytes Fixed Size 8899528 bytes Variable Size 536870912 bytes Database Buffers 1912602624 bytes Redo Buffers 7876608 bytes RMAN> restore controlfile from '/rmanbak/orcl/controlfile_bak/cntrl_3_1'; ------执行恢复控制文件 从备份的控制文件 Starting restore at 14-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=261 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_j7bmpnh4_.ctl output file name=/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_j7bmpnjz_.ctl Finished restore at 14-APR-21 RMAN> alter database mount;------修改数据库为mount模式 released channel: ORA_DISK_1 Statement processed RMAN> crosscheck backup;------检查数据库有效性 Starting implicit crosscheck backup at 14-APR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=261 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 14-APR-21 Starting implicit crosscheck copy at 14-APR-21 using channel ORA_DISK_1 Finished implicit crosscheck copy at 14-APR-21 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 RECID=1 STAMP=1069844495 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 RECID=2 STAMP=1069844495 Crosschecked 2 objects RMAN> catalog start with '/rmanbak';------将备份的目录导入到控制文件中 searching for all files that match the pattern /rmanbak List of Files Unknown to the Database ===================================== File Name: /rmanbak/orcl.tar.gz File Name: /rmanbak/orcl/controlfile_bak/cntrl_3_1 File Name: /rmanbak/orcl/log/backupall_20210414103821.log File Name: /rmanbak/orcl/log/backupall_20210414104042.log File Name: /rmanbak/orcl/log/backupall_20210414110132.log File Name: /rmanbak/orcl/spfile_bak/spf_4_1 Do you really want to catalog the above files (enter YES or NO)? yes------输入yes确认 cataloging files... cataloging done List of Cataloged Files ======================= File Name: /rmanbak/orcl/controlfile_bak/cntrl_3_1 File Name: /rmanbak/orcl/spfile_bak/spf_4_1 List of Files Which Were Not Cataloged ======================================= File Name: /rmanbak/orcl.tar.gz RMAN-07517: Reason: The file header is corrupted File Name: /rmanbak/orcl/log/backupall_20210414103821.log RMAN-07517: Reason: The file header is corrupted File Name: /rmanbak/orcl/log/backupall_20210414104042.log RMAN-07517: Reason: The file header is corrupted File Name: /rmanbak/orcl/log/backupall_20210414110132.log RMAN-07517: Reason: The file header is corrupted RMAN> crosscheck backup;------再次检查数据库有效性 using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 RECID=1 STAMP=1069844495 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 RECID=2 STAMP=1069844495 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/controlfile_bak/cntrl_3_1 RECID=3 STAMP=1069862926 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rmanbak/orcl/spfile_bak/spf_4_1 RECID=4 STAMP=1069862926 Crosschecked 4 objects RMAN> restore database preview summary;------预览数据库恢复详细信息 Starting restore at 14-APR-21 using channel ORA_DISK_1 List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 2 B F A DISK 14-APR-21 1 1 YES TAG20210414T110135 1 B F A DISK 14-APR-21 1 1 YES TAG20210414T110135 archived logs generated after SCN 2243398 not found in repository recovery will be done up to SCN 2243398 Media recovery start SCN is 2243398 Recovery must be done beyond SCN 2243398 to clear datafile fuzziness Finished restore at 14-APR-21 RMAN> restore database;------执行数据库恢复 重新存放数据库 Starting restore at 14-APR-21 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 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j7bmnbjn_.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j7bmo4rx_.dbf channel ORA_DISK_1: reading from backup piece /rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 channel ORA_DISK_1: piece handle=/rmanbak/orcl/database_bak/db_ORCL_02vs920f_1_1 tag=TAG20210414T110135 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 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 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_j7bmlmbb_.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j7bmo3o8_.dbf channel ORA_DISK_1: reading from backup piece /rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 channel ORA_DISK_1: piece handle=/rmanbak/orcl/database_bak/db_ORCL_01vs920f_1_1 tag=TAG20210414T110135 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 14-APR-21 RMAN> recover database;------执行数据库恢复 恢复数据库、 '该条恢复命令也许会报错。就跳过执行下一条' Starting recover at 14-APR-21 using channel ORA_DISK_1 starting media recovery unable to find archived log archived log thread=1 sequence=7 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 04/14/2021 16:12:13 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 2243398 RMAN> recover database until scn 2243398;------执行数据库恢复 上条不成功执行该条恢复语句 Starting recover at 14-APR-21 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 14-APR-21 RMAN> exit------恢复完成 退出 Recovery Manager complete. [oracle@localhost ~]$ sqlplus / as sysdba------进入sqlplus 以sysdba身份 SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 14 16:14:56 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter database open resetlogs;------用open resetlogs 打开数据库 Database altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@localhost ~]$ sqlplus------执行sqlplus命令 SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 14 16:17:06 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter user-name: owner------使用源库中创建的用户登录 Enter password: Last Successful login time: Wed Apr 14 2021 10:35:23 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select table_name from user_tables;------查询当前用户下的所有表 TABLE_NAME -------------------------------------------------------------------------------- USERSINFO USERSID SQL> select * from usersinfo;------验证表中的数据 USER_ID SEX AGE ---------- --------- ---------- 10001 男 21 10002 女 18 SQL> select * from usersid;------验证表中的数据 USER_ID NAME ---------- ------------------------------ 10002 李四 10001 张三 SQL>恢复过程中出现过的问题执行startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora';命令的时候也许会报错RMAN> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 04/14/2021 16:02:17 RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory RMAN> startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/spfile_orcl.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 04/14/2021 16:03:20 RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory报错中告知找不到参数（db_create_file_dest、db_recovery_file_dest）所指定的文件或目录，检查恢复的pfile文件中该参数指定的位置应该是不存在的，创建上目录再执行startup nomount pfile="" 就没问题了
-
Oracle检查死锁 检查死锁检查死锁用sysdba用户执行以下语句select username,lockwait,status,machine,client_info,program from gv$session where sid in (select session_id from gv$locked_object);## 如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明： ## ## Username：死锁语句所用的数据库用户； ## ## Lockwait：死锁的状态，如果有内容表示被死锁。 ## ## Status： 状态，active表示被死锁 ## ## Machine： 死锁语句所在的机器。 ## ## Program： 产生死锁的语句主要来自哪个应用程序查看被死锁的语句select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));死锁的解决办法查死锁进程SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;kill死锁的进程alter system kill session ‘sid,serial#’; (其中sid=l.session_id) ## 举例 sql > alter system kill session '71,1';生成Kill Session语句SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);开启session记录IP地址create or replace trigger login_on_record_ip after logon on database begin dbms_application_info.set_client_info(sys_context('userenv', 'ip_address')); end; / select username,lockwait,status,machine,client_info,program from v$session where sid = 1155; select trigger_name from all_triggers where trigger_name like '%RECORD%';杀掉所有客户端名称包含doctor.exe的sessionSELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "killcmd" FROM gv$session WHERE program like '%doctor.exe%'; set pagesize 1000 linesize 1000; spool conninfo select count(*), machine, program, username, osuser from gv$session group by machine, program, username, osuser order by count(*) desc; spool off exit
-
Oracle 部分报错处理 Oracle报错处理处理Oracle数据库启动报错 ORA-27104报错内容：system-defined limits for shared memory was misconfigured查看报错日志（我的日志位置，根据自身环境确定）cat /u01/app/oracle/diag/rdbms/oradb/oradb/alert/log.xml错误内容如下：<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>System cannot support SGA size of 2352 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Total system memory configured is 1838 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Instance maximum shared memory size should be less than 1654 MB. </txt>我的环境中，根据报错可以看出配置的SGA相关配置的内存太大了，要求小于1654MB解决办法：数据库不能启动，需要调整编辑参数文件来修改相关的SGA值我的spfile文件位置是：/u01/app/oracle/product/19c/dbs[oracle@oracle dbs]$ pwd /u01/app/oracle/product/19c/dbs [oracle@oracle dbs]$ ls hc_oradb.dat init20210220.ora init.ora lkORADB orapworadb spfileoradb.ora [oracle@oracle dbs]$先生成一个临时spfile文件来修改配置SQL> create pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora' from spfile; File created. SQL> !vi /u01/app/oracle/product/19c/dbs/init20210220.ora打开后配置如下：oradb.__data_transfer_cache_size=0 oradb.__db_cache_size=1778384896 oradb.__inmemory_ext_roarea=0 oradb.__inmemory_ext_rwarea=0 oradb.__java_pool_size=0 oradb.__large_pool_size=16777216 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=822083584 oradb.__sga_target=2466250752 oradb.__shared_io_pool_size=134217728 oradb.__shared_pool_size=520093696 oradb.__streams_pool_size=0 oradb.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/oradb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/ORADB/control01.ctl','/u01/app/oracle/oradata/ORADB/control02.ctl' *.db_block_size=8192 *.db_name='oradb' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.local_listener='LISTENER_ORADB' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=782m *.processes=320 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2346m *.undo_tablespace='UNDOTBS1'将 oradb.__sga_target= 和 .sga_target= 这两个参数修改到推荐值范围，然后保存。保存之后使用心得spfile启动数据库SQL> startup pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora'; ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>现在数据库能启动成功了，还需要将修改后的pfile文件覆盖原spfile文件SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora'; File created. SQL> startup ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>完成修改archivelog报错 ORA-00265执行过强制关闭数据库的操作就会出现的错误解决步骤：将数据库起到open状态，正常关闭shutdown immediate，再起到mount状态SQL> alter database open ;------启动数据库到open状态 Database altered. SQL> shutdown immediate------正常关闭immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount;------启动到mount状态 ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL> alter database archivelog;------执行更改archivelog Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7
-
Oracle 部分命令介绍 Oracle学习记录表空间和数据文件表空间分类系统表空间system表空间system表空间用来存储整个数据库的数据字典表（data dictionary table），该表空间不能被损坏，一旦损坏，数据库将无法打开。SQL> select ts#,name from v$tablespace where name='SYSTEM'; TS# NAME ---------- -------------------------------------------------------------------------------- 0 SYSTEM SQL>sysaux表空间从oracle 10g开始，oracle将工具放到SYSAUX,减轻system的压力。SYSAUX表空间不影响系统，但是会影响性能。SQL> select ts#,name from v$tablespace where name='SYSAUX'; TS# NAME ---------- -------------------------------------------------------------------------------- 1 SYSAUX SQL>非系统表空间undoundo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。undo数据用来：1）事务的回滚；2）实例恢复(回滚)；3）一致性读时需要构造CR块；SQL> select ts#,name from v$tablespace where name='UNDOTBS1'; TS# NAME ---------- -------------------------------------------------------------------------------- 2 UNDOTBS1 SQL> show parameter undo_tablespace; NAME TYPE VALUE ------------------------------------ -------------------- ------------------------------ undo_tablespace string UNDOTBS1 SQL>查看回滚段信息：SQL> select * from v$rollname; USN NAME CON_ID ---------- -------------------- ---------- 0 SYSTEM 0 1 _SYSSMU1_1261223759$ 0 2 _SYSSMU2_27624015$ 0 3 _SYSSMU3_2421748942$ 0 4 _SYSSMU4_625702278$ 0 5 _SYSSMU5_2101348960$ 0 6 _SYSSMU6_813816332$ 0 7 _SYSSMU7_2329891355$ 0 8 _SYSSMU8_399776867$ 0 9 _SYSSMU9_1692468413$ 0 10 _SYSSMU10_930580995$ 0 11 rows selected. SQL>temptemp表空间，即临时表空间，用来存放用户排序，分组等操作时的数据信息。SQL> select ts#,name from v$tablespace where name='TEMP'; TS# NAME ---------- -------------------- 3 TEMP SQL>users从oracle 10g开始，oracle将用户数据信息单独存放到users表空间中。SQL> select ts#,name from v$tablespace where name='USERS'; TS# NAME ---------- -------------------- 4 USERS SQL>用户表空间SQL> select ts#,name from v$tablespace where name not in('SYSTEM','SYSAUX','TEMP','USERS','UNDOTBS1'); TS# NAME ---------- -------------------- 6 HR SQL>表空间操作创建表空间SQL> create tablespace ORA_TEST_DAT datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' size 1G autoextend on next 200M maxsize 2G; Tablespace created. SQL>删除表空间不能删除的表空间为系统表空间，正在用的undo表空间，默认临时/永久表空间SQL> drop tablespace ORA_TEST_DAT including contents and datafiles; Tablespace dropped. SQL>修改表空间权限SQL> alter tablespace ORA_TEST_DAT read only; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT read write; Tablespace altered. SQL>脱机表空间SQL> alter tablespace ORA_TEST_DAT offline; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT online; Tablespace altered. SQL>表空间改名sysaux system不能改名用户表空间users，undo表空间，temp表空间最好不要改名，因为系统中部分参数定义名还是以原来的表空间名，下次数据库启动会报错。SQL> alter tablespace ORA_TEST_DAT rename to ORA_TEST_DAT01; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT01 rename to ORA_TEST_DAT; Tablespace altered. SQL>表空间说明system 表空间必须online 必须是read writesysaux 表空间可以offline 不能read onlyundo 表空间不能offline 不能read only只读表空间的对象可以删除，但是不能被update和insert数据文件操作修改数据文件自动扩展SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' autoextend off; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' autoextend on; Database altered. SQL>修改数据文件大小SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' resize 100M; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' resize 1G; Database altered. SQL>添加数据文件SQL> alter tablespace ORA_TEST_DAT add datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' size 200M autoextend off; Tablespace altered. SQL>删除数据文件SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF' offline drop; Database altered. SQL>数据文件重命名(必须要在归档模式下)如果数据库只在mont状态，那么只要在操作系统级别移动数据文件，执行数据库文件改名，启动数据库即可SQL> alter tablespace ORA_TEST_DAT offline; Tablespace altered. SQL> ho cp '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF' SQL> alter database rename file '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' to '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF'; Database altered. SQL> alter tablespace ORA_TEST_DAT online; Tablespace altered. SQL>注意事项修改数据文件ALTER DATABASE 语句修改单独的 DataFileALTER TABLESPACE 语句修改所有的 DataFileARCHIVRLOG 模式下的更改 DataFile只有在 ARCHIVELOG 模式下才可使用 ALTER DATABASE 来更改DataFileALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;由于在 NOARCHIVELOG 模式下，数据文件脱机后会造成数据的遗失，所以只能使用 ALTER DATABASE 语句下带有 DATAFILE 和 OFFLINE DROP 子句的选项将该 DataFile 直接取消，例如该 DataFile 只包含临时段数据，并没有备份时：alter database datafile '/u02/oracle/rbdb1/users3.dbf' offline drop;alter tablespace ....offline,会对数据文件进行检查点，并冻结数据文件SCN表空间 online 时，Oracle 会取得当前 SCN，解冻 offline 文件 SCN，和当前 SCN同步tablespace offline 有几种选项可供选择: normal, temporary,immediate, for recovery,而在 datafile 中则没有这些选项。数据文件reuse参数如果 file 已经存在，并且在创建时指定了 file size，那么就重用原文件，并应用新的 size，如果没有指定 file size，则保留原有的大小如果 file 不存在，oracle 将忽略该参数如果 Oracle 使用了已经存在的 file，那么之前 file 里的数据将全部丢失offline dropoffline drop 并不会 drop datafile， 仅仅是将 datafile 标记为 offline， 我们 online之后还可以 recover 回来。alter database datafile '/usr/ORA_TEST_DAT01.dbf' offline drop;该命令不会删除数据文件，只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删除数据文件。 数据文件的相关信息还会存在数据字典和控制文件中归档模式下，alter database datafile 'file_name' offline 和 offline drop 没有什么区别。 因为offline 之后多需要进行recover 才可以online。alter database datafile '/usr/ORA_TEST_DAT01.dbf' online;recover datafile '/usr/ORA_TEST_DAT01.dbf';alter tablespace ORA_TEST_DAT drop datafile '/usr/ORA_TEST_DAT01.dbf';该语句会删除控制文件和磁盘上的文件，删除之后的原数据文件序列号可以重用。该语句只能是datafile online的时候才可以使用。如果说对应的数据文件已经是offline for drop，那么仅针对 dictionary managed tablespaces 可用。控制文件控制文件概述二进制文件，初始化大小由CREATE DATABASE指定,可以使用RMAN备份记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等在参数文件中描述其位置，个数等等。通常采用分散放开，多路复用的原则。在mount阶段被读取，open阶段一直被使用维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)一个控制文件只能属于一个数据库控制文件的任意修改将写入到初始化参数中指定的所有控制文件中，读取时则仅读取第一个控制文件控制文件只能连接一个数据库，控制文件的大小一般不要超过MB,最多为个，最少一个，互为镜像控制文件中包含的内容数据库的名字、ID、创建的时间戳表空间的名字联机日志文件、数据文件的位置、个数、名字联机日志的Sequence号码检查点的信息撤销段的开始或结束归档信息备份信息查看控制文件信息列出实例中所有控制文件的名字及状态信息SQL> select * from v$controlfile; STATUS --------------------- NAME -------------------------------------------------------------------------------- IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS CON_ID --------- ---------- -------------- ---------- /u01/app/oracle/oradata/ORADB/control01.ctl NO 16384 646 0 /u01/app/oracle/oradata/ORADB/control02.ctl NO 16384 646 0 STATUS --------------------- NAME -------------------------------------------------------------------------------- IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS CON_ID --------- ---------- -------------- ---------- SQL>列出所有参数的位置及状态信息SQL> select name,value from v$parameter where name='control_files'; NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- control_files /u01/app/oracle/oradata/ORADB/control01.ctl, /u01/app/oracle/oradata/ORADB/contr ol02.ctl SQL>列出控制文件中记录的部分信息SQL> select * from v$CONTROLFILE_RECORD_SECTION; TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE 316 1 1 0 0 0 0 CKPT PROGRESS 8180 11 0 0 0 0 0 REDO THREAD 256 8 1 0 0 0 0 REDO LOG 72 16 3 0 0 3 0 DATAFILE 520 100 8 0 0 18 0 FILENAME 524 2298 11 0 0 0 0 TABLESPACE 180 100 7 0 0 7 0 TEMPORARY FILENAME 56 100 1 0 0 1 0 RMAN CONFIGURATION 1108 50 0 0 0 0 0 LOG HISTORY 56 292 9 1 9 9 0 OFFLINE RANGE 200 163 2 1 2 2 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- ARCHIVED LOG 584 28 0 0 0 0 0 BACKUP SET 96 170 0 0 0 0 0 BACKUP PIECE 780 209 0 0 0 0 0 BACKUP DATAFILE 200 245 0 0 0 0 0 BACKUP REDOLOG 76 215 0 0 0 0 0 DATAFILE COPY 736 200 0 0 0 0 0 BACKUP CORRUPTION 44 371 0 0 0 0 0 COPY CORRUPTION 40 409 0 0 0 0 0 DELETED OBJECT 20 818 1 1 1 1 0 PROXY COPY 928 246 0 0 0 0 0 BACKUP SPFILE 124 131 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE INCARNATION 56 292 2 1 2 2 0 FLASHBACK LOG 84 2048 0 0 0 0 0 RECOVERY DESTINATION 180 1 0 0 0 0 0 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0 RMAN STATUS 116 141 0 0 0 0 0 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0 MTTR 100 8 1 0 0 0 0 DATAFILE HISTORY 568 57 0 0 0 0 0 STANDBY DATABASE MATRIX 400 128 128 0 0 0 0 GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- RESTORE POINT 256 2108 0 0 0 0 0 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0 ACM OPERATION 104 64 11 0 0 0 0 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0 PDB RECORD 780 10 0 0 0 0 0 AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0 MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0 PDBINC RECORD 144 113 0 0 0 0 0 TABLESPACE KEY HISTORY 108 151 0 0 0 0 0 42 rows selected. SQL>列出控制文件的名字、状态、位置等SQL> SHOW PARAMETER CONTROL_FILES NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/ORADB/ control01.ctl, /u01/app/oracle /oradata/ORADB/control02.ctl SQL>使用STRINGS命令来查看控制文件中的具体内容[oracle@oracle ~]$ strings /u01/app/oracle/oradata/ORADB/control01.ctl备份控制文件到平面文件(然后查看控制文件中的具体内容)SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt'; Database altered. SQL>转储控制文件内容(查看控制文件中的具体内容)