【oracle】oracle11g 搭建

一、	搭建oracle11G
1、	内存配置
[root@localhost ~]# grep MemTotal /proc/meminfo
MemTotal:     32949816 kB
[root@localhost ~]# grep SwapTotal /proc/meminfo
SwapTotal:    16779884 kB
[root@localhost ~]# df -k /dev/shm/
Filesystem           1K-blocks      Used Available Use% Mounted on
tmpfs                 16474908         0  16474908   0% /dev/shm
[root@localhost ~]# df -h /tmp
Filesystem            Size  Used Avail Use% Mounted on
/dev/shm               16G     0   16G   0% /tmp
2、	文件系统
/dev/sda7      ext3    505G   37G  468G   8% /home
3、	兼容包
rpm -q \
binutils \
compat-libstdc++-33 \
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 \
numactl-devel \
sysstat \
unixODBC \
unixODBC-devel | grep "not installed"
4、	创建用户
chattr -i /etc/passwd /etc/shadow
groupadd –g 504 oinstall
groupadd –g 505 dba
useradd –u 504 –g oinstall –G dba oracle
passwd oracle
chattr +i /etc/passwd /etc/shadow
5、	配置系统参数
[root@localhost home]# vi /etc/sysctl.conf
	# for oracle
kernel.shmall = 8388608
kernel.shmmax = 34359738368
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
fs.file-max = 6815744
[root@localhost home]# sysctl -p
[root@localhost home]# vi /etc/security/limits.conf 
oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   10240
[root@localhost home]# vi /etc/pam.d/login 
session    required     pam_limits.so
[root@localhost home]# vi /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
6、	创建目录
[root@localhost home]# mkdir –p /home/U01
[root@localhost home]# ln –s /home/U01 /U01
[root@localhost home]# mkdir -p /U01/app/oracle/
[root@localhost home]# chown -R oracle:oinstall /U01/
[root@localhost home]# chmod -R 775 /U01/
7、	切换oracle用户配置环境变量
[oracle@bf_test_qd_119 ~]$ vi .bash_profile 
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
export ORACLE_BASE=/U01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.2/db_1
export ORACLE_SID=ora11g
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/jdk/bin:$PATH
export LANG="en_US"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
#export DISPLAY=172.0.0.172:0.0  ###如果是远程安装需要指定本机IP的DISPLAY
二、	搭建主库
1、	拷贝安装包
[oracle@ld_s_172 home]$ ll -thr p10098816_112020_Linux-x86-64_*
-rw-r--r-- 1 root root  1.3G Apr 11 11:39 p10098816_112020_Linux-x86-64_1of7.zip
-rw-r--r-- 1 root root 1002M Apr 11 11:39 p10098816_112020_Linux-x86-64_2of7.zip
2、	解压安装包
[oracle@bf_test_qd_119 U01]$ unzip p10098816_112020_Linux-x86-64_1of7.zip 
[oracle@bf_test_qd_119 U01]$ unzip p10098816_112020_Linux-x86-64_2of7.zip
3、	解压生成/home/database目录
[oracle@ld_s_172 database]$ ll -thr
drwxr-xr-x  4 oracle oinstall 4.0K Nov 16  2010 install
drwxr-xr-x 12 oracle oinstall 4.0K Nov 16  2010 doc
drwxr-xr-x  2 oracle oinstall   28 Nov 16  2010 sshsetup
-rwxr-xr-x  1 oracle oinstall 3.2K Nov 16  2010 runInstaller
drwxr-xr-x  2 oracle oinstall   33 Nov 16  2010 rpm
drwxr-xr-x 14 oracle oinstall 4.0K Nov 16  2010 stage
-rw-r--r--  1 oracle oinstall 5.3K Nov 16  2010 welcome.html
-rwxr-xr-x  1 oracle oinstall  27K Nov 16  2010 readme.html
drwxr-xr-x  2 oracle oinstall   86 Apr 15 17:04 response
4、	修改安装配置文件
[oracle@bf_test_qd_119 database]$cd /U01/database/response/
[oracle@bf_test_qd_119 database]$cp db_install.rsp db_install_ora11g.rsp
[oracle@bf_test_qd_119 database]$vi db_install_bfodb.rsp
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME= 10.0.0.1_hostname
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/U01/app/oracle/oraInventory
ORACLE_HOME=/U01/app/oracle/product/11.2.0.2/db_1
ORACLE_BASE=/U01/app/oracle
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.config.starterdb.characterSet=ZHS16GBK
DECLINE_SECURITY_UPDATES=true
5、	安装oracle软件
[oracle@bf_test_qd_119 database]$ ./runInstaller -silent -force -noconfig -responseFile /home/database/response/db_install_ora11g.rsp 
Starting Oracle Universal Installer...
 
Checking Temp space: must be greater than 120 MB.   Actual 16088 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 16386 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-03-20_05-19-18PM. Please wait ...[oracle@bf_test_qd_119 database]$ [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.
[WARNING] [INS-13014] Target environment do not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /tmp/OraInstall2012-03-20_05-19-18PM/installActions2012-03-20_05-19-18PM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /tmp/OraInstall2012-03-20_05-19-18PM/installActions2012-03-20_05-19-18PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
You can find the log of this install session at:
 /U01/app/oracle/oraInventory/logs/installActions2012-03-20_05-19-18PM.log
The installation of Oracle Database 11g was successful.
Please check '/U01/app/oracle/oraInventory/logs/silentInstall2012-03-20_05-19-18PM.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.3/db_1/root.sh
 
 
Successfully Setup Software.
使用root
[root@bf_test_qd_119 ~]# /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.
[root@bf_test_qd_119 ~]# /U01/app/oracle/product/11.2.3/db_1/root.sh
Check /U01/app/oracle/product/11.2.3/db_1/install/root_bf_test_qd_119.167.137.13_cnc_2012-03-20_17-22-51.log for the output of root script
6、	配置监听
[oracle@bf_test_qd_119 response]$ $ORACLE_HOME/bin/netca -silent -responsefile /home/database/response/netca.rsp
 
Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /U01/database/response/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.3/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@ld_s_172 ~]$ $ORACLE_HOME/bin/netca -silent -responsefile /home/database/response/netca.rsp  
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /home/U01/app/oracle/product/11.2.0.2/db_1/lib/libnjni11.so: /U01/app/oracle/product/11.2.0.2/db_1/lib/libclntsh.so.11.1: file too short
java.lang.UnsatisfiedLinkError: jniGetOracleHome
        at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
        at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
        at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
        at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
        at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
        at oracle.net.ca.NetCA.main(NetCA.java:427)             
解决：
[oracle@ld_s_172 ~]$ $ORACLE_HOME/bin/relink all
writing relink log to: /U01/app/oracle/product/11.2.0.2/db_1/install/relink.log
/bin/sh: /usr/bin/gcc: Permission denied
添加权限
chmod 777 /usr/bin/make
chmod 777 /usr/bin/gcc
[oracle@ld_s_172 ~]$ $ORACLE_HOME/bin/netca -silent -responsefile /home/database/response/netca.rsp
Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /home/database/response/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.2/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
7、	创建数据库
[oracle@bf_test_qd_119 response]$ vi dbca.rsp
GDBNAME = "ora11g"
SID = "ora11g"
CHARACTERSET = "ZHS16GBK"
TOTALMEMORY = "2048"
SOURCEDB = "bf_test_qd_119.167.137.13_cnc:1521:ora11g"
[oracle@bf_test_qd_119 response]$ dbca -silent -responseFile dbca.rsp
Enter SYS user password: oracle
Enter SYSTEM user password: oracle
Copying database files
1% complete
3% complete
11% complete
18% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
8、	进程检查
检查ps –ef | grep ora_
oracle   27679     1  0 13:34 ?        00:00:00 ora_pmon_bfodb
oracle   27681     1  0 13:34 ?        00:00:00 ora_psp0_bfodb
oracle   27683     1  0 13:34 ?        00:00:00 ora_vktm_bfodb
oracle   27687     1  0 13:34 ?        00:00:00 ora_gen0_bfodb
oracle   27689     1  0 13:34 ?        00:00:00 ora_diag_bfodb
oracle   27691     1  0 13:34 ?        00:00:00 ora_dbrm_bfodb
至此，oracle 11g 静默安装完毕，比图形化界面快多了，也比较简单
9、	修改参数，生成spfile文件重启
alter system set sessions=5510 scope=spfile;
alter system set processes=5000 scope=spfile;
alter system set license_max_sessions=5000;
alter system set license_sessions_warning=4000;
alter system set session_cached_cursors=1000 scope=spfile;
alter system set audit_sys_operations=TRUE scope=spfile;
alter system set db_files=1000 scope=spfile;
alter system set utl_file_dir='/U01/app/oracle/admin/ora11g/utl_file' scope=spfile;
alter system set sga_max_size=40G scope=spfile;
alter system set undo_retention=14400;
alter tablespace undotbs1 retention guarantee;
alter system set optimizer_index_caching=90;
alter system set audit_trail='DB' scope=spfile;
alter system set audit_sys_operations=true scope=spfile;
alter system set optimizer_index_cost_adj=30;
alter database force logging;
alter database add supplemental log data;
打开归档，需起到mount阶段
alter system set log_archive_dest_1 ='location=/U01/app/oracle/admin/ora11g/arch',valid_for=(ONLINE_LOGFILE,ALL_ROLES);
alter database archivelog;
alter database open;
三、	搭建备库，只需安装软件，无需建库
1、	开通主备防火墙，使之互通，配置信任
2、	备库安装oracle软件和监听，步骤跟上面一样，但不创建库
3、	配置tnsnames.ora,主备一样
主备库vi /$ORACLE_HOME/ network/admin/tnsnames.ora
ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ld_413s_172.0.0.171_tw)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )
SORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ld_s_172.0.0.172_tw)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )
检查tnsping ORA11G；tnsping SORA11G是否OK
4、	创建备库所需目录
mkdir -p /U01/app/oracle/admin
mkdir -p /U01/app/oracle/admin/ora11g/{a,dp}dump
mkdir -p /U01/app/oracle/flash_recovery_area
mkdir -p /U01/app/oracle/admin/ora11g/arch
mkdir -p /U01/app/oracle/admin/ora11g/pfile
mkdir -p /U01/app/oracle/admin/ora11g/utl_file
mkdir -p /U01/app/oracle/oradata/ora11g
5、	拷贝主库参数文件到备库
ora11g.__db_cache_size=15837691904
ora11g.__java_pool_size=67108864
ora11g.__large_pool_size=67108864
ora11g.__oracle_base='/U01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=6442450944
ora11g.__sga_target=19327352832
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=3087007744
ora11g.__streams_pool_size=134217728
*.archive_lag_target=0
*.audit_file_dest='/U01/app/oracle/admin/ora11g/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.compatible='11.2.0.2'
*.control_files='/U01/app/oracle/oradata/ora11g/control01.ctl','/U01/app/oracle/fast_recovery_area/ora11g/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_files=1000
*.db_name='ora11g'
*.db_recovery_file_dest='/U01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='sora11g'
*.dg_broker_start=TRUE
*.diagnostic_dest='/U01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.fal_server='ora11g'
*.fal_client=’sora11g’
*.license_max_sessions=5000
*.license_sessions_warning=4000
*.local_listener='sora11g'
*.log_archive_config='dg_config=(sora11g,ora11g)'
*.log_archive_dest_1='location=/U01/app/oracle/admin/ora11g/arch'
ora11g.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
ora11g.log_archive_trace=0
*.open_cursors=300
*.optimizer_index_caching=90
*.pga_aggregate_target=6442450944
*.processes=5000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=1000
*.sessions=5510
*.sga_target=19327352832
###*.standby_archive_dest='location=/U01/app/oracle/admin/ora11g/arch',';' 
###在Oracle 11g的Data Guard中，standby_archive_dest参数已经被取消了
*.standby_file_management='auto'
*.statistics_level='ALL'
*.undo_retention=14400
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/U01/app/oracle/admin/ora11g/utl_file'
6、	备库起到nomount阶段
sqlplus / as sysdba
startup nomount pfile='/U01/app/oracle/product/11.2.0.2/db_1/dbs/initora11g.ora';
7、	备份主库控制文件、数据文件、归档日志，和密码文件拷贝到备库
rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup format '/home/oracle/standby/%d_%s_%p_%c_%t' Database;
backup format '/home/oracle/standby/%d_%s_%p_%c_%t_ctl' current controlfile for standby;
Backup format '/home/oracle/standby/%d_%s_%p_%c_%t_arc'  ArchiveLog all;
release channel c2;
release channel c1;
}
###如果没有密码文件，手动生成
orapwd file=/U01/app/oracle/product/11.2.0.2/db_1/dbs/orapwora11g password=oracle
8、	将主库备份文件拷贝到备库
mkdir –p /home/oracle/standby/
scp /home/oracle/standby/* 172.0.0172:/home/oracle/standby
scp /U01/app/oracle/product/11.2.0.2/db_1/dbs/orapwora11g 172.0.0172: /U01/app/oracle/product/11.2.0.2/db_1/dbs
chown oracle:oinstall –R /home/oracle/standby/*
chown oracle:oinstall /U01/app/oracle/product/11.2.0.2/db_1/dbs/orapwora11g
9、	恢复备库, 开始duplicate
rman nocatalog auxiliary / target sys/'oracle'@ora11g		
run{
duplicate target database for standby dorecover nofilenamecheck;
}
10、	备库创建spfile文件
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup mount;
四、	后续问题
1、	备库添加standby redo log
在备库添加standby redo log需要先停MRP
SQL> alter database recover managed standby database cancel;
SQL> alter database add standby logfile '/U01/app/oracle/oradata/ora11g/stdbyredo04.log’ size 50m;
Database altered.
SQL> alter database add standby logfile '/U01/app/oracle/oradata/ora11g/stdbyredo05.log’ size 50m;
Database altered.
SQL> alter database add standby logfile '/U01/app/oracle/oradata/ora11g/stdbyredo06.log’ size 50m;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
2、	主库添加standby redo log
SQL> alter database add standby logfile ‘/U01/app/oracle/oradata/ora11g/stdbyredo01.log’size 50m;
SQL> alter database add standby logfile ‘/U01/app/oracle/oradata/ora11g/stdbyredo02.log’ size 50m;
SQL> alter database add standby logfile ‘/U01/app/oracle/oradata/ora11g/stdbyredo03.log’ size 50m;
SQL> alter database add standby logfile ‘/U01/app/oracle/oradata/ora11g/stdbyredo04.log’ size 50m;
3、	启用real-time apply，从而实现real-time query
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
五、	配置DG
dgmgrl /
DGMGRL> CREATE CONFIGURATION ora11g AS PRIMARY DATABASE IS 'ora11g' CONNECT IDENTIFIER IS 'ora11g';
DGMGRL> add database 'sora11g' as connect identifier is sora11g maintained as physical;
DGMGRL> enable configuration;
DGMGRL> show configuration;
六、	主备同步测试
Primary：
SQL> create table scott.dave(id number,name varchar2(20));
Table created.
SQL> insert into scott.dave values(1,'tianlesoftware');
1 row created.
SQL> commit;
Commit complete.
Standby：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
 
SQL> select * from scott.dave;
        ID NAME
---------- ---------------
         1 tianlesoftware