Be Alpha

Wednesday, July 15, 2020

Linux mount

1 uname -a
2 df -h
3 fdisk -l
4 fdisk /dev/sdb
5 fdisk /dev/sdb -l
6 fdisk /dev/sdc
7 fdisk /dev/sdc -l
8 fdisk /dev/sdd
9 fdisk /dev/sde
10 fdisk -l
11 mkfs -t ext4 /dev/sdb1
12 mkfs -t ext4 /dev/sdc1
13 mkfs -t ext4 /dev/sdd1
14 mkfs -t ext4 /dev/sde1
15 cd /
16 ll
17 mkdir /u01 /u02
18 ll
19 mkdir /u03 /u04
20 ll
21 mount /dev/sdb1 /u01
22 mount /dev/sdc1 /u02
23 mount /dev/sdd1 /u03
24 mount /dev/sde1 /u04
25 df -h
26 cd /u01
27 touch angel.txt

Tuesday, June 23, 2020

Place Holder For Feature Usage Tracking Bugs (Doc ID 1309070.1)

SQL Server: Get Jobs and Objects owned by user

select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
where l.name is not null and l.name = 'Jeelan'
order by l.name

SELECT *
FROM sys.objects
WHERE schema_id = SCHEMA_ID('DBO');

Tuesday, June 16, 2020

How To Resize Partition in RHEL 7

root@apollo dbapps]# fdisk /dev/sdc

The number of cylinders for this disk is set to 54827.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/sdc: 450.9 GB, 450971566080 bytes
255 heads, 63 sectors/track, 54827 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 39162 314568733+ 83 Linux

Command (m for help): d
Selected partition 1

Command (m for help): p

Disk /dev/sdc: 450.9 GB, 450971566080 bytes
255 heads, 63 sectors/track, 54827 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4) p
Partition number (1-4): 1
First cylinder (1-54827, default 1): [ENTER]
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-54827, default 54827): [ENTER]
Using default value 54827

Command (m for help): p

Disk /dev/sdc: 450.9 GB, 450971566080 bytes
255 heads, 63 sectors/track, 54827 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 54827 440397846 83 Linux

Command (m for help): wq
The partition table has been altered!

REBOOT

[root@apollo ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 16G 3.4G 12G 23% /

/dev/sda1 251M 17M 222M 7% /boot

tmpfs 5.9G 0 5.9G 0% /dev/shm

/dev/sdc1 296G 245G 36G 88% /dbapps

[root@apollo ~]# resize2fs -p /dev/sdc1

resize2fs 1.39 (29-May-2006)

Filesystem at /dev/sdc1 is mounted on /dbapps; on-line resizing required

Performing an on-line resize of /dev/sdc1 to 110099461 (4k) blocks.

The filesystem on /dev/sdc1 is now 110099461 blocks long.

[root@apollo ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 16G 3.4G 12G 23% /

/dev/sda1 251M 17M 222M 7% /boot

tmpfs 5.9G 0 5.9G 0% /dev/shm

/dev/sdc1 414G 245G 148G 63% /dbapps

[root@apollo ~]#

Monday, June 15, 2020

DISABLE AUTOMATIC SQL ADVISOR

Check if anything is enabled

select client_name, status from dba_autotask_operation;

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED

sql tuning advisor ENABLED

How to Enable or DISABLE Automatic SQL Tuning

To ENABLE Automatic SQL Tuning, use the ENABLE procedure as shown below

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

Connected to:

Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END; 2 3 4 5 6

7 /

PL/SQL procedure successfully completed.

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor DISABLED

SQL>

The plugin BI publisher configuration has failed its perform method 13c

Workaround: If patching is not an option or the install/upgrade is already in progress, this issue can be fixed by following the steps:

1. Close the installer window.

2. Unset the CLASSPATH variable:

$ export CLASSPATH=

3. Resume the Install/Upgrade using the runConfig.sh script:

$ OMS_HOME/oui/bin> ./runConfig.sh ORACLE_HOME=$OMS_HOME MODE=perform ACTION=configure COMPONENT_XML={encap_oms.1_0_0_0_0.xml}

This takes a long time

Actually, it never opened. I was expecting it will show the GUI again

I just started it. To know what the pots are. I went to

/u04/app/oracle/product/Middleware/install/setupinfo.txt

Sunday, June 14, 2020

How To Increase Redo Log Size in Oracle

I was installing EM13c and there were so many checks. If I can do this again I would have used the template.

One of the checks is to increase the redo size

We will need to drop inactive redo logs and recreate them.

1. List details about the redo log

col member format a60

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthbp_.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthl8_.log
2 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthgd_.log
3 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthc8_.log
3 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthnd_.log

3 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthjo_.log

It was using the oracle managed file (OMF) naming convention. It is ugly also it is only multiplexed on 2 disks.

We see that Group 2 and # are inactive so let us start with that.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2

('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log',

'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log',

'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log')

size 400m; 2 3 4 5

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3

('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log',

'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log',

'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log')

size 400m; 2 3 4 5

Database altered.

SQL>

Let us check the logfiles

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

---------- ---------------- ------------------------------------------------------------

1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

Now we need to move the redo group 1 to be unused. Do a switch logfile.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

---------- ---------------- ------------------------------------------------------------

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

Notice that group #1 is still ACTIVE so if you try to drop it

SQL> alter database drop logfile group 1;

alter database drop logfile group 1

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance oem (thread 1)

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log'

ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log'

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log'

Do another switch logfile

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

---------- ---------------- ------------------------------------------------------------

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 ACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

###--- wait for the checkpoint so it will write it to the archivelogs. Wait until Group#1 is INACTIVE -- give 2 minutes. Also, you can force the checkpoint if you want.

SQL> /

GROUP# STATUS MEMBER

---------- ---------------- ------------------------------------------------------------

1 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 ACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

Now Group 1 is INACTIVE, you can continue to recreate group1;

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1

('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_1.log',

'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_1.log',

'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_1.log')

size 400m; 2 3 4 5

Database altered.

SQL>

CONGRATULATIONS !!! you have successfully renamed, resize your redologs

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

---------- ---------------- ------------------------------------------------------------

1 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_1.log

1 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_1.log

1 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_1.log

2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 INACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.