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
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
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
Linux, Oracle

Oracle TFA upgrade from 18.1 to 18.3 on RAC database

[root@prod ~]# /u01/app/12.2.0/grid/bin/tfactl status

.——————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+—————–+—————+——-+——+————+———————-+——————+
| prod | RUNNING | 32071 | 5000 | 18.1.1.0.0 | 18110020180328025002 | COMPLETE |
| prod2 | RUNNING | 6505 | 5000 | 18.1.1.0.0 | 18110020180328025002 | COMPLETE |
‘—————–+—————+——-+——+————+———————-+——————‘

[root@prod tmp]# ./installTFA-LINUX
TFA Installation Log will be written to File : /tmp/tfa_install_25637_2018_08_28-11_21_28.log

Starting TFA installation

TFA Version: 183000 Build Date: 201808081359

TFA HOME : /u01/app/12.2.0/grid/tfa/prod/tfa_home

Installed Build Version: 181100 Build Date: 201803280250

TFA is already installed. Patching /u01/app/12.2.0/grid/tfa/prod/tfa_home…
TFA patching typical install from zipfile is written to /u01/app/12.2.0/grid/tfa/prod/tfapatch.log

TFA will be Patched on:
prod
prod2

Do you want to continue with patching TFA? [Y|N] [Y]: y
Checking for ssh equivalency in prod2
prod2 is configured for ssh user equivalency for root user
Using SSH to patch TFA to remote nodes :

Applying Patch on prod2:

TFA_HOME: /u01/app/12.2.0/grid/tfa/prod2/tfa_home
Stopping TFA Support Tools…
Shutting down TFA
Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.
Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
. . . . .
. . .
Successfully shutdown TFA..
Copying files from prod to prod2…

Current version of Berkeley DB in prod2 is 5 or higher, so no DbPreUpgrade required
Running commands to fix init.tfa and tfactl in prod2…
Updating init.tfa in prod2…
Starting TFA in prod2…
Starting TFA..
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Waiting up to 100 seconds for TFA to be started..
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands

Enabling Access for Non-root Users on prod2…
Applying Patch on prod:

Stopping TFA Support Tools…

Shutting down TFA for Patching…

Shutting down TFA
Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.
Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
. . . . .
. . .
Successfully shutdown TFA..

No Berkeley DB upgrade required

Copying TFA Certificates…

Running commands to fix init.tfa and tfactl in localhost
Starting TFA in prod…

Starting TFA..
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Waiting up to 100 seconds for TFA to be started..
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands

Enabling Access for Non-root Users on prod…

.———————————————————————–.
| Host | TFA Version | TFA Build ID | Upgrade Status |
+—————–+————-+———————-+—————-+
| prod | 18.3.0.0.0 | 18300020180808135947 | UPGRADED |
| prod2 | 18.3.0.0.0 | 18300020180808135947 | UPGRADED |
‘—————–+————-+———————-+—————-‘

cleanup serializable files

[root@prod tmp]# /u01/app/oracle/product/12.2.0.1/db_1/bin/tfactl toolstatus
-bash: /u01/app/oracle/product/12.2.0.1/db_1/bin/tfactl: No such file or directory
[root@prod tmp]# /u01/app/12.2.0/grid/bin/tfactl toolstatus

.——————————————————————.
| TOOLS STATUS – HOST : prod |
+———————-+————–+————–+————-+
| Tool Type | Tool | Version | Status |
+———————-+————–+————–+————-+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+———————-+————–+————–+————-+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.0.1 | RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+———————-+————–+————–+————-+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
‘———————-+————–+————–+————-‘

Note :-
DEPLOYED : Installed and Available – To be configured or run interactively.
NOT RUNNING : Configured and Available – Currently turned off interactively.
RUNNING : Configured and Available.

[root@prod tmp]# ps -ef |grep osw
grid 1881 1 0 11:24 ? 00:00:00 /bin/sh ./OSWatcher.sh 30 48 NONE /u01/app/grid/tfa/repository/suptools/prod/oswbb/grid/archive
grid 4687 1881 0 11:25 ? 00:00:00 /bin/sh ./OSWatcherFM.sh 48 /u01/app/grid/tfa/repository/suptools/prod/oswbb/grid/archive
root 18797 15763 0 11:27 pts/0 00:00:00 grep –color=auto osw
[root@prod tmp]# ls


[root@prod2 ~]# /u01/app/12.2.0/grid/bin/tfactl status

.——————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+—————–+—————+——-+——+————+———————-+——————+
| prod2 | RUNNING | 24858 | 5000 | 18.3.0.0.0 | 18300020180808135947 | COMPLETE |
| xqaytidmdbprod1 | RUNNING | 908 | 5000 | 18.3.0.0.0 | 18300020180808135947 | RUNNING |
‘—————–+—————+——-+——+————+———————-+——————‘
[root@prod2 ~]# /u01/app/12.2.0/grid/bin/tfactl toolstatus

.——————————————————————.
| TOOLS STATUS – HOST : prod2 |
+———————-+————–+————–+————-+
| Tool Type | Tool | Version | Status |
+———————-+————–+————–+————-+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+———————-+————–+————–+————-+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.0.1 | RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+———————-+————–+————–+————-+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
‘———————-+————–+————–+————-‘

Note :-
DEPLOYED : Installed and Available – To be configured or run interactively.
NOT RUNNING : Configured and Available – Currently turned off interactively.
RUNNING : Configured and Available.

[root@prod2 ~]# ps -ef |grep osw
grid 25682 1 0 11:22 ? 00:00:00 /bin/sh ./OSWatcher.sh 30 48 NONE /u01/app/grid/tfa/repository/suptools/prod2/oswbb/grid/archive
root 25948 20854 0 11:29 pts/0 00:00:00 grep –color=auto osw
grid 27200 25682 0 11:23 ? 00:00:00 /bin/sh ./OSWatcherFM.sh 48 /u01/app/grid/tfa/repository/suptools/prod2/oswbb/grid/archive
[root@prod2 ~]#

 

Br. Erman.

Standard
Oracle

How can I Estimate Table Sizes within Schema – oracle

Look at the “dba_segments” view (or user_segments if you don’t have dba rights). The following query should give you what you’re looking for:

select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;

demo :

Use LOOP to insert data to a table

create table ERMAN.Employee(
ID VARCHAR2(4 BYTE) NOT NULL primary key,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
)
/


BEGIN
FOR v_LoopCounter IN 1..5000 LOOP
INSERT INTO ERMAN.employee (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/

output:

demo

br. erman…

 

Standard
Oracle

Enabling ARCHIVELOG Mode

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size 8620272 bytes
Variable Size 872417040 bytes
Database Buffers 385875968 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
SP2-0734: unknown command beginning “sh▒utdown …” – rest of line ignored.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size 8620272 bytes
Variable Size 872417040 bytes
Database Buffers 385875968 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
ARCHIVELOG

SQL>

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

Standard
Oracle

Configuring HugePages for Oracle Database on Linux/Unix

Prepare:

Finally as per default the parameter use_large_pages should be enabled:

SQL> select value from v$parameter where name = 'use_large_pages';

VALUE
---------------------------
TRUE

if not:

sql>alter system set use_large_pages=true scope=spfile;

MEMORY_MAX_TARGET should be set to 0:

SQL> select value from v$parameter where name = 'memory_target';

VALUE
---------------------------
0 

if not:
SQL> alter system set memory_target=0;
SQL> alter system set memory_max_target=0;

 grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
$

How big is our SGA? In this example about 40 GB. Important: In the following query we directly convert into kB (value/1024). With that we can continue to calculate directly:

SQL> select value/1024 from v$parameter where name = 'sga_target';

VALUE
---------------------------
41943040 ->>40 GB.

Verify that the soft and hard values in kilobytes of memlock that are configured in /etc/security/limits.conf. For example, if the system has 40GB of RAM, the values shown here would be appropriate:

Calculate:
#(sga_ram_size*1024*1024)-1 =41943040

if you have grid user.

oracle soft memlock 41943040
oracle hard memlock 41943040
grid soft memlock 41943040
grid hard memlock 41943040

Note:

If you run more than one database on your server, you should include the SGA of all of your instances into the calculation:
1. Instance + SGA 2. Instance + … etc. ) / Hugepagesize = Number Hugepages

Correctly inserted, following result should show up:
for oracle and grid users:
su- oracle
#vi /etc/security/limits.conf
for grid user:
su – grid
#vi /etc/security/limits.conf

vi /etc/security/limits.conf

oracle soft memlock 41943040
oracle hard memlock 41943040
grid soft memlock 41943040
grid hard memlock 41943040

Check Hugepagesize

Default hugepagesize should be set to 2048 KB:

grep Hugepagesize /proc/meminfo

Hugepagesize:       2048 kB

Calculate Hugepages:

For the calculation of the number of hugepages there is a easy way:

SGA / Hugepagesize = Number Hugepages

Following our example:

41943040 / 2048 = 20480

Change Server Configuration

The next step is to enter the number of hugepages in the server config file. For that you need root permissions.

vi /etc/sysctl.conf

vm.nr_hugepages=20480

Server Reboot

If all parameter are set, make a complete reboot your server
alternative you can reload the parameters with sysctl -p command.

Check Configuration

Memlock correct?

ulimit -l

41943040 

You can see the HugePages are now being used.

Br.Erman.

huge_page_png

 

Standard