DBA
文章平均质量分 70
mygodhome
走自己的路,带上自己的猫,让别人去说吧……
展开
-
Flashback on ADG
Flashback on ADGFlashback on primary, standby will automatically flashback19c (New Feature) : Automatic flashback on standby (CDB) when flashback on primary (CDB) done and DB open with reset-logs (Doc ID 2465585.1) -- The COMPATIBLE initializat..原创 2021-03-12 15:15:36 · 171 阅读 · 0 评论 -
Relocate PDB from 18C to 19C
Steps1.Relocate PDB from 18C to 19C2.Upgrade PDB on 19C side3.Open Relocated PDB on 19C side.4.Wait for 10 mins to check if there is block corruption on relocated PDB on 19C sideEnvironment :source DB: 18CTarget DB: 19CSource DB: ATarget DB.原创 2021-03-03 21:31:57 · 228 阅读 · 1 评论 -
常用的Oracle x$ Tables
x$bhInformation onbuffer headers.Contains a record (the buffer header) for each block in thebuffer cache.This select statement lists how many blocks are Available, Free and Being Used.<span style="color:#444444">select count(*), State fro..转载 2021-02-03 21:28:02 · 283 阅读 · 0 评论 -
Calculate Elapsed Time shell script(计算消耗时间)
当我们用spool log的方式记录RMAN的输出,计算该输出里所有Elapsed Time的时间和:Here is Calculate Time shell script :=========================================================#!/bin/shlogfile=$1grep elapsed $logfile |awk -F " " '{print $NF}' > /tmp/timeList;...原创 2021-02-02 11:36:34 · 182 阅读 · 0 评论 -
ORACLE: Create block chain table and insert into data
Create Block Chain tablesqlplus / as sysdba <<EOFset echo on;set feedback on;select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;col name form a20;select inst_id,name,open_mode from gv\$pdbs where name='${tpdb}';alter session set cont.原创 2021-02-01 15:11:31 · 134 阅读 · 0 评论 -
计算数据库大小
1. 计算数据库里所有数据文件大小总和:SQL> SELECT SUM (bytes)/1024/1024/1024 as GB FROM cdb_data_files; GB----------29.79101562.原创 2021-01-13 19:33:46 · 630 阅读 · 0 评论 -
Oracle 19C CDB操作课程笔记
##########################监听检查lsnrctl start listenerlsnrctl start listener2启动cdb1和cdb2export ORACLE_SID=cdb1sqlplus '/as sysdba'startupexitexport ORACLE_SID=cdb2sqlplus '/as sysdba'startupexitcdb1注册到listenercdb2注册到listener2lsnrctl status原创 2021-01-07 16:53:04 · 1330 阅读 · 0 评论 -
如何在RAC上执行Drop database
在10G之前我们如果要删除一个DB的方法:1) 通过DBCA图形界面,去Delete DB2)用命令行在操作系统级别删除掉数据库的所有物理文件。但是从10g开始ORACLE提供DROP DATABASE语法用来删除数据库。在RAC上不过DROP DATABASE还是有一定的限制条件的: 停掉非第一节点上的DB instance 在第一节点: 关闭数据库SQL> shutdown immediate;以exclusive模式mount第二个实例,并enab...原创 2020-12-07 17:20:12 · 382 阅读 · 0 评论 -
Create refreshable PDB on 19C
1)On both source side and target side: Grant permission—————————————————————————grant execute on sys.DBMS_SQL to system container=all;grantselect any table to system container=all;grant sysdba to system CONTAINER=ALL;GRANT CREATE SESSION, RESO..原创 2020-11-27 11:25:18 · 197 阅读 · 0 评论 -
Oracle DB silent install 静默安装 precheck 失败的解决方法
${ORACLE_HOME}bin/dbca -silent -createDatabase -responseFile /home/dbca.rsp -ignorePrereqFailure [WARNING] [DBT-06208] The 'DBSNMP' password entered does not conform to the Oracle recommended standards. CAUSE: a. Oracle recommends t...原创 2020-11-25 11:19:53 · 1213 阅读 · 1 评论 -
计算ASM diskgroup大小
We are adding datafile in SYSAUX tablespace.First check in the diskgroup how much free space is available to add datafile.You can see in this output we have 3 diskgroups with total size and free size.SQL> SELECT name, free_mb, total_mb, free_mb/total原创 2020-10-30 12:54:20 · 166 阅读 · 0 评论 -
查看linux系统重启时间历史记录
查看linux系统的开机时间/重启历史记录1、who -b命令[root@sky opt]# who -b ---查看最后一次(上次)系统启动的时间system boot Dec 27 05:062、who -r命令[root@sky opt]# who -r ---查看最后一次(上次)系统启动的时间,及运行级别run-level 5 Dec 27 05:063、last reboot命令[root@sky opt]# last rebootreboot system boot 2.原创 2020-10-30 11:03:10 · 13252 阅读 · 0 评论 -
Dump trace files for files between SCN range and entire redo log with specified thread#,sequence#
Dump the trace files for 84 archived log files between SCN 6725691 and 67619071. I got the following 84 archived log files fromv$archived_log between SCN 6725691 and 6761907 atPART ONE.2. And on source CDB name: cdb dump the trace files for 84 ar...原创 2020-10-27 15:04:46 · 250 阅读 · 0 评论 -
如何查询隐含参数的值和描述 (How To query underscore parameter in oracle database)
查询隐含参数的名字,值,及其对应的描述:check_parameter.sqlset lines 1000;col name for a30;col value for a20;col description for a20;select a.KSPPINM name,b.KSPPSTVL value , a.KSPPDESC description from x$ksppi a,x$ksppcv b where a.INDX=b.INDX and a.KSPPINM like '%&原创 2020-10-19 17:02:25 · 169 阅读 · 0 评论 -
SCN 详解
Oracle中的SCN(system change number)和我们的北京时间的意义是相同的,SCN是Oracle中的时间号。为什么Oracle不用时间来界定呢?我在北京时间8:00的时候执行一条DML语句,然后修改机器上的时间为7:00,再执行一条DML语句。如果用机器上的时间区分的话,那Oracle根本区分不出来这两条DML语句的执行顺序——而这一点对于Oracle是很重要的。所以它采用自己产生的SCN来区分所有操作的先后顺序。SCN设计的值很大,所以不用担心达到最大值后怎么办。可.原创 2020-09-28 10:16:52 · 1528 阅读 · 0 评论 -
oradebug dump library cache
Shared_Pool 是 Oracle SGA中最复杂的一部分,在分析很多 library cache 问题时,经常会用到转储命令。oradebug dump library_cache 的常用级别包括 (注意,一定要测试之后再采用,要先看看 library cache 大小,如果库缓存非常大,这个转储的日志可能会是 Huge 的):1:关键结构的统计汇总信息等级2:HASH CHAIN 信息等级4:持有对象结构 Bucket 信息,可以看到一个对象的lock,pin,mutex信息。原创 2020-08-07 13:49:43 · 181 阅读 · 0 评论 -
BCT query 隐含参数
List the parameter name and its value:col name for a50;col value for a20;select a.KSPPINM name,b.KSPPSTVL value from x$ksppi a,x$ksppcv b where a.INDX=b.INDX and a.KSPPINM like '%_bct%';List the value of the parameter:select KSPPSTVL from x$ks...原创 2020-08-03 14:53:10 · 326 阅读 · 0 评论 -
Solved: libclntsh.so.19.1: cannot open shared object file: No such file or directory
Problem decription:[oracle@Host ~]$ rman target /rman: error while loading shared libraries: libclntsh.so.19.1: cannot open shared object file: No such file or directorySolution:Solution:[oracle@Host lib]$ export LD_LIBRARY_PATH=$ORACLE_HOME...原创 2020-07-06 11:35:45 · 3430 阅读 · 0 评论 -
debug related sql
In alert log , we can see:-------------------------------...PDB2(5):Dynamic CPU count change - Current effective cpu count = 8PDB2(5):Recovery scanning directory /oraclebase/oradata/CDB/A96C665A2DE313D5E0531236D60AAB32/datafile/ for any matching fi...原创 2020-07-06 11:18:57 · 120 阅读 · 0 评论 -
Hanlaysis releted sql
Generate hanganalysis:SQL> oradebug setmypid;Statement processed.SQL> oradebug unlimit;Statement processed.SQL> oradebug -g all hanganalyze 3SQL> oradebug -g all hanganalyze 3SQL> oradebug setmypid;Statement processed.SQL> or原创 2020-07-06 10:47:47 · 215 阅读 · 0 评论 -
RMAN crosscheck command作用
CROSSCHECK命令: 用于核对磁盘和磁带上的备份文件,以确保RMAN资料库与备份文件保持同步。注意:该命令只会检查RMAN资料库所记载的备份文件。当执行crosscheck命令时,如果资料库记录不匹配于备份文件的物理状态,那么该命令会更新资料库记录的状态信息。备份文件的状态包括:AVALIABLE、UNAVALIABLE、EXPIRED。 1、核对所有备份集 RMAN> crosscheck backup; 2、核对所有数据文件的备份集 RMAN...原创 2020-05-22 15:50:31 · 229 阅读 · 0 评论 -
How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
TheRMAN CATALOGcommand is used to accomplish the following:Add backup pieces and image copies on disk to the RMAN repository. Record a datafile copy as a level 0 incremental backup in the RMAN repository, which enables you to use it as part of an incr..原创 2020-05-18 09:28:30 · 152 阅读 · 0 评论 -
backup corrupted datafile as copy check V$COPY_CORRUPTION
https://docs.oracle.com/cd/B14117_01/server.101/b10755/dynviews_1051.htmV$COPY_CORRUPTION :Physical datafile block corrupted.Then backup the corrupted datafile:dba:bk $ rman target /c...原创 2020-04-27 18:53:21 · 246 阅读 · 0 评论 -
收集Oracle备份恢复信息
收集Oracle备份恢复信息rman target /spool log to rman_report.logset echo onshow all;report schema;list incarnation;list backup summary;list backup;list copy;report need backup;report obsolete;...原创 2020-04-27 18:48:54 · 195 阅读 · 0 评论 -
How to set and remove event value on oracle
Set mutiple event:alter system set event='10335 trace name context forever,level 1:10235 trace name context forever,level 2:600 trace name heapdump level 1025:7445 trace name heapdump level 1025:650...原创 2020-04-24 16:33:20 · 140 阅读 · 0 评论 -
已解决:ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
oracle@hostA:admin $ rman target sys/<passwd>@cdbRecovery Manager: Release 19.0.0.0.0 - Development on Wed Apr 8 06:19:36 2020Version 19.1.0.0.0RMAN-00571: =============================...原创 2020-04-08 22:50:30 · 884 阅读 · 0 评论 -
怎用oradebug生成call stack - Using oradebug to dump call stack
Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level0 – dump error ...原创 2020-03-31 10:31:06 · 234 阅读 · 0 评论 -
How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
TheRMAN CATALOGcommand is used to accomplish the following:Add backup pieces and image copies on disk to the RMAN repository. Record a datafile copy as a level 0 incremental backup in the RMAN re...原创 2020-02-19 13:07:24 · 173 阅读 · 0 评论 -
Cleanup oracle database on Windows OS
Oracle 11g是甲骨文公司在2007年年7月12日推出的最新数据库软件,Oracle 11g有400多项功能,经过了1500万个小时的测试,开发工作量达到了3.6万人/月。1、如果数据库配置了自动存储管理(ASM),应该先删除聚集同步服务CSS(Cluster Synchronization Services).删除CSS服务的方法是在DOS命令行中执行如下命令: localcon...原创 2020-01-06 10:18:59 · 165 阅读 · 0 评论 -
How to enable KRC and KRB tracing on Oracle
If you enabled BCT.Doc:KRC :Recovery Block Change Tracking (krc)KRC_CHANGES : Recovery Block Change Tracking CHANGES ((null))KRB_TRACE : Set krb trace options KRB : Kernel Backup Restore ...原创 2019-10-31 13:15:05 · 326 阅读 · 0 评论 -
oracle12创建用户错误ORA-65096: 公用用户名或角色名无效 invalid common user or role name
SQL> create user "fdf01" identified by "fdf01";create user "fdf01" identified by "fdf01" *ERROR at line 1:ORA-65096: invalid common user or role nameSQL> create user C##fdf01 ID转载 2017-03-14 12:55:39 · 2794 阅读 · 0 评论 -
OCA之旅(第一阶段:预备知识)
第一阶段:预备知识前面已经看了几遍35页以前的文档了。这里对那一部分不再过一遍笔记了。等以后有了时间再去补上。占用磁盘空间大小物理磁盘:磁盘空间约3GB:oracle11g 的software部分占用磁盘空间约3GB,且基本保持不变。磁盘空间约2GB:数据文件存放实际数据行,会随着数据库的使用情况而伸缩。相对较空的默认种子数据库约占2GB的磁盘空间。内存(RAM):至少原创 2016-11-25 10:07:54 · 465 阅读 · 0 评论 -
Socket是什么
一、问题的引入——socket的引入是为了解决不同计算机间进程间通信的问题1.socket与进程的关系1).socket与进程间的关系:socket 用来让一个进程和其他的进程互通信息(IPC),而Socket接口是TCP/IP网络的API接口函数。2).进程间通信(本机内)进程间通信(不同计算机,要联网)2、socket与文件的关系——如何理解so转载 2016-11-19 17:02:03 · 310 阅读 · 0 评论 -
数据库常用命令和如何删除数据库 solaris
1)查看数据库完整信息:srvctl config database -db database dbname 2)重启数据库:srvctl stop/start database -db dbname3) 查看数据库服务是否运行正常在nodes上:srvctl status service -db dbname4) 查看listener是否正常运行在nodes上:sr原创 2016-10-16 15:19:41 · 2830 阅读 · 0 评论 -
No asm ,database instances after DB re-installation
No asm ,database instances after DB re-installationProblem Description: root@itqe-t4seville-2-zn1-v6:~# crsctl start clusterCRS-4639: Could not contact Oracle High Availability ServicesCRS-400原创 2016-10-16 15:25:06 · 256 阅读 · 0 评论 -
Database can't be started on Node 1 due to serverpool Max value is 1
Database can't be started on Node 1 due to serverpool Max value is 1Problem Description: racusr@itqe-t4seville-1-ld1:~$ srvctl start database -db rig7_dbPRCC-1014 : rig7_db was already running原创 2016-10-16 15:23:20 · 305 阅读 · 0 评论 -
DB can't be started due to Memory lack
DB can't be started due to Memory lackAlert log:/u01/app/racusr/diag/rdbms/itqedbc10/itqedbc1_1/trace/alert_itqedbc1_1.log ORA-00020 ORA-27102: out of memory 1.source sql+.env ORACLE_SID=i原创 2016-10-16 15:21:04 · 272 阅读 · 0 评论 -
/ost-storage/disk6/c6d1 offline caused database been stopped
DB was stopped by itself after added disks into disk groupProblem Description:DB was stopped by itself after we added disks into disk group DIAGNOSTIC ANALYSIS: log /u01/app/crsusr/diag/a原创 2016-10-16 15:02:47 · 496 阅读 · 0 评论 -
[时钟同步]Can't create ASM instance on node2 during running root.sh on node2 due to Time Syc
Can't create ASM instance on node2 during running root.sh on node2 due to time sycProblem Description: Run /u01/app/12.1.0.2/grid/root.sh on node2 after it succesully on node1.Died at /u01/app/原创 2016-10-16 15:00:08 · 675 阅读 · 0 评论 -
test
create user "C##VALEN" identified by ******* profile "DEFAULT" account unlock default tablespace "SYSTEM";alter user "C##VALEN" set container_data=all container=current;原创 2017-03-14 13:51:13 · 712 阅读 · 0 评论