Linux7静默安装Oracle19C_Datagurd主备模式
Linux7静默安装Oracle19C_Datagurd主备模式
此部分整理了Oracle的datagurd主备模式搭建的过程和其中相关知识总结。此部分包括了前期的数据库搭建、linux操作系统前期配置等。具体过程记录如下：
前期操作系统配置
使用VMware安装两台一模一样的服务器，本次安装使用的镜像是redhat7.7版本的(rhel-server-7.7-x86_64-dvd.iso)，在初次安装的时候每台机器只需要一个网卡就OK了，方便在装操作系统的时候直接就把网络配好。
-
主机名称
主机名：oracle1
IP地址：10.16.35.60[root@oracle1 ~]# hostnamectl status //查看主机1的主机信息 Static hostname: oracle1 Icon name: computer-vm Chassis: vm Machine ID: b6a0d7bdcc4142bfa2028254515290da Boot ID: 3f4b20832123483e8cb224ab6d9092a5 Virtualization: vmware Operating System: Red Hat Enterprise Linux Server 7.7 (Maipo) CPE OS Name: cpe:/o:redhat:enterprise_linux:7.7:GA:server Kernel: Linux 3.10.0-1062.el7.x86_64 Architecture: x86-64 如果要修改主机名 [root@localhost ~]# hostnamectl set-hostname oracle1 关闭selinux -
关闭selinux
[root@localhost ~]# getenforce Enforcing [root@localhost ~]# vi /etc/sysconfig/selinux # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of three values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted -
关闭防火墙
[root@localhost ~]# systemctl status firewalld.service 鈼[0m firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2020-04-09 17:26:08 CST; 16h ago Docs: man:firewalld(1) Main PID: 1087 (firewalld) Tasks: 2 CGroup: /system.slice/firewalld.service 鈹斺攢1087 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid Apr 09 17:26:07 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon... Apr 09 17:26:08 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon. [root@localhost ~]# systemctl stop firewalld.service [root@localhost ~]# systemctl disable firewalld.service Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@localhost ~]# systemctl is-enabled firewalld.service disabled -
配置本地yum
[root@rac1 ~]# cd /etc/yum.repos.d/ [root@rac1 yum.repos.d]# vi local.repo [rhel7] name=rhel7 baseurl=file:///media/cdrom enabled=1 gpgcheck=0 [root@rac1 yum.repos.d]# mkdir -p /media/cdrom [root@rac1 yum.repos.d]# mount /dev/cdrom /media/cdrom/ mount: /dev/sr0 is write-protected, mounting read-only [root@rac1 yum.repos.d]# yum update Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. rhel7 | 2.8 kB 00:00:00 (1/2): rhel7/group_gz | 103 kB 00:00:00 (2/2): rhel7/primary | 2.0 MB 00:00:00 rhel7 -
检查软件要求
[root@oracle1 yum.repos.d]# rpm -q binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXi libXtst make sysstat unixODBC unixODBC-devel //检查软件是否安装 binutils-2.27-41.base.el7.x86_64 package compat-libcap1 is not installed package compat-libstdc++-33 is not installed gcc-4.8.5-39.el7.x86_64 gcc-c++-4.8.5-39.el7.x86_64 glibc-2.17-292.el7.x86_64 glibc-devel-2.17-292.el7.x86_64 package ksh is not installed libaio-0.3.109-13.el7.x86_64 package libaio-devel is not installed libgcc-4.8.5-39.el7.x86_64 libstdc++-4.8.5-39.el7.x86_64 libstdc++-devel-4.8.5-39.el7.x86_64 libXi-1.7.9-1.el7.x86_64 libXtst-1.2.3-1.el7.x86_64 make-3.82-24.el7.x86_64 sysstat-10.1.5-18.el7.x86_64 package unixODBC is not installed package unixODBC-devel is not installed //安装软件 yum install bc binutils compat-libcap1 compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc++-devel libxcb make net-tools nfs-utils python python-configshell python-rtslib python-six targetcli smartmontools sysstat Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. Resolving Dependencies --> Running transaction check ---> Package compat-libcap1.x86_64 0:1.10-7.el7 will be installed ---> Package ksh.x86_64 0:20120801-139.el7 will be installed ---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed ---> Package unixODBC.x86_64 0:2.3.1-14.el7 will be installed ---> Package unixODBC-devel.x86_64 0:2.3.1-14.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ==================================================================================================================================================== Package Arch Version Repository Size ==================================================================================================================================================== Installing: compat-libcap1 x86_64 1.10-7.el7 rhel7 19 k ksh x86_64 20120801-139.el7 rhel7 885 k libaio-devel x86_64 0.3.109-13.el7 rhel7 13 k unixODBC x86_64 2.3.1-14.el7 rhel7 413 k unixODBC-devel x86_64 2.3.1-14.el7 rhel7 55 k Transaction Summary ==================================================================================================================================================== Install 5 Packages Total download size: 1.4 M Installed size: 4.6 Mcompat-libstdc+±33-3.2.3-72.el7.x86_64.rpm需要单独安装
[root@oracle1 ~]# rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm //单独安装 -
配置/etc/hosts
[root@oracle1 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 10.16.35.60 oracle1 10.16.35.61 oracle2 -
修改内核参数及用户的shell系统配置
内核参数配置如下：
[root@rac1 conf]# vi /etc/sysctl.conf vm.swappiness = 1 vm.dirty_background_ratio = 3 vm.dirty_ratio = 80 vm.dirty_expire_centisecs = 500 vm.dirty_writeback_centisecs = 100 kernel.shmmni = 4096 //--最小共享内存大小 bytes kernel.shmall = 1073741824 kernel.shmmax = 4398046511104 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 //文件系统最大异步io fs.file-max = 6815744 //文件系统中文件的最大个数 kernel.panic_on_oops = 1 net.ipv4.conf.ens192.rp_filter = 2 [root@oracle1 ~]# sysctl -p //立即生效用户shell系统配置
[root@oracle1 oracle]# cd /etc/security/limits.d/ [root@oracle1 limits.d]# vi 20-nproc.conf oracle soft nofile 2024 oracle hard nofile 65536 oracle soft nproc 16384 oracle hard nproc 16384 oracle soft stack 10240 oracle hard stack 32768 oracle hard memlock 134217728 oracle soft memlock 134217728 -
创建操作系统组和用户
//创建组和用户 [root@oracle1 ~]# groupadd -g 54321 oinstall [root@oracle1 ~]# groupadd -g 54322 dba [root@oracle1 ~]# groupadd -g 54323 oper [root@oracle1 ~]# groupadd -g 54324 backupdba [root@oracle1 ~]# groupadd -g 54325 dgdba [root@oracle1 ~]# groupadd -g 54326 kmdba [root@oracle1 ~]# groupadd -g 54327 asmdba [root@oracle1 ~]# groupadd -g 54328 asmoper [root@oracle1 ~]# groupadd -g 54329 asmadmin [root@oracle1 ~]# groupadd -g 54330 racdba [root@oracle1 ~]# useradd -u 54321 -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba,oper oracle [root@oracle1 ~]# echo oracle | passwd --stdin oracle //修改密码 Changing password for user oracle. passwd: all authentication tokens updated successfully.
数据库静默安装
-
创建目录和环境变量
[root@oracle1 ~]# mkdir -p /u01/app/oracle [root@oracle1 ~]# chown -R oracle:oinstall /u01 [root@oracle1 ~]# chmod -R 755 /u01/ //上传及修改宿主 [root@oracle1 ~]# mv LINUX.X64_193000_db_home.zip /home/oracle/ [root@oracle1 ~]# cd /home/oracle/ [root@oracle1 oracle]# ll total 2987996 -rw-r--r--. 1 root root 3059705302 Apr 17 2020 LINUX.X64_193000_db_home.zip [root@oracle1 oracle]# chown oracle:oinstall LINUX.X64_193000_db_home.zip [root@oracle1 oracle]# ll total 2987996 -rw-r--r--. 1 oracle oinstall 3059705302 Apr 17 2020 LINUX.X64_193000_db_home.zip [oracle@oracle1 oracle]$ pwd /u01/app/oracle [oracle@oracle1 oracle]$ mkdir -p product/19.3.0/db_1 [oracle@oracle1 ~]$ vi .bash_profile umask 022 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1 export ORACLE_SID=wlandb export ORACLE_TERM=xterm export ORACLE_OWNER=oracle export TNS_ADMIN=$ORACLE_HOME/network/admin export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK" export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/lib:/usr/lib:/usr/local/lib export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/lib:/usr/lib:/usr/local/lib export PATH=$PATH:/sbin:/usr/lbin:/usr/sbin:$JAVA_HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/lib:$HOME/bin:$ORACLE_HOME/OPatch:. -
解压及静默安装软件
[oracle@oracle1 db]$ unzip LINUX.X64_193000_db_home.zip [oracle@oracle1 db]$ pwd /u01/app/oracle/product/19.3.0/db静默安装的文件内容如下：
[oracle@oracle1 ~]$ pwd /home/oracle [oracle@oracle1 ~]$ cat 19c_db_install.rsp //静默文件 oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0 oracle.install.option=INSTALL_DB_SWONLY UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oraInventory ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1 oracle.install.db.InstallEdition=EE oracle.install.db.OSDBA_GROUP=dba oracle.install.db.OSOPER_GROUP=oper oracle.install.db.OSBACKUPDBA_GROUP=backupdba oracle.install.db.OSDGDBA_GROUP=dgdba oracle.install.db.OSKMDBA_GROUP=kmdba oracle.install.db.OSRACDBA_GROUP=racdba oracle.install.db.rootconfig.executeRootScript=true oracle.install.db.rootconfig.configMethod=ROOT静默安装软件过程如下：
[oracle@oracle1 ~]$ $ORACLE_HOME/runInstaller -silent -force -noconfig -ignorePrereq -responseFile /home/oracle/19c_db_install.rsp Launching Oracle Database Setup Wizard... Enter password for 'root' user: The response file for this session can be found at: /u01/app/oracle/product/19.3.0/db_1/install/response/db_2020-10-20_10-38-41AM.rsp You can find the log of this install session at: /tmp/InstallActions2020-10-20_10-38-41AM/installActions2020-10-20_10-38-41AM.log Successfully Setup Software. Moved the install session logs to: /u01/app/oraInventory/logs/InstallActions2020-10-20_10-38-41AM备注：该过程非常快，整个运行不到1分钟，中途会提示输入root用户密码用于自动执行root.sh脚本
-
创建监听
编写监听配置文件
[oracle@oracle1 ~]$ cat 19c_netca.rsp [GENERAL] RESPONSEFILE_VERSION="19.3" CREATE_TYPE="CUSTOM" [oracle.net.ca] INSTALLED_COMPONENTS={"server","net8","javavm"} INSTALL_TYPE=""typical"" LISTENER_NUMBER=1 LISTENER_NAMES={"LISTENER"} LISTENER_PROTOCOLS={"TCP;1521"} LISTENER_START=""LISTENER"" NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"} NSN_NUMBER=1 NSN_NAMES={"EXTPROC_CONNECTION_DATA"} NSN_SERVICE={"PLSExtProc"} NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}静默配置监听
[oracle@oracle1 ~]$ netca /silent /responsefile /home/oracle/19c_netca.rsp Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /home/oracle/19c_netca.rsp Done parsing command line arguments. Oracle Net Services Configuration: Profile configuration complete. Oracle Net Listener Startup: Running Listener Control: /u01/app/oracle/product/19.3.0/db_1/bin/lsnrctl start LISTENER Listener Control complete. Listener started successfully. Listener configuration complete. Oracle Net Services configuration successful. The exit code is 0 -
创建数据库
编写数据库创建相应文件
[oracle@adg19c ~]$ cat 19c_dbca.rsp responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0 gdbName=fandb sid=fandb databaseConfigType=SI RACOneNodeServiceName= policyManaged=false createServerPool=false serverPoolName= cardinality= force=false pqPoolName= pqCardinality= createAsContainerDatabase=true numberOfPDBs=1 pdbName=fanpdb useLocalUndoForPDBs=true pdbAdminPassword= nodelist= templateName=/u01/app/oracle/product/19.3.0/db_1/assistants/dbca/templates/General_Purpose.dbc sysPassword=sys systemPassword= system serviceUserPassword= emConfiguration=DBEXPRESS emExpressPort=5500 runCVUChecks=FALSE dbsnmpPassword= omsHost= omsPort=0 emUser= emPassword= dvConfiguration=false dvUserName= dvUserPassword= dvAccountManagerName= dvAccountManagerPassword= olsConfiguration=false datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/ datafileDestination=/oradata/{DB_UNIQUE_NAME}/ recoveryAreaDestination= storageType=FS diskGroupName= asmsnmpPassword= recoveryGroupName= characterSet=ZHS16GBK nationalCharacterSet=AL16UTF16 registerWithDirService=false dirServiceUserName= dirServicePassword= walletPassword= listeners=LISTENER variablesFile= variables=ORACLE_BASE_HOME=/u01/app/oracle/product/19.3.0/db_1,DB_UNIQUE_NAME=fandb,ORACLE_BASE=/u01/app/oracle,PDB_NAME=,DB_NAME=fandb,ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1,SID=fandb initParams=undo_tablespace=UNDOTBS1,sga_target=4587MB,db_block_size=8192BYTES,nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP) (SERVICE=fandbXDB),diagnostic_dest={ORACLE_BASE},control_files=("/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/oradata/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=3200,pga_aggregate_target=1146MB,nls_territory=AMERICA,local_listener=LISTENER_FANDB,open_cursors=300,compatible=19.0.0,db_name=fandb,audit_trail=db sampleSchema=false memoryPercentage=40 databaseType=MULTIPURPOSE automaticMemoryManagement=false totalMemory=0DBCA安装库
[oracle@oracle1 ~]$ dbca -silent -createDatabase -responseFile /home/oracle/19c_dbca.rsp Enter PDBADMIN User Password: [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. [WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. b.The password entered is a keyword that Oracle does not recommend to be used as password ACTION: Specify a strong password. If required refer Oracle documentation for guidelines. Prepare for db operation 8% complete Copying database files 31% complete Creating and starting Oracle instance 32% complete 36% complete 40% complete 43% complete 46% complete Completing Database Creation 51% complete 54% complete Creating Pluggable Databases 58% complete 77% complete Executing Post Configuration Actions 100% complete Database creation complete. For details check the logfiles at: /u01/app/oracle/cfgtoollogs/dbca/fandb. Database Information: Global Database Name:fandb System Identifier(SID):fandb Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/fandb/fandb.log" for further details.
Datagurd数据库主库前期配置
-
开启归档模式和强制日志模式
查看归档现状发现没有开启归档如下：
[oracle@oracle1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 20 19:34:54 2020 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> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19.3.0/db_1/dbs/arch Oldest online log sequence 5 Current log sequence 7新建归档目录和开启归档模式
[root@oracle1 /]# mkdir -p /arch [root@oracle1 /]# chown oracle:oinstall arch/ -R [root@oracle1 /]# ll total 24 drwxr-xr-x. 2 oracle oinstall 6 Oct 20 19:42 arch SQL> shutdown immediate; //开启归档需要先关闭数据库 Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; //数据库启动为mount模式后才能开启 ORACLE instance started. Total System Global Area 4815060848 bytes Fixed Size 9150320 bytes Variable Size 905969664 bytes Database Buffers 3892314112 bytes Redo Buffers 7626752 bytes Database mounted. SQL> alter database archivelog; //开启归档模式 Database altered. SQL> alter system set log_archive_dest_1='location=/arch'; //修改归档目录 System altered. SQL> alter database open; //开启数据库 Database altered. SQL> archive log list; //正常开启后如下模式 Database log mode Archive Mode Automatic archival Enabled Archive destination /arch Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7查询和开启强制日志模式：
SQL> select log_mode,force_logging from v$database; //显示没有开启 LOG_MODE FORCE_LOGGING ------------ --------------------------------------- ARCHIVELOG NO SQL> alter database force logging; //开启强制日志模式 Database altered. SQL> select name,log_mode,force_logging from v$database; //开启正常 NAME LOG_MODE FORCE_LOGGING --------- ------------ --------------------------------------- FANDB ARCHIVELOG YES从Oracle Database 18c开始，引入了以下两个新的nologging子句，他们可以执行非日志记录操作，同时可以使日志导致性能下降。STANDBY NOLOGGING FOR DATA AVAILABILITY模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。
SQL> alter database set standby nologging for data availability; //修改为此模式 Database altered. SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING ---------- ----- ----- ----- -- --------------------------------------- ----- ----- ----- ----- ----- ----- NOARCHIVELOG STANDBY NOLOGGING FOR DATA AVAILABILITYSTANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。
SQL> alter database set standby nologging for load performance; //修改为此模式 Database altered. -
创建standby redolog日志组
原则：
1：standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2：standby redo log日志文件组的个数依照下面的原则进行计算：
Standby redo log组数公式>=(每个instance日志组个数+1) * instance个数假如只有一个节点，这个节点有三组redolog 所以Standby redo log组数>=(3+1)*1 == 4 所以至少需要创建4组Standby redo log
查看当前线程与日志组的对应关系及日志组的大小如下：
SQL> select thread#,group#,bytes/1024/1024 from v$log; THREAD# GROUP# BYTES/1024/1024 ---------- ---------- --------------- 1 1 200 1 2 200 1 3 200如上，这里有三组redo log，所以至少需要创建4组Standby redo log，大小均为200M。
SQL> alter database add standby logfile thread 4 '/oradata/FANDB/stdredo04.log' size 200m reuse; Database altered. SQL> alter database add standby logfile thread 5 '/oradata/FANDB/stdredo05.log' size 200m reuse; Database altered. SQL> alter database add standby logfile thread 6 '/oradata/FANDB/stdredo06.log' size 200m reuse; Database altered. SQL> alter database add standby logfile thread 7 '/oradata/FANDB/stdredo07.log' size 200m reuse; Database altered.查看standby 日志组的信息：
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log; GROUP# SEQUENCE# STATUS BYTES/1024/1024 ---------- ---------- ---------- --------------- 4 0 UNASSIGNED 200 5 0 UNASSIGNED 200 6 0 UNASSIGNED 200 7 0 UNASSIGNED 200 -
修改主库参数
查看安装好之前的参数情况如下：
SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string db_name string fandb db_unique_name string fandb global_names boolean FALSE instance_name string fandb lock_name_space string log_file_name_convert string pdb_file_name_convert string processor_group_name string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string fandb修改归档和数据库相关的参数
SQL> alter system set log_archive_config='DG_CONFIG=(FANDBP,FANDBS)' scope=spfile; System altered. SQL> alter system set log_archive_dest_2='service=FANDBS LGWR ASYNC valid_for=(online_logfile,primary_role) db_unique_name=FANDBS'; System altered. SQL> alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE; System altered. SQL> alter system set FAL_CLIENT=FANDBP; System altered. //写自己 SQL> alter system set FAL_SERVER=FANDBS; System altered. //写备库 SQL> alter system set DB_FILE_NAME_CONVERT='FANDBS','FANDBP' scope=spfile; System altered. //备库在前，主库在后 转换后的路径 SQL> alter system set LOG_FILE_NAME_CONVERT='FANDBS','FANDBP' scope=spfile; System altered. SQL> alter system set pdb_file_name_convert='FANDBS','FANDBP' scope=spfile; System altered. SQL> alter system set db_unique_name='FANDBP' scope=spfile; System altered. //设置主机的db_unique_name重启修改后的参数如下：
SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string FANDBS, FANDBP db_name string fandb db_unique_name string FANDBP global_names boolean FALSE instance_name string fandb lock_name_space string log_file_name_convert string FANDBS, FANDBP pdb_file_name_convert string FANDBS, FANDBP processor_group_name string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string FANDBP -
修改主库监听配置文件
[oracle@oracle1 admin]$ pwd /u01/app/oracle/product/19.3.0/db_1/network/admin [oracle@oracle1 admin]$ vi tnsnames.ora //新增以下 FANDBP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.35.60)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = FANDBP) (SERVICE = DEDICATED) ) ) FANDBS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.35.61)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = FANDBS) (SERVICE = DEDICATED) ) )服务名 ：SERVICE_ NAME指的是listener中的全局数据库名：这个名字是由listener.ora中GLOBAL_ DBNAME参数决定的。SERVICE_ NAME是Oracle8i新引进的，8i之前一个数据库只能有一个实例。8i之后一个数据库可以对应多个实例，例如RAC。为了充分利用所有实例，并且令客户端连接配置简单，ORACLE提出了SERVICE_NAME的概念。该参数直接对应数据库，而不是某个实例。
[oracle@oracle1 admin]$ vi listener.ora //新增以下 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = FANDBP ) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1) (SID_NAME = fandb ) ) ) -
主库生成密码文件传至备库
[oracle@oracle1 dbs]$ orapwd file=/u01/app/oracle/product/19.3.0/db_1/dbs/orapwfandb password=FANDB-123 OPW-00005: File with same name exists - please delete or rename //本身自带有文件,需要删除后才能创建相同名称文件 [oracle@oracle1 dbs]$ rm -rf orapwfandb [oracle@oracle1 dbs]$ orapwd file=/u01/app/oracle/product/19.3.0/db_1/dbs/orapwfandb password=FANDB-123 [oracle@oracle1 dbs]$ scp orapwfandb 10.16.35.61:/u01/app/oracle/product/19.3.0/db_1/dbs/orapwfandb //传送至备库相同目录 The authenticity of host '10.16.35.61 (10.16.35.61)' can't be established. ECDSA key fingerprint is SHA256:+cmQxIDu9HRK2SUPgBJW5hjL8LZgFIxDFklN+J9W0Zw. ECDSA key fingerprint is MD5:77:c3:14:0d:a9:7c:93:d1:71:7f:be:c8:af:05:52:07. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.16.35.61' (ECDSA) to the list of known hosts. oracle@10.16.35.61's password: orapwfandb在备库测试使用次密码文件登录如下：
[oracle@oracle2 dbs]$ sqlplus "sys/FANDB-123@FANDBP as sysdba" SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 26 15:04:45 2020 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> SQL> exit //登录成功 -
主库pfile文件传至备库
SQL> create pfile='/home/oracle/pfile.ora' from spfile; //生成可编辑的pfile文件 File created. [oracle@oracle1 ~]$ scp pfile.ora 10.16.35.61:/home/oracle/pfile.ora //命令行传送至备库 oracle@10.16.35.61's password: pfile.ora
Datagurd数据库备库前期配置
备库按照主库的前期配置一样一直到静默安装监听
-
修改主库监听配置文件
[oracle@oracle1 admin]$ pwd /u01/app/oracle/product/19.3.0/db_1/network/admin [oracle@oracle1 admin]$ vi tnsnames.ora //新增以下 FANDBP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.35.60)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = FANDBP) (SERVICE = DEDICATED) ) ) FANDBS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.35.61)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = FANDBS) (SERVICE = DEDICATED) ) ) [oracle@oracle1 admin]$ vi listener.ora //新增以下 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = FANDBP ) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1) (SID_NAME = fandb ) ) ) -
修改从主库传送过来的pfile文件
*.audit_file_dest='/u01/app/oracle/admin/fandbs/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/oradata/FANDBS/control01.ctl','/oradata/FANDBS/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='FANDBP','FANDBS' *.db_name='fandb' *.db_unique_name='FANDBS' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=fandbXDB)' *.enable_pluggable_database=true *.fal_client='FANDBS' *.fal_server='FANDBP' *.local_listener='LISTENER_FANDB' *.log_archive_config='DG_CONFIG=(FANDBS,FANDBP)' *.log_archive_dest_1='location=/arch' *.log_archive_dest_2='service=FANDBP LGWR ASYNC valid_for=(online_logfile,primary_role) db_unique_name=FANDBP' *.log_archive_dest_state_1='ENABLE' *.log_file_name_convert='FANDBP','FANDBS' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pdb_file_name_convert='FANDBP','FANDBS' *.pga_aggregate_target=1146m *.processes=3200 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=4587m *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' -
创建备库目录
[oracle@oracle2 db_1]$ mkdir /u01/app/oracle/admin/fandbs/adump -p //审计目录 [oracle@oracle2 db_1]$ mkdir /arch //归档目录 [oracle@oracle2 ~]$ mkdir -p /oradata/FANDBS/ //创建控制文件目录
Datagurd数据库备库复制并搭建完成
-
按修改好的参数文件启动数据库
[oracle@oracle2 ~]$ sqlplus "sys/FANDB-123@FANDBS as sysdba" SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 27 16:32:26 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/home/oracle/pfile1.ora' nomount; ORACLE instance started. Total System Global Area 4815060848 bytes Fixed Size 9150320 bytes Variable Size 872415232 bytes Database Buffers 3925868544 bytes Redo Buffers 7626752 bytes SQL> create spfile from pfile='/home/oracle/pfile1.ora'; File created. SQL> shutdown immediate; //先关闭数据库 ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; //再启动，会从spfile文件启动 ORACLE instance started. Total System Global Area 3.1810E+10 bytes Fixed Size 30392984 bytes Variable Size 3825205248 bytes Database Buffers 2.7917E+10 bytes Redo Buffers 36712448 bytesRMAN恢复
[oracle@oracle2 ~]$ rman target sys/FANDB-123@FANDBP auxiliary sys/FANDB-123@FANDBS Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 27 16:36:38 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: FANDB (DBID=520508244) connected to auxiliary database: FANDB (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 27-OCT-20 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=2421 device type=DISK contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19.3.0/db_1/dbs/orapwfandb' ; } executing Memory Script Starting backup at 27-OCT-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=3642 device type=DISK Finished backup at 27-OCT-20 contents of Memory Script: { restore clone from service 'FANDBP' standby controlfile; } executing Memory Script Starting restore at 27-OCT-20 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/FANDBS/control01.ctl output file name=/oradata/FANDBS/control02.ctl Finished restore at 27-OCT-20 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/system01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/sysaux01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/undotbs01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/pdbseed/system01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/pdbseed/sysaux01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/users01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/pdbseed/undotbs01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/fanpdb/system01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/fanpdb/sysaux01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/fanpdb/undotbs01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (datafile) file name /oradata/FANDB/fanpdb/users01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (tempfile) file name /oradata/FANDB/temp01.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (tempfile) file name /oradata/FANDB/pdbseed/temp012020-10-20_12-44-14-573-PM.dbf conflicts with a file used by the target database RMAN-05158: WARNING: auxiliary (tempfile) file name /oradata/FANDB/fanpdb/temp01.dbf conflicts with a file used by the target database contents of Memory Script: { set newname for tempfile 1 to "/oradata/FANDB/temp01.dbf"; set newname for tempfile 2 to "/oradata/FANDB/pdbseed/temp012020-10-20_12-44-14-573-PM.dbf"; set newname for tempfile 3 to "/oradata/FANDB/fanpdb/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradata/FANDB/system01.dbf"; set newname for datafile 3 to "/oradata/FANDB/sysaux01.dbf"; set newname for datafile 4 to "/oradata/FANDB/undotbs01.dbf"; set newname for datafile 5 to "/oradata/FANDB/pdbseed/system01.dbf"; set newname for datafile 6 to "/oradata/FANDB/pdbseed/sysaux01.dbf"; set newname for datafile 7 to "/oradata/FANDB/users01.dbf"; set newname for datafile 8 to "/oradata/FANDB/pdbseed/undotbs01.dbf"; set newname for datafile 9 to "/oradata/FANDB/fanpdb/system01.dbf"; set newname for datafile 10 to "/oradata/FANDB/fanpdb/sysaux01.dbf"; set newname for datafile 11 to "/oradata/FANDB/fanpdb/undotbs01.dbf"; set newname for datafile 12 to "/oradata/FANDB/fanpdb/users01.dbf"; restore from nonsparse from service 'FANDBP' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /oradata/FANDB/temp01.dbf in control file renamed tempfile 2 to /oradata/FANDB/pdbseed/temp012020-10-20_12-44-14-573-PM.dbf in control file renamed tempfile 3 to /oradata/FANDB/fanpdb/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 27-OCT-20 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/FANDB/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/FANDB/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/FANDB/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradata/FANDB/pdbseed/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00006 to /oradata/FANDB/pdbseed/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata/FANDB/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00008 to /oradata/FANDB/pdbseed/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00009 to /oradata/FANDB/fanpdb/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00010 to /oradata/FANDB/fanpdb/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00011 to /oradata/FANDB/fanpdb/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service FANDBP channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00012 to /oradata/FANDB/fanpdb/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 27-OCT-20 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=1054918053 file name=/oradata/FANDB/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1054918053 file name=/oradata/FANDB/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=1054918053 file name=/oradata/FANDB/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=1054918053 file name=/oradata/FANDB/pdbseed/system01.dbf datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=1054918053 file name=/oradata/FANDB/pdbseed/sysaux01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=1054918053 file name=/oradata/FANDB/users01.dbf datafile 8 switched to datafile copy input datafile copy RECID=10 STAMP=1054918053 file name=/oradata/FANDB/pdbseed/undotbs01.dbf datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=1054918053 file name=/oradata/FANDB/fanpdb/system01.dbf datafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=1054918053 file name=/oradata/FANDB/fanpdb/sysaux01.dbf datafile 11 switched to datafile copy input datafile copy RECID=13 STAMP=1054918053 file name=/oradata/FANDB/fanpdb/undotbs01.dbf datafile 12 switched to datafile copy input datafile copy RECID=14 STAMP=1054918053 file name=/oradata/FANDB/fanpdb/users01.dbf Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic. RMAN-05535: warning: All redo log files were not defined properly. Finished Duplicate Db at 27-OCT-20这里指的注意的是：
如果在RMAN恢复时不指定 nofilenamecheck 参数，则在数据文件相同文件名恢复时会出现RMAN-05501错误，当主库，备库的数据库文件目录是一样的时候，必须使用 nofilenamecheck参数告诉rman主库和被创建的备份库拥有一样的文件目录和文件名。
并且在RMAN日志里会有ORA-01275: Operation RENAME is not allowed if standby file management is automatic.这里提示如果standby_ file_management=AUTO 参数是自动的时候。重新改名的不被允许的。一般还是建议设置成为auto。这样在主库上创建数据文件时，从库会自动创建数据文件，省去了不必要的麻烦。
检查datagurd状态：
[oracle@oracle2 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 27 10:31:38 2020 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; SQL> select name,open_mode ,protection_mode,database_role,switchover_status from v$database; NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS --------- -------------------- -------------------- ---------------- -------------------- FANDB READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
