ORACLE DATABASE PROBLEM AND SOLUTIONS

Friday, August 30, 2019

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 ~]$

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 ~]#

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>

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 ~]#

Check database status using python

Check database status using python

save this file as dbstatus.py

import cx_Oracle

db=cx_Oracle.connect("hr","hr","192.168.43.68/ind")

cursor=db.cursor()
cursor.execute(""" select name,open_mode from v$database """)

for count in cursor:
print(count)

Output:

"C:\Program Files\Python37\python.exe" C:/Users/ANURAG/PycharmProjects/phython_master/conn.py
('IND', 'READ WRITE')

Process finished with exit code 0

Thursday, April 11, 2019

How to check and stop firewall on Oracle Linux 6.

How to check and stop firewall on Oracle Linux 6

[root@pri ~]# service iptables status
Table: nat
Chain PREROUTING (policy ACCEPT)
num target prot opt source destination

Chain INPUT (policy ACCEPT)
num target prot opt source destination

Chain OUTPUT (policy ACCEPT)
num target prot opt source destination

Chain POSTROUTING (policy ACCEPT)
num target prot opt source destination
1 MASQUERADE tcp -- 192.168.122.0/24 !192.168.122.0/24 masq ports: 1024-65535
2 MASQUERADE udp -- 192.168.122.0/24 !192.168.122.0/24 masq ports: 1024-65535
3 MASQUERADE all -- 192.168.122.0/24 !192.168.122.0/24

Table: mangle
Chain PREROUTING (policy ACCEPT)
num target prot opt source destination

Chain INPUT (policy ACCEPT)
num target prot opt source destination

Chain FORWARD (policy ACCEPT)
num target prot opt source destination

Chain OUTPUT (policy ACCEPT)
num target prot opt source destination

Chain POSTROUTING (policy ACCEPT)
num target prot opt source destination
1 CHECKSUM udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:68 CHECKSUM fill

Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:53
2 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:53
3 ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:67
4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:67
5 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
6 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0
7 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
8 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
9 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 192.168.122.0/24 state RELATED,ESTABLISHED
2 ACCEPT all -- 192.168.122.0/24 0.0.0.0/0
3 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
4 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-port-unreachable
5 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-port-unreachable
6 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
num target prot opt source destination

[root@pri ~]#
[root@pri ~]#
[root@pri ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: nat mangle filte[ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
[root@pri ~]#
[root@pri ~]#
[root@pri ~]#
[root@pri ~]# service iptables status
iptables: Firewall is not running.

[root@pri ~]#

How can I access Oracle database from Python language using unique way ?

How can I access Oracle database from Python language?

we usually get issue while working with oracle database using python langaugae . The big issue how to connect with database .

There are many articles are available but when you run inti issue very few helps on out .
if you are here then you issue is solved to how to connect with oracle database using python langaugae ?

* First you need to install python (in my case python version is 3.7.2)

* Install Oracle client (in my case oracle 18c)

* Update network configuration file tnanames.ora using db details

* Stop firewall on linux (db install on linux in my case )

* Install oracle cx_Oracle module in python using pip command as below

* Start listener (db service) on oracle server

save the file as oracle_test.py

from future import print_function

import cx_Oracle

# Connect as user "hr" with password "hr" to the "ind" service running on this server.
connection = cx_Oracle.connect("hr", "hr", "192.168.43.68/ind")

cursor = connection.cursor()
cursor.execute("""
SELECT first_name, last_name
FROM employees
WHERE department_id = :did AND employee_id > :eid""",
did = 55,
eid = 180)
for fname, lname in cursor:
print("Values:", fname, lname)

* you can upgrade pip module using below snapshot: