oracle查询是否开启加密以及开启加密传输 需求背景 在一些安全性要求高的环境内，需要开启加密传输，未加密的流量有可能被爬取后解密 查询状态 首先查询是否已经开启 select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat); 如果回显了加密的算法，则表示开启加密，如果只是显示了一些版本信息，则表示未开启 Oracle Advanced Security: AES256 encryption service adapter for Linux: Version 1 1.2.0.4.0 - Product Oracle Advanced Security: SHA1 crypto-checksumming service adapter 配置加密 linux环境使用netmgr命令开启 windows环境在开始菜单中找到net manager 定位到oracle高级安全 找到需要加密的算法 设置好后在 标题栏的 File 中选择保存即可 服务端会在sqlnet.ora中自动生成以下代码的第一行，其余两行需要手动添加，否则加密不生效 SQLNET.ENCRYPTION_TYPES_SERVER= (AES256, AES192) SQLNET.ENCRYPTION_SERVER = required SQLNET.CRYPTO_CHECKSUM_SERVER = required 客户端需要与服务端使用相同的加密方式，jdbc示例如下: jdbc.connectionProperties=oracle.net.encryption_client\=REQUIRED;oracle.net.encryption_types_client\=(AES256,AES192);oracle.net.crypto_checksum_client\=REQUIRED;oracle.net.crypto_checksum_types_client\=(SHA1)

oracle psu oracle11g psu OPatch替换到$ORACLE_HOME的OPatch 查询各个组件相关信息 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; 查询补丁情况 select ACTION_TIME, ACTION,version, COMMENTS from sys.DBA_REGISTRY_HISTORY; 查询是否存在无效对象 select count(*) from dba_objects where status<>'VALID'; 进入到psu补丁目录执行兼容性测试 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./ -oh $ORACLE_HOME 6.关闭数据库，监听，dbctl shutdown immediate lsnrctl stop emctl stop dbconsole 执行补丁 /opt/app/oracle/product/11.2.0.4/db_home/OPatch/opatch apply 可能出现CheckActiveFilesAndExecutables错误，需要执行如下命令 /sbin/fuser /opt/app/oracle/product/11.2.0.4/db_home/lib/libclntsh.so.11.1 还有可能出现提示需要cmrsp响应文件的错误，使用如下命令生成，默认生成在当前目录 /opt/app/oracle/product/11.2.0.4/db_home/OPatch/ocm/bin/emocmrsp 生成后修改执行命令 /opt/app/oracle/product/11.2.0.4/db_home/OPatch/opatch apply -ocmrf ocm.rsp 最后 @?/rdbms/admin/catbundle.sql psu apply @?/rdbms/admin/utlrp.sql @?/rdbms/admin/dbmsjdev.sql

oracle安装 oracle19c安装步骤 安装必须的依赖 yum install -y bc binutils compat-libcap1 compat-libstdc++ elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc++-devel libxcb make net-tools nfs-utils python python-configshell python-rtslib python-six targetcli smartmontools sysstat unzip vim 需要注意的是，19c的安装文件需要解压到$ORACLE_HOME下 2. 生成安装响应文件 oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0 oracle.install.option=INSTALL_DB_SWONLY UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/opt/app/oraInventory ORACLE_BASE=/opt/app/oracle ORACLE_HOME=/opt/app/oracle/product/19.3/dbhome_1 oracle.install.db.InstallEdition=EE oracle.install.db.OSDBA_GROUP=dba oracle.install.db.OSOPER_GROUP=oper oracle.install.db.OSBACKUPDBA_GROUP=backupdba oracle.install.db.OSDGDBA_GROUP=dgdba oracle.install.db.OSKMDBA_GROUP=kmdba oracle.install.db.OSRACDBA_GROUP=racdba oracle.install.db.rootconfig.executeRootScript=true oracle.install.db.rootconfig.configMethod=ROOT 生成监听响应文件 [GENERAL] RESPONSEFILE_VERSION="19.3" CREATE_TYPE="CUSTOM" [oracle.net.ca] INSTALLED_COMPONENTS={"server","net8","javavm"} INSTALL_TYPE=""typical"" LISTENER_NUMBER=1 LISTENER_NAMES={"LISTENER"} LISTENER_PROTOCOLS={"TCP;1521"} LISTENER_START=""LISTENER"" NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"} NSN_NUMBER=1 NSN_NAMES={"EXTPROC_CONNECTION_DATA"} NSN_SERVICE={"PLSExtProc"} NSN_PROTOCOLS={"TCP;HOSTNAME;1521"} 创建实例响应文件 templateName=General_Purpose.dbc gdbName=orcl sid=orcl sysPassword=wishyousuccez2020 systemPassword=wishyousuccez2020 datafileDestination=/opt/app/oracle/oradata recoveryAreaDestination=/opt/app/oracle/flash_recovery_area redoLogFileSize=500 storageType=FS characterSet=ZHS16GBK nationalCharacterSet=AL16UTF16 sampleSchema=true totalMemory=22220 databaseType=OLTP emConfiguration=NONE oracle11g/11.2.0.4安装 11g安装响应文件 oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0 oracle.install.option=INSTALL_DB_SWONLY ORACLE_HOSTNAME=oracle11 UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/opt/app/oracle/oraInventory SELECTED_LANGUAGES=en,zh_CN ORACLE_HOME=/opt/app/oracle/db_home ORACLE_BASE=/opt/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.isCustomInstall=false oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0 oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=dba oracle.install.db.CLUSTER_NODES= oracle.install.db.config.starterdb.type=GENERAL_PURPOSE oracle.install.db.config.starterdb.globalDBName=orcl oracle.install.db.config.starterdb.SID=orcl oracle.install.db.config.starterdb.characterSet=ZHS16GBK oracle.install.db.config.starterdb.memoryOption=true oracle.install.db.config.starterdb.memoryLimit= oracle.install.db.config.starterdb.installExampleSchemas=false oracle.install.db.config.starterdb.enableSecuritySettings=true oracle.install.db.config.starterdb.password.ALL=password 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.dbcontrol.enableEmailNotification=false oracle.install.db.config.starterdb.dbcontrol.emailAddress= oracle.install.db.config.starterdb.dbcontrol.SMTPServer= 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=admin@succez.com MYORACLESUPPORT_PASSWORD=123456 SECURITY_UPDATES_VIA_MYORACLESUPPORT= DECLINE_SECURITY_UPDATES=true PROXY_HOST= PROXY_PORT= PROXY_USER= PROXY_PWD= 11.2.0.4响应文件 oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0 oracle.install.option=INSTALL_DB_SWONLY ORACLE_HOSTNAME=oracle11g UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/opt/app/oracle/oraInventory SELECTED_LANGUAGES=en,zh_CN ORACLE_HOME=/opt/app/oracle/product/11.2.0.4/db_home ORACLE_BASE=/opt/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= oracle.install.db.racOneServiceName= oracle.install.db.config.starterdb.type=GENERAL_PURPOSE oracle.install.db.config.starterdb.globalDBName=orcl oracle.install.db.config.starterdb.SID=orcl oracle.install.db.config.starterdb.characterSet=ZHS16GBK oracle.install.db.config.starterdb.memoryOption=true oracle.install.db.config.starterdb.memoryLimit= oracle.install.db.config.starterdb.installExampleSchemas=false oracle.install.db.config.starterdb.enableSecuritySettings=true oracle.install.db.config.starterdb.password.ALL=password 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=admin@qq.com MYORACLESUPPORT_PASSWORD=123456 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= 安装步骤如下： 使用ustc（ 中国科学技术大学）的网络yum 安装依赖包 yum -y install binutils compat-libcap1 compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXext libXtst libX11 libXau libxcb libXi make sysstat 创建用户以及安装文件夹 groupadd -g 1000 oinstall groupadd -g 1001 dba useradd -u 1000 -g oinstall -G dba oracle mkdir -pv /opt/app/oracle/product/11.2.0.4/db_home chown -R oracle.oinstall /opt/app chmod -R 775 /opt/app 创建环境变量 /home/oracle/.bash_profile export ORACLE_HOME=/opt/app/oracle/product/11.2.0.4/db_home export ORACLE_BASE=/opt/app/oracle export ORACLE_SID=orcl export PATH=$PATH:${ORACLE_HOME}/bin 添加oracle用户的资源限制 echo -ne "oracle\\tsoft\\tnproc\\t65535\\noracle\\thard\\tnproc\\t65535\\noracle\\tsoft\\tnofile\\t65535\\noracle\\thard\\tnofile\\t65535\\n" >> /etc/security/limits.conf echo -ne "session\\trequired\\t/lib64/security/pam\_limits.so\\nsession\\trequired\\tpam\_limits.so\\n" >> /etc/pam.d/login 上传oracle压缩文件到根目录并且解压 unzip /linuxamd64_12102_database_1of2.zip unzip /linuxamd64_12102_database_2of2.zip 备份解压后文件夹中的原响应文件 mv /database/response/db_install.rsp /database/response/db_install.rsp.bak 假设自己的响应文件已经上传到根目录，现在移动至对应文件夹中 mv db_install.rsp /database/response/ 开始执行安装，如果不确定是否执行成功，查看后台中是否有java进程在运行（响应文件需要绝对路径） su - oracle -c '/database/runInstaller -silent -ignoreSysPrereqs -ignorePrereq -responseFile /database/response/db_install.rsp' 大约十分钟左右安装完成，根据提示执行对应的脚本 /opt/app/oracle/oraInventory/orainstRoot.sh /opt/app/oracle/db_home/root.sh 静默创建监听（响应文件需要绝对路径） su - oracle netca -silent -responsefile /database/response/netca.rsp 静默创建数据库 dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcl -sid orcl -sysPassword password -systemPassword password -responseFile NO_VALUE -characterSet ZHS16GBK -memoryPercentage 40 -emConfiguration LOCAL 静默参数说明： -silent指以静默方式执行dbca命令 -createDatabase指使用dbca -templateName指定用来创建数据库的模板名称，这里指定为General_Purposedbc，即一般用途的数据库模板 -gdbname指定创建的全局数据库名称，这里指定名称为ocp11g -sid 指定数据库系统标识符，这里指定为ocp11g，与数据库同名 -responseFile 指定安装响应文件，NO_VALUE表示没有指定响应文件 -characterSet 指定数据库使用的字符集，这里指定为AL32UTF8 -memoryPercentage 指定用于oracle的物理内存的百分比，这里指定为30% -emConfiguration 指定Enterprise Management的管理选项。LOCAL表示数据库由Enterprise Manager本地管理

mysql批量修改definer 问题背景 数据库恢复后视图中有较多的definer属性，需要修改，如果不嫌麻烦可以把视图删除重新创建，但是使用批量的方式修改definer属性更便捷 先查出所有的definer： select DEFINER from information_schema.VIEWS; 在查出的definer中如果需要修改"abc"@"%"为"zxc"@"%"，则执行以下语句生成修改语句： select concat("alter DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER='abc@%'; 导出为sql文件，然后直接执行 select concat("alter DEFINER=`dflbj1`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where table_schema='dflbj1' and DEFINER='dflbj@localhost' into outfile '/var/lib/mysql-files/dflbj1.sql'; mysqlpump备份提示 "dflbj"@"localhost" 无法查询某个表，但是这种一般是视图的错误，也没有提示是哪个库，有两个库都有这个表，那只能通过 information_schema.views 查询两个库中的视图使用了提示的 definer 了，发现是 dflbj1 这个用户和库下的表，视图definer很多都不对，使用上面的方法批量修改

mysql表文件或者数据损坏 异常问题 mysql运行异常、宕机，日志如下： InnoDB: End of page dump 2019-05-26T05:44:41.901012Z 44 [Note] InnoDB: Uncompressed page, stored checksum in field1 851205953, calculated checksums for field1: crc32 4086194213/29424421, innodb 2478445248, none 3735928559, stored checksum in field2 1958202064, calculated checksums for field2: crc32 4086194213/29424421, innodb 4236257487, none 3735928559, page LSN 61 1536263792, low 4 bytes of LSN at page end 2321078681, page number (if stored to page already) 1, space id (if created with >= MySQL-4.1.1 and stored already) 181012 InnoDB: Page may be an insert buffer bitmap page 2019-05-26T05:44:41.901034Z 44 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery. 2019-05-26T05:44:41.901047Z 44 [ERROR] [FATAL] InnoDB: Unable to read page [page id: space=181012, page number=1] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/5.7/en/ for more details. Aborting... 2019-05-26 13:44:41 0x7f5053596700 InnoDB: Assertion failure in thread 139982972479232 in file ut0ut.cc line 942 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 05:44:41 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. key_buffer_size=268435456 read_buffer_size=2097152 max_used_connections=71 max_threads=1024 thread_count=71 connection_count=71 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4470152 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f4f58000ae0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f5053595e28 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0xf54535] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x7d3b54] /lib64/libpthread.so.0(+0xf7e0)[0x7f5618fce7e0] /lib64/libc.so.6(gsignal+0x35)[0x7f5617a6e4f5] /lib64/libc.so.6(abort+0x175)[0x7f5617a6fcd5] /usr/sbin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7c2c78] /usr/sbin/mysqld(_ZN2ib5fatalD1Ev+0xb3)[0x10f4cc3] /usr/sbin/mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0x1606)[0x11363d6] /usr/sbin/mysqld[0xfc4a87] /usr/sbin/mysqld(_Z29ibuf_merge_or_delete_for_pageP11buf_block_tRK9page_id_tPK11page_size_tm+0x7dd)[0xfcbedd] /usr/sbin/mysqld(_Z20buf_page_io_completeP10buf_page_tb+0x4c9)[0x1130619] /usr/sbin/mysqld[0x11534fc] /usr/sbin/mysqld(_Z13buf_read_pageRK9page_id_tRK11page_size_t+0x40)[0x1153c80] /usr/sbin/mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0xd21)[0x1135af1] /usr/sbin/mysqld(_Z18btr_root_block_getPK12dict_index_tmP5mtr_t+0xd8)[0x10fd0e8] /usr/sbin/mysqld(_Z12btr_root_getPK12dict_index_tP5mtr_t+0x11)[0x10fd201] /usr/sbin/mysqld(_Z18btr_validate_indexP12dict_index_tPK5trx_tb+0x1a4)[0x110a264] /usr/sbin/mysqld(_ZN11ha_innobase5checkEP3THDP15st_ha_check_opt+0x19a)[0xf8bc7a] /usr/sbin/mysqld(_ZN7handler8ha_checkEP3THDP15st_ha_check_opt+0x79)[0x824089] /usr/sbin/mysqld[0xe8c3b3] /usr/sbin/mysqld(_ZN19Sql_cmd_check_table7executeEP3THD+0xcf)[0xe8ceef] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0xdb9)[0xd137a9] /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x40d)[0xd17e1d] /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x174e)[0xd195ee] /usr/sbin/mysqld(_Z10do_commandP3THD+0x194)[0xd19f54] /usr/sbin/mysqld(handle_connection+0x2b4)[0xdef2c4] /usr/sbin/mysqld(pfs_spawn_thread+0x174)[0x126ac94] /lib64/libpthread.so.0(+0x7aa1)[0x7f5618fc6aa1] /lib64/libc.so.6(clone+0x6d)[0x7f5617b24c4d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4f58005640): is an invalid pointer Connection ID (thread ID): 44 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 表损坏，备份也会失败，操作到这个表的时候会导致数据库立即崩溃 定位目标表： mysqlcheck -u root -p --auto-repair --all-databases 检测到指定表会导致数据库挂掉，仍然不会显示表名，但是能够显示最后一个正常的表，再进入 show tables 看这个表的下一个表就可以了 损坏恢复 mysql表数据文件frm，ibd损坏后的解决办法 现象：数据库无法正常启动，提示表空间或者表其他错误，需要重binlog恢复，如果此时没有对应的binlog，那么注释binlog日志即可。此时只能使用innodb_force_recovery来启动，这个参数有7个级别，其中0-3是允许使用drop，create等命令的，大于等于4级别innodb被设置为只读。所以设置为3后重启数据库，成功链接。 但此想在库中drop或者create表仍然不行，都提示table doesn't exists。 解决办法： 在以正常启动数据库为目标，无所谓数据丢失的前提下。停掉数据库，删除损坏表对应的frm和ibd，复制正常表的frm为原表frm名，然后启动数据库，可以成功drop此表，然后重建表即可。 PS. mysql如果某些bug或者问题导致数据库挂了，或无限重启，那么可以尝试修改innodb_force_recovery为3，如果3仍然不行，可以选择修改为4，此时虽然数据库不可写，但是能够启动库，接着再使用此方法来确定有问题的表