DBA

After GI is patched. the " kfod op=PATCHLVL " and " crsctl query crs softwarepatch " ,output mismatched from node 3 comparing to node 1&2, though "opatch lsinventory" output match... Patch ID  28729169 and  4104219146 were not in the output of " kfod op=PATCHES   " on node 3. Created oracle SR, the support suggested the following steps to fix the issue. root>patchgen commit -pi 28729169  root>patchgen commit -pi  4104219146    check the below on all nodes and notify if there is any difference  kfod op=PATCHLVL  kfod op=PATCHES  If they are same run the below  # rootcrs.sh -patch  That fixed the problem.

According to this Doc, I can remove the patch folder in .patch_storage if the patch is not listed in opatch inventory. How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. (Doc ID 550522.1) The following two scripts work the same way, they list the patch number that has zero match in opatch inventory. #1: $ORACLE_HOME/OPatch/opatch lsinventory>dropit; for patch in `ls|cut -d "_" -f 1|grep [0-9]`;   do echo $patch:`grep -c $patch dropit`; done|awk -F":" '{if ($2==0) print $0}' #2 ls $ORACLE_HOME/.patch_storage|cut -d "_" -f 1|grep [0-9] > patchstorage.txt; $ORACLE_HOME/OPatch/opatch lsinventory>opatchinventory.txt; for patch in `cat patchstorage.txt`; do echo $patch:`grep -c $patch opatchinventory.txt`; done|awk -F":" '{if ($2==0) print $0}' sample output: 19872484:0 20299018:0 20831113:0 21436941:0 23854735:0 #3: this will display the ls command of those pat...

We established some best practice rules for SQL Server, we need method to collect these data and analyze the compliance. Powershell script is our solution。 The Best Practice rules are: ##• Do not install sqlserver on C drive, ##• do not put database files on C drive ##• Set maximum memory: should not left as default, leave 1GB memory for OS ##• Agent start automatically ##• Database auto growth on, with 10% percentage ##• Database backup regularly, either by SQLServer maintenance, Netbackup agent or Doc Ave ##• Transaction log backup regularly to prevent db size growth out of control ##• Disable CLR, with exception on kdcwdbsqlprd03 where respontek requires CLR. ##• Disable xp_cmdshell ##• Database recovery mode: full recovery mode for production, simple recovery mode for PPE ##• Turn off DB autoshrink Step 1: create the tables in oracle where the data will be saved, we choose oracle, it can be MSSQL, create table mssqlhostcpuinfo (hostname varchar2(...

The Oracle RAC databases were being backed up regularly without using catalog, even worse, the database controlfile autobackup was turned off.  That configuration compromise the RPO. I restored and recovered the database to the latest scn that the last controlfile backup knows, but not the latest scn that available in the archivelogs. Below are the steps: The assumption is that all database files, controlfile are gone. 1. list available backup handles in netbackup after a particular date: /usr/openv/netbackup/bin/bplist -l -S $netbackupserver -C $client -t 4 -R -s 11/29/2018 / output: ... -rw-rw---- oracle    asmadmin     19922944 Nov 29 11:55 /ctrl_dAPIMGRS_u16tje9lj_s1062_p1_t993470131 -rw-rw---- oracle    asmadmin    142082048 Nov 29 11:54 /arch_dAPIMGRS_u15tje9kd_s1061_p1_t993470093 ... From the above output, we guess the latest controlfile backup is in /ctrl_dAPIMGRS_u16tje9lj_s1062_p1_t993470131 2.  restore cont...

The following are the end-to-end coding solution to collect MSSQL system information (CPU/cores, memroy, disk space and free space, OS version, database version and edition), and load the information to Oracle database as CMDB repository, the steps are: Create tables as CMDB repository in oracle db: mssqlhostcpuinfo ; mssqlhostmemoryinfo ; mssqlhostdiskinfo ; mssqlhostosinfo ; mssqlhostdbinfo ; Truncate the above tables Run powershell to collect MSSQL server information and output to files mssqlcpu.txt, ssqlos.txt,mssqldb.txt,mssqlmem.txt,mssqldisk.txt Run sqlldr and load above txt file into the repository tables Create view mssqlcmdball, mssqlcmdbserver, mssqlcmdbdb Step 1: create table mssqlhostcpuinfo (hostname varchar2(20), cores int,logicalcors int); create table mssqlhostmemoryinfo (hostname varchar2(20), membytes number(13)); create table mssqlhostdiskinfo (hostname varchar2(20), diskletter varchar2(2),capacity number(13), freedisk number(13)); c...

--   Yannick Jaquier  blog helps me to create this solution. Thank you Sir! --  https://blog.yannickjaquier.com/oracle/automatic-sql-tuning-task-overview.html spool c:\dropit\sqlidautosqltune.txt exec dbms_output.put_line('#####find which execution_name and object_id has this sql_id run') select distinct execution_name,object_id,trunc(TIMESTAMP) from DBA_ADVISOR_SQLPLANS where sql_id='&sqlid'; -- DBA_ADVISOR_SQLSTATS also have execution_name and object_id as in DBA_ADVISOR_SQLPLANS, but not always, I randomly checked about ten sql_id, all of them exists in DBA_ADVISOR_SQLPLANS, but some of them are not in DBA_ADVISOR_SQLSTATS exec dbms_output.put_line('#####gather the execution_name and object_id which has the sqlid, run report on it') SET lines 200 pages 1000 SET LONG 999999999 SET longchunksize 200 SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK('&&execution_name','&&execution_name','TEXT','ALL',...

I have a GG replication special run on multitenant databases, run it in ggsci GGSCI (KDCWASDBAPRD01) 8> view params rinitst1 replicat rinitst1 specialrun end runtime SETENV (ORACLE_SID = "orcl") SETENV (ORACLE_HOME = "D:\oracle\product\12.2.0\dbhome_1") userid c##ggadmin@test2 password password assumetargetdefs extfile ./dirdat/tcustord.dat discardfile ./dirrpt/rinitst1.dsc,purge map TEST1.*.*, target TEST2.*.*; GGSCI> start rinitst1 GGSCI> view report rinitst1 。。。 2018-10-03 11:11:48  ERROR   OGG-10144  (RINITST1.prm) line 2: Parameter [specia lrun] is not valid for this configuration. 。。。 because it complains about "specialrun", so I removed the line from parameter file, and run again, it does not throw any error message in ggserr.log, trace file, and report file,but data is not replicated either. After tried many things, finally, I run the replicat from command line, it throw error that I can chew on. D:\oracle\produc...