Category: Oracle

Oracle DBA Related

How to rename a column in MariaDB

Leave a comment 
mysql > desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int(11)     | YES  |     | NULL    |       |
| ename | varchar(15) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

mysql > alter table emp change column empno eno int(11);
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eno   | int(11)     | YES  |     | NULL    |       |
| ename | varchar(15) | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

mysql >

How to Migrate Non-CDB to CDB – With Downtime

Leave a comment

Step 1:Non-CDB Instance
1.1 Open Non-CDB in Read-Only Mode
1.2 execute dbms_pdb.describe and generate the PDB Description XML File
1.3 Shutdown Non-CDB Instance

Step 2: Target CDB
2.1. Check the PDB description compatibiliy
dbms_pdb.check_plug_compatibility
2.2. Confirm if any voilations are there pdb_plug_in_violation view for this PDB
All Voilations should be fixed except execute non-cdb_to_pdb.sql
2.3. Create pluggable database using PDB Description XML File
Options:
COPY – This will copy all the files from source.
NOn-COPY
MOVE
2.4. Execute the non-cdb_to_pdb.sql from rdbms/admin folder
2.5. Confirm the no voilations in pdb_plug_in_violation view for this PDB

How to Access Control Lists (ACL) in Oracle

Leave a comment

What is Access Control Lists(ACL) in Oracle?

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduced fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants. Oracle provide the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow ACL management from PL/SQL.

Oracle Database 12c has deprecated many of the procedures and functions in the DBMS_NETWORK_ACL_ADMIN package, replacing them with new procedures and functions. We still have the concept of Access Control Lists (ACLs), but these are often created implicitly when adding an Access Control Entry (ACE), which is similar to adding privileges using the previous API. The biggest change is an Access Control Entry can be limited to specific PL/SQL APIs (UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL).

How to see Network Access Level on your database is granted.

set linesize 200
set pagesize 200
col host format a30
col ACL format a50
col acl_owner format a10
select * from dba_network_acls;

set linesize 200
set pagesize 200
col host format a30
col start_date format All
col end_date format All
col principal format a25
col privilege format a10

select host,lower_port,upper_port,ace_order,
to_char(start_date,’DD-MON-YYYY’) AS start_date, to_char(end_date,’DD-MON-YYYY’) AS end_date,
grant_type,inverted_principal,principal,principal_type,
privilege
from dba_host_aces
order by bosts,ace_order;

How to ORA-20000: Failed to send email: Error Code-24247: ORA-24247 : network access denied by access control list(ACL)

The above is failed for Network Access Level for Sending Email. For email is port 25. You can grant access on to the particular host/domain to grant for port 25 as below

$ sqlplus / as sysdba

BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => ‘hostname or domain name’,
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(privilege_list => xs$name_list(‘smtp’),
principal_name => ‘test1’,
principal_type => xs_acl.ptype_db));
END;
/

Note: From a security standpoint, it’s not a good idea to allow complete network access from the database,

privilege_list : The list of privileges available to the ACE.
principal_name : The database user the ACE applies to.
principal_type : You will always use XS_ACL.PTYPE_DB for these network ACEs as they apply to users and roles.

The privilege_list specifies one or more privileges in a comma separated list. The available privileges are shown below.

http : Access restricted to the UTL_HTTP package and the HttpUriType type.
http_proxy : Needed in conjunction with http if HTTP access is via a proxy.
smtp : Access restricted to the UTL_SMTP and UTL_MAIL packages.
resolve : Access restricted to the UTL_INADDR packages.
connect : Opens access to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and DBMS_LDAP packages and the HttpUriType type.
jdwp : Enables Java Debug Wire Protocol debugging operations.

How to rotate listener log online

Leave a comment

How to rotate listener log online

In oracle listener log file contains a lot of useful information, like the program which was used for the connection, the IP address where the connection is coming from, the OS user which was used on the client and we can add many more to be recorded.

Since it records more information the log file will keep growing. We may need to do some maintenance to rotate the log. As the log file is actively used by oracle it will be difficult to rotate during db is online.

One best way is we can have procedure to off the listener logging and rotate the log and enable the listener logging during offbusiness hours. This does not required any downtime on the database

lsnrctl <<-EOF set current_listener
set log_status off
EOF

mv

lsnrctl <<-EOF set current_listener
set log_status on
EOF

How to get DDL in Oracle

Leave a comment

We can get the DDL of an object using GET_DDL()

It is a function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL(‘TABLE’,'<table_name>’) from DUAL;

Example:

MUDHALVAN@UPGR:SQL> set long 1000
MUDHALVAN@UPGR:SQL> set pagesize 0
MUDHALVAN@UPGR:SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,’EMP’) from DUAL;

Output:

CREATE TABLE “MUDHALVAN”.”EMP”
( “ENO” NUMBER(10,0) NOT NULL ENABLE,
“ENAME” VARCHAR2(20),
“DOB” DATE,
CONSTRAINT “EMP_PK” PRIMARY KEY (“ENO”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”

How to Invalid Partition Indexes

Leave a comment

Indexes will become invalid for various reason. When indexes are invalid your query may not perform well and sometime it will lead into your operation fail during few DML. So it is DBA responsibility periodically check and rebuild during off business hours.

1. How to find the Invalid Indexes
Indexes can be from normal tables or partitioned/subpartitioned tables respectively we will have NONPARTITIONED INDEXES and PARTITIONED INDEXES/SUBPARTITIONED INDEXES

We have three different views to check for respective Indexes.
1.1 DBA_INDEXES
1.2 DBA_IND_PARTITIONS
1.3 DBA_IND_SUBPARTITIONS

You can use the STATUS of these views to find the invalid Indexes. Status with VALID, USABLE and N/A is good.

you have have to write simple SQL using the all three with proper subtitle and run it periodically.

——Snip of Invalid Index SQL – Start——–

set linesize 200
set pagesize 200
col owner format a20
col index_owner format a20
col index_name format a30
col parition_name format a30
col subpartition_name format a30
col status format a10

prompt —– Invalid NONPARTITIONED Indexes
select owner,index_name,status from dba_indexes where
status not in (‘VALID’,’USABLE’,’N/A’);
prompt —– Invalid PARTITIONED Indexes
select index_owner,index_name,parition_name,status from
dba_indexes where status not in (‘VALID’,’USABLE’,’N/A’);
prompt —– Invalid SUBPARTITIONED Indexes
select index_owner,index_name,parition_name,subpartition_name,status
from dba_indexes where status not in (‘VALID’,’USABLE’,’N/A’);

——Snip of Invalid Index SQL – End——–

2. How to fix the Invalid Indexes:

Once invalid indexes are identified we have to rebuild

We can use REBUILD to rebuild the indexes. We can use below keywords appropriately to rebuild indexes and safer manner

2.1 PARALLEL
2.2 NOLOGGING
2.3 ONLINE

Note: Please be careful while doing rebuilding indexes will make some current operation on hold/abort so better you check before doing rebuild operation and I would recommend you to do it during off business hours until unless you need it as emergency. If need to be done during business hours please make sure users aware that you are doing it.

NONPARTITIONED indexes rebuild:

ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD NOLOGGING;

PARTITIONED indexes rebuild:

ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD PARTITION <PARTITION_NAME> PARALLEL NOLOGGING;

How to Advance Compression 12c

Leave a comment

1. What is Advance Compression:

As you already know in oracle compression started playing major role these days on saving cost and improving performance.

Oracle Advanced Compression option help to improve performance while reducing storage costs. It allows significantly reduce overall database storage footprint by enabling compression for all types of data –relational (table), unstructured (file), index, network and backup data.
Oracle Advanced Compression option are designed to improve performance for all components of your IT infrastructure, including memory, network bandwidth and storage.

2. Benefits of Advanced Row Compression

1. Reduce the storage space consumption by a factor of 2x to 4x by using Advanced Row Compression.
2. 2.5x faster on table scan compare with uncompressed data
3. Significant advantage is to read compressed blocks (data and indexes) directly, in memory, without uncompressing the blocks. This helps improve performance due to the reduction in I/O, and the reduction in system calls related to the I/O operations. Further, the buffer cache becomes more
efficient by storing more data without having to add memory.

3. Minimal Performance Overhead

1. A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only the transaction that performs the compression of the block will experience the slight compression overhead – the majority of DML transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.
2. Less than 3% cpu overhead on compressed data

4. Advanced Row Compression Implementation:

4.1 For new tables and partitions, enabling Advanced Row Compression is easy: simply CREATE the table or partition and specify “ROW
STORE COMPRESS ADVANCED”
Example:
CREATE TABLE emp (emp_id NUMBER, first_name VARCHAR2(128), last_name VARCHAR2(128)) ROW STORE COMPRESS ADVANCED;

4.2 For existing tables and partitions, there are a number of recommended approaches to enabling Advanced Row Compression:
4.2.1. ALTER TABLE … ROW STORE COMPRESS ADVANCED
Enable Advanced Row Compression for all future DML — however, the existing data in
the table will remain uncompressed.

4.2.2. Online Redefinition (DBMS_REDEFINITION)
Enable Advanced Row Compression for future DML and also compress existing data.
DBMS_REDEFINITION keeps the table online for both read/write activity during the migration. Run DBMS_REDEFINITION in parallel for best performance.
There is no interruption in the use of the indexes during, or after.
The only exception is when online redefinition is used for redefining a partition — any global indexes are invalidated and need to be rebuilt

4.2.3. ALTER TABLE … MOVE ROW STORE COMPRESS ADVANCED
Enable Advanced Row Compression for future DML and also compress existing data.
While the table is being moved it is online for read activity but has an exclusive (X)
lock – so all DML will be blocked until the move command completes. Run ALTER
TABLE…MOVE in parallel for best performance.
ALTER TABLE… MOVE will invalidate any indexes on the partition or table; those indexes will
need to be rebuilt after the ALTER TABLE… MOVE.

4.2.4. ALTER TABLE … MOVE TABLE/PARTITION/SUBPARTITION … ONLINE
Enable Advanced Row Compression for future DML and also compress existing data. ALTER TABLE … MOVE TABLE/PARTITION/SUBPARTITION … ONLINE allows
DML operations to continue to run uninterrupted on the table/partition/subpartition that is being
moved. Indexes are maintained during the move operation, so a manual index rebuild is not
required. New in 12.2 on Oracle Cloud, move tables online as well as partitions/subpartitions.

5. Advanced Index Compression
OLTP Databases will have more indexes to support the multitude of access paths for OLTP applications, this can cause indexes to contribute a greater share to overall storage of a database.

New in 12.2 on Oracle Cloud, the “HIGH” level of Advanced Index Compression provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the following advantages over LOW compression:
Gives higher compression ratios in most cases.
Employs more complex compression algorithms than advanced low compression.
Stores data in a compression unit, which is a special on-disk format.

The following is an example of enabling Advanced Index Compression (high level):

CREATE INDEX hr.emp_mndp_ix ON hr.employees(manager_id, department_id) COMPRESS ADVANCED HIGH;

Restriction on Index Compression:

Bitmap indexes can’t be compressed.
Partitioned indexes can’t be compressed. From 11g you can do compress partition index .

6. Here I tried to implement Index compression using Oracle Lab:

6.1 Create a Table with 6 columns type of varchar
CREATE TABLE test (
ENAME VARCHAR2(75),
EADD1 VARCHAR2(75),
EADD2 VARCHAR2(75),
EADD3 VARCHAR2(75),
EADD4 VARCHAR2(75),
CITY VARCHAR2(75)
);
Table created.

INSERT /*+ APPEND */ INTO test
SELECT RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(TO_CHAR(level),75, ‘X’)
FROM dual
CONNECT BY level <= 10000;

COMMIT;
10000 rows created.
col owner format a10
col segment_name format a15
col segment_type format a15
select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST TABLE 6291456

6.2 Created Non-Unique Index without compression and checked the size of an Index

SQL> CREATE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 6291456
MUDHALVAN TEST TABLE 6291456

SQL>

6.3 Created Non-Unique Index with compression and checked the size of an Index

SQL> CREATE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS 5;

Index created.

SQL>EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456

SQL>

Note : You can see the size of an index is reduced more than 2.5x

6.4 Created Unique Index without compression and checked the size of an Index

SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 6291456
MUDHALVAN TEST TABLE 6291456

SQL>

6.5 Created Unique Index with compression and checked the size of an Index

SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS 5;

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456

SQL>

6.6 Created Unique Index with compression advanced and checked the size of an Index

SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS ADVANCED LOW;

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;

OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456

SQL>

How to Import Oracle 12C Dump into 11g Database

Leave a comment

What is the scenario:
Exported the database using 12c expdp. Now we have to import into 11g Rel 2 database.

What Command you used:
impdp system/system directory=backup dumpfile=test12c.dmp logfile=test12.log schemas=test version=11.2

What Error message you get:
ORA-39142 incompatible version

Solution:
impdp system/system directory=backup dumpfile=test12c.dmp logfile=test12.log schemas=test version=latest

Note: You can also do expdp again with version=11.2 and do direct impdp on target database.

1 2