Oracle

How do I learn more advanced SQL

Hello friends I want to give information about the sql online training:

What is the SQL:
SQL is one of the most important skill for any programmer be it a Java, C++, PHP, Ruby or etc.  developer.

What We learn in this course:
Their online SQL Tutorial guides you through syntax and most important statements, keywords, and functions. Short but concrete descriptions and many examples make this tutorial easy to read and understand.

A distinctive feature is the ability to test queries online. At the start of the tutorial, there is a sample database which you can modify and restore back to its original content at any time. The guide ends with a simple quiz.

The course consists of 4 lessons that focus on:

  • basic SQL commands to manipulate data stored in relational databases
  • the most commonly used SQL commands to query a table in a database
  • using SQL to perform calculations during a query
  • querying multiple tables using joins

Who is the Instructor :

Yusuf Arslan :  Sakarya University computer engineering department graduate. 12 years of work in my life, I’ve been to a lot of data warehouse in business intelligence projects &.Data warehouse business intelligence issues 8 years active & training as my top priority. starting in 2017, the number of Turkish content and training to contribute to the education I’m releasing my Udemy. Training and other questions, requests and comments can be directed to yusufarslan@verinep.com and you can contact me from yusufarslanbm@gmail.com.

Join SQL Course: 

Br. Erman.

Advertisements
Standard
Troubleshooting

ORA-00600: internal error code, arguments: [723], [132616], [156344], [memory leak], [], [], [], [], [], [], [], []

On 12.1.0.2, the following error was reported in the alert log when running a Spatial query:

ORA-00600: internal error code, arguments: [723], [140904], [195968], [memory leak], [], [], [], [], [], [], [], []

The trace file shows the leak is in “mdorelOutputRing”

========================================
SUMMARIZED MEMORY USAGE FOR THIS PROCESS
—————————————-
34337704 bytes, 127 chunks: “free memory ”
top uga heap ds=0x7fec27e5ddc0 dsprt=(nil)
17102032 bytes, 79 chunks: “free memory ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
1287448 bytes, 48 chunks: “permanent memory ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
263256 bytes, 3 chunks: “permanent memory ”
top call heap ds=0x7fec27e5dba0 dsprt=(nil)
260984 bytes, 1 chunk : “free memory ”
top call heap ds=0x7fec27e5dba0 dsprt=(nil)
189344 bytes, 42 chunks: “mdorelOutputRing:c “
pga heap ds=0x7fec27e579a0 dsprt=(nil)
4352 bytes, 1 chunk : “mdrcncColumnNameCache ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
2088 bytes, 1 chunk : “mdcsWktCache ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
184 bytes, 1 chunk : “heap_kdlipga ”
pga heap ds=0x7fec27e579a0 dsprt=(nil)
112 bytes, 1 chunk : “permanent memory ”
top uga heap ds=0x7fec27e5ddc0 dsprt=(nil)

******************************************************
HEAP DUMP heap name=”pga heap” desc=0x7fec27e579a0
extent sz=0x20c0 alt=240 het=32767 rec=0 flg=3 opc=3
parent=(nil) owner=(nil) nex=(nil) xsz=0xffff0 heap=(nil)
fl2=0x60, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0x7fec23706b60
dsx empty ext bytes=5800416 subheap rc link=0x7fec23706bd0,0x7fec23706bd0
pdb id=0
EXTENT 0 addr=0x7fec1e100010
Chunk 7fec1e100020 sz= 1048544 free ” ”
EXTENT 1 addr=0x7fec1e0c0010
Chunk 7fec1e0c0020 sz= 5016 free ” ”
Chunk 7fec1e0c13b8 sz= 10712 freeable “mdorelOutputRin”

BUG 22828244 – MEMORY LEAK IN SDO_UNION().

Apply Patch 22828244.

Standard
Linux

Changing SYS Password in Dataguard Environment

If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:

In this situation set your password file in standby server with:
orapwd file=$ORACLE_HOME/dbs/orapwSID password=newpassword; (don’t forget to move/delete old one)

Create a password file in ASM diskgroup:

orapwd file=‘+DATA/orapwORCL’ ENTRIES=10 DBUNIQUENAME=‘ORCL’

We can view users authenticated through password file in v$pwfile_users table

SQL> select username,sysdba from v$pwfile_users;
USERNAME  SYSDB
SYS       TRUE
Standard
Oracle

ORA-01665: control file is not a standby control file

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
SQL>
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> SHU IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
SP2-0714: invalid combination of STARTUP options
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL>

Standard
Linux

How to Add New Disks Using LVM to an Existing Linux System

 

[oracle@ermantest ~]$ su –
Password:
[root@ermantest ~]# clear
[root@ermantest ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000accd6

Device Boot Start End Blocks Id System
/dev/sda1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 64 3917 30944256 8e Linux LVM

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
Disk /dev/mapper/vg_ermantest-lv_root: 28.5 GB, 28496101376 bytes
255 heads, 63 sectors/track, 3464 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
Disk /dev/mapper/vg_ermantest-lv_swap: 3187 MB, 3187671040 bytes
255 heads, 63 sectors/track, 387 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[root@ermantest ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xdf59b502.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won’t be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It’s strongly recommended to
switch off the mode (command ‘c’) and change display units to
sectors (command ‘u’).

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4):
Value out of range.
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-261, default 261):
Using default value 261

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ermantest ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xd3696a9a.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won’t be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It’s strongly recommended to
switch off the mode (command ‘c’) and change display units to
sectors (command ‘u’).

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-522, default 522):
Using default value 522

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ermantest ~]#
[root@ermantest ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000accd6

Device Boot Start End Blocks Id System
/dev/sda1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 64 3917 30944256 8e Linux LVM

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xdf59b502

Device Boot Start End Blocks Id System
/dev/sdb1 1 261 2096451 83 Linux

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xd3696a9a

Device Boot Start End Blocks Id System
/dev/sdc1 1 522 4192933+ 83 Linux

Disk /dev/mapper/vg_ermantest-lv_root: 28.5 GB, 28496101376 bytes
255 heads, 63 sectors/track, 3464 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
Disk /dev/mapper/vg_ermantest-lv_swap: 3187 MB, 3187671040 bytes
255 heads, 63 sectors/track, 387 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

[root@ermantest ~]# pvcreate /dev/sdb1
Physical volume “/dev/sdb1” successfully created
You have new mail in /var/spool/mail/root
[root@ermantest ~]# pvcreate /dev/sdc1
Physical volume “/dev/sdc1” successfully created
[root@ermantest ~]# vgcreate oracledb /dev/sdb1 /dev/sdc1
Volume group “oracledb” successfully created
[root@ermantest ~]# vgdisplay oracledb
— Volume group —
VG Name oracledb
System ID
Format lvm2
Metadata Areas 2
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 2
Act PV 2
VG Size 5.99 GiB
PE Size 4.00 MiB
Total PE 1534
Alloc PE / Size 0 / 0
Free PE / Size 1534 / 5.99 GiB
VG UUID 8wD0HH-OZwX-rYXn-idYf-7zxR-JDuT-0A6jWN

[root@ermantest ~]# lvcreate -n lv_db1 –size 2G oracledb
Logical volume “lv_db1” created
[root@ermantest ~]# lvcreate -n lv_db1 –size 1G oracledb
Logical volume “lv_db1” already exists in volume group “oracledb”
[root@ermantest ~]# lvcreate -n lv_db2 –size 1G oracledb
Logical volume “lv_db2” created
[root@ermantest ~]# lvdisplay oracledb
— Logical volume —
LV Path /dev/oracledb/lv_db1
LV Name lv_db1
VG Name oracledb
LV UUID cu6uqE-4fpY-ihnN-IxJr-uoBP-ckQ0-s61uiV
LV Write Access read/write
LV Creation host, time ermantest, 2018-08-30 13:02:17 +0300
LV Status available
# open 0
LV Size 2.00 GiB
Current LE 512
Segments 1
Allocation inherit
Read ahead sectors auto
– currently set to 256
Block device 252:2

— Logical volume —
LV Path /dev/oracledb/lv_db2
LV Name lv_db2
VG Name oracledb
LV UUID 91HVRv-8pBZ-tfko-dleI-UqUH-0P6w-KnMTfB
LV Write Access read/write
LV Creation host, time ermantest, 2018-08-30 13:02:33 +0300
LV Status available
# open 0
LV Size 1.00 GiB
Current LE 256
Segments 1
Allocation inherit
Read ahead sectors auto
– currently set to 256
Block device 252:3

[root@ermantest ~]# mkfs.ext4 /dev/oracledb/lv_db1
mke2fs 1.43-WIP (20-Jun-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
131072 inodes, 524288 blocks
26214 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=536870912
16 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912

Allocating group tables: done
Writing inode tables: done
Creating journal (16384 blocks): done
Writing superblocks and filesystem accounting information: done

[root@ermantest ~]# mkfs.ext4 /dev/oracledb/lv_db2
mke2fs 1.43-WIP (20-Jun-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
65536 inodes, 262144 blocks
13107 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=268435456
8 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376

Allocating group tables: done
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

[root@ermantest ~]# mount /dev/oracledb/lv_db1 /db1
mount: mount point /db1 does not exist
[root@ermantest ~]# mkdir /data1
[root@ermantest ~]# mkdir /data2
[root@ermantest ~]# mount /dev/oracledb/lv_db1 /data1
[root@ermantest ~]# mount /dev/oracledb/lv_db1 /data2
[root@ermantest ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_ermantest-lv_root 26G 19G 5.9G 77% /
tmpfs 1.5G 721M 797M 48% /dev/shm
/dev/sda1 477M 55M 397M 13% /boot
/dev/mapper/oracledb-lv_db1 2.0G 3.0M 1.9G 1% /data1
/dev/mapper/oracledb-lv_db1 2.0G 3.0M 1.9G 1% /data2
[root@ermantest ~]#

Standard
Linux

OpenSSH

Install/Update OpenSSH Server
OpenSSH Server packages is available under default apt repositories under all versions of operating systems. You can install or update latest OpenSSH server using following command.

sudo apt-get update
sudo apt-get install openssh-server

>Versiyon:
ssh -V

>Status:
sudo systemctl status ssh

>Stop,Start,Status OpenSSH:
sudo stop ssh
sudo start ssh
sudo restart ssh
sudo status ssh

>If you are using iptables to secure your server, use following command to open port for SSH access.

sudo iptables -A INPUT -s 192.168.1.0/24 -p tcp –dport 22 -j ACCEPT
sudo ufw allow from 192.168.1.0/24 to any port 22

Standard
Oracle

How to cleanup the log table FGA_LOG$ ?

How to cleanup the log table FGA_LOG$ ?

The FGA_LOG$ table can be deleted from or truncated to manage its space, for example:

SQL> connect / as sysdba
Connected.
SQL> truncate table fga_log$;
Table truncated.

Alternatively you can delete records based on the TIMESTAMP# (date) column as follows:

SQL> delete from fga_log$ where timestamp# < sysdate-14;

This deletes all rows older than 2 weeks.

Standard