sticky notes

MySQL Oracle

============Updated Sticky Notes (15/10/2019===============

crosscheck backup;
delete noprompt obsolete ;
delete noprompt expired backup;

list expired archivelog all;
crosscheck archivelog all;
delete expired archivelog all;
delete noprompt archivelog all;

delete noprompt backup;
delete noprompt archivelog all;


find /Backup/ -mtime +15 -exec rm {} \;

find /Backup/ -mtime +15 -exec rm {} \;

update test1 set moddtm=concat(date(now()),’ ‘,time(moddtm)) ; << like this

update test1 createdtm=concat(date(now()),’ ‘,time(createdtm)) ;

update test1 set createdtm=concat(date(now()),’ ‘,time(createdtm)) ;


To get all tables and columns of type ‘datetime’ you can use:

SELECT
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
DATA_TYPE=’datetime’
One possibility is to run this query, and in code, foreach such column, run an update query, such as: (assuming scheme, table, column are variables from previous query)

UPDATE scheme.table
SET column= ADDTIME(column, ’36:00:00′)
WHERE (column BETWEEN ‘2015-09-29 10:15:55’ AND ‘2015-01-30 14:15:55’)


create tablespace tbsgg DATAFILE ‘+DATA’ SIZE 2G autoextend on;

create tablespace tbsgg DATAFILE ‘/u01/app/oracle/testdb/tbsgg.dbf’ SIZE 2G autoextend on;

/u01/app/oracle/testdb/


gzip fullbkp_12122018.dmp
vi /usr/local/nagios/etc/nrpe.cfg

cat /dev/null > listener.log

find . -name ‘+ASM1_ora_*’ | xargs rm
tail -f /var/log/cron
find / -name file.look
SET GLOBAL max_allowed_packet=1073741824;
/Data/wildfly-12.0.0.Final/bin

—–calculate total used disk space by files older than 70 days using find—–

find . -mtime +70 -exec du -ks {} \; | cut -f1 | awk ‘{total=total+$1}END{print total/1024}’

select sum(bytes)/(102410241024) from dba_data_files;

—–RMAN KILL SESSION/JOB—-

select b.sid, b.serial#, a.spid, b.client_info from v$process a, v$session b where a.addr=b.paddr and client_info like ‘rman%’;

alter system kill session ‘9,59671’ immediate;

  • if you want to make sure the query cache is fully disabled, change query_cache_size and query_cache_type to 0 and restart MySQL.

stop slave;
reset slave;
start slave;

plus below if resetting doesnt works,

mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000012 | 154 |test | mysql | |
+——————+———-+————–+——————+——————-+
1 row in set (0.01 sec)

CHANGE MASTER TO MASTER_HOST=’192.168.55.54′, MASTER_USER=’replication’, MASTER_PASSWORD=’slave’, MASTER_LOG_FILE=’mysql-bin.000015′, MASTER_LOG_POS=154;

mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000011 | 154 |test1 | mysql | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

CHANGE MASTER TO MASTER_HOST=’192.168.54.56′, MASTER_USER=’replication’, MASTER_PASSWORD=’slave’, MASTER_LOG_FILE=’mysql-bin.000014′, MASTER_LOG_POS=154;

Two of my favorite quotes are:

ìA penny saved is a penny earnedî, and ìTime is moneyî.


ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

——————-VIEW Permission——————————–

GRANT CREATE ANY VIEW TO Scott;
grant GLOBAL QUERY REWRITE to scott;
grant CREATE TABLE to scott;
grant CREATE MATERIALIZED VIEW to scott;
grant UNLIMITED TABLESPACE to CORESTAGE;


alter system set DB_RECOVERY_FILE_DEST_SIZE=10G SID=’‘; alter system set DB_RECOVERY_FILE_DEST=’+FRA’ SID=’‘;

Select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE;

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status=’ACTIVE’ AND UserName IS NOT NULL;

[[email protected] incident]$ pwd
/Data/app/oracle/diag/rdbms/shiredb/shiredb/incident

[[email protected] incident]$ adrci

ADRCI: Release 12.2.0.1.0 – Production on Wed Jul 31 07:33:25 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

ADR base = “/Data/app/oracle”
adrci> show homes
ADR Homes:
diag/rdbms/shiredb/shiredb
adrci> set homepath diag/rdbms/shiredb/shiredb
adrci> show control

ADR Home = /Data/app/oracle/diag/rdbms/shiredb/shiredb:


ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME


1434864270 720 8760 2018-06-28 01:16:30.405489 -04:00 2019-07-31 03:22:48.935505 -04:00 1 2 107 1 2018-06-28 01:16:30.405489 -04:00
1 row fetched

adrci> purge -age 10080

==================================================================================================

sqlplus -prelim “/as sysdba”

SQLNET.EXPIRE_TIME=10

find

delete/hide project in exchange module

select * from transformation where Name=’Validation_Project’ and studyinfoid=’24a62a22-730c-11e9-9a12-0050568817ef’
select * from studyinfo where title=’CDISCPILOT01′ and tenantid=’cfbbc25d-7ea1-11e8-97d0-0050568817ef’

select * from tenant

set foreign_key_checks=1;

crosscheck backup;
delete noprompt obsolete ;

delete noprompt expired backup;

list expired archivelog all;
crosscheck archivelog all;
delete expired archivelog all;
delete noprompt archivelog all;

delete backup;

delete archivelog all;

cat /dev/null > listener.log

gzip fullbkp_12122018.dmp


Alter system set job_queue_processes=20;

SELECT * FROM users FETCH NEXT 10 ROWS ONLY;

grep “24-MAR” /opt/oracle/diag/tnslsnr/stagedevdb2/listener/trace/listener.log | awk ‘{ if ( $NF != 0 ) print $0 }’

$ export TWO_TASK =

sqlplus /nolog

conn sys/pass as sysdba

The setting of TWO_TASK overrides the ORACLE_SID when set. You will not connecting to a local database with two_task but rather using sqlnet to connect to a remote database.
setted in sb/pp/prod =10

———————high cpu usage oracle—————————————–

If you do notice the run queue exceeding the amount of CPUs, it’s a good indication that your server has a CPU bottleneck. Inside Oracle, you can display CPU for any Oracle user session with this script:

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like ‘%CPU used by this session%’
and
se.SID = ss.SID
and
ss.status=’ACTIVE’
and
ss.username is not null
order by VALUE desc;

———————-fIND DUPLICATES RECORDS—————————————————–

select StudyName, count(StudyName) as times from StudySourceInfo group by StudyName having times>1;


openssl enc -aes-256-cbc -in /root/test.sql -out /root/test.file

openssl enc -aes-256-cbc -d -in /root/test.file > /root/test.sql

/usr/sbin/mysqld –help –verbose –skip-networking –pid-file=$(tempfile) 2> /dev/null | grep -A1 ‘Default options are read’
use mysql;
CREATE USER [email protected]’%’ IDENTIFIED BY ‘maxissb*123’;
GRANT SELECT ON ctrdb.UserStudyInfo TO [email protected]’%’;
FLUSH PRIVILEGES;


/u01/app/grid/diag/crs/ctr24val-ora2/crs/trace

Script Location : http://sdlc.maxissoftware.com:6060/svn/DBScripts/ctr_2_4/Scripts

strings expdp_fullbkp_Thu28Mar2019_01.dmp | head -n 10


srvctl stop instance -d demodb -n DEMO-ORA1

———-kill user session————————-

SELECT username, count(username) FROM v$session WHERE username IS NOT NULL group by username;

BEGIN
FOR r IN (select sid,serial# from v$session where username = ‘USER’)
LOOP
EXECUTE IMMEDIATE ‘alter system kill session ”’ || r.sid
|| ‘,’ || r.serial# || ””;
END LOOP;
END;


Below setted in dev 221 on 20/march
set global table_open_cache=2500;

————-expdp/impdp missing grant after import————-

expdp full=y dumpfile=expdp.dmp logfile=expdp.log content=metadata_only exclude=STATISTICS

impdp schemas=FOO dumpfile=expdp.dmp sqlfile=impdp.sql

impdp full=y dumpfile=expdp.dmp sqlfile=fullimpdp.sql INCLUDE=GRANT

expdp full=y dumpfile=fullbkp_31jan2020.dmp logfile=fullbkp_31jan2020.log directory=DATA_PUMP_DIR

Ref:https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=363812589722040&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=795784.1&_afrWindowMode=0&_adf.ctrl-state=14q3m6tpea_4

SHOW OPEN TABLES WHERE In_use > 0

You get the locked tables only of the current database.

— Allow read-only access to single table in database.

use mysql;
CREATE USER ‘test’@’%’ IDENTIFIED BY ‘test’;
GRANT SELECT ON ctrdb.UserStudyInfo TO ‘test’@’%’;
FLUSH PRIVILEGES;

oracle db size:

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;
select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;

select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;

DROP all tables starting with ìEXT_î in Oracle SQL

select ‘drop table ‘ || table_name || ‘;’
from user_tables
where table_name like ‘EXT_%’;

——————-create database date——————————————-

SELECT
table_schema,
MAX(create_time) create_time,
MAX(update_time) update_time
FROM information_schema.tables
Group by TABLE_SCHEMA
Order by create_time desc


Truncate file

cat /dev/null > listener.log

gzip fullbkp_12122018.dmp

find . -name ‘+ASM1_ora_*’ | xargs rm

alter table assetactivity add column DefaultReason varchar(1024) default null;

— Nagios alert Space check
cat /usr/local/nagios/etc/nrpe.cfg

SELECT CONCAT(‘DROP DATABASE ', SCHEMA_NAME, ';’)
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE ‘cms_%’;


sqlplus OLTP_READONLY/[email protected]'(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.61.130)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = CTMSBDEV.shire.com)))’

declare
cursor tab_names is select table_name from user_tables;
begin
for tab in tab_names loop
execute immediate ‘GRANT SELECT ON ‘||tab.table_name||’ to SHIPPRO_READONLY’;
end loop;
end;

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner=’bZstDPqbeCMUeJhrqJ’) LOOP
EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.’||R.table_name||’ to CORESTAGE’;
END LOOP;
END;
/

BEGIN
FOR R IN (select view_name from user_views) LOOP
EXECUTE IMMEDIATE ‘grant select on ‘||R.view_name||’ to STAGEREAD with grant option’;
END LOOP;
END;
/


The following stored procedure illustrates the idea:

CREATE PROCEDURE grant_select(
username VARCHAR2,
grantee VARCHAR2)
AS
BEGIN
FOR r IN (
SELECT owner, table_name
FROM all_tables
WHERE owner = username
)
LOOP
EXECUTE IMMEDIATE
‘GRANT SELECT ON ‘||r.owner||’.’||r.table_name||’ to ‘ || grantee;
END LOOP;
END;
This example grants the SELECT object privileges of all tables that belong to the user OT to the user DW:

1
EXEC grant_select(‘OT’,’DW’);
When you use the user DW to login to the Oracle Database, the user DW should have the SELECT object privilege on all tables of the OTës schema.


select * from tenant where subdomain=’sandbox10′
select * from project where tenantid = (select tenantid from tenant where subdomain=’sandbox10′) order by name
select * from projectproperty where projectid=’93f05075-d56e-11e8-bcea-0050568850fc’


solve this issue by commenting on the values of wait_timeout, interactive_timeout, connect_timeout on my mysql.sock. My warning message is stopped after commenting this lines.
By default, wait_time and interactive_timeout will be 28800, when installing MySQL 5.7 in my production server, MySQL added this lines, after commenting this 3 lines it worked without showing a warning message:

[Note] Aborted connection to db: ‘—–‘ user: ‘—-‘ host: ‘localhost’ (Got timeout reading communication packets)”

wait_timeout = 1800
connect_timeout=15
max_allowed_packet = 256M

show variables like “wait_timeout”
show variables like “interactive_timeout”
show variables like “connect_timeout”

wait_timeout is how long to keep a connection open. connect_timeout is how long to wait for a connection to be made.

innodb_log_buffer_size=32M

wait_timeout = 1800

connect_timeout=15

interactive_timeout=28800

max_allowed_packet = 512M
innodb_log_file_size=2G


MySQL Database Creation and Update Data Time

SELECT
table_schema,
MAX(create_time) create_time,
MAX(update_time) update_time
FROM information_schema.tables
Group by TABLE_SCHEMA
Order by create_time desc

— Database Size

SELECT table_schema AS “Database”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;


How can I copy data from one column to another in the same table?

UPDATE table SET columnB = columnA;
This will update every row.

————————Amrendra Script for duplicate removal—————————————————————–

update assetentityproperty set DISPLAYCAPTION=’Country’ where PROPERTYKEY=’getCountry’ and
ASSETENTITYID in (select ASSETENTITYID from assetentity where ENTITYNAME=’UserInfo’ and tenantid=);

select * from assetentityproperty where DISPLAYCAPTION=’Country’ and PROPERTYKEY=’getCountry’ and
ASSETENTITYID in (select ASSETENTITYID from assetentity where ENTITYNAME=’UserInfo’)


Memory which is allocated by Oracle instance is SGA and PGA.

To get SGA size, you can SQL*Plus statement SHOW SGA or SQL statement:
select sum(value) from v$sga;
To get PGA size, you can use:
select name, value from v$pgastat where name like ‘total PGA a%’;


select to_char(SYSDATE, ‘HH24:MI:SS’) from dual;

ANALYZE TABLE table_name;

OPTIMIZE TABLE tbl; will rebuild the indexes and do ANALYZE; it takes time.

ANALYZE TABLE tbl; is fast for InnoDB to rebuild the stats. With 5.6.6 it is even less needed.


Long Running Query check MySQL

SELECT id,state,command,time,left(replace(info,’\n’,”),120)
FROM information_schema.processlist
WHERE command <> ‘Sleep’
AND info NOT LIKE ‘%PROCESSLIST%’
ORDER BY time DESC LIMIT 50;

SELECT
pl.id ‘PROCESS ID’
,trx.trx_started
,esh.event_name ‘EVENT NAME’
,esh.sql_text ‘SQL’
FROM information_schema.innodb_trx AS trx
INNER JOIN information_schema.processlist pl
ON trx.trx_mysql_thread_id = pl.id
INNER JOIN performance_schema.threads th
ON th.processlist_id = trx.trx_mysql_thread_id
INNER JOIN performance_schema.events_statements_history esh
ON esh.thread_id = th.thread_id
WHERE trx.trx_started < CURRENT_TIME – INTERVAL 59 SECOND AND pl.user <> ‘system_user’
ORDER BY esh.EVENT_ID;

SELECT
SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2)) AS “SIZE IN GB”
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = “shire__723256016”;

SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘shire__723256016’;

show full processlist;

———————-Determine backup size before backup——————————-

SELECT
Data_BB / POWER(1024,1) Data_KB,
Data_BB / POWER(1024,2) Data_MB,
Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema NOT IN (‘information_schema’,’performance_schema’,’mysql’)) A;

—————————————normal backup————————————————–

mysqldump -uroot -p –single-transaction –routines –triggers ctrdb > ctrdb_02FEB2019.sql
mysqldump -uroot -p –single-transaction –routines –triggers ctr241sqa1_1733696691 > ctr241sqa1_1733696691.sql

–shire-preprod ILTDB—-

mysqldump -uroot -p –single-transaction –routines –triggers shire__723256016 > shire__723256016.sql
mysqldump -uroot -p –single-transaction –routines –triggers shire__470417920 > shire__470417920.sql
mysqldump -uroot -p –single-transaction –routines –triggers shire__1780221583 > shire__1780221583.sql
mysqldump -uroot -p –single-transaction –routines –triggers shire_32909238 > shire_32909238.sql
mysqldump -uroot -p –single-transaction –routines –triggers shire__724233331 > shire__724233331.sql
mysqldump -uroot -p –single-transaction –routines –triggers shire__1300733570 > shire__1300733570.sql

expdp dumpfile=lnyLzhVVFDlNaUIUvO_05Feb2020.dmp schemas=lnyLzhVVFDlNaUIUvO directory=DATA_PUMP_DIR

expdp dumpfile=gwosyWBhojMewAaFeR_29JAN2020.dmp schemas=gwosyWBhojMewAaFeR directory=DATA_PUMP_DIR

mysqldump -uroot -p –single-transaction –routines –triggers ctrdb > ctrdb_13feb2020.sql

mysqldump -uroot -p –single-transaction –routines –triggers ctrassetdb > ctrassetdb_13feb2020.sql

mysqldump -uroot -p –single-transaction –routines –triggers –all-databases > ILTDB_06092018_fullbkp.sql


select * from SUBJECT ORDER BY CREATEDDATE OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

select * from SUBJECTSTATUS ORDER BY CREATEDDATE OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

—————–Compressed Backup———————————————————–

mysqldump -uroot -p –single-transaction –routines –triggers ctrdb | gzip > ctrdb_03012019_fullbkp.sql.gz

mysqldump -uroot -p –single-transaction –routines –triggers ctrassetdb | gzip > ctrassetdb_03012019.sql.gz

mysqldump -uroot -p –single-transaction –all-databases –routines –triggers | gzip > fullbkp_alldb_29102019.sql.gz


mysqldump -uroot -p ctrassetdb assetactivity > assetactivity.sql

mysqldump -u root -p ctrdb > ctrdb.sql > /Softwares/mysqldump.log 2>&1

SELECT CONCAT(‘SHOW GRANTS FOR ”’,user,”’@”’,host,”’;’) FROM mysql.user;

select * from userinfo where LockInd=’1′


Kill Inactive session Oracle

select * from v$resource_limit where resource_name in (‘processes’,’sessions’);

select sid, serial#, username, SECONDS_IN_WAIT from v$session where type=’USER’ and SECONDS_IN_WAIT>1200;

SELECT ‘ALTER SYSTEM KILL SESSION ‘||sid||’,’||serial#||’;’ FROM v$session where type=’USER’ and SECONDS_IN_WAIT>1200;

select ‘alter system kill session ”’ ||sid|| ‘,’ || serial#|| ”’ immediate;’ from v$session where status=’INACTIVE’;

SELECT username, machine, program,seconds_in_wait,state,LOGON_TIME
FROM v$session
WHERE type = ‘USER’ and status=’INACTIVE’

desc v$session;

show parameter resource_limit (must be true)

connect_time — Maximum connect time per session, in minutes.
idle_time — Maximum idle time before user is disconnected, in minutes.

desc v$session;

I have set the connect_time = 1 for a profile, and assigned the profile to the scott user.

connect time is exactly that — the duration of the connection.

if you want a connection to last no more than N minutes/hours, you would use this

select * from dba_profiles

select * from user_resource_limits a where a.resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);


put screenshot no in val doc
step 18 password masking in screenshot

screenshot 23 steps 1 0r 2? remove 1

Oracle – V24ORA

atleast 15 gb for oracle setup grid home and oracle home in root partition

IP Assign in RAC Get from IT

select * from userinfo where username=’Yam6aQ62Vqa8Z9kzNQkikA==’

–Register listener at database level

show parameter local_listener

alter system set local_listener=”;
alter system register;

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.13.134)(PORT=1521))’;
alter system register;


Manually gather Database Statistics in all Schemas!

exec DBMS_STATS.PURGE_STATS(SYSDATE-31);

— Probably need to CONNECT / AS SYSDBA
EXEC dbms_stats.gather_database_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;

EXEC DBMS_STATS.gather_schema_stats(‘FMJEKLVYWJUMGKOEFU’);

SELECT LAST_START_DATE FROM DBA_SCHEDULER_JOBS WHERE job_name=’GATHER_STATS_JOB’;


SELECT *
FROM v$session_longops
WHERE opname LIKE ‘%Gather%’ AND time_remaining != 0
ORDER BY SID
desc all_indexes

–check progress
SELECT *
FROM v$session_longops
WHERE opname LIKE ‘%Gather%’ AND time_remaining != 0
ORDER BY SID

check stats update status view

DBA_TAB_STATS_HISTORY

USER_TAB_STATS_HISTORY

ALL_TAB_STATS_HISTORY


I had such a problem and even with Oracle 12c on my linux server and what i did was that each time I open a new terminal I first type

“export TWO_TASK=”

(without the the quotations) followed by pressing

enter.

Next I could then go ahead to type sqlplus “/as sysdba” (now as it is with the quotations)

to connect and continue.


wget –[email protected][email protected] –no-check-certificate “http://download.oracle.com/otn/utilities_drivers/jdbc/121010/ojdbc6.jar”


crosscheck backup;
delete noprompt obsolete ;
delete noprompt expired backup;

list expired archivelog all;
crosscheck archivelog all;
delete expired archivelog all;


We have 3 options to fix this error
Kill the DB session and get the tables unlocked
Kill the application which holds this particular session(sql connection)
The ideal solution is to get to the actual process(application) to debug/fix the issue

  1. Killing the DB session
    To kill the DB session execute the sql
    alter system kill session ‘sid,serial#’
    In my case it will be
    alter system kill session ‘953,40807’

preprod/sb/prod : admin#567$

alter user sys identified by admin#567$;


innodb_force_recovery = 1


grep -n “Mon Jul 25” alertSID.log

[[email protected] ~]$ echo $TWO_TASK
pdb1
[[email protected] ~]$ export TWO_TASK=
[[email protected] ~]$ echo $TWO_TASK


create user backup identified by rman321;

grant sysbackup to backup;

ANALYZE TABLE tasks COMPUTE STATISTICS;
made my original SQL statement execute much faster.


== DEV/SQA/SANDBOX SHIRE

group_concat_max_len =52428800

SELECT TABLE_NAME,
ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘testlink’;


Provided Support to CT Renaissance, Checked Health of servers, monitored logs ,
spaces, status, Executed scripts given by developers,created database users for developers

Provided Support to CT Renaissance, Worked with colleague in static script management , Checked Health of servers, monitored logs ,
checked free spaces,server status, Executed scripts given by developers in various environments

Provided Support to Monthly Server Maintenance Activity for Various Environments


Performed IQ Activity for New Environment

Provided Support to Dyax Production Environment

sqlplus -prelim “/as sysdba”

SQLNET.EXPIRE_TIME=10

Status INACTIVE means session is not executing any query now. ACTIVE means it’s executing query.

inactive sessions can become active when user executes some sql.


CREATE USER username
IDENTIFIED by password
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE Temp
QUOTA UNLIMITED ON Users;


tar -zcvf mysql.tgz mysql
tar -xvf mysql.tgz

select * from mysql.user;

SELECT * from session_privs;

alter system set sec_case_sensitive_logon=false;

select NAME,VALUE from V$SPPARAMETER where NAME=’sec_case_sensitive_logon’;

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
at the beginning, and

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

set global innodb_flush_log_at_trx_commit = 2;

set global innodb_flush_log_at_trx_commit = 1;


oracle installtion and creation of db manually

https://dbaseworld.wordpress.com/category/databases/oracle/

upgrading from 11 to 12 c

https://dbaseworld.wordpress.com/category/databases/oracle/page/2/

Setting Up an Oracle Database Instant Client

http://www.interfaceware.com/manual/oracle_instant_client.html

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in ‘/root/.mysql_secret’.


To See/list files before deleting :-

find /to/your/directory -mtime +15 -print

Example: find /Software/bkp/* -mtime +15 -print

To See/list files before deleting (Current Directory) :-

$ find . -mtime +15 -exec rm -f {} \;

$ find . -mtime +15 -exec mv -f {} \;

Note: The latter is slightly more complex, but offers more flexibility if want to copy them to a temp directory instead of deleting. In that case, you can swap out the rm with an mv.

——————————————————————————

AP/HYD/0059669/000/0000336

UAN: 100798682708


For disabling sysdba remote login:

show parameter remote_login_passwordfile

alter system set remote_login_passwordfile=none scope=spfile;

restart db

other options SHARED/EXCLUSIVE
—-Generate DDL—

SET LONG 20000
SET PAGESIZE 0

SELECT DBMS_METADATA.get_ddl (object_type, object_name, USER)
FROM user_objects;

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql


OLTP system (short, frequent transactions)

Do not used shared server for data warehousing.

dbv help=y
dbv file=test01.dbf

analyze table validate structure;


purge binary logs to ‘bin-log.00003525’;
show binary logs\G
show slave status\G

PURGE BINARY LOGS BEFORE DATE(NOW() – INTERVAL 3 DAY) + INTERVAL 0 SECOND;

jdbc:oracle:thin:@10.10.8.104:1521/sqa3
mysqld_safe –skip-grant-tables &

sqlplus username/[email protected]:port/service

From windows:

sqlplus scott/[email protected]:1521/pmiora

jdbc:oracle:thin:scott/[email protected]//10.10.8.187:1521/pmiora


no profile expire

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


show variables like “max_connections”
set global max_connections=1000;

lower_case_table_names=1
lower_case_table_names=2
(for removing case sensitiveness)

SELECT ROW_COUNT();


If you are a company that uses MySQL
a) internal apps with lot of users using MYSQL server – License required
(because this is believed to make money in terms of savings)

b) external apps – License required

security tips oracle:
SYS, SYSTEM, SYSMAN, and DBSNMP

only this should be open change password for manager

ALTER USER mdsys PASSWORD EXPIRE ACCOUNT LOCK;

alter profile DEFAULT limit password_life_time UNLIMITED;

[email protected]


STOP SLAVE –FOR STOPPING REPLICATION

IQ = Inspection Qualification

SOURCE command to execute sql file from mysql terminal

SID means System Identification Number so for each instance there is a unique SID in the server. Service_Name is nothing but a name alias for the the database name in a non RAC system. But in a RAC system it has some other additional functionalities.

systemctl disable mysql


Database Name. It is the name of the physical database structure. It is stored at the controlfile and datafile header. It is used to identify all physical structures that belong to the same database. It can be defined at install time. It is originally defined by the static instance parameter database_name, and it cannot be changed, and the only way to change it is by means of rebuilding the controlfile and resetting the log sequence.

Instance Name. It is the name of the memory structures + Background process (MEM + BGP) use to mount/open a database. In a RAC environment there are more than one instance opening the same database, and each instance has a different name. On a single instance (non rac) environments, the instance name and the database name are named (generallly) the same. There is no reason to name the instance and the database differently.
Instance name is defined by the ORACLE_SID environment variable (unix) and by the windows service name (windows).
In a connect string this can be used to establish connection to a database, however this is valid for pre-8i versions (8.0 compatible) and even though it can work with 10g databases, it is not a good practice as it reduces the functionality provided since 8i.

Service Name. This is a way to identify a target instance to get connected to. It is defined by the dynamic instance parameter service_names, note the names word, it is plural, that is because an oracle instance can have several service names. The concept service is more frequently used in a single instance to refer to an instance with several alias. In a RAC environment it is used to balance power, create failed over / stand by instances and it is used in combination with resource manager.

It is common to name all three the same, and there is no use in changing them (single instance), except for the service name, just in case there it can be created more than one alias to refer to the same instance


SID = identifies the database instance (database name + instance number). So if your database name is somedb and your instance number is 3, then your SID is somedb3.

DB Name = Name of the database (database can be shared b/t multiple instances)

DB Domain = Usually the same as your company domain (somecompany.com)

Global Database Name = Database name + database domain (somedb.somecompany.com)

Service Name = A “connector” to one or more instances. It is often useful to create additional service names in a RAC environment since the service can be modified to use particular SIDs as primary or secondary connections, or to not use certain SIDs at all.

Service Alias = An alias to the service name (just like a CNAME, etc). Say you make your service name something meaningful to the dba, but perhaps it’s a bit esoteric. Create a service alias and name it something that will be meaningful to the user.

Instance name = same as SID

history -c ====for clearing history

last to check user login details on linux server

innodb_buffer_pool_size = 5G
innodb_log_file_size = 1G
max_connections = 1000

CROSSCHECK BACKUP;
DELETE OBSOLETE;
list backup summary;

show alert ñtail ñf (live)

show alert ñtail 15

show alert ñtail

show alert

connect,resource,create session privileges revoked from CDR_Regression user in sqa1

select count(*) from v$session_longops where time_remaining>0;

@?/rdbms/admin/awrrpt.sql

select count(*)
from user_tab_columns
where table_name=’MYTABLE’

sqlplus -prelim “/as sysdba”
SQLNET.EXPIRE_TIME=10

Status INACTIVE means session is not executing any query now. ACTIVE means it’s executing query.

inactive sessions can become active when user executes some sql.

CREATE USER username
IDENTIFIED by password
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE Temp
QUOTA UNLIMITED ON Users;

tar -zcvf mysql.tgz mysql
tar -xvf mysql.tgz

select * from mysql.user;

SELECT * from session_privs;

alter system set sec_case_sensitive_logon=false;

select NAME,VALUE from V$SPPARAMETER where NAME=’sec_case_sensitive_logon’;

=””””&A1&””””

I simply

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
at the beginning, and

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

set global innodb_flush_log_at_trx_commit = 2;

set global innodb_flush_log_at_trx_commit = 1;

sudo screen -S

last -a
users
finger
whoami
less /var/log/secure
w

oracle installtion and creation of db manually

https://dbaseworld.wordpress.com/category/databases/oracle/

upgrading from 11 to 12 c

https://dbaseworld.wordpress.com/category/databases/oracle/page/2/

Setting Up an Oracle Database Instant Client

http://www.interfaceware.com/manual/oracle_instant_client.html

mysql / oracle partitioning
mysql/oracle performance tuning
mysql/oracle space management

create table t2 as select distinct * from t1;

echo exit | sqlplus user/[email protected] @scriptname

To Make lifetime

select profile,USERNAME,EXPIRY_DATE from dba_users;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

finding particular column in all tables–mysql

select *
from information_schema.columns
where table_schema = ‘ctrdb’
and column_name =’TenantId’


Check MySQL Performance Tuning

wget https://github.com/major/MySQLTuner-perl/tarball/master

tar xf master

cd major-MySQLTuner-perl-993bc18/

# ./mysqltuner.pl

You must not always Upgrade your database to Enterprise Edition and buy the Option Packs. There are also some good options which can be done in Oracle Standard Edition. OK, some of the options are not so smart forward as they are in the Enterprise Edition but a good DBA should be also able to tune a database well without a option pack.

[email protected]

Note
Oracle Statspack is no longer supported by Oracle and has been replaced by the more advanced Automatic Workload Repository (AWR). AWR is available only for Oracle Enterprise Edition customers who have purchased the Diagnostics Pack. Oracle Statspack can be used with any Oracle DB engine on Amazon RDS.

Ref:https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.Statspack.html

Statspack is the ancestor of AWR. It has been deprecated since the 10G version. Less complete than its successor, it is still a good alternative to the AWR.

STATSPACK is still there, which is the original performance data collection tool for older databases and is very similar to AWR in functions. Best of all, we can use STATSPACK as a replacement for AWR in the Standard Edition without limitations.

SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘dictionary’;

SELECT FILE_NAME||’ ‘||TABLESPACE_NAME||’ ‘||BYTES/1024/1024
FROM DBA_TEMP_FILES;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/Data/app/oracle/maxissb/temp02.dbf’ SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

SELECT ‘DROP TABLE “‘ || TABLE_NAME || ‘” CASCADE CONSTRAINTS;’ FROM user_tables;

show full processlist;

SELECT id,state,command,time,left(replace(info,’\n’,”),120)
FROM information_schema.processlist
WHERE command <> ‘Sleep’
AND info NOT LIKE ‘%PROCESSLIST%’
ORDER BY time DESC LIMIT 50;

mysql -u root -p -H -e”CALL sys.diagnostics(60, 30, ‘current’);” > ./mysql_instance_report.html

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

1.As per Oracle Corp [Ref: SR 3-7512111031 :]:With the DIAGNOSTIC and TUNING packs, the best way to analyze performance issues, is to generate AWR reports.
Without this license, the best alternative to the AWR reports is to use Statspack reports, which will contain much of the same information, that is available in AWR reports.

—begin and end

exec statspack.snap

@?/rdbms/admin/spreport

STATSPACK provides 95% of the data that can be found in AR and it is 100% free to install and use.


SELECT sum(bytes)/1024/1024/1024 as “Size in GB” from dba_segments WHERE owner = UPPER(‘&GWOSYWBHOJMEWAAFER’);

4.27 GB

SELECT sum(bytes)/1024/1024/1024 as “Size in GB” from dba_segments WHERE owner = UPPER(‘&schema_name’);


Oracle Coherence Remove –
Virtual IP Adding –

admin567#
desc dba_tab_cols;

select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE from dba_tab_cols
where owner = ‘GWOSYWBHOJMEWAAFER’;

select to_char(creation_time, ‘MM-RRRR’) “Month”,
sum(bytes)/1024/1024 “Growth in Meg”
from sys.v_$datafile
where to_char(creation_time,’RRRR’)=’2019′
group by to_char(creation_time, ‘MM-RRRR’);

—————————SCHEMA GROWTH———————————

select sum(space_used_delta)/1024/1024 “Space used(M)”, sum(c.bytes)/1024/1024 “Total space(M)”, round(sum(space_used_delta)/sum(c.bytes)*100, 2) || ‘%’ “Percent of Total Disk Usage”
from
dba_hist_snapshot sn,
dba_hist_seg_stat a, dba_objects b, dba_segments c
where end_interval_time > trunc(sysdate) – 13
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = ‘GWOSYWBHOJMEWAAFER’;


MTU (Maximum Transmission Unit) is related to TCP/IP networking in Linux/BSD/UNIX oses. It refers to the size (in bytes) of the largest datagram that a given layer of a communications protocol can pass at a time.

Error reported during RMAN backup

ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2 Source Script: rman_backup.ksh

CAUSE

MTU of loopback adapter too high

# ifconfig

lo: flags=73 mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10
loop txqueuelen 1 (Local Loopback)
RX packets 392813331 bytes 87660546735 (81.6 GiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 392813331 bytes 87660546735 (81.6 GiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

SOLUTION
Change the MTU of loopback adapter to 16384

# ifconfig lo mtu 16384

(OR)

This happens due to less space available for network buffer reservation.

SOLUTION

  1. On servers with High Physical Memory, the parameter vm.min_free_kbytes should be set in the order of 0.4% of total Physical Memory. This helps in keeping a larger range of defragmented memory pages available for network buffers reducing the probability of a low-buffer-space conditions.

*** For example, on a server which is having 256GB RAM, the parameter vm.min_free_kbytes should be set to 1073742 ***

On NUMA Enabled Systems, the value of vm.min_free_kbytes should be multiplied by the number of NUMA nodes since the value is to be split across all the nodes.

On NUMA Enabled Systems, the value of vm.min_free_kbytes = n * 0.4% of total Physical Memory. Here ‘n’ is the number of NUMA nodes.

  1. Additionally, the MTU value should be modified as below

ifconfig lo mtu 16436

To make the change persistent over reboot add the following line in the file /etc/sysconfig/network-scripts/ifcfg-lo :

MTU=16436
Save the file and restart the network service to load the changes

service network restart

Note : While making the changes in CRS nodes, if network is restarted while CRS is up, it can hung CRS. So cluster services should be stopped prior to the network restart.

—————————–VIEW USER CREATION—————————–

grant connect,resource to STAGEREAD identified by STAGEREAD;

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner=’bZstDPqbeCMUeJhrqJ’) LOOP
EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.’||R.table_name||’ to STAGEREAD’;
END LOOP;
END;
/

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner=’CORESTAGE’) LOOP
EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.’||R.table_name||’ to STAGEREAD’;
END LOOP;
END;
/

BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner=’TSEMIGQpYzmhiHiyEt’) LOOP
EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.’||R.table_name||’ to STAGEREAD WITH GRANT OPTION’;
END LOOP;
END;
/

GRANT CREATE ANY VIEW TO STAGEREAD;
GRANT GLOBAL QUERY REWRITE to STAGEREAD;
GRANT CREATE TABLE to STAGEREAD;
GRANT CREATE MATERIALIZED VIEW to STAGEREAD;
GRANT UNLIMITED TABLESPACE to STAGEREAD;


grant create any job to CORESTAGE;
grant execute on DBMS_SCHEDULER to CORESTAGE;
grant manage scheduler to CORESTAGE;


declare
cursor view_names is select view_name from user_views;
begin
for v in view_names loop
execute immediate ‘GRANT SELECT ON ‘||v.view_name||’ to STAGEREAD’;
end loop;
end;

Leave a Reply

Your email address will not be published. Required fields are marked *