How to Connect Windows 11 to virtualBox VM

Below are the steps to perform password-less ssh setup from Windows 11 to Linux 

just paste your id_rsa.pub file content on target machine /home/<user>/.ssh/authorized_keys file.

and do from win 11 cmd 

ssh <user>@target_machine 

 Tablespace Usage and Add-resize Datafile

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

In day to day work  dba or developer come across tablespace alerts regarding usage spike to 90% or above threshold value, so in this case we have options like adding one more datafile to the tablespace or truncating the tables or objects in tablespace or more often resizing the datafile , these options do well and kind of solve the issue but there is a option in adding datafile like "autoextend on" so database will take care of the usage and will reserve disk space automatically when tablespace needed it. 

To see the tablespace usage execute following query :

 SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024 * 1024) "Size (GB)",

       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

 order by 5 desc;

After you from above query result which tablespace usage has spike then run the following to know datafiles inside that tablespace so you can add or resize datafile :

select file_name,autoextensible,sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='GGS_DATA' group by file_name,autoextensible;

To resize a datafile :

alter database datafile '/path/to/datafile.dbf ' resize nG ;   .......................(n is no of GB eg. 5G) 

To add datafile :

alter tablespace tablespace_name add datafile '/path/to/datafile.dbf ' size nG autoextend on 

alter tablespace tablespace_name add datafile '/path/to/datafile.dbf ' size nG autoextend off;

 SELECT /* + RULE */  

(select sys_context('userenv','service_name') from dual) service_name,

df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024 * 1024) "Size (GB)",

       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

 order by 5 desc;

 Listener configuration and commands in Oracle Database

listener.ora file content

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

LISTENER_DATABASENAME  =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))

      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

USE_SID_AS_SERVICE_LISTENER_DBNAME =ON

SID_LIST_LISTENER_DBNAME =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /path/to/oracle/home/dir)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = instance_name)

      (ORACLE_HOME = /path/to/oracle/home/dir)

    )

  )

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

tnsnames.ora file content :

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

DBNAME = 

(DESCRIPTION = 

    (ADDRESS_LIST = 

        (ADDRESS = (PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=1521)

    )

    (CONNECT_DATA = 

        (SID = SIDNAME)

        (INSTANCE_NAME = INSTANCENAME)

    )

)

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

Listener commands:

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

$ lsnrctl status 

$ lsnrctl start listener_name

$ lsnrctl stop listener_name

$ lsnrctl reload listener_name  .......................(if you've modified listener.ora and want implement                                                                                      without stoping the listener)

How to Start - Stop Oracle Database on Single Instance and RAC

 How to Start and Stop Oracle Database 

For single instance Database (Shutdown Sequence):

$ sudo -iu oracle
$ cat /etc/oratab 
$ export ORACLE_HOME=/path/to/oracle/home/dir            
$ export ORACLE_SID=your_sid_from_oratab
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
oracle@host $ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> exit;
$ ps -ef | grep -i tns 
$ lsnrctl stop Listener_Name;

For single instance Database (Startup Sequence) :

$ sudo -iu oracle
$ export ORACLE_HOME=/path/to/oracle/home/dir            
$ export ORACLE_SID=your_sid_from_oratab
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
oracle@host $ sqlplus / as sysdba
SQL> startup ;
SQL> set linesize 400;
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
SQL> select inst_id,instance_name,host_name,status,logins,status_time,database_status from                   gv$instance order by inst_id;

For RAC Database (Shutdown Sequence):

do it on all nodes:

$ ps -ef | grep -i pmon 

$ sudo -iu oracle

$ srvctl stop database -d db_unique_name;

$ srvctl status database -d db_unique_name;

$ ps -ef | grep -i d.bin

$ cd [path/to]/bin 

/bin # ./crsctl stop cluster 

/bin # ./crsctl stop crs 

For RAC Database (Startup Sequence):

do it on all nodes if  crs auto restart is not enable

/bin # ./crsctl start crs

/bin # ./crsctl start cluster

# sudo -iu oracle 

$ srvctl start database -d db_unique_name 

$ srvctl status database -d db_unque_name

$ ps -ef | grep -i d.bin 

$ cd [path/to]/bin 

$ ./crsctl status res -t 

Note: to only start and shut single database instance execute below command

$ sudo -iu oracle

$ srvctl start instance -d database_name -i instance_name .............( to start the instance)

$ srvctl stop instance -d database_name -i instance_name .............( to stop the instance) 

Note: crsctl utility can only run with root user 

Starting and Stoping Sequence for Golden Gate

 Sequence of start and stop for Golden Gate

===============================

Hint:-

1. If Linux Server needs to reboot then stop "Manager" process also.

2. If Patching, upgrades then stop manager also.

3. When stopping golden gate processes keep in mind always stop extract, replicat, pump first and then stop manager if server needs to reboot. 

Hint:-

1. find the latest status of processes using :-- cat dirrpt/PROCESS_NAME.rpt or cat ggserr.log

2. dirprm---> parameter files 

3. dirdat ---> data files trails files 

4. dirchk ---> 

Stop Sequence:

==============================

  =  stop the extract process on the source

  =  wait for the pump process to catch up

  =  stop the pump process on the source

  =  wait for the replicat process to catch up

  =  stop the replicat process on the target

  =  optionally (because it does not belong to a specific GoldenGate replication) stop the manager and  jagent process on both source and target

=========================================================================

Start Sequence:

===============================

 =   optionally start the manager and the jagent process

 =   start the replicat process on the target

 =   start the pump process on the source

 =   start the extract process on the source

=========================================================================

special commands >>

====================

GGSCI> 

> stop extract extract_name; ------------------------if stop extr doesnt worked then move to send extract                                                                                   extract_name,forcestop

> send extract extract_name, forcestop; -----------if send extract extract_name, forcestop doesnt                                                                                           worked then move to kill extract extract_name

> kill extract extract_name; -------------------------final option that kill the process and ckpt will, stop                                                                                       mgr would not try to start kill process, no db loss

> alter extract extract_name, begin now; ............will alter the sequence no and bring the ckpt to                                                                                            current trail 

> start extract extract_name; ...............................start the extract,

> alter extract extract_name, etrollover; .............will alter extract to new trail file;

Note: You can also apply above commands to replicate.

How to Perform Result-Cache-flush and UTLRP (Recompile objects)

 Executing Result Cache flush and UTLRP 

Result Cache flush: 

generally result cache flush done on the database to flush the buffer to remove all the object from result result cache.

We should perform result cache flush on each node if its RAC database 

For single instance database result cache flush perform once only.

Note: It should not performed on "Production Database Server" as per std practice.

SQL> EXECUTE DBMS_RESULT_CACHE.FLUSH; 

PL/SQL procedure successfully completed. 

UTLRP or recompile objects

utlrp or recompile objects is the procedure to recompile invalid objects if after this you find any invalid component then it needs to get validate.

Note: Needs to perform  only one node if RAC database and for single instance database perform once.

SQL> @?/rdbms/admin/utlrp.sql  

###################################################################################

Enter this query to return the number of objects that utlrp.sql has compiled. This number increases over time as the script runs.

SQL> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; 

Enter this query to return the number of objects that utlrp.sql has compiled with errors. 

SQL> select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors; 

###################################################################################

col comp_id format a14; 

col comp_name format a36; 

col version format a12; 

col status format a16; 

select comp_id,comp_name,version,status from dba_registry;

###################################################################################

select count(1) from dba_objects where status = 'INVALID';  

select count(1) from dba_objects where status = 'INVALID' and owner not in ('SYS' , 'SYSTEM');  

select count(1) from dba_objects where status = 'INVALID' and owner in ('SYS' , 'SYSTEM');  

select owner, object_name, object_type from dba_objects where status = 'INVALID' and owner in ('SYS' , 'SYSTEM');  

select owner,object_type,count(*) from dba_objects where status='INVALID' group by object_type,owner;   col owner format a20;

col object_name format a32;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by object_type,owner;

********************************************

1. Execute the UTLRP.SQL script for validating all the invalid components and objects in Oracle:

 @ORACLE_HOME\rdbms\admin\utlrp.sql;

2. Check the list of objects invalid in the Oracle schemas.

select owner,object_name,object_type,status from dba_objects where status = 'INVALID' group by owner,object_name,object_type, status;

3. Compile the objects manually if utlrp.sql is not working for them:

--For package specification:

alter package owner.object_name compile;

--For package body:

alter package owner.object_name compile body;

--For Procedure:

alter procedure owner.object_name compile;

--For Function:

alter function owner.object_name compile;

4. Execute the following command to make the CATALOG or CATPROC components valid.

-- For validate the CATPROC:

execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

-- For validate the CATALOG:

execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;

5. Verify the registry component. If no row return means all registry components are valid.

select comp_name, version,status from dba_registry where STATUS <> 'VALID';

Note: You can also use the following script to find invalid objects in step 2

-- Execute this one by one and make objects valid until it return "CATPROC can be validated".

 $ sqlplus / as sysdba

 set serveroutput on;

 declare

     start_time date;

     end_time date;

     object_name varchar(100);

     object_id char(10);

 begin

select date_loading, date_loaded into start_time, end_time from registry$ where cid = 'CATPROC';

select obj#,name into object_id,object_name from obj$ where status > 1 and (ctime between start_time and end_time

or mtime between start_time and end_time

or stime between start_time and end_time)

and rownum <=1;

dbms_output.put_line('Please compile the invalid object '||object_name||' object_id '||object_id );

exception

     when no_data_found then

          dbms_output.put_line('CATPROC can be validated now');

 end;

 /

 

Note:

If the registry component still invalid state, then we can use the following script to recreate the objects:

Before running the following script you have the database backup or take a fresh backup or cold backup:

I run these 2 scripts to valid them:

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

Blocking Sessions and How to Kill them

 

 ALTER SYSTEM KILL SESSION

==============================================================

Blocking sessions occur when a session issues an "insert, update or delete" command that changes a row. When the change occurs,"the row is locked until the session either commits the change, rolls the change back or the user logs off the system." 

    select

blocking_session,

sid,

serial#,

wait_class,

seconds_in_wait

    from

        v$session

    where

blocking_session is not NULL

    order by

blocking_session;

=========================================================================

How to check Locking session/Blocking session

 

col username for a15

 col machine for a20

 col event for a20

 col program for a20

 col osuser for a20

 set line 3000

 set pagesize 2000

 SELECT

 Inst_id,

    s.username,

    s.blocking_session blk_sess,

    s.blocking_instance blk_inst,

    s.sid, 

    s.serial#, 

    s.sql_id,

    s.status,

    s.seconds_in_wait waittime,

    s.event,

    s.machine 

 FROM

    gv$session s

 WHERE

    blocking_session IS NOT NULL order by SECONDS_IN_WAIT;

=========================================================================

  How to search users on database 

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

################################################################################

To get spid and then kill them 

$ ps -ef | grep ora 

$ kill -9 spid 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';   -------------------------> single instance db

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';  ------------------> RAC instance db

###################################################################################

for a single instance database : 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

============================================================

In a RAC environment, you optionally specify the INST_ID, 
shown when querying the GV$SESSION view.This allows you 
to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

=========================================================================

The KILL SESSION command doesn't actually kill the session. 

It merely asks the session to kill itself. 

In some situations, like waiting for a reply from a remote database or rolling

back transactions, the session will not kill itself immediately and will wait 

for the current operation to complete. In these cases the session will have a 

status of "marked for kill". 

It will then be killed as soon as possible.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

=========================================================================

ALTER SYSTEM DISCONNECT SESSION
POST_TRANSACTION >> DISCONNECT AFTER THE TRANSACTION COMPLETES
IMMEDIATE >> DISCONNECT SUDDENLY

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

=========================================================================

ALTER SYSTEM CANCEL SQL (18c+)
to cancel a SQL statement in a session, providing an alternative to killing a rogue session.

SQL> ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

=========================================================================

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

#############################################################################

col inst_id for a13

col sid for a12

col serial# for a12

col username for a14

col status for a12

col server for a14

col schemaname for a12

col osuser for a14

col machine for a13

col program for a13

select inst_id,sid,serial#,username,status,server,schemaname,osuser,machine,program,sql_id from gv$session

where status = 'ACTIVE';

##################################################################################

Working status and seconds v$session 

col "Description" format a50

select sid,

        decode(state, 'WAITING','Waiting',

                'Working') state,

        decode(state,

                'WAITING',

                'So far '||seconds_in_wait,

                'Last waited '||

                wait_time/100)||

        ' secs for '||event

        "Description"

from v$session

where username = 'username';

###################################################################################

select SID, osuser, machine, terminal, service_name,

       logon_time, last_call_et

from v$session

where username = 'userename';

###################################################################################

Session wait for each machine 

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

col username format a5

col program format a10

col state format a10

col last_call_et head 'Called|secs ago' format 999999

col seconds_in_wait head 'Waiting|for secs' format 999999

col event format a50

select sid, username, program,

        decode(state, 'WAITING', 'Waiting',

                'Working') state,

last_call_et, seconds_in_wait, event

from v$session

where machine = 'hostname';

###################################################################################