This is excellent series article to read, looks like it's easy to start processing big data. http://www.sqlservercentral.com/stairway/142480/
Posts
This is cool query that worth to refering to in order to find duplicate index that can be removed. However, I do not think the author should use " ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal" because the column order do not need to be same but still can be a duplicated indexed column. I would rather to use "ORDER BY COLUMN_DATA_KEY_COLS.name", this way, does not matter which order the column is indexed in the column list. Same thing about "ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal", I would use "ORDER BY COLUMN_DATA_INC_COLS.name" Below is the link and query: http://www.sqlservercentral.com/articles/Indexing/110106/ ; WITH CTE_INDEX_DATA AS ( SELECT SCHEMA_DATA . name AS schema_name , TABLE_DATA . name AS table_name , INDEX_DATA . name AS index_name , STUFF (( SELECT ', ' + COLUMN_DATA_KEY_COLS . name + ' ' + CASE WHEN...
I use the following SQL to reset the password: select distinct 'alter profile '||profile||' limit PASSWORD_REUSE_MAX UNLIMITED;' from dba_profiles where profile in (select profile from dba_users where ACCOUNT_STATUS like '%EXPIRED%'); select distinct 'alter profile '||profile||' limit PASSWORD_REUSE_TIME UNLIMITED;' from dba_profiles where profile in (select profile from dba_users where ACCOUNT_STATUS like '%EXPIRED%'); select distinct 'alter profile '||profile||' limit PASSWORD_LIFE_TIME unlimited;' from dba_profiles where profile in (select profile from dba_users where ACCOUNT_STATUS like '%EXPIRED%'); select 'alter user '||sys.user$.name||' identified by values '||''''||sys.user$.password||''''||';' from sys.user$, dba_users b where sys.user$.name=b.username and b.account_status like '%EXPIRED%';
Active DataGuard failover: https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB4769 check the status before switchover or failover: SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS; SELECT SWITCHOVER_STATUS FROM V$DATABASE; Switchover steps: Old Primary: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; SHUTDOWN ABORT; STARTUP MOUNT; New primary: SELECT SWITCHOVER_STATUS FROM V$DATABASE; -- value should be TO PRIMARY or SESSIONS ACTIVE ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; ALTER DATABASE OPEN; Bounce the database – this is important, without this step, the v$archive_dest_status will show “bad parameter” and no redo apply on standby. New standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Select * from v$dataguard_stats; SELECT ...
I use this command on linux: for db in `ps -ef|grep ckpt|awk '{print $2}'`; do ps -fp $db; ls -l /proc/$db/cwd done sample output: UID PID PPID C STIME TTY TIME CMD oracle 2452 1 0 Feb19 ? 00:05:30 ora_ckpt_DB1 lrwxrwxrwx 1 root root 0 Feb 19 11:50 /proc/2452/cwd -> /u01/app/oracle/product/11.2.0.3EE/dbs UID PID PPID C STIME TTY TIME CMD oracle 9221 1 0 Feb26 ? 00:02:05 ora_ckpt_DB2 lrwxrwxrwx 1 root root 0 Mar 1 12:40 /proc/9221/cwd -> /u01/app/oracle/product/12.1.0.2/dbs UID PID PPID C STIME TTY ...
We are using OEM 12C, it reports spike of CPU utilization of a server, but vmstat result does not. According to MOS doc, OEM use kstat to calculate CPU usage. How To Calculate CPU Usage on Solaris (Doc ID 1910409.1) And this link has some info about kstat on Solaris: https://blogs.oracle.com/tpenta/entry/how_solaris_calculates_user_system So I deleveloped the following shell script to verify the result: while true; do date >> kstat.dropit kstat -n sys -s 'cpu_nsec*'|awk 'BEGIN {idletotal=0;usertotal=0;systotal=0;total=0} {if ($1~/cpu_nsec_idle/) {idletotal=idletotal+$2;} else if ($1~/cpu_nsec_user/) {usertotal=usertotal+$2;} else if ($1~/cpu_nsec_kernel/) {systotal=systotal+$2;}} END {total=idletotal+usertotal+systotal; userpercent=usertotal*100/total;syspercent=systotal*100/total;idlepercent=idletotal*100/total; print "total="total,"idletotal="idletotal,"usertotal="usertotal,"systotal="systotal,"us...