Skip to main content

Posts

Showing posts from 2017

Drop Tuning Set of Oracle Database

Tuning Set Name : Test_TOP_SQL_1503437315950

select name from dba_sqlset;
select * from dba_sqlset where name like 'Test%';
Dropping SQL Task
select name from dba_sqlset;
execute dbms_sqltune.drop_sqlset('Test_TOP_SQL_1503437315950');
You could get an error, if it is reference to advisory task
Get advisory task name
select description, created, owner  from DBA_SQLSET_REFERENCES  where sqlset_name = 'Test_TOP_SQL_1503437315950';
task: SQL_TUNING_1503437363054
select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = 'SQL_TUNING_1503437363054';
execute dbms_sqltune.drop_tuning_task('SQL_TUNING_1503437363054');
execute DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'Test_TOP_SQL_1503437315950');
If you can't drop SQL Set then check whether record exist in work load repository or not
SELECT count(*)  FROM wri$_sqlset_definitions a, wri$_sqlset_references b  WHERE a.name = 'Test_TOP_SQL_1503437315950'  …

Smart scan for ASM disk group in Exadata

Enable smart scan for ASM Disk group while creating disk group in Exadata

SQL> CREATE DISKGROUP data HIGH REDUNDANCY
DISK 'o/*/DATA*'
ATTRIBUTE 'AU_SIZE' = '4M',
          'content.type' = 'data',
          'cell.smart_scan_capable'='TRUE',
          'compatible.rdbms'='11.2.0.2',
          'compatible.asm'='11.2.0.3';

Ref.: http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-administering-asm.htm#SAGUG20526

Kfod tool to inspect ASM disk in Exadata environment

Oracle kfod command line tool for ASM disk inspection:

kfod tool is located in grid home's bin directory.

List optinos for kfod toll using

$kfod -h
_asm_a/llow_only_raw_disks              KFOD allow only raw devices [_asm_allow_only_raw_disks=(TRUE)/FALSE]
_asm_l/ibraries         ASM Libraries[_asm_libraries=lib1,lib2,...]
_asms/id                ASM Instance[_asmsid=sid]
_b/oot          Running in pre-install env (boot=TRUE/FALSE)
_f/lexinfo              Provide flexinfo      (_flexinfo=TRUE/FALSE)
_p/atch_lib             Patchlib [_patch_lib=<asmclntsh_path>]
_u/ser          OS Username
asm_/diskstring         ASM Diskstring [asm_diskstring=discoverystring, discoverystring ...]
asmc/ompatibility               Include diskgroup ASM compatibility [asmcompatibility=TRUE/(FALSE)]
cli/ent_cluster         client cluster name
clus_/version           cluster version
clust/er                KFOD cluster [cluster=TRUE/(FALSE)]
db_/unique_name         db_unique_name for ASM instance…

Oracle Parallel Server Option in Explain Plan

Oracle parallel option:
Parallel option parameter in int.ora:

parallel_degree_level                        integer     100
parallel_degree_limit                        string      CPU
parallel_degree_policy                      string      AUTO
parallel_execution_message_size     integer     15343
parallel_max_servers                        integer     743
parallel_min_servers                         integer     76
parallel_min_time_threshold            string      AUTO
parallel_server                                   boolean     TRUE
parallel_server_instances                  integer     4
parallel_servers_target                      integer     374
parallel_threads_per_cpu                  integer     2

In an explain plan PX stands for Parallel processing.
PX Send are producer
PX Receiver receiver
PX Coordinator
PX COORDINATOR FORCED SERIAL operation means that, although the plan looks like a parallel execution, Oracle effectively will run this serially at execution time.
IN-OUT col…

Oracle Generate Explain Plan and plan statistics for SQL statement

Option 1: Display sql statement execution plan and sql execution statistics also.
SQL> set pagesize 0
SQL> set linesize 5000
SQL> spool plan_q1.log
SQL> set auto trace on
SQL> @sql_statement.sql
SQL> spool off
Explain plan could lie explore at Kerry Osborne's Blog - http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/
Option 2: Use DBMS_XPLAN, get explain plan from awr, using SQL_ID.
SQL> set pagesize 0
SQL> set linesize 5000
SQL> spool plan_q1.log
SQL> select * from table(dbms_xplan.display_awr('sql_id'));
SQL> spool off
Option 3: Enterprise Manager





Find out location of OCR and Voting Disk for Oracle RAC environment

Find out OCR Location for RAC environment:-
Option 1: Get the ocr location form file ocr.loc location at /etc/oracle
cat /etc/oracle/ocr.loc
Option 2: Execute ocrcheck utility form grid home. Typical grid home is Typical grid home is /u01/app/<version>/grid/bin
./ocr check
Find out location of Voting disk:-
Option 1: Query V$ASM_DISK view.
If it is Exadata you would be able to see path as o/cell_ip_address/cell_griddisk_name. i.e. o/192.168.1.1.....
SELECT PATH FROM V$ASM_DISK WHERE VOTING_FILE='Y';
Option 2: Use utility crsctl. crsctl is location in bin directory of grid home. Typical grid home is /u01/app/<version>/grid/bin
./crsctl query css votedisk

MySQL mysqladmin

mysqladmin utility:-


mysqladmin is use for administrative operations. Check the server's configuration, current status and other task.

$mysqladmin [options] command [command-arg] [command [command-arg]] ...

create db_name - To create new database.

debug - Write debug information to error log.

drop db_name - Delete the database and tables.

extended-status - Display server status variables and their values.

flush-hosts - Flush all the information in host cache.

flush-logs [log_type ...] - Flush all logs.

flush-privileges - Reload the grant tables it is same as reload.

flush-status - Clear status variables

flush-tables - Flush all tables.

flush-threads - Flush the thread cache.

kill id,id,... - Kill server threads.

password new_password - Set a new password.

ping - Check whether the server is available.

processlist - Show a list of active server threads.

reload - Reload the grant tables.

refresh - Flush all tables and close and open log files.

shutdown - Stop the server.

start-slav…

MySQL NDB Cluster Installation for Data, SQL and Management Node

Management node: It manages the other nodes within the NDB Cluster, provides configuration data, start and stop nodes, running backups. As it manages the configuration of the other nodes, it should be started first, before any other node. Use ndb_mgmd to start it.
It also maintains a log of cluster activities. Management clients can connect to the management server and check the cluster's status.
Data node: It stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you need four data nodes. One replica is good for data storage, but no redundancy. For redundancy and high availability 2 more nodes are recommended.To start data node use ndbd or ndbmtd. Tables are normally stored completely in memory, that's why it can be refer as in-memory database. Some data can be stored on disk.
SQL node: It accesses the cluster data. It is a traditional MySQL server that uses the NDBCLUSTER…

Oracle Daily SQL

-- Enable System-wide tracing:
alter system set events '10046 trace name context forever,level 12';

-- Disabled in all sessions
alter system set events '10046 trace name context off';

-- Session Tracing
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- crsctl for Oracle RAC

./crsctl start cluster
./crsctl check crs

- Make column to CLOB

ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME LONG);
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME CLOB);
RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>;

- Rename column, change data type, drop column

ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME TO COLUMN_NAME1;
ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME VARCHAR2(10));
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

-- Create Primary Key

ALTER TABLE TABLE_NAME ADD C…

Oracle Golden Gate Basic Classic

Create environment
01. Source & target - Create GG tablespace.
02. Source & target - Create the GoldenGate Schema Owner
03. Grant privilege including DBA to GG schema owner
04. Add schema owner to global parameter file ./GLOBALS
05. Execute role set up script to create role GGS_GGSUSER_ROLE
06. Grant role GGS_GGSUSER_ROLE to GG user at both Source and target
Configure GG  Extract
01. Source and Target - Configure Manager Parameters
$ ggsci
GGSCI 1> EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
02. Source, create parameter file for Extract - ex1,
EXTRACT ex1
USERID <id>, PASSWORD <pwd>
EXTTRAIL /home/oracle/goldengate/dirdat/ex
TABLE <Schema>.*;
03. Source, configure Data Pump Parameters
EXTRACT dp1
USERID <id>, PASSWORD <pwd>
RMTHOST <hostname>, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
TABLE <schema>.*;
Target - Create check point table
$ ggsci
GGSCI 1> DBLOGIN USERID <id>, PASSWORD <pwd>
GGSCI 2>…

Oralce Pluggable Database

Pluggable database administration:-

Open all pluggable databases:
Connect to container database using oraenv
$. oraenv=DB_Name
$sqlplus "/as sysdba"
SQL> alter pluggable database all open;
Get pluggable database status:
SQL> select con_id, dbid, name, open_mode from v$pdbs;
Switch to Puggable database from container :
SQL> alter session set container = <Puggable_DB>;
Get status of Pluggable database
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
Get file location for pluggable database:
SQL>select file_name from dba_data_files;
Create table space for pluggable datatbase:
SQL> alter session set container=<Pluggable_DB>;
Session altered.
SQL> CREATE TABLESPACE TB_Name LOGGING DATAFILE '/<Path>/<File_Name>.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Set new t…