Dblink待整理

  1. ##1.通过cdrom挂载
  2. mount /dev/cdrom /mnt
  4. ##2.通过安装镜像源挂载
  5. mount -o loop /soft/rhel-server-7.9-x86_64-dvd.iso /mnt
  11. ##1.创建安装介质上传目录
  12. mkdir /soft
  13. [root@localhost ~]# cd /soft/
  15. ##2.上传安装介质
  16. [root@localhost soft]# du -sh *
  17. ##linux7系统缺少补丁包
  18. 192K    compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
  19. ##无人值守shell脚本
  20. 140K    OracleShellInstall.sh
  21. ##oracle 11GR2官方安装包
  22. 1.3G    p13390677_112040_Linux-x86-64_1of7.zip
  23. 1.1G    p13390677_112040_Linux-x86-64_2of7.zip
  24. ##oracle 11GR2 PSU 最终版补丁包+6880880 OPatch补丁包
  25. 374M    p31537677_112040_Linux-x86-64.zip
  26. 118M    p6880880_112000_Linux-x86-64.zip
  27. ##上下文切换软件（可选）
  28. 276K    rlwrap-0.42.tar.gz
  30. ##3.授权脚本执行权限
  31. chmod +x OracleShellInstall.sh
  35. cd /soft
  36. ./OracleShellInstall.sh -i 10.211.55.100 `#Public ip`\
  37. -n s11g `# hostname`\
  38. -o s11g `# oraclesid`\
  39. -op oracle `# oracle user password`\
  40. -b /oracle/app `# install basedir`\
  41. -s AL32UTF8 `# characterset`\
  42. -opa 31537677 `# oracle psu number`
  46. ##1.通过cdrom挂载
  47. mount /dev/cdrom /mnt
  49. ##2.通过安装镜像源挂载
  50. mount -o loop /isofile/CentOS-7-x86_64-DVD-1503-01.iso /mnt
  53. cd /soft
  55. ./OracleShellInstall.sh -i 192.168.60.166 -n s11g -o oracle -c FLASE -op oracle -b /oracle/app -s ZHS16GBK
  57. # 3. 验证
  58. [oracle@s11g:/home/oracle]$ sas
  59. SQL> select instance_name,status from v$instance;
  60. INSTANCE_NAME     STATUS
  61. ---------------- ------------
  62. oracle         OPEN

1) 修改用户的profile将客户端的路径写入

  1. vi /etc/profile
  2. export  ORACLE_HOME=/oracle/oracle/app/oracle/product/11.2.0/db
  3. export  TNS_ADMIN=$ORACLE_HOME/network/admin
  4. export  LD_LIBRARY_PATH=$ORACLE_HOME/lib
  5. export  PATH=$ORACLE_HOME/bin:$PATH
  7. export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/opt/oracle/instantclient_11_2"
  11. ORACLE_BASE=/oracle/oracle/app/oracle
  12. ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db
  13. ORACLE_SID=orcl
  14. PATH=$PATH:$ORACLE_HOME/bin
  15. export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
  18. export  ORACLE_HOME=/usr/lib/oracle/11.2/client64
  19. export  TNS_ADMIN=$ORACLE_HOME/network/admin
  20. export  LD_LIBRARY_PATH=$ORACLE_HOME/lib
  21. export  PATH=$ORACLE_HOME/bin:$PATH
  23. vim ~/.bash_profile                                                --修改
  24. export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/data/dmdbms/bin"
  26. source ~/.bash_profile
  28. vi /etc/profile
  31. source /etc/profile
  34. scott
  36. ./sqlplus SCOTT/oracle@82.156.213.133:1521/ORCL    @/ as sysdba@ORCL
  39. [dmdba@VM-0-4-centos admin]$ vi ~/.bash_profile 
  40. cd /oracle/oracle/app/oracle/product/11.2.0/db/network/admin
  41. [dmdba@VM-0-4-centos admin]$ source ~/.bash_profile
  43. select * from scott.t1@LINKORA;
  45. create table scott.T1 (name  VARCHAR2(20),age   INTEGER,sex   VARCHAR2(3),grade INTEGER );
  48. insert into scott.t1 values('123',18,'1',1000);
  51. cd /oracle/app/oracle/product/11.2.0/db/lib
  52. ln -s libclntsh.so.11.1  /oracle/app/oracle/product/11.2.0/db/bin/libclntsh.so
  1. sqlplus azl/123456789@tshtest
  4. scp –r /oracle root@62.234.115.217：/root
  8. https://cdn.modb.pro/db/146204
  1. [root@localhost ~]# echo /opt/oracle/instantclient_11_2 > /etc/ld.so.conf.d/oracle-instantclient.conf
  2. [root@localhost ~]# cat /etc/ld.so.conf.d/oracle-instantclient.conf
  3. /opt/oracle/instantclient_19_13
  4. [root@localhost ~]# ldconfig
  1. create or replace public LINK test1 connect 'ORACLE' with scott identified by "oracle" 
  2. using '  (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = 82.156.213.133)(PORT = 1521))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SERVER = DEDICATED)
  8.       (SID = oracle )
  9.     )
  10.   )'; 
  13. select * from v$instance@test1;

笔记整理

  1. [oracle@s11g:/home/oracle]$ cd $ORACLE_HOME
  2. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db]$ cd network/admin/
  3. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ vi tnsnames.ora
  4. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ tnsping orcl
  6. ## vi tnsnames.ora
  7. #################################################### 
  8. orcl =
  9.   (DESCRIPTION =
  10.     (ADDRESS = (PROTOCOL = TCP)(HOST = 82.156.213.133)(PORT = 1521))
  11.     (CONNECT_DATA =
  12.       (SERVER = DEDICATED)
  13.       (SERVICE_NAME = oracle)
  14.     )
  15.   )
  16. #################################################### 
  17. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ vi listener.ora 
  18. ####################################################
  19. ## vi listener.ora
  20. #这是一个名为 LISTENER1 的监听器
  21. #监听的协议是TCP协议
  22. #监听的主机IP是127.0.0.1
  23. #监听的端口是1521端口
  24. LISTENER1 =
  25.     (DESCRIPTION =
  26.     (ADDRESS = (PROTOCOL = TCP)(HOST = 82.156.213.133)(PORT = 1521))
  27. )
  28. #记录了监听器LISTENER1服务的全局数据库名、数据库路径和数据库实例名
  29. SID_LIST_LISTENER1 =
  30.     (SID_LIST =
  31.         (SID_DESC =
  32.             (GLOBAL_DBNAME = ORCL)
  33.             (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db)
  34.             (SID_NAME = orcl)
  35.         )
  36.     )
  37. #################################################### 
  38. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ lsnrctl restart
  39. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ lsnrctl start
  40. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ tnsping orcl
  41. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ lsnrctl status
  43. [oracle@s11g:/oracle/app/oracle/product/11.2.0/db/network/admin]$ sqlplus
  48. create or replace public LINK test1 connect 'ORACLE' with scott identified by "oracle" 
  49. using '  (DESCRIPTION =
  50.     (ADDRESS_LIST =
  51.       (ADDRESS = (PROTOCOL = TCP)(HOST = 82.156.213.133)(PORT = 1521))
  52.     )
  53.     (CONNECT_DATA =
  54.       (SERVER = DEDICATED)
  55.       (SID = oracle )
  56.     )
  57.   )'; 
  59.   create table T03("id" CHAR(10),"create_date" DATE )
  60.    select * from T03@test1  where create_date > '2019-02-01'
  62.    select "id","create_date" from "SYSDBA"."test_demo" where to_date("create_date" , 'yyyy-mm-dd hh24:mi:ss') >= '2019-02-01';
  65.    select * from test_demo where create_date > '2019-02-01'
  67.    CREATE VIEW "test_demo" AS select * from T03@test1;
  71. export TMP=/tmp
  72. export TMPDIR=$TMP
  73. export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK #AL32UTF8,ZHS16GBK
  74. export ORACLE_BASE=/oracle/app/oracle
  75. export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db
  76. export ORACLE_HOSTNAME=s11g
  77. export ORACLE_TERM=xterm
  78. export TNS_ADMIN=$ORACLE_HOME/network/admin
  79. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  80. export ORACLE_SID=oracle
  81. export PATH=/usr/sbin:$PATH
  82. export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
  86. PATH=$PATH:/opt/oracle/app/oracle/product/11.2.0/db/bin:/usr/local/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
  87. export PATH
  88. export ORACLE_HOME=/opt/oracle/app/oracle/product/11.2.0/db
  89. export LD_LIBRARY_PATH=/opt/dmdbms/bin:/lib64:$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:$ORACLE_HOME/rdbms/lib
  90. export LANG=zh_CN.utf8

重启网卡

  1. 虚拟机完成后，系统安装了一个默认的网卡，即eth0，
  2. 1. 其配置文件的路径为/etc/sysconfig/network-scripts/ifcfg-eth0，
  3. 2. 在系统终端中输入命令：vi /etc/sysconfig/network-scripts/ifcfg-eth0，启动vi编辑器编辑此文件
  5. 2. 编辑默认网卡配置文件，将ONBOOT由no改为yes，编辑完成后，按ESC回至命令模板，输入":wq"，回车，保存修改并退出
  7. 3. 重启网卡
  8. systemctl stop NetworkManager
  10. systemctl disable NetworkManager
  12. systemctl start network.service