本次测试环境
| 系统版本 | CentOS release 6.8 | |
| 主机名 | ec2t-userdata-01 | ec2t-userdata-01 |
| IP地址 | 10.189.102.118 | 10.189.100.195 |
| 数据库版本 | 11.2.0.4.0 | |
| 数据库角色 | primary | standby |
Oracle 11.2.0.4.0下载地址
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_1of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_1of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_2of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_2of7.zip
1. 系统配置
1.1. CentOS系统安装,IP,主机名配置(略),swap大小是内存的1.5~2倍
1.2. 在主从节点创建oracle用户和dba组
# groupadd -r -g 501 oinstall # groupadd -r -g 502 dba # useradd -r -u 501 -g oinstall -G dba,root oracle -d /home/oracle # echo "888888" | passwd --stdin oracle
1.3. 在主从节点创建oracle安装目录并赋予oracle用户和组权限
# mkdir -p /u01/app/oracle/product/11.2.0/db_1 # chown -R oracle:oinstall /u01/ # chmod -R 775 /u01/
1.4. 在主从节点配置oracle用户环境变量
- 主节点
$ cat /home/oracle/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMP=/tmp export TMPDIR=/tmp export TEMP=/tmp export ORACLE_HOSTNAME=ec2t-userdata-01 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export PATH=/usr/bin:/usr/sbin:/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH export ORACLE_SID=userdata export ORACLE_UNQNAME=userdata1 export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS" export TNS_ADMIN=$ORACLE_HOME/network/admin export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/ctx/lib:/lib:/usr/lib64 export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
- 从节点
$ cat /home/oracle/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMP=/tmp export TMPDIR=/tmp export TEMP=/tmp export ORACLE_HOSTNAME=ec2t-userdata-02 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export PATH=/usr/bin:/usr/sbin:/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH export ORACLE_SID=userdata export ORACLE_UNQNAME=userdata2 export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS" export TNS_ADMIN=$ORACLE_HOME/network/admin export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/ctx/lib:/lib:/usr/lib64 export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib 1.5. 在主从节点关闭防火墙和selinux
# service iptables stop # chkconfig iptables off # setenforce 0 # sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
1.5. 在主从节点优化内核参数
# tail -n 11 /etc/sysctl.conf fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 12884901888 kernel.shmmni = 4096 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 = 1048586 # sysctl -p
1.6. 在主从节点优化oracle用户资源使用限制
1.6.1. 修改用户限制配置文件,增加如下参数
# tail -n 6 /etc/security/limits.conf oracle soft nproc 16384 oracle hard nproc 16384 oracle soft nofile 65536 oracle hard nofile 65536 oracle soft stack 10240 oracle hard stack 32768
1.6.2 修改login配置文件,将用户限制加入到登录验证模块
# grep 'pam_limits.so' /etc/pam.d/login session required /lib64/security/pam_limits.so
1.6.3 编辑环境变量文件,增加对oracle用户的限制
# tail -n 10 /etc/profile if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi
1.7. 在主从节点安装依赖软件包
1.7.1 在主从节点安装oracle依赖包
yum -y install binutils-* compat-libstdc++-* elfutils-libelf-* elfutils-libelf-devel-* gcc-* gcc-c++-* glibc-* glibc-common-* glibc-devel-* glibc-headers-* ksh-* libaio-* libaio-devel-* libgcc-* libgomp-* libstdc++-* libstdc++-devel* make-* sysstat-* unixODBC-* libXp ksh readline readline-devel
1.7.2 在主从节点安装rlwrap包(用以解决sqlplus不能查询历史命令和后退键不能回退的问题)
# wget http://download.openpkg.org/components/cache/rlwrap/rlwrap-0.42.tar.gz # tar -zxf rlwrap-0.42.tar.gz # cd rlwrap-0.42 # ./configure # make # make install # echo "alias sqlplus='rlwrap sqlplus'" >> /home/oracle/.bashrc # echo "alias rman='rlwrap rman'" >> /home/oracle/.bashrc
1.8. 配置主从节点oracle用户之间的信任关系
- 主节点
$ ssh-keygen -t rsa $ cp ~/.ssh/id_rsa.pub ~/.ssh/authorized_keys $ ssh-copy-id oracle@ec2t-userdata-02
- 从节点
$ ssh-keygen -t rsa $ cp ~/.ssh/id_rsa.pub ~/.ssh/authorized_keys $ ssh-copy-id oracle@ec2t-userdata-01
2. 在主节点安装oracle数据库(静默方式)
2.1. 安装oracle数据库软件
2.1.1. 创建oracle数据库软件安装的应答文件
$ unzip p13390677_112040_Linux-x86-64_1of7.zip ; unzip p13390677_112040_Linux-x86-64_2of7.zip
$ cp database/response/db_install.rsp /home/oracle/ $ cat /home/oracle/db_install.rsp | grep -v "#"|grep -v "^$" oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0 oracle.install.option=INSTALL_DB_SWONLY ORACLE_HOSTNAME=ec2t-userdata-01 UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oracle/oraInventory SELECTED_LANGUAGES=en,zh_CN ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ORACLE_BASE=/u01/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.EEOptionsSelection=false oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0 oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=dba oracle.install.db.CLUSTER_NODES= oracle.install.db.isRACOneInstall=false oracle.install.db.racOneServiceName= oracle.install.db.config.starterdb.type=GENERAL_PURPOSE oracle.install.db.config.starterdb.globalDBName=userdata oracle.install.db.config.starterdb.SID=userdata oracle.install.db.config.starterdb.characterSet=AL32UTF8 oracle.install.db.config.starterdb.memoryOption=true oracle.install.db.config.starterdb.memoryLimit=2048 oracle.install.db.config.starterdb.installExampleSchemas=false oracle.install.db.config.starterdb.enableSecuritySettings=true oracle.install.db.config.starterdb.password.ALL=888888 oracle.install.db.config.starterdb.password.SYS= oracle.install.db.config.starterdb.password.SYSTEM= oracle.install.db.config.starterdb.password.SYSMAN= oracle.install.db.config.starterdb.password.DBSNMP= oracle.install.db.config.starterdb.control=DB_CONTROL oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL= oracle.install.db.config.starterdb.automatedBackup.enable=false oracle.install.db.config.starterdb.automatedBackup.osuid= oracle.install.db.config.starterdb.automatedBackup.ospwd= oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE oracle.install.db.config.starterdb.fileSystemStorage.dataLocation= oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation= oracle.install.db.config.asm.diskGroup= oracle.install.db.config.asm.ASMSNMPPassword= MYORACLESUPPORT_USERNAME= MYORACLESUPPORT_PASSWORD= SECURITY_UPDATES_VIA_MYORACLESUPPORT= DECLINE_SECURITY_UPDATES=true PROXY_HOST= PROXY_PORT= PROXY_USER= PROXY_PWD= PROXY_REALM= COLLECTOR_SUPPORTHUB_URL= oracle.installer.autoupdates.option= oracle.installer.autoupdates.downloadUpdatesLoc= AUTOUPDATES_MYORACLESUPPORT_USERNAME= AUTOUPDATES_MYORACLESUPPORT_PASSWORD=
从节点需要修改"ORACLE_HOSTNAME"的值
2.1.2. 安装oracle数据库软件
$ ./database/runInstaller -silent -responseFile /home/oracle/db_install.rsp Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB. Actual 5262 MB Passed Checking swap space: must be greater than 150 MB. Actual 4095 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-06-19_09-28-46AM. Please wait ...[oracle@ec2t-userdata-02 u01]$ [WARNING] [INS-32055] The Central Inventory is located in the Oracle base. CAUSE: The Central Inventory is located in the Oracle base. ACTION: Oracle recommends placing this Central Inventory in a location outside the Oracle base directory. You can find the log of this install session at: /u01/app/oracle/oraInventory/logs/installActions2017-06-19_09-28-46AM.log The installation of Oracle Database 11g was successful. Please check '/u01/app/oracle/oraInventory/logs/silentInstall2017-06-19_09-28-46AM.log' for more details. As a root user, execute the following script(s): 1. /u01/app/oracle/oraInventory/orainstRoot.sh 2. /u01/app/oracle/product/11.2.0/db_1/root.sh Successfully Setup Software. $ cat /u01/app/oracle/oraInventory/logs/silentInstall2017-06-19_09-28-46AM.log | grep "OverallStatus:" INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL INFO: OverallStatus:SUCCESSFUL
2.1.3. 重新打开一个session窗口以root用户执行以上两个脚本
# /u01/app/oracle/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oracle/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oracle/oraInventory to oinstall. The execution of the script is complete. # /u01/app/oracle/product/11.2.0/db_1/root.sh Check /u01/app/oracle/product/11.2.0/db_1/install/root_ec2t-userdata-02_2017-06-19_09-40-24.log for the output of root script # cat /u01/app/oracle/product/11.2.0/db_1/install/root_ec2t-userdata-02_2017-06-19_09-40-24.log Performing root user operation for Oracle 11g The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1 Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Finished product-specific root actions. Finished product-specific root actions.
2.1.4. 安装软件错误解决
- 内核参数值设置不正确
INFO: ********************************************* INFO: OS Kernel Parameter: shmmax: This is a prerequisite condition to test whether the OS kernel parameter "shmmax" is properly set. INFO: Severity:IGNORABLE INFO: OverallStatus:VERIFICATION_FAILED INFO: ----------------------------------------------- INFO: Verification Result for Node:ec2t-userdata-01 INFO: Expected Value:Current=1909168128; Configured=1909168128 INFO: Actual Value:Current=536870912; Configured=536870912 INFO: Error Message:PRVG-1201 : OS kernel parameter "shmmax" does not have proper value on node "ec2t-userdata-01" [Expected = "1909168128" ; Current = "536870912"; Configured = "536870912"]. INFO: Cause: Kernel parameter configured value does not meet the requirement.
修改shmmax为Expected的值
- 缺少pdksh-5.2.14包
INFO: ********************************************* INFO: Package: pdksh-5.2.14: This is a prerequisite condition to test whether the package "pdksh-5.2.14" is available on the system. INFO: Severity:IGNORABLE INFO: OverallStatus:VERIFICATION_FAILED INFO: ----------------------------------------------- INFO: Verification Result for Node:ec2t-userdata-01 INFO: Expected Value:pdksh-5.2.14 INFO: Actual Value:missing INFO: Error Message:PRVF-7532 : Package "pdksh" is missing on node "ec2t-userdata-01" INFO: Cause: A required package is either not installed or, if the package is a kernel module, is not loaded on the specified node.
下载安装即可
# wget http://vault.centos.org/5.11/os/x86_64/CentOS/pdksh-5.2.14-37.el5_8.1.x86_64.rpm # rpm -ivh pdksh-5.2.14-37.el5_8.1.x86_64.rpm --nodeps
2.2. 安装oracle监听程序
2.2.1. 创建oracle监听程序安装的应答文件
$ cp ./database/response/netca.rsp /home/oracle/
2.2.2. 创建oracle监听程序
$ netca /silent /responsefile /home/oracle/netca.rsp Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /home/oracle/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/11.2.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
2.3. 安装oracle数据库
2.2.1. 创建oracle数据库安装的应答文件
$ cp ./database/response/dbca.rsp /home/oracle/ $ cat /home/oracle/dbca.rsp | grep -v "#"|grep -v "^$" [GENERAL] RESPONSEFILE_VERSION = "11.2.0" OPERATION_TYPE = "createDatabase" [CREATEDATABASE] GDBNAME = "userdata" SID = "userdata" TEMPLATENAME = "General_Purpose.dbc" DATAFILEDESTINATION = "/u01/app/oracle/oradata" RECOVERYAREADESTINATION="/u01/app/oracle/flash_recovery_area" CHARACTERSET = "AL32UTF8" NATIONALCHARACTERSET= "UTF8" [createTemplateFromDB] SOURCEDB = "myhost:1521:orcl" SYSDBAUSERNAME = "system" TEMPLATENAME = "My Copy TEMPLATE" [createCloneTemplate] SOURCEDB = "orcl" TEMPLATENAME = "My Clone TEMPLATE" [DELETEDATABASE] SOURCEDB = "orcl" [generateScripts] TEMPLATENAME = "New Database" GDBNAME = "orcl11.us.oracle.com" [CONFIGUREDATABASE] [ADDINSTANCE] DB_UNIQUE_NAME = "orcl11g.us.oracle.com" NODELIST= SYSDBAUSERNAME = "sys" [DELETEINSTANCE] DB_UNIQUE_NAME = "orcl11g.us.oracle.com" INSTANCENAME = "orcl11g" SYSDBAUSERNAME = "sys"
2.2.2. 修改引用的通用模板General_Purpose.dbc
$ cp $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc.bak $ cat $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc <?xml version = '1.0'?> <DatabaseTemplate name="General_Purpose" description=" " version="11.2.0.0.0"> <CommonAttributes> <option name="OMS" value="false"/> <option name="JSERVER" value="true"/> <option name="SPATIAL" value="true"/> <option name="IMEDIA" value="true"/> <option name="XDB_PROTOCOLS" value="true"> <tablespace id="SYSAUX"/> </option> <option name="ORACLE_TEXT" value="true"> <tablespace id="SYSAUX"/> </option> <option name="SAMPLE_SCHEMA" value="false"/> <option name="CWMLITE" value="true"> <tablespace id="SYSAUX"/> </option> <option name="EM_REPOSITORY" value="true"> <tablespace id="SYSAUX"/> </option> <option name="APEX" value="true"/> <option name="OWB" value="true"/> <option name="DV" value="false"/> </CommonAttributes> <Variables/> <CustomScripts Execute="false"/> <InitParamAttributes> <InitParams> <initParam name="db_name" value=""/> <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/> <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/> <initParam name="compatible" value="11.2.0.4.0"/> <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/> <initParam name="processes" value="600"/> <initParam name="undo_tablespace" value="UNDOTBS1"/> <initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl")"/> <initParam name="diagnostic_dest" value="{ORACLE_BASE}"/> <initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/fast_recovery_area"/> <initParam name="audit_trail" value="db"/> <initParam name="memory_target" value="2048" unit="MB"/> <initParam name="db_block_size" value="8" unit="KB"/> <initParam name="open_cursors" value="600"/> <initParam name="db_recovery_file_dest_size" value="4096" unit="MB"/> <initParam name="JAVA_JIT_ENABLED" value="FALSE"/> </InitParams> <MiscParams> <databaseType>MULTIPURPOSE</databaseType> <maxUserConn>20</maxUserConn> <percentageMemTOSGA>40</percentageMemTOSGA> <customSGA>false</customSGA> <archiveLogMode>false</archiveLogMode> <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName> </MiscParams> <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile> </InitParamAttributes> <StorageAttributes> <DataFiles> <Location>{ORACLE_HOME}/assistants/dbca/templates/Seed_Database.dfb</Location> <SourceDBName>seeddata</SourceDBName> <Name id="1" Tablespace="SYSTEM" Contents="PERMANENT" Size="740" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf</Name> <Name id="2" Tablespace="SYSAUX" Contents="PERMANENT" Size="470" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf</Name> <Name id="3" Tablespace="UNDOTBS1" Contents="UNDO" Size="25" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf</Name> <Name id="4" Tablespace="USERS" Contents="PERMANENT" Size="5" autoextend="true" blocksize="8192">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf</Name> </DataFiles> <TempFiles> <Name id="1" Tablespace="TEMP" Contents="TEMPORARY" Size="20">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf</Name> </TempFiles> <ControlfileAttributes id="Controlfile"> <maxDatafiles>200</maxDatafiles> <maxLogfiles>16</maxLogfiles> <maxLogMembers>3</maxLogMembers> <maxLogHistory>1</maxLogHistory> <maxInstances>8</maxInstances> <image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/> <image name="control02.ctl" filepath="{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/"/> </ControlfileAttributes> <RedoLogGroupAttributes id="1"> <reuse>false</reuse> <fileSize unit="KB">102400</fileSize> <Thread>1</Thread> <member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/> </RedoLogGroupAttributes> <RedoLogGroupAttributes id="2"> <reuse>false</reuse> <fileSize unit="KB">102400</fileSize> <Thread>1</Thread> <member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/> </RedoLogGroupAttributes> <RedoLogGroupAttributes id="3"> <reuse>false</reuse> <fileSize unit="KB">102400</fileSize> <Thread>1</Thread> <member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/> </RedoLogGroupAttributes> </StorageAttributes> </DatabaseTemplate>
2.2.3. 安装oracle数据库
$ dbca -silent -responseFile /home/oracle/dbca.rsp Enter SYS user password: 888888 Enter SYSTEM user password: 888888 Copying database files 1% complete 2% complete 4% complete 37% complete Creating and starting Oracle instance 38% complete 40% complete 45% complete 50% complete 51% complete 56% complete 57% complete 61% complete 62% complete Completing Database Creation 66% complete 70% complete 73% complete 74% complete 85% complete 86% complete 98% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/userdata/userdata.log" for further details.
2.2.4. 在glogin.sql文件增加常用的配置
$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql -- -- Copyright (c) 1988, 2011, Oracle and/or its affiliates. -- All rights reserved. -- -- NAME -- glogin.sql -- -- DESCRIPTION -- SQL*Plus global login "site profile" file -- -- Add any SQL*Plus commands here that are to be executed when a -- user starts SQL*Plus, or uses the SQL*Plus CONNECT command. -- -- USAGE -- This script is automatically run -- define_editor=vi set serveroutput on size 1000000 set trimspool on set long 5000 set linesize 200 set pagesize 9999 column plan_plus_exp format a80 column global_name new_value gname set termout off define gname=idle column global_name new_value gname select lower(user) || '@' || substr(global_name,1,decode( dot,0,length(global_name),dot-1) ) global_name from (select global_name,instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> ' set termout on set sqlprompt"_user'@'_connect_identifier>" column member format a50 column name format a50 column DEST_NAME format a30 column DESTINATION format a40 column FILE_NAME format a60 -- Used by Trusted Oracle COLUMN ROWLABEL FORMAT A15 -- Used for the SHOW ERRORS command COLUMN LINE/COL FORMAT A8 COLUMN ERROR FORMAT A65 WORD_WRAPPED -- Used for the SHOW SGA command COLUMN name_col_plus_show_sga FORMAT a24 COLUMN units_col_plus_show_sga FORMAT a15 -- Defaults for SHOW PARAMETERS COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE -- Defaults for SHOW RECYCLEBIN COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME' COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME' COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE' COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME' -- Defaults for SET AUTOTRACE EXPLAIN report -- These column definitions are only used when SQL*Plus -- is connected to Oracle 9.2 or earlier. COLUMN id_plus_exp FORMAT 990 COLUMN parent_id_plus_exp FORMAT 990 COLUMN plan_plus_exp FORMAT a60 COLUMN object_node_plus_exp FORMAT a8 COLUMN other_tag_plus_exp FORMAT a29 COLUMN other_plus_exp FORMAT a44
3. 在从节点安装oracle数据库(静默方式,仅需要安装数据库软件和创建监听)
步骤略,参考步骤2
4. 主库状态查看
- 查看数据库信息
column PLATFORM_NAME format a20 column NAME format a10 column DB_UNIQUE_NAME format a15 column OPEN_MODE format a10 column LOG_MODE format a15 column FLASHBACK_ON format a15 select PLATFORM_NAME,NAME,DBID,DB_UNIQUE_NAME,CREATED,OPEN_MODE,CURRENT_SCN,LOG_MODE,FORCE_LOGGING,FLASHBACK_ON from v$database; PLATFORM_NAME NAME DBID DB_UNIQUE_NAME CREATED OPEN_MODE CURRENT_SCN LOG_MODE FORCE_LOG FLASHBACK_ON -------------------- ---------- ---------- --------------- ------------------- ---------- ----------- --------------- --------- --------------- Linux x86 64-bit USERDATA 3890525137 userdata 2017-06-21 19:44:17 READ WRITE 995537 NOARCHIVELOG NO NO column INSTANCE_NAME format a10 column HOST_NAME format a20 column VERSION format a15 column STATUS format a10 column DATABASE_STATUS format a10 column INSTANCE_ROLE format a20 column ACTIVE_STATE format a10 select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE from v$instance; INSTANCE_N HOST_NAME VERSION STARTUP_TIME STATUS DATABASE_S INSTANCE_ROLE ACTIVE_STA ---------- -------------------- --------------- ------------------- ---------- ---------- -------------------- ---------- userdata ec2t-userdata-01 11.2.0.4.0 2017-06-21 19:47:53 OPEN ACTIVE PRIMARY_INSTANCE NORMAL
- 查看数据库控制文件信息
column NAME format a60 select NAME,STATUS,TO_CHAR(block_size*file_size_blks,'999,999,999,999') File_Size from v$controlfile; NAME STATUS FILE_SIZE ------------------------------------------------------------ --------------------- ------------------------------------------------ /u01/app/oracle/oradata/userdata/control01.ctl 11,108,352 /u01/app/oracle/fast_recovery_area/userdata/control02.ctl 11,108,352
- 查看联机重做日志文件信息
column SIZE_MB format 999 column STATUS format a10 column TYPE format a10 column NEXT_CHANGE# format 999999999999999 SELECT l.thread#, lf.group#, lf.member, TRUNC(l.bytes/1024/1024) AS size_mb, l.status, l.archived, lf.type, lf.is_recovery_dest_file AS rdf, l.sequence#, l.first_change#, l.next_change# FROM v$logfile lf JOIN v$log l ON l.group# = lf.group# ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER SIZE_MB STATUS ARCHIVED TYPE RDF SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- -------------------------------------------------- ------- ---------- --------- ---------- --------- ---------- ------------- ---------------- 1 1 /u01/app/oracle/oradata/userdata/redo01.log 100 CURRENT NO ONLINE NO 4 990659 281474976710655 1 2 /u01/app/oracle/oradata/userdata/redo02.log 100 INACTIVE NO ONLINE NO 2 929203 961989 1 3 /u01/app/oracle/oradata/userdata/redo03.log 100 INACTIVE NO ONLINE NO 3 961989 990659
- 查看数据文件使用信息
SET PAGESIZE 100 SET LINESIZE 265 COLUMN tablespace_name FORMAT A20 COLUMN file_name FORMAT A50 COLUMN USED_PCT FORMAT A20 SELECT df.tablespace_name, df.file_name, df.size_mb, f.free_mb, df.max_size_mb, f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb, RPAD(' '|| RPAD('X',ROUND((df.max_size_mb-(f.free_mb + (df.max_size_mb - df.size_mb)))/max_size_mb*10,0), 'X'),11,'-') AS used_pct FROM (SELECT file_id, file_name, tablespace_name, TRUNC(bytes/1024/1024) AS size_mb, TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb FROM dba_data_files) df, (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb, file_id FROM dba_free_space GROUP BY file_id) f WHERE df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name;
TABLESPACE_NAME FILE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB USED_PCT -------------------- -------------------------------------------------- ------- ---------- ----------- ----------- -------------------- SYSAUX /u01/app/oracle/oradata/userdata/sysaux01.dbf 520 31 32767 32278 ---------- SYSTEM /u01/app/oracle/oradata/userdata/system01.dbf 740 0 32767 32027 ---------- UNDOTBS1 /u01/app/oracle/oradata/userdata/undotbs01.dbf 75 66 32767 32758 ---------- USERS /u01/app/oracle/oradata/userdata/users01.dbf 5 3 32767 32765 ----------
- 查看表空间使用信息
column TABLESPACE_NAME format a15 SELECT UPPER(F.TABLESPACE_NAME) "TABLESPACE_NAME", D.TOT_GROOTTE_MB "TABLESPACE_SIZE(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "TABLESPACE_USED(M)", F.TOTAL_BYTES "TABLESPACE_FREE(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "USED_PERCENTAGE", F.MAX_BYTES "MAX_BLOCK(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;
TABLESPACE_NAME TABLESPACE_SIZE(M) TABLESPACE_USED(M) TABLESPACE_FREE(M) USED_PERCENTAGE MAX_BLOCK(M) --------------- ------------------ ------------------ ------------------ ------------------------ ------------ SYSAUX 520 488.87 31.13 94.01% 31.13 SYSTEM 740 739.12 .88 99.88% .88 UNDOTBS1 75 9.5 65.5 12.67% 44 USERS 5 1.31 3.69 26.20% 3.69
- 查看每个数据文件的IO信息
COLUMN 'File Name' FORMAT A50 COLUMN 'Blocks Read' FORMAT 999999 COLUMN 'Blocks Writen' FORMAT 999999 COLUMN 'Total I/O' FORMAT 999999 SELECT Substr(d.name,1,50) "File Name", f.phyblkrd "Blocks Read", f.phyblkwrt "Blocks Writen", f.phyblkrd + f.phyblkwrt "Total I/O" FROM v$filestat f, v$datafile d WHERE d.file# = f.file# ORDER BY f.phyblkrd + f.phyblkwrt DESC; File Name Blocks Read Blocks Writen Total I/O -------------------------------------------------- ----------- ------------- --------- /u01/app/oracle/oradata/userdata/system01.dbf 20333 2525 22858 /u01/app/oracle/oradata/userdata/sysaux01.dbf 7586 8515 16101 /u01/app/oracle/oradata/userdata/undotbs01.dbf 22 5480 5502 /u01/app/oracle/oradata/userdata/users01.dbf 34 0 34