Friday, August 30, 2019

Find Out Virtual Memory PAGESIZE on Linux




[root@localhost ~]# getconf PAGESIZE
4096
[root@localhost ~]# getconf PAGE_SIZE
4096
[root@localhost ~]#


 [root@localhost ~]# getconf -a | grep -i pagesize
PAGESIZE                           4096
[root@localhost ~]#

- No comments:
Labels:

Flush Bad SQL Plan from Shared Pool



SQL>
SQL> show con_name

 CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

 ADDRESS          HASH_VALUE
---------------- ----------
0000000064E29800 1689401402

 SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');

 PL/SQL procedure successfully completed.

 SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

 no rows selected

 SQL>
- No comments:
Labels: ,

PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared


To declare the DBMS_SHARED_POOL.PURGE you have to run dbmspool.sql under ORACLE_HOME/rdbms/admin/  directory

 SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');
BEGIN DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C'); END;

       *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 30 16:55:58 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

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

Session altered.


Package created.


Grant succeeded.


Session altered.

SQL>

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

ADDRESS          HASH_VALUE
---------------- ----------
0000000064E29800 1689401402

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';

no rows selected

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>


- 2 comments:
Labels: ,

How To Clone A Pluggable Database From Existing PDB

[oracle@localhost ~]$ sqlplus "/as sysdba"

 SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 30 16:13:57 2019

 Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 SQL> select name,open_mode from v$pdbs;

 NAME          OPEN_MODE
------------- ----------
PDB$SEED      READ ONLY
DB            MOUNTED

 SQL>
SQL>
SQL>
SQL> alter session set container=db;

 Session altered.

 SQL> alter database open;

 Database altered.

 SQL> select file_name from dba_data_files;

 FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/cdb/db/system01.dbf
/u01/app/oracle/oradata/cdb/db/sysaux01.dbf
/u01/app/oracle/oradata/cdb/db/undotbs01.dbf
/u01/app/oracle/oradata/cdb/db/users01.dbf

 SQL>
SQL> show con_name

 CON_NAME
------------------------------
DB
SQL> shut immediate
Pluggable Database closed.
SQL> startup open read only;
Pluggable Database opened.
SQL> show con_name

 CON_NAME
------------------------------
DB
SQL>
SQL> alter session set container=CDB$ROOT;

 Session altered.
SQL> show con_name

 CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database PROD_DB from DB FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb/db','/u01/app/oracle/oradata/cdb/db/prod_db');

 Pluggable database created.

 SQL> select name,open_mode from v$pdbs;

 NAME            OPEN_MODE
------------    ----------
PDB$SEED        READ ONLY
DB              READ ONLY
PROD_DB         MOUNTED

 SQL> alter session set container=DB;

 Session altered.

 SQL> shut immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL>
SQL> select name,open_mode from v$pdbs;

 NAME           OPEN_MODE
-------------- ----------
DB             READ WRITE

 SQL> alter session set container=PROD_DB;

 Session altered.

 SQL> startup
Pluggable Database opened.
SQL>
SQL> select name,open_mode from v$pdbs;

 NAME                          OPEN_MODE
----------------------------- ----------
PROD_DB                       READ WRITE
SQL> alter session set container=CDB$ROOT;

 Session altered.

 SQL>  select name,open_mode from v$pdbs;

 NAME             OPEN_MODE
---------------  -----------
PDB$SEED         READ ONLY
DB               READ WRITE
PROD_DB          READ WRITE

 SQL>
[oracle@localhost ~]$ ls -lrt /u01/app/oracle/oradata/cdb/db
total 843692
-rw-r-----. 1 oracle oinstall 135274496 Jul 26 19:20 temp01.dbf
drwxr-x---. 2 oracle oinstall       104 Aug 30 16:19 prod_db
-rw-r-----. 1 oracle oinstall   5251072 Aug 30 16:20 users01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 30 16:20 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 367009792 Aug 30 16:20 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 262152192 Aug 30 16:20 system01.dbf
[oracle@localhost ~]$
[oracle@localhost ~]$ ls -lrt /u01/app/oracle/oradata/cdb/db/prod_db/
total 722008
-rw-r-----. 1 oracle oinstall   5251072 Aug 30 16:21 users01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 30 16:21 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 367009792 Aug 30 16:21 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 262152192 Aug 30 16:21 system01.dbf
-rw-r-----. 1 oracle oinstall 135274496 Aug 30 16:21 temp01.dbf
[oracle@localhost ~]$

- No comments:
Labels:

Monday, August 12, 2019

Add/Remove port in Linux firewall



Add port in firewall-cmd
===============================

 [root@localhost ~]# firewall-cmd --zone=public --add-port=1521/tcp --permanent
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: ssh dhcpv6-client
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

 [root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: ssh dhcpv6-client
  ports: 1521/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

 [root@localhost ~]#

 Remove port from Firewall 
===================================

 [root@localhost ~]# firewall-cmd --zone=public --remove-port=1521/tcp --permanent
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: ssh dhcpv6-client
  ports: 1521/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

 [root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: ssh dhcpv6-client
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

 [root@localhost ~]#

- No comments:
Labels: ,

Tuesday, April 30, 2019

RMAN Incremental Level 1 backup with out Level 0 backup




 Incremental Level 1 backup with out Level 0 backup

level 1 incremental backup can be either of the following types:

 Differential backup:  

 Its backs up all blocks changed after the most recent incremental backup at level 1 or 0

  Cumulative backup : 

 Its backs up all blocks changed after the most recent incremental backup at level 0




RMAN> list backup;

 specification does not match any backup in the repository

 RMAN>

 RMAN>

 RMAN> backup incremental level 1 database;

 Starting backup at 30-APR-19
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 7 found
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/IND/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/IND/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/IND/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/IND/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-19
channel ORA_DISK_1: finished piece 1 at 30-APR-19
piece handle=/u01/app/oracle/product/18.0.0/db/dbs/IND/backupset/2019_04_30/o1_mf_nnnd0_TAG20190430T171432_gdjf91z7_.bkp tag=TAG20190430T171432 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:27
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 30-APR-19
channel ORA_DISK_1: finished piece 1 at 30-APR-19
piece handle=/u01/app/oracle/product/18.0.0/db/dbs/IND/backupset/2019_04_30/o1_mf_ncsn1_TAG20190430T171432_gdjfhrcq_.bkp tag=TAG20190430T171432 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-APR-19

 RMAN>
RMAN>

 RMAN>

 RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
9       B  0  A DISK        30-APR-19       1       1       NO         TAG20190430T171432
10      B  1  A DISK        30-APR-19       1       1       NO         TAG20190430T171432

 RMAN>

- No comments:
Labels: ,

Friday, April 12, 2019

Disable firewall in Linux


Disable firewall in Linux



 Stop the firewall using root user

[root@grac43 ~]# service iptables status

iptables: Firewall is not running.

 [root@pri ~]#
[root@pri ~]# chkconfig --list  | egrep 'iptables|ip6tables|libvirt'
ip6tables       0:off   1:off   2:on    3:on    4:on    5:on    6:off
iptables        0:off   1:off   2:on    3:on    4:on    5:on    6:off
libvirt-guests  0:off   1:off   2:on    3:on    4:on    5:on    6:off
libvirtd        0:off   1:off   2:off   3:on    4:on    5:on    6:off
[root@pri ~]#
[root@pri ~]#
[root@pri ~]# chkconfig libvirtd off
[root@pri ~]# chkconfig libvirt-guests off
[root@pri ~]# chkconfig ip6tables off
[root@pri ~]# chkconfig iptables off
[root@pri ~]# chkconfig --list  | egrep 'iptables|ip6tables|libvirt'
ip6tables       0:off   1:off   2:off   3:off   4:off   5:off   6:off
iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off
libvirt-guests  0:off   1:off   2:off   3:off   4:off   5:off   6:off
libvirtd        0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@pri ~]#
[root@pri ~]#
reboot the system using init 6 

 [root@pri ~]# init 6
[root@pri ~]#
login as: oracle
oracle@192.168.43.68's password:
Last login: Fri Apr 12 17:36:20 2019 from 192.168.43.70
[oracle@pri ~]$
[oracle@pri ~]$
[oracle@pri ~]$
[oracle@pri ~]$ su - root
Password:
[root@pri ~]#
[root@pri ~]# service iptables status
iptables: Firewall is not running.
[root@pri ~]# chkconfig --list  | egrep 'iptables|ip6tables|libvirt'
ip6tables       0:off   1:off   2:off   3:off   4:off   5:off   6:off
iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off
libvirt-guests  0:off   1:off   2:off   3:off   4:off   5:off   6:off
libvirtd        0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@pri ~]#

- No comments:
Labels: ,
Subscribe to: Comments (Atom)

Interview Question 18 : What are oinstall and dba groups? Why we assign these groups to oracle user ?

  What are oinstall and dba groups in Oracle? Short answer oinstall → Controls software ownership and installation dba → Controls databa...