Dba daily struggle

In this post i´m going to try to describe some connection problems with oracle database. First of all, you should know that there are two main configuration files involved on that: tnsnames.ora --> This file is located on client machine and has necessary information to connect to an oracle database trough a Listener. listener.ora --> This file is in the server side and has a description of a process called listener that is running on server machine listening for new connections to a database. both files are located on $ORACLE_HOME/network/admin directory in their respective servers. TNS-03505: Failed to resolve name This problem is due $ORACLE_HOME/network/admin/tnsnames.ora file do not has an entry for orcltest connection identifier as you can see: [oracle@wcp12cr2 admin]$ tnsping orcltest TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 30-OCT-2017 05:15:52 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /oracle/db/oh

I´m configuring two Virtualbox machines on my laptop, and i need these two machines had connectivity between them, so i configured both with "Host-only Adapter" on virtual-box network setup as you can see below: After that i assign an static Ip address on both machines: [root@wcp12cr2_clone ~]# ifconfig -a eth2 Link encap:Ethernet HWaddr 08:00:27:40:8F:EE inet addr: 192.168.56.100 Bcast:192.168.56.255 Mask:255.255.255.0 inet6 addr: fe80::a00:27ff:fe40:8fee/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:4428 errors:0 dropped:0 overruns:0 frame:0 TX packets:3478 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:375028 (366.2 KiB) TX bytes:1246289 (1.1 MiB) [root@wcp12cr2 ~]# ifconfig -a eth1 Link encap:Ethernet HWaddr 08:00:27:E0:01:7F inet addr: 192.168.56.101 Bcast:192.168.56.255 Mask:255.255.255.0 inet

This document changes DBNAME AND DBID with NID utility method witch is available since Oracle 9.2 If you change the DBID you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 . So your previous backups and archivelogs will be invalidated.. However with NID you can change DBNAME only, without below side effects with NID parameter SETNAME=Y, witch allow you change DBNAME without changing DBID... SQL> select dbid,name,open_mode,activation#,created from v$database; DBID NAME OPEN_MODE ACTIVATION# CREATED ---------- --------- -------------------- ----------- --------- 1423238009 ORCL READ WRITE 1423232377 11-NOV-15 We create a new pfile from spfile in order to modify it later... SQL> create pfile from spfile; File created. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Adva

Today i receive a monitor alert about some locks in one of the databases i manage, so i run this query because is faster than traditional queries based in v$lock view: SQL> SELECT * FROM dba_waiters where MODE_HELD <>'None' or MODE_REQUESTED <>'Share'; WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 --------------- --------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ---------- 398 336 Transaction Exclusive Share 327692 9591040 397 336 Transaction Exclusive Share 327692 9591040 394 336 Transaction

Sometimes is useful to have a database growth estimation in order to avoid some issues or to be a proactive DBA, or maybe your boss wants to know how much money is needed to add some space to oracle database during the next year. In such situations is good to know dbms_space.OBJECT_GROWTH_TREND procedure and dba_hist_tbspc_space_usage view. With dbms_space.OBJECT_GROWTH_TREND you will get an output like that, in this example i´m selecting MYTABLE table of MYOWNER owner grown trends, as you can see QUALITY column is equal to PROJECTED , this means that the value is in the future, and this is a grow prevision. SQL> set linesize 190 col TIMEPOINT for a40 select TIMEPOINT,SPACE_USAGE/1024/1024 as SPACE_USAGE_MB, SPACE_ALLOC/1024/1024 as SPACE_ALLOC_MB, QUALITY from table(dbms_space.OBJECT_GROWTH_TREND ('MYOWNER','MYTABLE','TABLE')); SQL> SQL> TIMEPOINT SPACE_USAGE_MB SPACE_ALLOC_MB QUALITY -------------------------

By default, Mysql logs are disabled, but if you started mysqld with mysqld_safe script log-error will be enabled by this script to a standard location. Some of then can be dynamically enabled or disabled and other not as you can see below: Error log --> Problems encountered starting, running, or stopping mysqld General query log--> Established client connections and statements received from clients --> Can be enable or disabled without instance restart Binary log--> Statements that change data (also used for replication) Relay log --> Data changes received from a replication master server Slow query log--> Queries that took more than long_query_time seconds to execute --> Can be enable or disabled without instance restart DDL log (metadata log)--> Metadata operations performed by DDL statements Error log To enable fisrt one (Error log) it is needed to restart Mysql instance and startup it with this parameter --log-error={path

It is not strange thing that you have a Mysql running, but you miss the root password for it.. There are two ways of doing that: If you can restart your database or not, lets start at the first case which is the simplest way to do that: With database restart [root@quickstart bin]# mysql -u root -p123 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) fisrt thing to do is to perform a ps -ef | grep mysqld in order to see mysqld startup options [root@quickstart bin]# ps -ef | grep mysqld root 10421 7905 0 05:29 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock mysql 10538 10421 0 05:29 pts/1 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/m

In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files

This article is about automatic Sql Tunning in Oracle database, it is available from 10g version. First step is to create the tunning task. To do that we need to know sql_id of the query, a begin snapshot and end snapshot in which this query was executed: SQL> DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 2 3 begin_snap => 27409 , end_snap => 27451 , sql_id => '6ay80wrrk0a5w' , scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 560, task_name => '6ay80wrrk0a5w_AWR_tuning_task', description => 'Tuning task for statement 6ay80wrrk0a5w in AWR.'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / 4 5 6 7 8 9 10 11 12

In this article i´m trying to make a query performance analysis based on ASH (active session history) views. Sometimes you need an historical point of view of a query due to this query is increasing its executions times, or query changed its execution plan. First step is to select top queries your database has: select * from ( select SQL_ID , sum(decode(session_state,'ON CPU',1,0)) as CPU, sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT, sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO, sum(decode(session_state,'ON CPU',1,1)) as TOTAL from v$active_session_history where SQL_ID is not NULL group by sql_id order by sum(decode(session_state,'ON CPU',1,1)) desc ) where rownum <11; for example, in my database: SQL> select * from ( select SQL_ID ,