Oracle 静默安装-单机

一、前期准备
1.硬件检查

cat /etc/issue
uname -r (版本)
grep MemTotal /proc/meminfo (内存大小)
grep SwapTotal /proc/meminfo (交换区大小)
grep "model name" /proc/cpuinfo (CPU信息)
free (可用内存)

2.检查依赖包

[root@dgc ~]# rpm -q binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc-2.5 glibc-common glibc-devel glibc-headers ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel
binutils-2.20.51.0.2-5.42.el6.x86_64
package compat-libstdc++-33 is not installed
elfutils-libelf-0.158-3.2.el6.x86_64
package elfutils-libelf-devel is not installed
gcc-4.4.7-11.el6.x86_64
package gcc-c++ is not installed
package glibc-2.5 is not installed
glibc-common-2.12-1.149.el6.x86_64
glibc-devel-2.12-1.149.el6.x86_64
glibc-headers-2.12-1.149.el6.x86_64
package ksh is not installed
libaio-0.3.107-10.el6.x86_64
package libaio-devel is not installed
libgcc-4.4.7-11.el6.x86_64
libstdc++-4.4.7-11.el6.x86_64
package libstdc++-devel is not installed
make-3.81-20.el6.x86_64
sysstat-9.0.4-27.el6.x86_64
unixODBC-2.2.14-14.el6.x86_64
package unixODBC-devel is not installed

3.创建所需的操作系统组和用户

groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba oracle
passwd oracle

4.创建安装目录及权限

mkdir -p /app/oracle
chown -R oracle.oinstall /app/oracle

5.修改内核参数

vi /etc/sysctl.conf 
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
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 = 1048576
sysctl -p

6.修改用户限制

vi /etc/security/limits.conf
#for oracle
oracle           soft    nproc             2047
oracle           hard    nproc           16384
oracle           soft    nofile             1024
oracle           hard    nofile            65536

vi /etc/pam.d/login
session required /lib/security/pam_limits.so

7.创建/etc/oraInst.loc文件,内容如下

nventory_loc=/app/oracle/oraInventory
inst_group=oinstall

更改文件的权限

chown oracle:oinstall /etc/oraInst.loc
chmod 664 /etc/oraInst.loc

8.设置oracle环境变量

vi /home/oracle/.bash_profile
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11204/db
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022

9.使设置生效

source /home/oracle/.bash_profile

10.配置hosts文件

vi /etc/hosts
10.102.98.231   adgc

注:
同时,在使用静默安装的时候会检查DISPLAY的设置,如果经常说检查DISPLAY不行的话,到root用户下执行 “xhost + 你的ip:0.0”
二、实施部署
1.解压oracle安装文件

unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

2.复制响应文件模板,设置响应文件权限(注意调整个人环境路径 )

mkdir $ORACLE_BASE/etc
cp /backup/software/database/response/* /app/oracle/etc/
su - root
chmod 700 /app/oracle/etc/*.rsp(注意所有者为 oinstall)

3.静默安装Oracle软件
(1)修改安装Oracle软件的响应文件/app/oracle/etc/db_install.rsp (可根据里面的配置提示来操作,下面是一些常用配置,作为参考)

oracle.install.option=INSTALL_DB_SWONLY // 安装类型
ORACLE_HOSTNAME=dgc // 主机名称(hostname查询)
UNIX_GROUP_NAME=oinstall // 安装组
INVENTORY_LOCATION=/app/oracle/oraInventory //INVENTORY目录(不填就是默认值)
SELECTED_LANGUAGES=en,zh_CN,zh_TW // 选择语言
ORACLE_HOME=/app/oracle/product/11204/db_1 // oracle_home
ORACLE_BASE=/app/oracle // oracle_base
oracle.install.db.InstallEdition=EE // oracle版本
oracle.install.db.isCustomInstall=false //自定义安装,否,使用默认组件
oracle.install.db.DBA_GROUP=dba // dba用户组
oracle.install.db.OPER_GROUP=oinstall // oper用户组
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE //数据库类型
oracle.install.db.config.starterdb.globalDBName=orcl //globalDBName
oracle.install.db.config.starterdb.SID=orcl //SID
oracle.install.db.config.starterdb.memoryLimit=32768 //自动管理内存的内存(M)
oracle.install.db.config.starterdb.password.ALL=oracle //设定所有数据库用户使用同一个密码

注:
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false(手动写了false)
DECLINE_SECURITY_UPDATES=true  //设置安全更新(貌似是有bug,这个一定要选true,否则会无限提醒邮件地址有问题,终止安装。PS:不管地址对不对)

(2)开始静默安装

[root@dgc database]# su - oracle
[root@dgc oracle]# cd /backup/software/database/
[oracle@dgc database]$ ./runInstaller -silent -responsefile /app/oracle/etc/db_install.rsp 
正在启动 Oracle Universal Installer...

检查临时空间: 必须大于 120 MB。   实际为 725253 MB    通过
检查交换空间: 必须大于 150 MB。   实际为 32279 MB    通过
准备从以下地址启动 Oracle Universal Installer /tmp/OraInstall2018-03-12_06-24-28PM. 请稍候...[oracle@dgc database]$ [WARNING] [INS-13014] 目标环境不满足一些可选要求。
   原因: 不满足一些可选的先决条件。有关详细信息, 请查看日志。/app/oracle/oraInventory/logs/installActions2018-03-12_06-24-28PM.log
   操作: 从日志 /app/oracle/oraInventory/logs/installActions2018-03-12_06-24-28PM.log 中确定失败的先决条件检查列表。然后, 从日志文件或安装手册中查找满足这些先决条件的适当配置, 并手动进行修复。
可以在以下位置找到本次安装会话的日志:
 /app/oracle/oraInventory/logs/installActions2018-03-12_06-24-28PM.log
Oracle Database 11g 的 安装 已成功。
请查看 '/app/oracle/oraInventory/logs/silentInstall2018-03-12_06-24-28PM.log' 以获取详细资料。

以 root 用户的身份执行以下脚本:
        1. /app/oracle/product/11204/db_1/root.sh

Successfully Setup Software.

(3)以 root 用户的身份执行脚本/app/oracle/product/11204/db_1/root.sh
(4)另一种方式,直接使用命令加参数方式安装

$ ./runInstaller -silent -debug -force \
FROM_LOCATION=/backup/software/database/stage/products.xml \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/app/oracle/oraInventory \
ORACLE_HOME=/app/oracle/product/11204/db_1 \
ORACLE_HOME_NAME="OraDb11g_Home" \
ORACLE_BASE=/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

… … 
结束时会出现
Oracle Database 11g 的 安装 已成功。
请查看 '/app/oracle/oraInventory/logs/silentInstall2018-03-12_05-28-14PM.log' 以获取详细资料。

(5)以 root 用户的身份执行脚本/app/oracle/product/11204/db_1/root.sh

[root@dgc tmp]#  /app/oracle/product/11204/db_1/root.sh
Check /app/oracle/product/11204/db_1/install/root_dgc_2018-03-12_17-31-43.log for the output of root script

4.静默安装DB
(1)编辑配置文件dbca.rsp (可根据里面的配置提示来操作,下面是一些常用配置,作为参考)

[GENERAL]
RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]
GDBNAME = "orcl"
SID = "orcl"
TEMPLATENAME = "General_Purpose.dbc"  /*根据下面的模板来设定*/
CHARACTERSET = "AL32UTF8"  /* Default value US7ASCII */

(2)安装DB

[oracle@dgc ~]$ dbca -silent -responsefile /app/oracle/etc/dbca.rsp 

报错: 模板 new_orcl.dbc 不存在。请为数据库创建操作指定现有模板。

安装模版所在位置:

[oracle@dgc ~]$ cd /app/oracle/product/11204/db_1/assistants/dbca/templates
[oracle@dgc templates]$ ls
Data_Warehouse.dbc  example.dmp          New_Database.dbt   Seed_Database.dfb
example01.dfb       General_Purpose.dbc  Seed_Database.ctl

[oracle@dgc etc]$ dbca -silent -responsefile /app/oracle/etc/dbca.rsp 
输入 SYS 用户口令: 
 
输入 SYSTEM 用户口令: 
  
复制数据库文件
1% 已完成
3% 已完成
11% 已完成
18% 已完成
26% 已完成
37% 已完成
正在创建并启动 Oracle 实例
40% 已完成
45% 已完成
50% 已完成
55% 已完成
56% 已完成
60% 已完成
62% 已完成
正在进行数据库创建
66% 已完成
70% 已完成
73% 已完成
85% 已完成
96% 已完成
100% 已完成
有关详细信息, 请参阅日志文件 "/app/oracle/cfgtoollogs/dbca/orcl/orcl.log"。

5.安装配置网络监听
(1)安装监听

[oracle@dgc etc]$ netca -silent -responsefile /app/oracle/etc/netca.rsp 

正在对命令行参数进行语法分析:
参数"silent" = true
参数"responsefile" = /app/oracle/etc/netca.rsp
完成对命令行参数进行语法分析。
Oracle Net Services 配置:
完成概要文件配置。
Oracle Net 监听程序启动:
    正在运行监听程序控制: 
      /app/oracle/product/11204/db_1/bin/lsnrctl start LISTENER
    监听程序控制完成。
    监听程序已成功启动。
监听程序配置完成。
成功完成 Oracle Net Services 配置。退出代码是0

(2)检查监听状态

[oracle@dgc etc]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2018 19:50:19

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                12-MAR-2018 19:49:58
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11204/db_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/dgc/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgc)(PORT=1521)))
The listener supports no services
The command completed successfully

(3)增加tnsnames.ora文件

[oracle@dgc admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dgc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

(4)测试连接

[oracle@dgc admin]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 12 23:00:54 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dgc admin]$ 

(5)检查并修改数据库参数(根据情况而定)
例如: memory_max_target, sga_max_size, processes, pga_aggregate_target 等等

SYS@orcl > alter system set processes=1000 scope=spfile;
System altered.

SYS@orcl >  alter system set sga_max_size=20g scope=spfile;
System altered.

SYS@orcl > alter system set sga_target=20g scope=spfile;
System altered.

SYS@orcl > alter system set pga_aggregate_target=6g scope=spfile;
System altered.

SYS@orcl > alter system set nls_time_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
System altered.

三、总结
安装结束了,早就想做这个实验了,就是一直犯懒,2018年不能再这样懒下去了。静默安装分为3步,分别是安装软件,建立数据库,配置监听。个人感觉分开弄比较好,也是前辈提倡的步骤,因为这样可以在出现问题时,很容易定位,并且做好一步是一步,比如软件安装好了,建立数据库时出错时,软件就不用重新安装了,只是找到原因,重新建立数据库即可。还有就是可以选择使用rsp响应文件或者直接命令方式,后面直接写上想着配置参数。最后一点就是要注意在安装后记得检查修改参数,因为一但数据库投入运行再发现参数不正确,修改就显得被动了。最后还是给自己打打气,2017未实现的,希望能在2018实现,加油!Where there is a will there is a way.

 

发表在 Basic, oracle | 标签为 , | 留下评论

【Partition table】分区表删除分区数据时,导致索引失效

一、描述
今天有个小任务就是要删除些数据,哈哈,先自己小开心一下。因为要删除的数据表是我之前转换成的分区表。这个分区表是按照里面有个创建时间字段来分区的,1个季度为1个分区。所以我现在要将2017年7月1日之前的数据删除(数据量约1000万),可以直接删除表分区数据就好。如果要是用delete去删除这么多的数据,我还要写存储过程,分批提交的这样做。就是这样的一简单的truncate partition 引发了后继的业务故障。最终查询到该表的索引失效,重建立后恢复。真是汗!

二、实验
1.创建环境

SQL> create table TEST_PARTAS (id number(11), ACCOUNT_ID number(11) ,CTIME date) 
  2  partition by range (CTIME)
  3  interval( NUMTOYMINTERVAL(3,'month'))
  4  (partition P0 values less than (TO_DATE('2016-01-01','yyyy-mm-dd')),
  5   partition p1 values less than (to_date('2017-01-01','yyyy-mm-dd')));

Table created.

SQL>  insert into TEST_PARTAS select t.id,t.account_id,t.create_time from act_test t;
3483178 rows created.

SQL> commit;
Commit complete.

SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS('SAM','TEST_PARTAS');
PL/SQL procedure successfully completed.

2.检查分区表及数据

SQL> select count(*) from TEST_PARTAS;

  COUNT(*)
----------
   3483178

SQL> set lines 120 pages 200;
SQL> set long 9999999
SQL> col table_name for a15
SQL> col PARTITION_NAME for a10

SQL> select t.table_name,t.partition_name,t.num_rows,t.blocks,t.interval,t.high_value from USER_TAB_PARTITIONS t;  

TABLE_NAME      PARTITION_   NUM_ROWS     BLOCKS INT HIGH_VALUE
--------------- ---------- ---------- ---------- --- --------------------------------------------------
TEST_PARTAS     P0            2182116       6046 NO  TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     P1             616290      36506 NO  TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1611       44829       4030 YES TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1612       21706       3022 YES TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1613      172525       3022 YES TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1614      442435       2014 YES TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1615        3277        238 YES TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')


7 rows selected.

3.创建主键和索引

SQL> alter table TEST_PARTAS add constraint pk_id primary key(ID);
Table altered.

SQL> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID);
Index created.

4.检查索引状态,当前状态可用

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    VALID
IND_ACCOUNT_ID                 TEST_PARTAS                    VALID

5.用truncate 删除p0分区数据,不加update index参数

SQL> alter table test_partas truncate partition p0;

Table truncated.

6.检查索引状态,状态不可用

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    UNUSABLE
IND_ACCOUNT_ID                 TEST_PARTAS                    UNUSABLE

7.重建立索引,要加online ,尽量减小对业务的冲

SQL> alter index PK_ID rebuild online;

Index altered.

SQL> alter index IND_ACCOUNT_ID rebuild online;

Index altered.

8.检查索引状态,此时索引恢复正常可用状态

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    VALID
IND_ACCOUNT_ID                 TEST_PARTAS                    VALID

9.用truncate 删除p1分区数据,增加update index参数

SQL> alter table test_partas truncate partition p1 update indexes;

Table truncated.

10.检查索引状态,此时索引正常可用状态

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    VALID
IND_ACCOUNT_ID                 TEST_PARTAS                    VALID

三、扩展
通过这个问题,我们再扩展一下,如果drop分区会不会同样影响索引,答案是肯定的,删除分区,索引仍然失效。

SQL> alter table test_partas drop partition SYS_P1611;

Table altered.

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    UNUSABLE
IND_ACCOUNT_ID                 TEST_PARTAS                    UNUSABLE

四、总结
一个小小的失误,带来了大大的问题,还好这次操作,影响的不是核心业务表。通过失误,也让我看到了自己对知识点掌握上的不足。以后的路还很远,振作起来,努力学习吧。让自己在后面的DB生涯中,少范错误,多多提高效率。没有什么比学习与进步更快乐,In the end, Happy spring festival 2018 to everyone.

 

发表在 Basic, oracle | 标签为 , | 留下评论

【ERROR】rm删除时,参数列表过长,及处理方法

一、问题描述
由于日志过多,将磁盘空间占满,处理时碰到参数列表过报错。主要原因是要删除的文件数量过大导致。需要采用 find […] | xargs rm -rf […] 方式来处理。

二、故障处理
1.删除过程中报错

[oracle@zw-oradb02 adump]$ rm -rf *.aud
-bash: /bin/rm: 参数列表过长

2.修改命令行

[oracle@zw-oradb02 adump]$ find . -name "*.aud" |xargs rm -rf "*.aud”

三、总结
如果天天都出这样的小问题,每个都能掌握起来,不久的将来也可以成长为IT精英小人物。事上无难事,只怕有心人,让小事情来得再猛烈些,哈哈。Where there is a will there is a way.

 

发表在 ERROR, system | 标签为 , | 留下评论

【Mysql】连接数过多,应急处理方法

一、问题描述
今天突然接到个问题,网页报错:503 Service Temporarily Unavailable。经过查询发现是某个用户的连接超级多,已经将数据库连接占满。处理方案,即时杀掉堵塞的进程,之后可以扩大max_connections参数。

ebd429bc-1a22-49d6-8264-236056c9770c

二、处理方法
1.查询连接情况

root@localhost > show processlist;
…...
1001 rows in set (0.00 sec)
root@localhost > show variables like '%proces%';
Empty set (0.00 sec)

2.检查参数

root@localhost > show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1001  |
+----------------------+-------+
1 row in set (0.00 sec)

3.通过命令生成杀进程脚本

root@localhost > select concat('KILL ',id,';') from information_schema.processlist where user=’sam' into outfile '/tmp/a.txt';
Query OK, 991 rows affected (0.02 sec)

脚本内容如下:

+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 31964612;         |
| KILL 31964609;         |
| KILL 31964611;         |
…...
| KILL 31966619;         |
| KILL 31966620;         |
+------------------------+
991 rows in set (0.02 sec)
root@localhost > 

4.执行上面生成的KILL脚本

root@localhost > source /tmp/a.txt
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
……

5.检查连接状况,恢复正常

root@localhost > show processlist;

6.修改Max_used_connections参数(注:记得要修改my.cnf文件,下次重启动后仍然有效)

mysql> set GLOBAL max_connections=2000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

三、总结
Mysql的参数学习之max_connections,一个控制连接数的参数。此问题背后肯定存在着某些问题,不要只是一味地调大参数。后来经过对语句的分析,最终此问题定位为安全部门在做安全测试,导致问题产生。2017年只剩下最后1周了,提前祝大家元旦快乐。Happy every day.

 

发表在 mysql | 标签为 | 留下评论

Oracle Function — wmsys.wm_concat 合并行记录函数

一、描述
小需求又来了,现在需要将某表上的记录查询出来并且要求连接使用。通过查找后发现Oracle有个内部函数 wm_concat,这个函数在wmsys用户下。使用时那是当然的容易。一般人我都不告诉他(开个小玩笑)。

二、实验
1.创建实验表

SAM@orcl > create table t1 (id int, name varchar2(10));
Table created.

SAM@orcl > insert into t1 values (1,'sam1');
1 row created.

SAM@orcl > insert into t1 values (2,'sam2');
1 row created.

SAM@orcl > insert into t1 values (3,'sam3');
1 row created.

SAM@orcl > insert into t1 values (4,'sam4');
1 row created.

SAM@orcl > insert into t1 values (5,'sam5');
1 row created.

SAM@orcl > commit;
Commit complete.

SAM@orcl > select * from t1;

        ID NAME
---------- ----------
         1 sam1
         2 sam2
         3 sam3
         4 sam4
         5 sam5

2.使用wm_concat函数查询,并得到想要的结果

SAM@orcl > select wm_concat(id),wm_concat(name) from t1;

WM_CONCAT(ID)
--------------------------------------------------------------------------------
WM_CONCAT(NAME)
--------------------------------------------------------------------------------
1,2,3,4,5
sam1,sam2,sam3,sam4,sam5

3.到此处问题已被解决,但更加深入了解一下,我打算建立一个t2表,将查询出来的数据插入到t2表中,得到的结果却报错,数据类型不匹配。

SAM@orcl > create table t2 (sum_id int,sum_name varchar2(100));
Table created.

SAM@orcl > insert into t2  select wm_concat(id),wm_concat(name) from t1;
insert into t2  select wm_concat(id),wm_concat(name) from t1
                       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

4.再次验证,看数据类型到底变为什么?将查出结果直接放在新建表t3中,成功。

SAM@orcl > create table t3 as  select wm_concat(id) as sum_id,wm_concat(name) as sum_name from t1;
Table created.

5.查看新表t3结构,数据类型发生变化,由源来的int, varchar2变为了clob

SAM@orcl > desc t3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SUM_ID                                             CLOB
 SUM_NAME                                           CLOB

6.查询t3表内容

SAM@orcl > select * from t3;

SUM_ID
--------------------------------------------------------------------------------
SUM_NAME
--------------------------------------------------------------------------------
1,2,3,4,5
sam1,sam2,sam3,sam4,sam5

四、扩展知识
1.此时开发人员认为这个函数果然厉害,就说能不能看一下这个函数如何写的,与此同时也想到我的心里去了,看看Oracle开发人员的思路。说不定能大开眼界。采用dbms_matadata.get_ddl来取function脚本。结果发现该Function已被加密,无法读取。加密方式为wrap加密。

SAM@orcl > set long 99999 pages 1000
SAM@orcl > SELECT SYS.DBMS_METADATA.get_ddl ('FUNCTION','WM_CONCAT','WMSYS') FROM DUAL;

SYS.DBMS_METADATA.GET_DDL('FUNCTION','WM_CONCAT','WMSYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "WMSYS"."WM_CONCAT" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
53 96
antgYqrbNGLSC7Re+71hueZFyT4wg0SvLZ6pyi+mUCJD1KOso
xPiallQXtwu7BTsCmx9/hIg
+ln6MEC75cHHT8YFQPvfjqPM1MuiY1Z0kXN0TQ0W8KE1SkAqjh/+tB/q
+oI45dREmV5OHaYy
H/E=

五、总结
有时候通过小小的知识点就可以帮助我们DBA和开发人员解决很大的问题,所以此时我想到的就是知识是无限的海洋,等待着我们不停的探索与学习。再有就是我认为古人云:三人行,必有我师。那是相当的corrent and nice. 最后还是希望我能够不断的成长与进步。fighting! Where there is a will, there is a way.

 

发表在 Basic, oracle | 标签为 , | 留下评论

Oracle通过Sqlplus结合Shell脚本方式生成Excel文件

一、问题描述
今天接到一个任务,是要求每天通过SQL脚本生成excel文件,并且自动发送到相关人员邮箱。这个需求我还真是没有做过,之前只做过通过SQL脚本生成为HTML网页文件。于是乎,我又开始的学习过程,如今的Internet时代,解决问题就是easy,很快就找到了eygle的博客(使用SQL*PLUS,构建完美excel或html输出http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html)。下面是学习过程中的实验部分。

二、实验
1.建立测试表

SAM@dzwj >  create table test1(id int,name varchar2(10),loc varchar2(30),hire_date date,email varchar2(20),department varchar2(20));
Table created.

SAM@dzwj > insert into test1 values (1,'sam1','beijing',sysdate,'sam1@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (2,'sam2','beijing',sysdate,'sam2@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (3,'sam3','beijing',sysdate,'sam3@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (4,'sam4','beijing',sysdate,'sam4@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (5,'sam5','beijing',sysdate,'sam5@oracle.com','it');
1 row created.

SAM@dzwj > SAM@dzwj > commit;
Commit complete.

SAM@dzwj > select * from test1;

        ID NAME       LOC                            HIRE_DATE          EMAIL                DEPARTMENT
---------- ---------- ------------------------------ ------------------ -------------------- --------------------
         1 sam1       beijing                        28-NOV-17          sam1@oracle.com      it
         2 sam2       beijing                        28-NOV-17          sam2@oracle.com      it
         3 sam3       beijing                        28-NOV-17          sam3@oracle.com      it
         4 sam4       beijing                        28-NOV-17          sam4@oracle.com      it
         5 sam5       beijing                        28-NOV-17          sam5@oracle.com      it

2.编辑 main.sql

[oracle@testdb ~]$ cat main.sql
set linesize 200 pagesize 10000
set term off verify off feedback off 
set markup html on entmap on spool on preformat off
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
spool /home/oracle/test1.xls
@/home/oracle/get_tables.sql
spool off
exit

3.编辑 get_tables.sql

[oracle@testdb ~]$ cat get_tables.sql 
select * from test1;

4.编辑执行文件 collect.sh

[oracle@testdb ~]$ cat collect.sh 
#!/bin/bash
. /home/oracle/.bash_profile
DATE=`date +%Y%m%d`
sqlplus sam/oracle@dzwj @/home/oracle/main
mv /home/oracle/test1.xls /home/oracle/test1_${DATE}.xls

5.给collect.sh 执行权限

[oracle@testdb ~]$ chmod u+x collect.sh 

6.执行

[oracle@testdb ~]$ ./collect.sh 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 11:00:19 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

7.验证
将文件传回到本地机器打开,得到想要的excel文件

94012998-ae37-4e2f-be0b-fca4c74b962b

三、总结
生活在Internet时代真是件幸福的事,此次任务算是告一段落,但是当中还是碰到一些小problems,比如一开始没有加时间NLS_DATE_FORMAT变量的修改,导出的时间类型数据时没有时间,只有年月日。总而言之,多学习,多实践,没错的。向eygle大神致谢。  Where there is a will, there is a way.

发表在 Basic, oracle | 标签为 , | 留下评论

crontab 问题检查与处理

一、描述
       今天给一台linux机器配置了crontab ,希望每天0点与14点分别跑两个导入数据脚本,可是配置好后。检查时发现该脚本未成功运行,于是需要检查原因并处理。
二、故障排查
1.检查crond 服务
[root@testdb ~]# service crond status
crond (pid  31955) is running…
2.检查系统日志
查询/var/spool/mail/oracle日志发现报错信息 /home/oracle/impdp_xf.sh: line 2: impdp: command not found
这下找到未何失败的原因了,命令没找到,看来是缺少环境变量导致。
3.问题处理
在执行/home/oracle/impdp.sh脚本中增加 source /home/oracle/.bash_profile 后正常。
三、总结
       到处都会有小坑,DBA们要注意喽。Where there is a will, there is a way.
发表在 ERROR, system | 留下评论

利用修改sql_mode=NO_TABLE_OPTIONS收集建表语句,去掉engine和charset关键字

一、描述
平时我们有需求是要根据已有的表来生成建立表语句,直接查看生成过程中会有ENGINE和CHARSET两个关键字,其实我们大多数时候是不需要该参数,如果导出后手工修改脚本,实在是在麻烦了。在这里我们可以使用sql_mode参数,修改为NO_TABLE_OPTIONS来进行收集。当然我们注意点是要修改当前session的变量,而不是全局变量。下面是个小小的实验,演示一下这个过程。
二、实验
1.查看sql_mode变量当然值

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

2.查看t1表的建立语句,带有ENGINE与CHARSET两个参数

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.修改sql_mode为NO_TABLE_OPTIONS

mysql> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| sql_mode      | NO_TABLE_OPTIONS |
+---------------+------------------+
1 row in set (0.00 sec)

4.再次查看建t1表SQL语句,没有ENGINE和CHARSET两个参数

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------+
| Table | Create Table                                                                      |
+-------+-----------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) |
+-------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.退出会话,使sql_mode参数恢复默认值
三、总结
一个小小sql_mode的参数调整,可以省去我们迁移过程中,大量修改脚本的操作。当然在MYSQL中sql_mode参数还有很对应的值,需要我们继续学习与研究。MYSQL真是有意思,继续学习中……

发表在 mysql | 标签为 , | 留下评论

Linux系统互信ssh的配置方法

Linux系统互信ssh的配置方法
一、ssh互信的介绍
ssh互信是两台机器(terminal-1和terminal-2)经过预先设置好认证的key文件,双方互相访问时,进行自动认证,无需再次输入密码,从而实现互信。
实现原理:
1.在要配置互信的机器(terminal-1和terminal-2)上生成各自经过认证的key文件。
2.将所有的key文件汇总到一个总的认证文件夹中。
3.将打包的key发给想要进行互信的机器(terminal-1和terminal-2)
4.互信验证

二、实验
1. 两台机器检查(sam 172.16.211.129 suzzy 172.16.211.130)
terminal-1:

[root@sam ~]# hostname
sam
terminal-2:
[root@suzzy ~]# hostname
suzzy

2. sam机器ssh到suzzy机器(需要输入正确密码才可以登录)
密码正确:

[root@sam ~]# ssh suzzy
The authenticity of host 'suzzy (172.16.211.130)' can't be established.
RSA key fingerprint is e0:4b:15:f3:fe:6c:2d:11:f7:ad:7e:a6:d6:65:0e:0d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'suzzy,172.16.211.130' (RSA) to the list of known hosts.
root@suzzy's password: 
Last login: Fri Oct 30 15:27:15 2015 from 172.16.211.1
[root@suzzy ~]# 

密码错误:

[root@sam ~]# ssh suzzy
root@suzzy's password: 
Permission denied, please try again.
root@suzzy's password: 
Permission denied, please try again.
root@suzzy's password: 
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
[root@sam ~]# 

3. 创建互信所用到的目录并修改权限(如果没有的话),如果你用过ssh登录过对方机器,该目录会自动创建,即便登录不成功。

[root@sam ~]# rm -rf .ssh
[root@sam ~]# mkdir .ssh
[root@sam ~]# chmod 755 .ssh
[root@sam ~]# ls -la
total 376
dr-xr-x---. 31 root root      4096 Oct 30 16:05 .
dr-xr-xr-x. 28 root root      4096 Aug 31 15:28 ..
drwxr-xr-x.  2 root root      4096 Nov 27  2014 .abrt
… ...
drwxr-xr-x   2 root root      4096 Oct 30 16:05 .ssh

4. 创建密钥(默认回车)
sam机器:

[root@sam ~]#  /usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
70:d2:c8:c6:01:6d:1c:2b:2e:8c:89:c0:ae:fc:14:2d root@sam
The key's randomart image is:
+--[ RSA 2048]----+
|    .+o.         |
|.    o+=         |
|..  ..O o        |
|++ ..o +         |
|+.oE..  S        |
|o  .o            |
|.. .             |
|  o              |
|   .             |
+-----------------+
[root@sam .ssh]# ll
total 8
-rw------- 1 root root 1675 Oct 30 17:42 id_rsa
-rw-r--r-- 1 root root  390 Oct 30 17:42 id_rsa.pub

[root@sam .ssh]#  /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
9e:12:19:4e:6a:d5:46:64:47:3d:f9:2a:11:e0:49:ad root@sam
The key's randomart image is:
+--[ DSA 1024]----+
|       .*+o. .   |
|       * oo +    |
|      + =. . o   |
|     = +E .   .  |
|    o + S  . .   |
|   .   o .. .    |
|      . o  .     |
|       .         |
|                 |
+-----------------+
[root@sam .ssh]# ll
total 16
-rw------- 1 root root  672 Oct 30 17:49 id_dsa
-rw-r--r-- 1 root root  598 Oct 30 17:49 id_dsa.pub
-rw------- 1 root root 1675 Oct 30 17:42 id_rsa
-rw-r--r-- 1 root root  390 Oct 30 17:42 id_rsa.pub

注:suzzy机器同上

[root@suzzy ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
d9:d1:27:75:5b:85:a2:af:77:75:83:74:d1:2a:02:35 root@suzzy
The key's randomart image is:
+--[ RSA 2048]----+
|         .E   ..*|
|        .  o...o+|
|         ...o..o.|
|         oo..+.. |
|        S .o..o  |
|            .. .o|
|           .   .o|
|          . . .  |
|           . .   |
+-----------------+
[root@suzzy ~]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
c4:94:b1:87:9a:34:1d:35:cb:51:03:12:f1:86:b7:fe root@suzzy
The key's randomart image is:
+--[ DSA 1024]----+
|        B*=oo    |
|       +.O + .   |
|      o B B      |
|     . = + .     |
|      o S .      |
|         .       |
|          .      |
|           .     |
|            E    |
+-----------------+

5. 将每个主机上的公共密钥文件id_rsa.pub和id_dsa.pub的内容复制到~/.ssh/authorized_keys文件中。并把这个文件分别放到所有机器中。注意,当您第一次使用ssh访问远程主机时,其RSA密钥是未知的,所以提示确认一下,确认完毕后SSH将记录远程主机的RSA密钥,以后连接该主机就不用密码了。

[root@sam .ssh]# cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[root@sam .ssh]# cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
[root@sam .ssh]# ssh root@suzzy cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
The authenticity of host 'suzzy (172.16.211.130)' can't be established.
RSA key fingerprint is e0:4b:15:f3:fe:6c:2d:11:f7:ad:7e:a6:d6:65:0e:0d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'suzzy,172.16.211.130' (RSA) to the list of known hosts.
root@suzzy's password: 
[root@sam .ssh]# ssh root@suzzy cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
root@suzzy's password: 
[root@sam .ssh]# 

6. 检查总密钥文件

[root@sam .ssh]# ls -l authorized_keys 
-rw-r--r-- 1 root root 1980 Oct 30 18:19 authorized_keys
[root@sam .ssh]# cat authorized_keys 
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAzxsnq3tyb50Z+uRGp0tFpMOSTqZpvOvcyrB0S2vbL0YdUl4oJg2xnwo6duteS5EHzoVKzRjSdfrDM5owKRVsWJnufAA/o6z0kiiYje6Cvfd5hlw/jgJtU1TVuzZsj+bwnCzWuSKfkkM/uhBvWk9UQe0GuTClUn4bxuXuFNGwWuDi020pwwNLdUbEtH93rhWFGskUrj9s2RLd3eDquT18TQzNGwwG0PrbedxyT57aVdbqKyLnxMDx/eOHKW4dWZQMIaUe/n73rjuGG43F6oRFk3R52bMSdOqYqljUSI5FmtBAAO1AyTALldg09rdg6PqTlYyQvLt1T9JVok6BLm9nHQ== root@sam
ssh-dss AAAAB3NzaC1kc3MAAACBAPR47OE9s4nnzVEpoH6/rPgJexROcNSwMPJeF9QSXpuIcbUNmLocrab8xQ33rfFkjUOAaf5PG9AJa3T0FReobaEA8uRui4NjJJ74/Bjs+Rm6Lg9FAeboIwBAuEyIG4VcoFXCf7bpplg6+hiRd4oNkuDA+GJdVMyBqWvborAIhuPJAAAAFQCnj9Ft5BQs5tzP6uoV0pBvx7lEAQAAAIEA15+u2GTGGiEMeeCCOwu4kQNjKUGHrZsMBepbOmaBImBeYJP3Q76dpSxjJpPjQ/bk61oVHnKjceuXwpmiND08BvjrGAtw15q2wodKdr0EnRjFQ/8MIWsfSgkBx1ngWDvAD+YqZI1V9imnUUEU+Xlq84PDxDKn45tZ2sauSkYWrhwAAACBAK3I2fkbaLwd/3gWcXkMoA7jm2euYB48DXm2NMYe0WddI/lN+LocRKEf9ZEwts48tw7G/lwcD+eP6jWPYNnSBr8xavjt3ZtVlGnuUlU4yuzUFvtBC8AnG7nyihEFM7lM+V2N0Tx/83K6gRXFGDoOpT4/xfEFxlVFsLdfuuodHhh3 root@sam
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA1H5kArvHN1jagQEUIwTHBEQXI0CHNgMJMZrnIlgNY2ssSFKfJdCdA8bfBtoIesfBSLfyQHFFqwh5CZqfXTlhL6JLlVL0anUnpEHX9v5B1vrNIfsQTXhfjXpyJOJNd1pWFweOQLq/fSvuoWvxQQESBloN8rUFs+eXvxMYi4y5rfQ+9MkJ6y+6HA1JB2KlHadzoA0vbZ4JxS/gcifhAzCv0goEw6ulNwHxdgx4Sp3EG+i8QxlSjV3BJ16FknaMRV8eMy8+pRibY6dWB+FW7sV5rQoT9/2PaqgUf0rMvCPzDE4aNpPYPXiU53dX+691iarmQo1Km26YHu7gDPsGbxa+lw== root@suzzy
ssh-dss AAAAB3NzaC1kc3MAAACBAI4e0Ul5sHev0cAc0uwTgIU9x7oNTQq0YYSdBySfJ1iqpeKM5B1nf4y8C6o+m8IOh5C6BH7Jx3oLyW0oBetlfGroU3WHVSC2D/lAY9CywvRqmLB96OFICJG7NMU48vfFTyYj7m4ARo/gFnhF6svY65tUkrZgf7vxX8F+PNH66YDdAAAAFQCVKW/d0cQ6HBwIZheTbZ+mwkDEYwAAAIAEG0tEw2CKvysYNglifESkHmNw8DqgFvZ0azXTkr8OVxfNKB/h9lzV7U+IyIMMcsSfZaukGVntTtg0RVFPTMq/5rhUrupUWfRNgm0vgTGS2v5JPc5xYdoqZXQS8EIFvndkDyqGU233aievALTITY6bCyt4Nks95obUrSDl4T5ZnAAAAIAv5IVdJ8l2XKNdWMCSJXPhzepDtuzXbx5hKMRoNtoi+Qz8s/uAn3wEJC4qB7zjTnZQcfOdoV0R0JegvI46GO1D3sQhtUy76I2DlwXr0HjrOd/+UXQzfXf3rY3/B4rCTuGjwbbuAZeJVS+joV+MkeaiFrXoXisXjFDOoUiAIX1amw== root@suzzy

7. 将总密钥文件传到其他机器对应目录

[root@sam .ssh]# scp authorized_keys root@suzzy:~/.ssh/
root@suzzy's password: 
authorized_keys                                            100% 1980     1.9KB/s   00:00 

8. 测试连接(首次还是需要YES下,第二次便可以不需要)

[root@sam ~]# ssh suzzy
Last login: Fri Oct 30 18:25:38 2015 from sam
[root@suzzy ~]# ssh sam
Last login: Fri Oct 30 18:26:34 2015 from suzzy
[root@sam ~]# 

9. 将authorized_keys文件权限变更为600,以便安全,每台都需要更改

[root@sam ~]# cd .ssh
[root@sam .ssh]# ls -l authorized_keys 
-rw-r--r-- 1 root root 1980 Oct 30 18:19 authorized_keys
[root@sam .ssh]# chmod 600 authorized_keys 
[root@sam .ssh]# ls -l authorized_keys 
-rw------- 1 root root 1980 Oct 30 18:19 authorized_keys

三、总结
这个互信操作在Oracle 10g配置RAC(real application cluster)前是需要手工来操作的,从11G安装开始,可以在图形界面按钮式配置,相当容易,但我们还是应该掌握该技巧,在需要免密登录时还是要通过手工配置。看家的本领可不能丢。

 

发表在 oracle, RAC, system | 标签为 , | 留下评论

【DATAPUMP】导出时使用exclude排除表

一、问题描述
今天有个小小需求,要从生产数据库上导数据到测试库,问题是有张大表(30G)不需要导出到测试环境,所以要使用到expdp中’exclude’参数。但是在使用过程中还是碰到一点点question。

二、操作过程
1.根据expdp -help 信息中得知exclude参数的for example如下:
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:”=’HR’”.

当然 = 也可以替换成 in ( ) 或 like ‘%..%’这种方式,如:exclude=index:”like ‘emp%’” ,也可以只给object types,如: exclude=VIEW,PACKAGE, FUNCTION
官方文档给出的Syntax是
EXCLUDE=object_type[:name_clause] [, …]

按照上面的例子,我推断出exclude参数针对表的使用
exclude=table:”=‘table name’”
2.执行语句报错,此处说明语法有问题,最终查询到原来是由于没有加转义符。

[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log  exclude=table:"='WSCT_DATA'" compression=all
LRM-00116: syntax error at 'table:' following '='

3.修改,增加转义符后执行成功

[oracle@rac2 ~]$ expdp cloan/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp log=cloan_20170713.log  EXCLUDE=TABLE:\"= \'WSCT_DATA\'\" compression=all

Export: Release 11.2.0.4.0 - Production on Thu Jul 13 10:17:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=cloan_20170713.log" Location: Command Line, Replaced with: "logfile=cloan_20170713.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "CLOAN"."SYS_EXPORT_SCHEMA_02":  cloan/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp logfile=cloan_20170713.log EXCLUDE=TABLE:"= 'WSCT_DATA'" compression=all reuse_dumpfiles=true 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
… … 
Dump file set for CLOAN.SYS_EXPORT_SCHEMA_02 is:
  /backup/cloan_20170713.dmp
Job "CLOAN"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Jul 13 10:19:18 2017 elapsed 0 00:01:27

4.导入到测试环境成功

[oracle@testdb backup]$  impdp cloancp/xxxx DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan;

Import: Release 11.2.0.4.0 - Production on Thu Jul 13 10:45:30 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CLOANCP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CLOANCP"."SYS_IMPORT_FULL_01":  cloancp/******** DIRECTORY=backup DUMPFILE=cloan_20170713.dmp remap_schema=cloan:cloancp remap_tablespace=cloan:newcloan
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
…...
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "CLOANCP"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Thu Jul 13 10:46:34 2017 elapsed 0 00:01:04

三、总结
一个小小问题,使得对expdp的参数exclude加以学习与掌握。重点是单引号’ 双引号“ 都需要进行转义才可以使命令正常执行。 Where there is a will, there is a way.

发表在 expdp/impdp, oracle | 标签为 , | 留下评论