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;

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

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

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

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.