Jun-15

30-June

List of all Master tables registered in a materialized view log.

 SQL> select MOWNER,SNAPTIME from sys.slog$ where MASTER='<table name>’

25-June

HDFS file system commands

$  hadoop fs => List of commands

$  hadoop fs –ls => file system list

$  hadoop fs –ls /directory1/director2/ => list a directory

$ hadoop fs –ls hdfs://NN hostname/user/ => accessing HDFS from  DN

$ hadoop fs –mkdir <dir name>

$ hadoop fs –put <source> <destination>  => copy a file

$ hadoop fs –CopyFromLocal  => copy from local FS

$ hadoop fs –MoveFromLocal => move from local

$ hadoop fs –get <hdfs > <local dir>

$ hadoop fs –MoveToLocal

$ hadoop fs –CopyToLocal

$ hadoop fs –cat <file name> => to display the file contents

$ hadoop fs –rm

$ hadoop fs –rmr  => remove dir recursively

$ hadoop fs –cp <source> <destination>

$ hadoop fs –mv <source> <destination>

$ hadoop fs –du => disk usage

$hadoop fs –setrep <n>  <file name> => Setting number of replications, default is 3

22-June

Extended Oracle statistics on a column groups – a good blog.

https://blogs.oracle.com/optimizer/entry/extended_statistics

21-June

DISK_REPAIR_TIME is default is 3.6 Hrs, is the delay time a ASM rebalance will kick in after disk failure.  You can increase this diskgroup attribute as

SQL> Alter diskgroup data set attribute ‘disk_repair_time’=’5h’;

When you drop a disk in diskgroup, ASM will not wait for this threshold for the rebalance.

20-June

Hanganalyze in cluster level

SQL>  oradebug setmypid
SQL> oradebug setinst all
SQL> oradebug –g all hanganalyze 3

18-June

Waits for a user ID

set linesize 200
col event for a30
col username for a20
set pagesize 40
col p1 for 999999999999
col OSUSER for a12
select inst_id,v.seq#,SQL_EXEC_ID,v.sid,username,v.event,
v.p1,v.p2,v.p3,sql_hash_value,sql_id,SQL_CHILD_NUMBER,s.seconds_in_wait
from gv$session_wait v, gv$session s  where s.sid=v.sid  and
s.INST_ID=v.INST_ID and username =’&username’
order by sql_id;

16-June

AWR_SNAPSHOT_TIME_OFFSET:  typically the AWR snapshots will be taken at the top of the hour and all the databases in a server triggering snapshot may lead to CPU spikes.  You can specify this parameter between 0 – 3599 to specify an offset of an hour.  All the instance of a database should share the same value.  A constant 1,000,000 will enable automatic mode and the AWR snapshot time will be based on the name of the database.

Temporary UNDO:  Oracle 12c can direct the temporary undo (UNDO generated by operations on temporary tables) to the temporary tablespace rather than to instance UNDO tablespace.  You may set TEMP_UNDO_ENABLED=TRUE and default value is TRUE.  These temporary undo segments are known as Temporary Undo Segments.

http://docs.oracle.com/database/121/ADMIN/undo.htm#ADMIN13741

15-June

List 12c new underscore parameters

http://flaviosoares.com/2014-09/os-novos-parametros-oracle-database-12c/

12c New parameters

1. cell_offloadgroup_name Set the offload group name
2. clonedb          clone database
3. connection_brokers                     connection brokers specification
4. cursor_bind_capture_destination Allowed destination for captured bind variables
5. db_big_table_cache_percent_target Big table cache target size in percentage
6. db_index_compression_inheritance options for table or tablespace level compression inheritance
7. db_unrecoverable_scn_tracking Track nologging SCN in controlfile
8. dnfs_batch_size        Max number of dNFS asynch I/O requests queued per session
9. enable_pluggable_database Enable Pluggable Database
10. heat_map            ILM Heatmap Tracking
11. max_string_size        controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
12. nls_timestamp_tz_format timestamp with timezone format
13. noncdb_compatible     Non-CDB Compatible
14. optimizer_adaptive_features controls adaptive features
15. optimizer_adaptive_reporting_only use reporting-only mode for adaptive optimizations
16. parallel_degree_level      adjust the computed degree in percentage
17. parallel_degree_policy     policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)
18. parallel_fault_tolerance_enabled     enables or disables fault-tolerance for parallel statement
19. pdb_file_name_convert PDB file name convert patterns and strings for create cdb/pdb
20. pga_aggregate_limit      limit of aggregate PGA memory consumed by the instance
21. processor_group_name Name of the processor group that this instance should run in.
22. spatial_vector_acceleration   enable spatial vector acceleration
23. temp_undo_enabled   is temporary undo enabled
24. threaded_execution     Threaded Execution Mode
25. unified_audit_sga_queue_size Size of Unified audit SGA Queue
26. use_dedicated_broker    Use dedicated connection broker

12-June

DB_WRITER_PROCESS default value is either 1 or number of CPUs / 8.    There can be maximum of 36 writer processes from DBW0 – DBW9 and DBWa – DBWj.

It is always recommended to keep 1 DB Writer processes.  A very good blog, explaining why we should configure only one DB_WRITER_PROCESS.

http://kevinclosson.net/category/dbwr-performance/

taskset linux command to pin a process to a CPU socket.  For example the PID 6528 to pin to a socket

# taskset –pc 2-3 6528

07-June

PARALLEL_FORCE_LOCAL: TRUE parameter restricts the slave processes to open in the local instance if FALSE will open the parallel slave connections in all nodes in the cluster.

Global Data Services (GDS): is global service configuration which spans across multiple databases and severs geographically different locations.  Global Service Catalog holds the configuration information and directs the connections to an appropriate local service.   Various components in the service catalog communicated through ONS (Oracle Notification Services).  GDSCTL utility can be used to add, modify and delete Global Service catalog information.

02-June

The view V$RECOVERY_PROGRESS will give the SCN and time of the last applied redo.

COMMENTS : SCN numbers

TIMESTAMP : Last applied log time stamp

V$DATAGUARD_STATE also gives the status of both Transport and Apply lag.