Dba daily struggle

Sybase database occupation script set nocount on declare @pgsize float --select @pgsize = (1048576. / v.low) from master.dbo.spt_values v where v.number = 1 and v.type = 'E' select @pgsize = 1048576 / @@maxpagesize select 'DbName' = substring(d.name, 1, 30), 'Usage' = convert(varchar(15),substring(m.description, 1, 14)), 'Size' = convert(varchar(13),right(space(13) + convert(varchar(10),convert(decimal(10,1), sum(u.size) / @pgsize)),10) + ' Mo'), --'Used' = convert(varchar(13),right(space(10) + convert(varchar(10),convert(numeric(10,1), 'Used' = convert(varchar(13),right(space(10) + convert(varchar(10),convert(decimal(10,1), sum(size - convert(float,curunreservedpgs(d.dbid,u.lstart, u.unreservedpgs))) / @pgsize)),10) + ' Mo'), 'Free' = convert(varc

guide on how to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take a backup) 1. Make the necessary changes to the primary database. a. Enable force logging. b. Creating the password file if one does not exist. c. Create standby redologs. d. Modify the parameter file suitable for Dataguard. 2. Ensure that the sql*net connectivity is working fine. 3. Create the standby database over the network using the active(primary) database files. a. Create the password file b. Create the initialization parameter file for the standby database (auxiliary database) c. Create the necessary mount points or the folders for the database files d. Run the standby creation ON STANDBY by connecting to primary as target database. DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '', '' SET DB_FILE_N

It is an interesting oracle feature.. This feautore allows you to now when a table or a row was modified last time... Each oracle table has a pseudocolum called ora_rowscn , this pseudocolum contains last scn in which a row was modified. So if you need a table last modification date (DML point of view) you can do this.. SQL> select scn_to_timestamp(MAX(ora_rowscn)) FROM OWNER1.TEMP; SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) --------------------------------------------------------------------------- 09-FEB-18 03.22.18.000000000 PM This select works selecting the max oracle SCN (System Change Number, each transaction in database generates a new SCN), so the max(scn) is the last DML in the table... Same way you can see when a specific row was modified last time by: SQL> select scn_to_timestamp(ora_rowscn) FROM OWNER1.TEMP where ID=13; SCN_TO_TIMESTAMP(ORA_ROWSCN) --------------------------------------------------------------------------- 08-FEB-18 02.12.14.000000000 PM

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