Jobs
Automatic Optimizer Statistics Collection -> Gathers stale or missing statistics
Automatic Segment Advisor -> Identifies segments that reorganized to save space
Automatic SQL Tuning Advisor -> Tune high load SQL
Check auto tasks
SQL> SELECT client_name, status FROM dba_autotask_client;
To disable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;
To enable all auto task jobs
SQL> EXEC DBMS_AUTO_TASK_ADMIN.enable;
To disable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
To enable a spesific task
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
Ref: https://smarttechways.com/2015/09/03/disable-and-enable-auto-task-job-for-11g-and-12c-version-in-oracle/
Tuesday 5 September 2017
Tuesday 29 August 2017
e.g. shrink table usage
Steps:
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
Monday 28 August 2017
DBMS_JOBS vs DBMS_SCHEDULER
-> DBMS_SCHEDULER introduced in 10g releases so before this, we were able to use only DBMS_JOBS for scheduler jobs.
e.g. DBMS_JOBS
SQL>
VARIABLE job_id NUMBER;
BEGIN
DBMS_JOB.submit (
job => :job_id,
what => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1 /* 1 Day */');
COMMIT;
END;
/
PRINT job_id
e.g. DBMS_SCHEDULER
SQL>
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'example_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');
END;
/
Ref for example: https://ozsoyler.blogspot.com.tr/2017/02/how-to-kill-all-inactive-sessions-with.html
e.g. DBMS_JOBS
SQL>
VARIABLE job_id NUMBER;
BEGIN
DBMS_JOB.submit (
job => :job_id,
what => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1 /* 1 Day */');
COMMIT;
END;
/
PRINT job_id
e.g. DBMS_SCHEDULER
SQL>
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'example_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN P_KILL_ALL_INACTIVE_SESSIONS; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');
END;
/
Ref for example: https://ozsoyler.blogspot.com.tr/2017/02/how-to-kill-all-inactive-sessions-with.html
Tuesday 22 August 2017
How to monitor TEMP tablespace resource usage?
-> Check Total TEMP usage:
SQL>
SELECT B.TOTAL_MB,
B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
FROM V$SORT_SEGMENT A,
(SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;
-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
S.USERNAME,
U.TABLESPACE,
S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
U.SEGTYPE,
U.CONTENTS,
U.BLOCKS
FROM V$SESSION S, V$TEMPSEG_USAGE U
WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;
-> Find problematic SQL for TEMP
SQL>
SELECT
S.SID
,S.CLIENT_INFO
,S.MACHINE
,S.PROGRAM
,S.TYPE
,S.LOGON_TIME
,S.OSUSER
,SQ.SORTS
,SQ.DISK_READS
,SQ.BUFFER_GETS
,SQ.ROWS_PROCESSED
,SQ.SQLTYPE
,SQ.SQL_TEXT
FROM GV$SESSION S
, GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
--AND s.inst_id= 1
AND S.SID = 555
AND SQ.INST_ID= S.INST_ID;
SQL>
SELECT B.TOTAL_MB,
B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,
ROUND (USED_BLOCKS * 8 / 1024) CURRENT_USED_MB,
ROUND (MAX_USED_BLOCKS * 8 / 1024) MAX_USED_MB
FROM V$SORT_SEGMENT A,
(SELECT ROUND (SUM (BYTES) / 1024 / 1024) TOTAL_MB FROM DBA_TEMP_FILES) B;
-> Check Total TEMP usage according to database blocks:
SQL>
SELECT DISTINCT S.SID,
S.USERNAME,
U.TABLESPACE,
S.SQL_HASH_VALUE || '/' || U.SQLHASH HASH_VALUE,
U.SEGTYPE,
U.CONTENTS,
U.BLOCKS
FROM V$SESSION S, V$TEMPSEG_USAGE U
WHERE S.SADDR = U.SESSION_ADDR
ORDER BY U.BLOCKS DESC;
-> Find problematic SQL for TEMP
SQL>
SELECT
S.SID
,S.CLIENT_INFO
,S.MACHINE
,S.PROGRAM
,S.TYPE
,S.LOGON_TIME
,S.OSUSER
,SQ.SORTS
,SQ.DISK_READS
,SQ.BUFFER_GETS
,SQ.ROWS_PROCESSED
,SQ.SQLTYPE
,SQ.SQL_TEXT
FROM GV$SESSION S
, GV$SQL SQ
WHERE S.SQL_HASH_VALUE = SQ.HASH_VALUE
--AND s.inst_id= 1
AND S.SID = 555
AND SQ.INST_ID= S.INST_ID;
Monday 7 August 2017
e.g. simple DDL audit trigger
e.g. DDL audit trigger
CREATE TABLE SYSTEM.audit_ddl_history
(
osuser varchar2(50),
session_user varchar2(50),
host varchar2(50),
terminal varchar2(50),
object_owner varchar2(50),
obejct_type varchar2(50),
object_name varchar2(50),
sysevent varchar2(50),
audit_date date
);
CREATE OR REPLACE TRIGGER SYSTEM.ALPERDB_AUDIT_HISTORY
AFTER DDL
ON DATABASE
begin
if (ora_sysevent='GRANT')
then
NULL; -- don't care to grant!
else
insert into SYSTEM.audit_ddl_history(osuser, session_user, host, terminal, object_owner, obejct_type, object_name, sysevent,audit_date)
values(
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','SESSION_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
sysdate
);
end if;
end;
/
e.g.
SQL>
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
Ref: https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm
CREATE TABLE SYSTEM.audit_ddl_history
(
osuser varchar2(50),
session_user varchar2(50),
host varchar2(50),
terminal varchar2(50),
object_owner varchar2(50),
obejct_type varchar2(50),
object_name varchar2(50),
sysevent varchar2(50),
audit_date date
);
CREATE OR REPLACE TRIGGER SYSTEM.ALPERDB_AUDIT_HISTORY
AFTER DDL
ON DATABASE
begin
if (ora_sysevent='GRANT')
then
NULL; -- don't care to grant!
else
insert into SYSTEM.audit_ddl_history(osuser, session_user, host, terminal, object_owner, obejct_type, object_name, sysevent,audit_date)
values(
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','SESSION_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
sysdate
);
end if;
end;
/
e.g.
SQL>
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
Ref: https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm
Saturday 5 August 2017
Solution ORA-01031, ORA-01994
Error
ORA-01031: insufficient privileges
Solution
Give sysdba role to sys
SQL> grant sysdba to sys;
If you get this error ->
ORA-01994: GRANT failed: password file missing or disabled
Run following command;
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=alper entries=2 force=y
-> entries; number of users can login as sysdba role
-> force; enable to overwrite current pwd file
Check remote_login parameter
SQL> show parameter remote_login_passwordfile;
If remote_login_passwordfile is not "EXCLUSIVE";
Change it permanently:
add this into init.ora file;
*.remote_login_passwordfile='EXCLUSIVE'
Check who has sysdba role in database lastly
SQL> select * from V$PWFILE_USERS;
ORA-01031: insufficient privileges
Solution
Give sysdba role to sys
SQL> grant sysdba to sys;
If you get this error ->
ORA-01994: GRANT failed: password file missing or disabled
Run following command;
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=alper entries=2 force=y
-> entries; number of users can login as sysdba role
-> force; enable to overwrite current pwd file
Check remote_login parameter
SQL> show parameter remote_login_passwordfile;
If remote_login_passwordfile is not "EXCLUSIVE";
Change it permanently:
add this into init.ora file;
*.remote_login_passwordfile='EXCLUSIVE'
Check who has sysdba role in database lastly
SQL> select * from V$PWFILE_USERS;
Tuesday 1 August 2017
How to start/stop/configure samba service?
If you need, uninstall old samba
# yum erase samba samba-common samba-client
install tool
# yum install samba4
or upgrade tool
# yum upgrade samba4
configure settings
# vi /etc/samba/smb.conf
e.g.
[share_folder]
comment = Share Folder
path = /share_folder
create mode = 0777
readonly = no
browseable = yes
public = yes
Check configuration of samba
# testparm
check samba service and version
# smbstatus
startup options
# chkconfig smb on
# service smb start
connect to share folder
e.g.
RUN> \\10.10.10.10\share_folder
remove startup option and stop vnc
# chkconfig smb off
# service smb stop
# yum erase samba samba-common samba-client
install tool
# yum install samba4
or upgrade tool
# yum upgrade samba4
configure settings
# vi /etc/samba/smb.conf
e.g.
[share_folder]
comment = Share Folder
path = /share_folder
create mode = 0777
readonly = no
browseable = yes
public = yes
Check configuration of samba
# testparm
check samba service and version
# smbstatus
startup options
# chkconfig smb on
# service smb start
connect to share folder
e.g.
RUN> \\10.10.10.10\share_folder
remove startup option and stop vnc
# chkconfig smb off
# service smb stop
ORA-28002 solution
Error
ORA-28002: the password will expire within [A] days
Solution
Disable password expiration
SQL> select profile from DBA_USERS where username = 'HR';
-- e.g. DEFAULT
Change password_life_time parameter
SQL> alter profile "DEFAULT" limit password_life_time UNLIMITED;
Check it
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
Apply the new setting for user
SQL> alter user "HR" identified by "hr";
ORA-28002: the password will expire within [A] days
Solution
Disable password expiration
SQL> select profile from DBA_USERS where username = 'HR';
-- e.g. DEFAULT
Change password_life_time parameter
SQL> alter profile "DEFAULT" limit password_life_time UNLIMITED;
Check it
SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';
Apply the new setting for user
SQL> alter user "HR" identified by "hr";
Tuesday 25 July 2017
SQL PLUS system parameters usage (set/show)
To show parameter in detail
SQL> SHOW DEFINE;
To escape from substitution variable (&):
SQL> SET DEFINE OFF;
To enable DBMS_OUTPUT:
SQL> SET SERVEROUTPUT ON;
To see old and new values of variables:
SQL> SET VERIFY ON;
To calculate total execution time of SQL:
SQL> SET TIMING ON;
To print time on the SQL terminal:
SQL> SET TIME ON;
To see table columns name and types for a table:
SQL> DESC hr.employees@XE;
To see currently logged-in user:
SQL> SHOW user;
SQL> SHOW DEFINE;
To escape from substitution variable (&):
SQL> SET DEFINE OFF;
To enable DBMS_OUTPUT:
SQL> SET SERVEROUTPUT ON;
To see old and new values of variables:
SQL> SET VERIFY ON;
To calculate total execution time of SQL:
SQL> SET TIMING ON;
To print time on the SQL terminal:
SQL> SET TIME ON;
To see table columns name and types for a table:
SQL> DESC hr.employees@XE;
To see currently logged-in user:
SQL> SHOW user;
e.g. changing the DBID and Database Name
e.g.
Backup database
RMAN> backup database
Mount database
SQL> shu immediate;
SQL> startup mount;
Change DBID
$ nid TARGET=SYS
or
Change both DBID and DBNAME
$ nid TARGET=SYS DBNAME=new_xe
Change database name from parameter file (init.ora)
*.db_name='NEW_XE'
Mount database again
SQL> startup mount;
Open database
SQL> alter database open resetlogs;
Ref: https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544s
Backup database
RMAN> backup database
Mount database
SQL> shu immediate;
SQL> startup mount;
Change DBID
$ nid TARGET=SYS
or
Change both DBID and DBNAME
$ nid TARGET=SYS DBNAME=new_xe
Change database name from parameter file (init.ora)
*.db_name='NEW_XE'
Mount database again
SQL> startup mount;
Open database
SQL> alter database open resetlogs;
Ref: https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544s
Monday 17 July 2017
SYS_CONTEXT function parameters and examples
Active parameters
ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_NAME, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USER, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'SESSION_USER') from dual;
-- HR
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual;
-- HR
Deprecated parameters
AUTHENTICATION_TYPE, CURRENT_USER, CURRENT_USERID, EXTERNAL_NAME
Extra for sys_context
Link
Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_NAME, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USER, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'SESSION_USER') from dual;
-- HR
e.g.
SQL> select SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') from dual;
-- HR
Deprecated parameters
AUTHENTICATION_TYPE, CURRENT_USER, CURRENT_USERID, EXTERNAL_NAME
Extra for sys_context
Link
Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
Friday 14 July 2017
Quiesce restricted mode vs Restricted mode
Differences;
* Quiesce restricted option is less raugh than restricted option and they are quite similar.
* In quiesce restricted option, active session allows to continue until it become inactive.
* In restricted session, only new users can login who has "restrict session" privileges, but in quiesce restricted session, system blocks new non-dba users.
commands;
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM UNQUIESCE;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Ref: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276
* Quiesce restricted option is less raugh than restricted option and they are quite similar.
* In quiesce restricted option, active session allows to continue until it become inactive.
* In restricted session, only new users can login who has "restrict session" privileges, but in quiesce restricted session, system blocks new non-dba users.
commands;
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM UNQUIESCE;
check the state;
SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;
-- normal, quiescing, quiesced
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Ref: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:902149038276
Tuesday 11 July 2017
log_buffer, log_checkpoint_timeout and log_checkpoint_interval parameters
Purpose
We are able to tune CKPT process with these paramaters and they should be configured together.
Usage
e.g.
Check current parameter:
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- -------------------
log_buffer integer 5361664
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1200
Check log file size of one group
SQL> select group#, bytes, blocksize, members from v$log;
GROUP# BYTES BLOCKSIZE MEMBERS
---------- ---------- ---------- ----------
1 52428800 512 2
2 52428800 512 2
Log file size -> 52428800 bytes -> 50 MB
Log buffer should be -> 50 MB * 3 = 150 MB -> 157286400
Log checkpoint timeout could be -> 600 -> 10 minutes
Log checkpoint interval could be -> 0 or 102400 (102400 * 512(OS Block size) -> 52428800 bytes -> 50 MB is equal to log file size)
Apply the setting:
init.ora file:
*.log_buffer=157286400
*.log_checkpoint_interval=102400
*.log_checkpoint_timeout=600
Then restart the instance to perform.
Check lastly:
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- -------------------
log_buffer integer 157286400
log_checkpoint_interval integer 102400
log_checkpoint_timeout integer 600
Deep notes
Log group members should be at least 2, same size, same amount and located in different paths to take backup. (log members are exactly same, 1-> 1)
Log checkpoint timeout parameter means that at most 600 seconds later, dirty blocks are written to database files from SGA.
Log checkpoint interval parameter means that after active log file size exceeds to 50 MB, CKPT is triggered for DBWR. If this parameter is "0", Oracle decides CKPT running time automatically.
We are able to tune CKPT process with these paramaters and they should be configured together.
e.g.
Check current parameter:
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- -------------------
log_buffer integer 5361664
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1200
Check log file size of one group
SQL> select group#, bytes, blocksize, members from v$log;
GROUP# BYTES BLOCKSIZE MEMBERS
---------- ---------- ---------- ----------
1 52428800 512 2
2 52428800 512 2
Log file size -> 52428800 bytes -> 50 MB
Log buffer should be -> 50 MB * 3 = 150 MB -> 157286400
Log checkpoint timeout could be -> 600 -> 10 minutes
Log checkpoint interval could be -> 0 or 102400 (102400 * 512(OS Block size) -> 52428800 bytes -> 50 MB is equal to log file size)
Apply the setting:
init.ora file:
*.log_buffer=157286400
*.log_checkpoint_interval=102400
*.log_checkpoint_timeout=600
Then restart the instance to perform.
Check lastly:
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- -------------------
log_buffer integer 157286400
log_checkpoint_interval integer 102400
log_checkpoint_timeout integer 600
Deep notes
Log group members should be at least 2, same size, same amount and located in different paths to take backup. (log members are exactly same, 1-> 1)
Log checkpoint timeout parameter means that at most 600 seconds later, dirty blocks are written to database files from SGA.
Log checkpoint interval parameter means that after active log file size exceeds to 50 MB, CKPT is triggered for DBWR. If this parameter is "0", Oracle decides CKPT running time automatically.
Wednesday 28 June 2017
plsql_optimize_level and plsql_code_type parameters
Purpose
We are able to use plsql_optimize_level parameter for getting better execution performance on plsql codes. On the other way, plsql_code_type parameter is using to change compilation mode.
Usage
Check current parameter:
SQL> show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
Apply the setting:
init.ora file:
*.plsql_optimize_level=3
*.plsql_code_type='NATIVE'
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql;
SQL> shutdown;
SQL> startup;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Check lastly:
SQL> show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
plsql_code_type string NATIVE
plsql_debug boolean FALSE
plsql_optimize_level integer 3
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
To see current status of objects;
SQL>
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;
Deep notes
INTERPRETED -> PL/SQL library units compile as bytecode format
NATIVE -> PL/SQL library units compile as native (machine) code format
Ref: https://becomeadba.com/?pages-list
We are able to use plsql_optimize_level parameter for getting better execution performance on plsql codes. On the other way, plsql_code_type parameter is using to change compilation mode.
Check current parameter:
SQL> show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_debug boolean FALSE
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
Apply the setting:
init.ora file:
*.plsql_optimize_level=3
*.plsql_code_type='NATIVE'
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql;
SQL> shutdown;
SQL> startup;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Check lastly:
SQL> show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_ccflags string
plsql_code_type string NATIVE
plsql_debug boolean FALSE
plsql_optimize_level integer 3
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
To see current status of objects;
SQL>
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;
Deep notes
INTERPRETED -> PL/SQL library units compile as bytecode format
NATIVE -> PL/SQL library units compile as native (machine) code format
Ref: https://becomeadba.com/?pages-list
Tuesday 20 June 2017
How to find database object via SQL statements?
SQL>
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'
Saturday 10 June 2017
How to solve SQL Developer redrawing problem on Windows?
Problem
Solution
Tools -> Preferences -> Lock and Feel -> Set Windows -> Click OK
Thursday 8 June 2017
e.g. bind variable usage
bind variable;
When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well.
e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;
DECLARE
v_value VARCHAR2 (30);
BEGIN
:v_bind_value := 100;
SELECT last_name
INTO v_value
FROM employees
WHERE employee_id = :v_bind_value;
DBMS_OUTPUT.put_line (v_value);
END;
/
When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well.
e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;
DECLARE
v_value VARCHAR2 (30);
BEGIN
:v_bind_value := 100;
SELECT last_name
INTO v_value
FROM employees
WHERE employee_id = :v_bind_value;
DBMS_OUTPUT.put_line (v_value);
END;
/
Wednesday 7 June 2017
A brief look to Oracle backup mode
Backup mode;
Oracle runs slightly different that database files are able to only use for read (not for write) in SGA and all database changes (DML, DDL, etc) are written into redo log files by Oracle. In that time, user can copy database files to desired folders because database file are frozen by system.
For example, when DML execute, all data block changes are written to redo log files as a exact block not as a delta log. Therefore,
Oracle runs slowly according to normal mode and staying backup mode for a long time is also important risk for recovery operations.
e.g. -> if you run "shutdown abort", database gets crash in startup (ORA-10873, ORA-01110). To solve it, run "recover database".
Enable backup mode:
SQL> alter database begin backup;
If you get ORA-01123 error;
Enable archivelog mode;
https://ozsoyler.blogspot.com.tr/2014/10/how-to-checkopen-archieve-log-in-sqlplus.html
To check current db file status:
SQL> select * from v$backup;
-- status column output should be "ACTIVE"
Now we are able to copy/paste operation on disk:
find datafile paths;
SQL> select * from v$datafile;
Finish file transfer operations then disable backup mode:
SQL> alter database end backup;
Oracle runs slightly different that database files are able to only use for read (not for write) in SGA and all database changes (DML, DDL, etc) are written into redo log files by Oracle. In that time, user can copy database files to desired folders because database file are frozen by system.
For example, when DML execute, all data block changes are written to redo log files as a exact block not as a delta log. Therefore,
Oracle runs slowly according to normal mode and staying backup mode for a long time is also important risk for recovery operations.
e.g. -> if you run "shutdown abort", database gets crash in startup (ORA-10873, ORA-01110). To solve it, run "recover database".
Enable backup mode:
SQL> alter database begin backup;
If you get ORA-01123 error;
Enable archivelog mode;
https://ozsoyler.blogspot.com.tr/2014/10/how-to-checkopen-archieve-log-in-sqlplus.html
To check current db file status:
SQL> select * from v$backup;
-- status column output should be "ACTIVE"
Now we are able to copy/paste operation on disk:
find datafile paths;
SQL> select * from v$datafile;
Finish file transfer operations then disable backup mode:
SQL> alter database end backup;
Monday 5 June 2017
How to calculate maximum database file size?
Info:
-- Generally it is 8192 byte (8 KB)
e.g. if block size is 8 KB;
maximum database file size = 4194304 x 8 KB = 33554432 KB = 32768 MB = 32 GB
e.g. if block size is 32 KB (this can be maximum 32 KB)
maximum database file size = 4194304 x 32 KB = 134217728 KB = 131072 MB = 128 GB
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915
https://docs.oracle.com/cd/B28359_01/server.111/b32009/appg_db_lmts.htm#UNXAR408
- Maximum number of DB blocks are 4194304 blocks (222) per database file
- Maximum DB block size is 32 KB per DB block
-- Generally it is 8192 byte (8 KB)
e.g. if block size is 8 KB;
maximum database file size = 4194304 x 8 KB = 33554432 KB = 32768 MB = 32 GB
e.g. if block size is 32 KB (this can be maximum 32 KB)
maximum database file size = 4194304 x 32 KB = 134217728 KB = 131072 MB = 128 GB
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915
https://docs.oracle.com/cd/B28359_01/server.111/b32009/appg_db_lmts.htm#UNXAR408
Sunday 4 June 2017
statistics_level parameter
Purpose
The purpose of this parameter is to gather database and operating system statistics into SYSAUX tablespace. For example, it uses for defining the detail of AWR reports.
Usage
Current setting:
SQL> show parameter statistics_level;
-- default is "TYPICAL"
(options -> BASIC | TYPICAL | ALL)
Change setting permanently:
add this into init.ora file;
*.statistics_level='ALL'
-- BASIC is not enough to get AWR Report
-- TYPICAL is recommended for AWR Report
-- ALL gathers operating system and plan execution statistics records in extra for AWR Report
Then restart the instance to perform.
Change setting for current instance - temporary:
SQL> alter system set statistics_level = ALL;
Result
SQL> show parameter statistics_level;
-- output; ALL
To check detail information, we may look at v$statistics_level view.
SQL> select * from v$statistics_level;
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214
The purpose of this parameter is to gather database and operating system statistics into SYSAUX tablespace. For example, it uses for defining the detail of AWR reports.
Usage
Current setting:
SQL> show parameter statistics_level;
-- default is "TYPICAL"
(options -> BASIC | TYPICAL | ALL)
Change setting permanently:
add this into init.ora file;
*.statistics_level='ALL'
-- BASIC is not enough to get AWR Report
-- TYPICAL is recommended for AWR Report
-- ALL gathers operating system and plan execution statistics records in extra for AWR Report
Then restart the instance to perform.
Change setting for current instance - temporary:
SQL> alter system set statistics_level = ALL;
Result
SQL> show parameter statistics_level;
-- output; ALL
To check detail information, we may look at v$statistics_level view.
SQL> select * from v$statistics_level;
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214
Subscribe to:
Posts (Atom)