DBA

The following three links are my guide documents: https://carlos-sierra.net/2013/05/02/migrating-an-execution-plan-using-sql-plan-management/ https://aprakash.wordpress.com/2012/07/05/loading-sql-plan-into-spm-using-awr/ https://www.databasejournal.com/features/oracle/article.php/3730391/Oracle-Database-11g-SQL-Plan-Management-Part-2.htm Option 1: Create SPB on source then migrate SPB into target Steps: Create SQL Plan Baseline (SPB) in Source From Memory; or From AWR (requires Diagnostics Pack license) Package & Export SPB from Source Import & Restore SPB into Target Pros: Simple Cons: Generates a SPB in Source system Option 2: Create SQL Tuning Set (STS) on source, migrate STS into target, promote STS into SPB in target Steps: Create SQL Tuning Set (STS) in Source (requires Tuning Pack license) From Memory; or From AWR (requires Diagnostics Pack license) Package & Export STS from Source Import & Restore STS into Target Create SPB

#!/bin/bash # ========================================================================= # Name ...........: /u01/dba/bin/bcfenv.sh (ugo+x) # Function .......: Displays the Oracle BCF environment selection menu # Usage ..........: This file is called once at log-on from bashrc, and #                   is also called from command line by typing 'bcfenv'. #                   The menu selection that this script displays is built #                   dynamically from the oratab file. This file, aside from #                   displaying the menu, also echoes back variables by #                   calling it with the 'env' parameter eg: 'bcfenv.sh env', #                   there is an alias in the 'bashrc' called 'echov' which #                   calls this script in that manner. # ------------------------------------------------------------------------- # # History # # Date         Rev  Who                          Comments # ----------

A lot of time I need to know who owns a PC that connecting to database etc. I used to use nbtstat but it's not always tell me the answer. now I use wmic. C:\windows\system32>wmic.exe /node:pcname computersystem get username UserName domainname\jsun thanks to this link: https://community.spiceworks.com/how_to/40336-use-cmd-to-return-the-logged-in-user-of-a-remote-computer

I got a file with two fields delimitered by various length of space in between. CA    CADevserver1 CA      CADevserver1 CA        CADevserver1 ... I need to get the second field only, this is the powershell command I use: PS C:\> gc "c:\dropit\\tmp.txt" |foreach {($_ -split '\s+',2)[1]} > temp.txt The idea come from this link, thanks for that: https://stackoverflow.com/questions/2503010/extracting-columns-from-text-file-using-powershell Though the second field content are saved in temp.txt, the lines in the file cannot be processed properly by the following code: " @echo off for /F "tokens=*" %%A in (temp.txt) do call :processline %%A pause goto :eof :processline set pcpserver=%1 echo "pcpserver name is :"%pcpserver% goto :eof :eof " The above script suppose to loop each line but it did not, I do not have time to troubleshoot, the workaround is to copy the lines from temp.txt and paste to new file an

My PC sometime is rebooted overnight by patching process without noticing me, I do not remember which windows servers I remote control login to, I need to logoff those otherwise when my windows password change next time then my account will be locked because those orphaned login. I saved the remote desktop connection manager configuration in a few groups, for example, one group name is others.rdg. I use this power shell command to query the login session on the servers: PS C:\dropit\remotedesktop> findstr "<name>" others.rdg|foreach-object { $_ -replace "<name>","query session /server:"}|foreach-object { $_ -replace "</name>", ""}         query session /server:servername1         query session /server:servername2 ... copy and paste the output and run it either in powershell or in dos command, I will have the list of sessions who logon to those windows server, then I can logoff from there. PS C:\dropit\

@echo off set passwd=mypassword for /F "tokens=*" %%A in (pcpserverlist.txt) do call :processline %%A pause goto :eof :processline set pcpserver=%1 sqlplus myusername/%passwd%@"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(Host = %pcpserver%.company.com)(Port = port)))(CONNECT_DATA =(sid = sidname)))" < plsqlscript.sql > %pcpserver%.txt goto :eof :eof all the server name need to be saved in the file  pcpserverlist.txt, each server take 1 line

I am setting up new sqlserver maintenance plan, a quick way to do this is to export an existing sqlserver maintenance plan from a gold image server and import it to the new server. the steps are: On the gold image sqlserver: SSMS connect to SSIS, find the maintenance plan in MSDB, right click -> export, save it as a file. On the new image sqlserver: SSMS connect to SSIS, import the package into MSDB SSMS connect to Database engine, find the imported maintenance plan, schedule it to run After completion of the steps, I right click on the job and test run, it failed with error, something like below: "Failed to acquire connection "Local server connection"." The error is caused by the configuration of the gold image, when the maintenance plan is configured there, it uses the server name in the connection manager, and sa account for sqlserver authentication. The fix is to open the exported file, find the line "data source" like below, and modi

created a new database COGSTD, but it's not registered on scan_listener, when connect, the error is: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor In the alert log, when the database started, I usually see the remote_listener and local_listener are set dynamically before database is mount, but on this problematic database, I only see local_listener are set, but not remote_listener ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx0)(PORT=1521))' SCOPE=MEMORY SID='COGSTD3'; Troubleshooting: 1. The service is registered with local listener: node1>lsnrctl status listener|grep COGSTD Service "COGSTD" has 1 instance(s).   Instance "COGSTD1", status READY, has 1 handler(s) for this service... Similar output on node2 and node 3 with the local instnance name 2. The service is not registered with scan_listener: -bash-4.1>srvctl status scan_listener

Here is the case: On standby database: Check the apply lag: SQL>         select name,value from v$dataguard_stats where name in ('transport lag','apply lag') and (value > '+00 01' or value is null); NAME                 VALUE                                                                           -------------------- --------------------                                                           apply lag            +00 08:19:40                                                                    SQL> SELECT INST_ID,  DEST_ID,  DEST_NAME,  STATUS,  TYPE,  SRL,  RECOVERY_MODE FROM  gV$ARCHIVE_DEST_STATUS WHERE  DEST_ID=1;    INST_ID    DEST_ID DEST_NAME                            STATUS    TYPE           SRL RECOVERY_MODE ---------- ---------- ------------------------------------ --------- -------------- --- -----------------------          2          1 LOG_ARCHIVE_DEST_1                   VALID     LOCAL          NO  MANAGED REAL TIME A