centos6.8_manul_install_oracle112040&manu_create_db
--1.1上传oracle软件包及安装环境检查
--redhat6.8下载链接：https://pan.baidu.com/s/1eTyw102 密码：cpfs
--虚拟机使用独立磁盘时不能拍摄快照
--创建swap空间 -v显示详细信息
--上传软件包到/opt
df -h
mkdir -pv /u01/swap
dd if=/dev/zero of=/u01/swap/swap1 bs=1024M count=1
--dd if=/dev/zero of=/u01/swap/swap1 bs=1024M count=16（实际环境）
mkswap /u01/swap/swap1
--生效交换分区
swapon /u01/swap/swap1
echo swapon /u01/swap/swap1>>/etc/rc.local
df -h
fdisk -l
fdisk /dev/sdb 输入 n p 1 enter enter w
--创建文件系统并格式化分区
mkfs.ext3 /dev/sdb
--反挂载
--umount /dev/sdb
--硬盘分区并挂载到/u01下
mount /dev/sdb /u01
df -h
--查询UUID 配置开机挂载存储
blkid
vi /etc/fstab
UUID=3ba1d7f6-cf6c-4d9e-83df-aeff405e74a0 /u01 ext3 defaults 1 2
--查询配置
free
df -h
fdisk -l
cat /etc/issue
uname -a
--1.2安装所需包 (用互联网yum或者是本地yum) 虚拟机中可能yum源有问题，不能批量安装
--用第一个centos6.8镜像包
cd /media/CentOS_6.8_Final/Packages --或配置yum源
yum -y install binutils-* compat-libstdc++-* gcc-* gcc-c++-* glibc-* glibc-devel libaio-* libaio-devel libgcc-4.1.2 libstdc++-* \
libstdc++-devel make sysstat elfutils-libelf-devel unixODBC-* unixODBC-devel-* elfutils-libelf-devel
cd /opt
rpm -ivh pdksh-5.2.14-30.x86_64.rpm
--参考
yum -y install 包名（支持*） ：自动选择y，全自动
yum install 包名（支持*） ：手动选择y or n
yum remove 包名（不支持*）
yum search 包名
yum list 包名关键字 查询已安装包
rpm -ivh 包名（支持*）：安装rpm包
rpm -e 包名（不支持*）：卸载rpm包
yum grouplist 查看可能批量安装的列表
--若yum源中的包不全，可以rpm先查询出包然后rpm逐一安装
--或者rpm -q 或按包全名中的关键字查询rpm -qa | grepbinutils
yum list binutils \
compat-libstdc \
gcc \
gcc-c \
glibc \
glibc-devel \
libaio \
libaio-devel \
libgcc \
libstdc \
libstdc++-devel \
make \
sysstat \
elfutils-libelf-devel \
unixODBC \
unixODBC-devel \
elfutils-libelf-devel
--Available Packages下面显示未安装软件包 可以用yum -y install安装 -y静默安装
--所以所需安装的软件包为
yum -y install elfutils-libelf-devel.x86_64
yum -y install libaio-devel.x86_64
yum -y install libstdc++-devel.x86_64
yum -y install unixODBC.x86_64
yum -y install unixODBC-devel.x86_64
--额外安装软件包pdksh pdksh-5.2.14-30.x86_64.rpm
--1.3 相关内核参数修改 当前服务器内存：16G
--(如果是内存不一样需要修改kernel.shmall，kernel.shmmax，kernel.shmmax应大于sga)
--修改/etc/sysctl.conf
--注释掉之前参数
sed -i '/kernel\.shmmax/s/^/#/' /etc/sysctl.conf
sed -i '/kernel\.shmall/s/^/#/' /etc/sysctl.conf
--编辑/etc/sysctl.conf
echo -e "#oracle insert\n">>/etc/sysctl.conf
echo "fs.aio-max-nr = 1048576">>/etc/sysctl.conf
echo "fs.file-max = 6815744">>/etc/sysctl.conf
echo "kernel.shmall = 4194304">>/etc/sysctl.conf
echo "kernel.shmmax = 15536870912">>/etc/sysctl.conf
echo "kernel.shmmni = 4096">>/etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128">>/etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500">>/etc/sysctl.conf
echo "net.core.rmem_default = 262144">>/etc/sysctl.conf
echo "net.core.rmem_max = 4194304">>/etc/sysctl.conf
echo "net.core.wmem_default = 262144">>/etc/sysctl.conf
echo "net.core.wmem_max = 1048586">>/etc/sysctl.conf
--运行以下命令使配置生效
sysctl -p
--修改/etc/security/limits.conf 增加以下内容
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
--编辑/etc/pam.d/login增加以下参数
vi /etc/pam.d/login
session required pam_limits.so
--1.4 修改服务器主机名及hosts
--更改主机名
hostname hostname-92-33
vim /etc/sysconfig/network
vim /etc/hosts
--修改完重启服务器
1.5.增加相关用户及目录
groupadd oinstall
groupadd oper
groupadd dba
useradd -g oinstall -G dba,oper oracle
echo "oracle" | passwd --stdin oracle
mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/soft
chown -R oracle:oinstall /u01/
--配置vnc 需要安装tigervnc
rpm -qa |grep vnc
yum search vnc
yum -y install tigervnc-server.x86_64
rpm -qa |grep vnc
vim /etc/sysconfig/vncservers 增加
VNCSERVERS="1:root"
VNCSERVERARGS[1]="-geometry 1000x700"
--禁用防火墙和selinux
service iptables stop
chkconfig iptables off
vi /etc/selinux/config 改为
SELINUX=disabled
1.6修改用户配置文件
su - oracle
vi ~/.bash_profile
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
TNS_ADMIN=$ORACLE_HOME/network/admin;export TNS_ADMIN
NLS_LANG=AMERICAN_AMERICA.AL32UTF8; export NLS_LANG
SQLPATH=/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin;export SQLPATH
stty erase ^H
export PATH
--二.安装数据库软件
--解压软件包
exit
mv /opt/p* /u01/app/oracle/soft
ll /u01/app/oracle/soft/
chown -R oracle:oinstall /u01/app/oracle/soft
su - oracle
cd /u01/app/oracle/soft
unzip p13390677_112040_Linux-x86-64_1of7.zip
unzip p13390677_112040_Linux-x86-64_2of7.zip
cd database/
--若用xshell调用远程界面 需要设置 DISPLAY variable
su -
xhost +
su - oracle
export DISPLAY=192.168.92.2:0
--2.1安装数据库软件 静默方式
/*
--配置响应文件respons file
[root@bspdev ~]# cd /etc
[root@bspdev etc]# vi oraInst.loc
inventory_loc=/u01/app/oracle
inst_group=oinstall
~
[root@bspdev etc]# chown oracle:oinstall oraInst.loc
[root@bspdev etc]# chmod 664 oraInst.loc
注意：这个步骤在GUI版本时候，是不强制的。
我们首先需要安装Oracle软件，创建单独的响应文件。
[oracle@bspdev response]$ cp enterprise.rsp creat.rsp
使用vi打开编辑，修改各种选项。模板响应文件设置的很好，每个选项都提供了详细的解释和设置法则。我们可以根据自己的需要进行修改，例如：
#------------------------------------------------------------------------------
#Name : UNIX_GROUP_NAME
#Datatype : String
#Description: Unix group to be set for the inventory directory.
#Example : UNIX_GROUP_NAME = "install"
#------------------------------------------------------------------------------
UNIX_GROUP_NAME=ointall
在配置过程中，同GUI安装Oracle Software一样，我们是可以选择在安装Software过程中创建数据库的。注意，一般我们不推荐这样做。我们本篇中单独有介绍dbca的响应文件使用部分。
之后，我们就可以启动runInstaller程序进行安装。
[oracle@bspdev database]$ ./runInstaller -silent -force -noconfig-responseFile /upload/database/response/creat.rsp
*/
./runInstaller -silent -debug -force \
FROM_LOCATION=/u01/app/oracle/soft/database/stage/products.xml \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oracle/oraInventory \
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
DECLINE_SECURITY_UPDATES=true
--安装完成后以root用户运行
/oracle/app/oraInventory/orainstRoot.sh
/oracle/app/oracle/product/12.1.0/db_1/root.sh
2.2 数据库patch安装
mv /u01/app/oracle/soft/p6880880_112000_Linux-x86-64.zip $ORACLE_HOME/
mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_BAK
cd $ORACLE_HOME
unzip p6880880_112000_Linux-x86-64.zip
cd /u01/app/oracle/soft
unzip p24006111_112040_Linux-x86-64.zip
cd 24006111
/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
--打补丁
/u01/app/oracle/product/11.2.0/db_1/OPatch/opatch apply
--打补丁选项
Do you want to proceed? [y|n] 键入y
Email address/User Name: 回车键
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: 键入y
Is the local system ready for patching? [y|n] 键入y
--三. DBCA建库
--如果dbca静默建库
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ogg1 -sid ogg1 -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
--注意相关数据库参数设置
sga:10240M
pga:2048M
process:800
归档路径:/u01/archive
字符集:AMERICAN_AMERICA.AL32UTF8
redo:四组每组100M
--四.手工配置监听
vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mysql-92.34)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER = OFF
--启动监听
lsnrctl start
--1. 如果用手工建库
--建立audit_dump等所有目录：
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/oradata/orcl
mkdir -p /u01/app/oracle/oradata/orcl/disk1
mkdir -p /u01/app/oracle/oradata/orcl/disk2
mkdir -p /u01/app/oracle/oradata/orcl/disk3
--需要创建/u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
--手工建后可以执行dbca
vi /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/db_1:Y
--建立password file：
orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl force=y password=oracle ignorecase=n
--编辑初始化参数文件： 当数据库内存为1G时 11G2040的表空间变为undotbs1
--生产环境pfile参数 需要创建/u01/app/oracle/fast_recovery_area存放control02.ctl
--control02.ctl可以设置在闪回区 当数据库名用小写且归档设置在闪回区时归档会存放在大写的SID下
vi /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
orcl.__db_cache_size=641728512
orcl.__java_pool_size=2097152
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=134217728
orcl.__sga_target=859832320
orcl.__shared_io_pool_size=67108864
orcl.__shared_pool_size=134217728
orcl.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=30
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.instance_name=orcl
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.open_links=100
*.open_links_per_instance=100
*.pga_aggregate_target=134217728
*.sga_max_size=859832320
*.processes=800
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_target=859832320
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
--启动实例到nomount状态：字符集 AL32UTF8 国家字符集 AL16UTF16
sqlplus / as sysdba
startup nomount
--手工建库过程中不能指定sga和pga的大小
--数据库名应该用大写，否则影响：若启用fast_recovery_dest时，归档目录仍创建大写的对应文件夹名
CREATE DATABASE orcl
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 40
MAXLOGMEMBERS 5
MAXDATAFILES 200
DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 300M AUTOEXTEND ON MAXSIZE 2G
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 250M AUTOEXTEND ON MAXSIZE 2G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 2G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 2G
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/orcl/disk1/redo01a.log',
'/u01/app/oracle/oradata/orcl/disk2/redo01b.log',
'/u01/app/oracle/oradata/orcl/disk3/redo01c.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/orcl/disk1/redo02a.log',
'/u01/app/oracle/oradata/orcl/disk2/redo02b.log',
'/u01/app/oracle/oradata/orcl/disk3/redo02c.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/orcl/disk1/redo03a.log',
'/u01/app/oracle/oradata/orcl/disk2/redo03b.log',
'/u01/app/oracle/oradata/orcl/disk3/redo03c.log') SIZE 100M
USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle;
/*
--若建库出错后删除了数据库的文件不能startup或create database
shutdown immediate
startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
*/
--执行相关的脚本： catalog.sql catproc.sql 比较花时间 无执行顺序区别 也可以按照onlinhelp上面执行，也可以不执行
--官方文档执行catalog.sql catproc.sql pupbld.sql
conn / as sysdba
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catblock.sql;
--忽略drop table的报错
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catproc.sql;--cost long time
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/owminst.plb;
connect system/oracle
@/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/pupbld.sql;
@/u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
--重编译失效对象：若报错找不到recomp_serial 应该以sys用户执行
conn / as sysdba
execute utl_recomp.recomp_serial();
create spfile from pfile;
shutdown immediate;
startup;
--切换日志到归档目录
alter system switch logfile;
/
--参考 从pfile启动oracle
--startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
--五.修改数据库参数
--修改数据库参数(其他参数必须在dbca中已经改了sga,pga dbfile redo size processes archivelog)
su - oracle
mkdir /u01/app/oracle/archivelog
sqlplus / as sysdba
alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';
alter profile default limit password_life_time unlimited;
alter system set deferred_segment_creation=false;
alter system set open_links=100 scope=spfile;
alter system set open_links_per_instance=100 scope=spfile;
alter system set audit_trail=none scope=spfile;
alter system set control_file_record_keep_time=30 scope=spfile;
alter system set undo_retention=10800;
alter system set db_recovery_file_dest_size=10240m;
shutdown immediate;
startup;
/*
alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' resize 8G;
alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' autoextend off;
alter tablespace undotbs1 add datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 8G autoextend off;
alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 8G;
alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend off;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 8G autoextend off;
*/
--如果是手工建库 另外更改的参数
--六．部署数据
--6.1创建directory
--查询源端目录
select * from dba_directories;
--查询源端字符集
select * from nls_database_parameters;
--查询源端用户对应的表空间
select username,default_tablespace from dba_users order by 1;
--导出源端数据
create tablespace sde datafile '/u01/app/oracle/oradata/orcl/sde.dbf' size 100m autoextend off extent management local segment space management auto;
expdp test/test.2017@orcl directory=dumpdir dumpfile=test20170527.dmp logfile=test20170527.log
mkdir –pv /u01/dumpdir
sqlplus / as sysdba
create directory dumpdir as '/u01/dumpdir';
grant read,write on directory dumpdir to sys;
6.2创建和源端用户同名的表空间
create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 8G autoextend off;
alter tablespace test add datafile '/u01/app/oracle/oradata/orcl/test02.dbf' size 8G autoextend off;
6.3 导入数据 可能版本不同需要先创建用户（logfile日志中会有错误信息提示）
impdp \'sys/Sendinfo2017@orcl as sysdba\' directory=dumpdir dumpfile=hht20170527.dmp logfile=hht20170527.log
--若expdp不成功，则先创建用户
--目标端创建用户并配置权限
sqlplus / as sysdba
create user test identified by test default tablespace sde;
grant connect to test;
grant create indextype to test;
grant create job to test;
grant create materialized view to test;
grant create procedure to test;
grant create public synonym to test;
grant create sequence to test;
grant create session to test;
grant create table to test;
grant create trigger to test;
grant create type to test;
grant create view to test;
--授予私有database link权限
grant create database link to test;
grant unlimited tablespace to test;
--权限检查
conn gglcxticketbd/gglcxticketbd@orcl
--查看当前用户的角色和权限
select * from user_role_privs;
select * from session_privs;
--查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
--查询警告信息
SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
PACKAGE BODY | TRIGGER | VIEW
| TYPE | TYPE BODY | DIMENSION
| JAVA SOURCE | JAVA CLASS } [schema.]name]
show error procedure test."NEXT_VAL"
show error package test.PDTYPES
select text from dba_source t where t.name='NEXT_VAL';
select text from dba_source t where t.name='PDTYPES';
--查询用户的默认表空间是否正确
select username,default_tablespace from dba_users order by 1;
--查询数据
select t.supply_name from test.order_info t where rownum<=10;
--七.修改默认统计信息时间
--查询默认job时间，修改完可查询一下
set line 500;
set pagesize 100;
col window_name format A50;
col REPEAT_INTERVAL format A50;
col duration format A50;
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SATURDAY_WINDOW"',
force=>TRUE);
END;
/
--1200->240
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(240, 'minute'));
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0');
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SATURDAY_WINDOW"');
END;
/
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SUNDAY_WINDOW"',
force=>TRUE);
END;
/
--1200->240
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SUNDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(240, 'minute'));
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SUNDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=SUN;byhour=22;byminute=0; bysecond=0');
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SUNDAY_WINDOW"');
END;
/
--八.部署脚本
--备库部署脚本时需要更改对应的ORACLE_SID,备库直读模式不支持expd
su - root
service ntpd stop
chkconfig ntpd off
crontab -e
*/5 * * * * /usr/sbin/ntpdate 192.168.92.33>/tmp/ntp.log 2>&1
--以下不用添加
服务器一： 1.cn.pool.ntp.org
服务器二： 2.asia.pool.ntp.org
服务器三： 3.asia.pool.ntp.org
su - oracle
crontab -e
00 00 * * * /bin/bash /u01/scripts/by_listener.sh >/u01/scripts/by_listener.log 2>&1
30 03 * * * /bin/bash /u01/scripts/rman_backup.sh >/u01/scripts/rman_backup.log 2>&1
00 01 * * * /bin/bash /u01/scripts/expdpuser.sh >/u01/scripts/expdpuser.log 2>&1
00 01 * * * /bin/bash /u01/scripts/del_arc.sh >/u01/scripts/del_arc.log 2>&1
chmod 755 del_arc.sh
chmod 755 by_listener.sh
chmod 755 expdpuser.sh
chmod 755 rman_backup.sh
--创建相关目录
mkdir –pv /u01/scripts
mkdir –pv /u01/backupsets
-------------------------------------------------------------------------------------------------------
--1. rman_backup.sh
##################################################################
## V_2011-2-18 ##
##################################################################
#!/bin/ksh
export LANG=en_US
BACKUP_DATE=`date +%d`
RMAN_LOG_FILE=/u01/scripts/rmanbackup.out
TODAY=`date`
USER=`id|cut -d "(" -f2|cut -d ")" -f1`
echo "-----------------$TODAY-------------------">$RMAN_LOG_FILE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
RMAN=$ORACLE_HOME/bin/rman
export RMAN
ORACLE_SID=orcl
export ORACLE_SID
ORACLE_USER=oracle
export ORACLE_USER
echo "ORACLE_SID: $ORACLE_SID">>$RMAN_LOG_FILE
echo "ORACLE_HOME:$ORACLE_HOME">>$RMAN_LOG_FILE
echo "ORACLE_USER:$ORACLE_USER">>$RMAN_LOG_FILE
echo "==========================================">>$RMAN_LOG_FILE
echo "BACKUP DATABASE BEGIN......">>$RMAN_LOG_FILE
echo " ">>$RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE
WEEK_DAILY=`date +%a`
case "$WEEK_DAILY" in
"Mon")
BAK_LEVEL=0
;;
"Tue")
BAK_LEVEL=0
;;
"Wed")
BAK_LEVEL=0
;;
"Thu")
BAK_LEVEL=0
;;
"Fri")
BAK_LEVEL=0
;;
"Sat")
BAK_LEVEL=0
;;
"Sun")
BAK_LEVEL=0
;;
"*")
BAK_LEVEL=error
esac
export BAK_LEVEL=$BAK_LEVEL
echo "Today is : $WEEK_DAILY incremental level= $BAK_LEVEL">>$RMAN_LOG_FILE
RUN_STR="
BAK_LEVEL=$BAK_LEVEL
export BAK_LEVEL
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN nocatalog TARGET / msglog $RMAN_LOG_FILE append <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset incremental level= $BAK_LEVEL skip inaccessible filesperset 6 Database format='/u01/backupsets/orcl_lev"$BAK_LEVEL"_%U_%T' tag='orcl_lev"$BAK_LEVEL"' ;
sql 'alter system archive log current';
backup archivelog all tag='arc_bak' format='/u01/backupsets/arch_%U_%T' skip inaccessible filesperset 6 not backed up 1 times delete input;
backup current controlfile tag='bak_ctlfile' format='/u01/backupsets/ctl_file_%U_%T';
backup spfile tag='spfile' format='/u01/backupsets/orcl_spfile_%U_%T';
release channel c2;
release channel c1;
}
allocate channel for maintenance device type disk;
crosscheck backup;
delete noprompt expired backup;
report obsolete;
delete noprompt obsolete;
list backup summary;
release channel;
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
echo "Root Command String: $RUN_STR" >> $RMAN_LOG_FILE
su - $ORACLE_USER -c "$RUN_STR" >> $RMAN_LOG_FILE
RSTAT=$?
else
echo "User Command String: $RUN_STR" >> $RMAN_LOG_FILE
/bin/sh -c "$RUN_STR" >> $RMAN_LOG_FILE
RSTAT=$?
fi
# ---------------------------------------------------------------------------
# Log the completion of this script.
# ---------------------------------------------------------------------------
if [ "$RSTAT" = "0" ]
then
LOGMSG="ended successfully"
else
LOGMSG="ended in error"
fi
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
#/u01/scripts/upload_rman.sh
exit $RSTAT
---------------------------------------------------------------
--2. expdpuser.sh by zhuyj保留最近2天的dmp备份 启用压缩和并行 全schema
##################################################################
## V_2016 ##
##################################################################
#删除2天前的文件
#find /root/test/ -name *.dmp -mtime +2 -exec rm -rf {} \;
#find /root/test/ -name *.log -mtime +2 -exec rm -rf {} \;
#!/bin/bash
BAK_DIR=/u01/dumpdir
DUMPDIR=dumpdir
FTP_HOST=x.x.x.x
FTP_USER=db_bak
FTP_PASS="xxxxxx"
FILE_NAME=orcl`date +%Y%m%d`.dmp
LOG_NAME=orcl`date +%Y%m%d`.log
SCHEMAS=orcl
REV_DATE=+7
source /home/oracle/.bash_profile
#begin start
expdp \"/ as sysdba\" directory="$DUMPDIR" dumpfile="$FILE_NAME" logfile="$LOG_NAME" full=y compression=all parallel=4
#ftp upload
#cd $BAK_DIR
#echo "Begin start Ftp Trans"
#ftp -n -i $FTP_HOST <<EOF
#user $FTP_USER $FTP_PASS
#passive
#prompt
#prompt
#mput $FILE_NAME
#quit
#EOF
#echo $?
#delete expire file
if [ $? -eq 0 ];then
find ${BAK_DIR:=/tmp} -type f -name "*.dmp" -mtime $REV_DATE |xargs rm -rvf
find ${BAK_DIR:=/tmp} -type f -name "*.log" -mtime $REV_DATE |xargs rm -rvf
echo "Backup Success"
else
echo "Backup fail please check log!!!"
exit 1
fi
---------------------------------------------------------------
--3. by_listener.sh
####################################################
## Date:2016-01-01 ##
####################################################
#!/bin/bash
source /home/oracle/.bash_profile
listenlog=$ORACLE_HOME/network/log
oldlistener=listenerold`date +%Y%m%d`.log
rev_date=15
lsnrctl set log_status off
cd $listenlog
mv listener.log $oldlistener
lsnrctl set log_status on
find ${listenlog:=/tmp} -type f -name "listenerold*.log" -mtime +$rev_date |xargs rm -rvf
---------------------------------------------------------------
--3. by_listener.sh
--先检查归档日志，把物理删除的信息从控制文件删除,然后执行物理删除
vi /u01/scripts/del_arc.sh
#! /bin/bash
source ~/.bash_profile
#记录归档删除的日志
exec >> /u01/scripts/del_arch`date +%F-%H`.log
$ORACLE_HOME/bin/rman target / <<EOF
#检查归档
crosscheck archivelog all;
#删除无效归档
delete noprompt expired archivelog all;
#删除3天前的归档
delete noprompt archivelog until time 'sysdate-3';
#删除所有的归档
#delete noprompt archivelog all;
exit;
EOF
---------------------------------------------------------------
--5. upload_rman.sh
##################################################################
## DESCRIPTION: upload_rman.sh ##
## Author:Netdata ##
## Author:2016-05-23 ##
##################################################################
#!/bin/bash
BACKUP_DIR=/oradata/backupsets
ORACLE_SID=orcl
REV_DATE=+7
FTP_HOST=10.46.20.134
FTP_USER=db_bak
FTP_PASS="t<pO86ugfpuzInhvpzwt"
FILE_NAME="$ORACLE_SID"_rman_`date +%Y%m%d`.tar.bz2
TAR_FILE=*`date +%Y%m%d`
#tar rman backupsets
cd $BACKUP_DIR
tar -jcvf $FILE_NAME $TAR_FILE
if [ $? -eq 0 ];then
#ftp upload
cd $BACKUP_DIR
echo "Begin start Ftp Trans"
ftp -n -i $FTP_HOST <<EOF
user $FTP_USER $FTP_PASS
passive
prompt
prompt
mput $FILE_NAME
quit
EOF
else
echo "Upload File please check log!!!"
exit 1
fi
#delete rev_date_file
if [ $? -eq 0 ];then
find ${BACKUP_DIR:=/tmp} -type f -name "*.tar.bz2" -mtime $REV_DATE |xargs rm -rvf
echo "Upload Success"
else
echo "Upload fail please check log!!!"
exit 1
fi
-----------------------------------------------------------------------------------
以下操作用root用户登录
1、编辑 /etc/oratab，把所有的 instance 的重启动标志设置成 'Y'，如:
orcl:/u01/app/oracle/product/11.2.0/db_1:Y
2、做一个启动脚本 /etc/init.d/oracle ，如下所示:
#!/bin/sh
# chkconfig: 345 99 10
# description: script for the Oracle Instance, Listener
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/u01/app/oracle/product/11.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
;;
'restart')
$0 stop
$0 start
;;
esac
在脚本中以下两行必须存在
# chkconfig: 345 99 10
# description: script for the Oracle Instance, Listener
------------------备注说明---------------------------------
# chkconfig: 345 99 10
指出3，4，5级别启动这个服务，99是在相应的/etc/rc.d/rcN.d(N为前面指定的
级别，这里是345)目录下生成的链接文件的序号(启动优先级别)S99oracle，
10为在除前面指出的级别对应的/etc/rc.d/rcN.d(N为除345之外的级别)目录生成的
链接文件的序号(服务停止的优先级别)K10oracle
3、赋予执行权限
chmod 751 /etc/init.d/oracle
4、添加服务
chkconfig --add oracle
chkconfig --list oracle
--chkconfig --del oracle
5、启动服务
添加服务服务后，不会立即启动服务，如果需要立即启动服务，需要执行
service oracle start #启动服务
service oracle stop #停止服务
service oracle restart #重启服务
-----------------------------------------------------------------------------------
--生产环境pfile配置 mem=8G 定制内存 sga:pga=4:1
/*
[oracle@HHTDB01-238-31 dbs]$ more initorcl.ora |more
orcl.__db_cache_size=5133828096
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1073741824
orcl.__sga_target=6878658560
orcl.__shared_io_pool_size=536870912
orcl.__shared_pool_size=1073741824
orcl.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=30
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/f
ast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.instance_name=orcl
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.open_links=100
*.open_links_per_instance=100
*.pga_aggregate_target=1073741824
*.processes=800
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=885
*.sga_target=6871318528
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
--若需要更改参数
su - oracle
mkdir /u01/app/oracle/fast_recovery_area
shutdown immediate
startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
create spfile from pfile;
shutdown immediate
startup
*/
