S1156578253-CSDN博客

原创 XTTS迁移11g到19c

1、确认源端和目标端的平台IDline 200col PLATFORM_NAME for a40select a.PLATFORM_ID,a.PLATFORM_NAME,a.ENDIAN_FORMAT from v$transportable_platform a, v$database b where a.PLATFORM_NAME=b.PLATFORM_NAME;2、源端和目标端的字符集要相同SET LINESIZE 200COL PARAMETER FOR A40COL VALUE F

2021-11-04 09:41:25 1810 1

原创 Oracle 11.2.0.1 rac升级到11.2.0.4

升级过程分为三部分：1、升级grid2、升级rdbms3、升级数据字典前期准备：新建grid_home和oracle_home 升级前升级后版本 11.2.0.1 11.2.0.4 grid_base /u01/app/grid /u01/app/grid grid_home /u01/app/11.2.0/grid /u01/app/11.2.0.4/grid oracle_base /u01/app/ora

2021-11-01 17:26:26 574

原创 oracle透明加密不可用

事情的经过：一套12c的rac数据库搭建了adg同步。1、将主备切换。2、将切换后的新主库升级到19c。升级后检查发现TDE不可用，影响部分业务的正常使用。将sqlnet.ora和wallet文件重新拷贝到目标主机。administer key management set keystore close;administer key management set keystore open identified by <wallet_password> ;admini

2021-10-29 16:31:16 327

原创执行计划-循环嵌套

select /*+ leading(e) use_nl(f) */ * from e,f where e.col1=f.col1;SQL> select /*+ leading(e) use_nl(f) */ * from e,f where e.col1=f.col1;Execution Plan----------------------------------------------------------Plan hash value: 3901460093--------

2021-10-15 15:25:38 170

原创 postgresql常用命令

2021-10-11 17:19:04 194

原创 postgresql源码安装

1、创建用户和组groupadd postgresuseradd -g postgres postgres[root@test1 /]# groupadd postgres[root@test1 /]# useradd -g postgres postgres2、创建postfresql的安装目录[root@test1 tmp]# mkdir -p/postgresql/2.6[root@test1 tmp]# chown -R postgres:postgres /postg.

2021-10-11 13:27:06 454

原创 postgresql主备同步

数据库复制：master节点：创建用户：create user replica with replication login password '123456';alter user replica with password '123456';修改pg_hba.conf文件：host replication replica 0.0.0.0/0 md5修改配置：wal_level = replica #max_wal_senders= 5 #设置最多可以有几个...

2021-10-09 23:46:01 1019

原创 postgresql备份恢复

逻辑导出：pg_dump -d test > /tmp/test.sql导入：psql < /tmp/test.sqlpg_dumpall > /tmp/alldbs.sql物理备份：pg_basebackup -D /pgdata/pg_backup -Ft -Pv -Upostgres -h 192.168.10.1 -p 5432 -R恢复：vi postgresql.auto.confrestore_command='cp /archive/%f %p're.

2021-10-09 23:44:49 122

原创 postgresql安装

1、创建用户useradd postgres2、安装依赖包：yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility"yum install -y bison flex readline* zlib-devel gcc* gmake3、创建目录并授权mkdir -p /usr/local/pg12mkdir -p /pgdata/12/datachown -R postgresql. /pgdatacho..

2021-10-09 13:46:21 134

原创 LFI-00142: Unable to delete an existing file [olsnodes][l03] not owned by Oracle

巡检数据库发现日志无法轮训，报如下错误：2021-08-09 19:46:00.952: [client(199287)]CRS-0014:An error occurred while attempting to delete file "/u01/app/11.2.0/grid/log/###/client/olsnodes.l03" during log file rotation. Additional diagnostics: LFI-00142: Unable to delete an

2021-10-08 10:27:06 630

原创 rman常用命令

1、目标数据库的物理结构report schema;2、最近7天没有备份的文件report need backup days=73、查看某个表空间中最近几天没有备份的文件report need backup days=7 tablespace users;4、恢复数据文件需要的增量备份个数超过2个的数据文件report need backup incremental 2;5、查看哪些文件的备份树低于2个report need backup redun...

2021-09-28 16:11:48 617

原创 Oracle从单机文件系统迁移到rac asm磁盘组

1、使用rman将数据文件迁移到asm磁盘组run {ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;BACKUP AS COPY TAG 'TESTORCL' INCREMENTAL LEVEL 0 DATABASE FORMAT "+DATADG/%d_%t_%u.dbf";release channel ch1;}RMAN> run {2> allocate channel ch1 device type disk;3> backup

2021-09-08 10:11:52 521

原创下载最新的Oracle补丁

Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

2021-08-31 11:22:00 365

原创 sqlnet.ora文件的使用

1、SQLNET.AUTHENTICATION_SERVICESOracle可以使用数据库认证，也可以是操作系统认证的方式。SQLNET.AUTHENTICATION_SERVICES=（NONE）表示使用数据库认证的方式，在这种情况下，登录数据库必须输入用户名和密码，SQLNET.AUTHENTICATION_SERVICES=（NTS）表示操作系统方式认证，此时使用sys登录可以不用输入用户名/密码，例如：sqlplus / as sysdbarman target /2

2021-08-25 16:19:39 472

原创 DBMS_SQLTUNE包失效处理

今天同事说Oracle报错，DBMS_SQLTUNE失效了，让帮忙看一下检查包的状态，发现果然失效了。SQL> SELECT 'Object_Status' "CHECK_NAME",owner,object_name,object_id,object_type,status from dba_objects 2 where owner='SYS' and object_name = 'DBMS_SQLTUNE';CHECK_NAME OWNER

2021-08-23 17:29:38 249

原创逻辑DataGuard FailOver

备库执行failover切换：ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;

2021-08-20 10:14:14 78

原创逻辑DataGuard SwitchOver

1、查看主库是否处于to standby状态SQL> select switchover_status from v$database;2、将primary置为准备转换standby的状态：SQL> alter database prepare to switchover to logical standby;3、此时检查主库的Switchover状态为PREPARING SWITCHOVER。如果不是，需要暂停主备切换，排查原因。SQL> select .

2021-08-20 10:11:34 95

原创如何使用logminer查看日志

1、选定Logminer字典Oracle提供了三种方式选定Logminer字典（1）使用在线目录EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);（2）抽取字典到redo日志文件中EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);（3）抽取字典到平面文件中这

2021-08-19 10:47:09 966

原创创建逻辑standby

1、创建物理standby。。。略2、停止redo应用alter database recover managed standby database cancel;3、主库生成LogMiner字典信息execute dbms_logstdby.build;4、物理standby转换为逻辑standbyalter database recover to logical standby std（std为数据库名字，自己新设置）;5、打开逻辑standby数据库SQL&

2021-08-18 17:25:43 132

原创 DataGuard主备切换

1、SwitchOver主库：ALTER DATABASE COMMIT TO SWITCHOVER TO physical STANDBY WITH SESSION SHUTDOWN;备库：ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;ALTER DATABASE OPEN;2、FailOver备库：alter database recover managed standby

2021-08-18 17:14:41 459

原创 DataGuard搭建

1、环境说明：主库：Oracle11g，rac+asm备库：Oracle11g单机2、主库参数配置：*.log_archive_config='dg_config=(rac-pri,std)'*.log_archive_dest_1='location=+DATADG/arch'*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std'*.log_arch

2021-08-18 15:40:31 298

原创 dataguard常用SQL

查看主备库角色select inst_id,database_role,force_logging from gv$database;主库：备库：查看数据库状态select name,open_mode,database_role,protection_mode,switchover_status from v$database;主库：备库：查看日志文件状态select GROUP#,THREAD#,SEQUENCE#,STATUS from v$standb.

2021-08-17 09:39:46 137

原创达梦数据库安装

1、下载安装包并解压官方下载达梦数据库，解压并且挂在安装包unzipDM8-20210618-x86-rh7-64位.zipmount -o loop /dm8_20210618_x86_rh7_64_ent_8.1.2.18_pack3.iso /mnt/dm2、安装达梦数据库进入到安装包所在目录，开始安装[dmdba@test1 dm]$ ./DMInstall.bin -iPlease select the installer s language (E/e:Englis.

2021-08-09 10:49:38 278

原创 ORA-19909: datafile 6 belongs to an orphan incarnation

本篇接着上一篇ORA-16433: The database must be opened in read/write mode.继续，在重建控制文件后，漏掉了一个数据文件，导致改数据文件时offline状态，那么如何恢复这个文件呢？查看当前数据文件SQL> select file#,status,checkpoint_change#,name from v$datafile_header; FILE# STATUS CHECKPOINT_CHANGE# NAME------

2021-07-27 17:39:01 277

原创在Oracle中增进SCN

接上一节，在上一节数据库恢复中（ORA-16433: The database must be opened in read/write mode.），遇到ORA-00600错误，可以通过增进SCN解决。先看一下报错：SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-00603: ORACLE server session terminated by fatal e

2021-07-27 15:14:09 335

原创 ORA-16433: The database must be opened in read/write mode.

今天上午刚回到公司，接到开发组同事电话，测试数据库主机被新来的小伙非正常关机了，现在数据库起不来了。数据库open时，提示需要介质恢复，恢复时就会报ORA-16433错误。SQL> alter database open;alter database open*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'

2021-07-27 14:47:49 3200

原创 ORA-01200: actual file size of 4 is smaller than correct size of 128 blocks

数据库主机异常重启，遭遇ORA-01200: actual file size of 4 is smaller than correct size of 128 blocks查看ora-01200报错是什么意思oracle@test1:/home/oracle$ oerr ora 120001200, 00000, "actual file size of %s is smaller than correct size of %s blocks"// *Cause: The size of

2021-07-23 14:51:57 406

原创无备份情况下，将数据文件从offline到online

模拟在没有案发的说法别模拟在

2021-07-22 11:30:52 1250 1

原创 Oracle正常关闭后redo丢失

同事自己搭建的测试环境，数据库正常关闭，但在第二天启动时报错：Wed Jul 21 04:48:10 2021Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_11462.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/u01/oradata/orcl/redo

2021-07-21 17:00:05 80

S1156578253的博客