12c database - ORACLEAGENT BLOG

REDO LOG FILE

Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

SQL> select * from v$log;

SQL> select group#,status,sequence# from v$log;

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 CURRENT                  91
         2 INACTIVE                 89
         3 INACTIVE                 90

SQL> select * from v$logfile;

Active (Current) and Inactive Redo Log Files

Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.

If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

Add New redo log file

SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/TESTDB/redo04.dbf' 
size 100m;

Database altered.

Add Member to an existing group

SQL> alter database add logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log' 
to group 4;

Database altered.

Drop member

SQL> alter database drop logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log';

Database altered.

Drop Logfile Group

SQL> alter database drop logfile group 4;

Database altered.


SQL> select group#,status from v$log;

    GROUP#                STATUS
----------            ----------------
         1               CURRENT
         2               INACTIVE
         3               INACTIVE

Adding Two member

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log',
'/u01/app/oracle/oradata/TESTDB/redo04C.log') size 100m;

Database altered.

SQL> select group#,member from v$logfile;

    GROUP#         MEMBER
----------       ---------------------------                       
         4    /u01/app/oracle/oradata/TESTDB/redo04a.log

         4     /u01/app/oracle/oradata/TESTDB/redo04C.log

Log Switches and Log Sequence Numbers

A log switch is a point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

Each online or archived redo log file is uniquely identified by its log sequence number. During a crash, instance, or media recovery, the database properly applies to redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Feb, 2021

Create and Configure ACLs in Oracle database

Let us see how to create and configure ACLs in Oracle database.

ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21

We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.

Below Steps need to follow configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

— Drop an access control list :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

— TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

— If we want to delete or revoke this permission :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for INDIA user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;
HOST                   LOWER_PORT      UPPER_PORT       PRIVILEGE       STATUS
------------------- -----------------  ---------------  ----------  ------------
10.0.06.01                25               25          connect           GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

–Status column must have value granted.

SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.

ACL                     PRINCIPAL    PRIVILEGE     IS_GRANT   START_DATE   END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml    INDIA       connect        true

Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-

ACL                     PRINCIPAL      PRIVILEGE     IS_GRANT    START_DATE    END_DATE
---------------------   -----------   -----------    --------     --------     -------
/sys/acls/utl_mail.xml       INDIA       connect      true
/sys/acls/utl_smtp.xml       INDIA       connect      true
/sys/acls/utl_tcp.xml        INDIA       connect      true

TO enable this ACL permission for a different USER other than above user(INDIA) :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

ACL                      PRINCIPAL      PRIVILEGE     IS_GRANT   START_DATE   END_DATE
-----------------       -------------   -----------   ----------  --------   ----------
/sys/acls/utl_smtp.xml     INDIA         connect        true
/sys/acls/utl_smtp.xml     TEST          connect        false

— This query has to be run with TEST user and Status column should have value “GRANTED“

SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;

HOST                      LOWER_PORT       UPPER_PORT        PRIVILEGE      STATUS
--------------------       ----------     -----------       ---------      -------
10.0.06.01                    25              25              connect       GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

Now check your procedure to send mail through test user , it will work.

After this configuration is completed. we can send a mail with and without attachment. Please check the link for further.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Feb, 2021

Create and Configure ACLs in Oracle database

Let us see how to create and configure ACLs in Oracle database.

ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21

Below Steps need to follow configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

— Drop an access control list :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

— TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

— If we want to delete or revoke this permission :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for INDIA user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;
HOST                   LOWER_PORT      UPPER_PORT       PRIVILEGE       STATUS
------------------- -----------------  ---------------  ----------  ------------
10.0.06.01                25               25          connect           GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

–Status column must have value granted.

SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.

ACL                     PRINCIPAL    PRIVILEGE     IS_GRANT   START_DATE   END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml    INDIA       connect        true

ACL                     PRINCIPAL      PRIVILEGE     IS_GRANT    START_DATE    END_DATE
---------------------   -----------   -----------    --------     --------     -------
/sys/acls/utl_mail.xml       INDIA       connect      true
/sys/acls/utl_smtp.xml       INDIA       connect      true
/sys/acls/utl_tcp.xml        INDIA       connect      true

TO enable this ACL permission for a different USER other than above user(INDIA) :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

ACL                      PRINCIPAL      PRIVILEGE     IS_GRANT   START_DATE   END_DATE
-----------------       -------------   -----------   ----------  --------   ----------
/sys/acls/utl_smtp.xml     INDIA         connect        true
/sys/acls/utl_smtp.xml     TEST          connect        false

— This query has to be run with TEST user and Status column should have value “GRANTED“

SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;

HOST                      LOWER_PORT       UPPER_PORT        PRIVILEGE      STATUS
--------------------       ----------     -----------       ---------      -------
10.0.06.01                    25              25              connect       GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

Now check your procedure to send mail through test user , it will work.

After this configuration is completed. we can send a mail with and without attachment. Please check the link for further.

Oracle Linux 7 (OEL7) Installation

Introduction:

This article guides you to perform the installation of Oracle Linux 7 (OEL7).

Choose “Install Oracle Linux 7.3” and press enter.

Step:1 Select the type of configuration do you want, typical or custom. Mostly in this below options typical is recommended if you need to set some advanced configuration you can choose custom option.

Step:2 Browse the iso file location and select I will install the operating system later option.

Step:3 Select a guest operating system, I have chosen the Linux operating system.

Step:4 Specify the virtual machine name and location of the virtual machine.

Step:5 Configure the maximum disk size I have configured the 200GB disk size and select the option store virtual disk as a single file.

Step:6 Specify the memory size of the virtual machine.

Step:7 Use ISO image file, specify the OS location OEL 7(Linux 7) software.

Step:8 Set the Date and time, choose the timezone, region and city as Asia and Kolkata respectively.

Step:9 Select the base environment and adds on for selected environment, select all the check boxes for all the base environments.

Step:10 Specify the installation destination, here we can partition our storage either by automatic partitioning or customized partitioning.

Step:11 Do the partition manually. Such as create the following mount points /boot , / , swap.

Step:12 This page asks for the confirmation, whether we can go with the configured partitions.

Step:13 Setup the password for the root user.

Step:14 We can create the user, I have created the user oracle and set up the password for the oracle user. Now we all set for the installation we can proceed further.

Step:15 Now we can login to the oracle user and get in to the VM.

Step:16 Choose the language as English(United states).

Step:17 Now we can use the Virtual Machine installed with Linux 7 OS and I checked my internet is working within the VM by pinging google.com.

Convert Non CDB to CDB in Oracle 12c

Description:-

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database. This is the article which will guide step-by-step Converting Non CDB to CDB in Oracle Database 12c Release.

Step 1: Check the database its properly up and running. shutdown the non-CDB and start it in read-only mode.

Non Container Database Name : NONCDB

Container database Name : ORA_CDB

Non Container Database (Noncdb)

[oracle@localhost ~]$ . ora12c.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 21 14:45:36 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

NONCDB READ WRITE

SQL> SELECT name, open_mode FROM v$pdbs;
no rows selected

Container database Name (Ora_cdb)

[oracle@localhost ~]$ . oracdb.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 21 14:56:55 2021

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size     8792296 bytes
Variable Size   872417048 bytes
Database Buffers   369098752 bytes
Redo Buffers     7983104 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

ORA_CDB READ WRITE

SQL> col name for a20

SQL> select name, open_mode FROM v$pdbs;

NAME OPEN_MODE

PDB$SEED READ ONLY

CDB READ WRITE

[oracle@localhost ~]$ ps -ef | grep pmon

Step 2: Shutdown the non-CDB database and start it in read-only mode.

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size     8620224 bytes
Variable Size   872417088 bytes
Database Buffers   369098752 bytes
Redo Buffers     8155136 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

Step 3: Create the XML file needed for cdb conversion. the non-cdb database using the DBMS_PDB_DESCRIBE procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/tmp/noncdb.xml’);
END;
/

Step 4:Shutdown the NON-CDB database ( Noncdb).

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 5: Open the cdb database (ORA_CDB)

[oracle@localhost Desktop]$ cd

[oracle@localhost ~]$ . oracdb.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 20:36:39 2021

Connected to an idle instance.

SQL> startup

SQL> select name,open_mode,cdb from v$database;

NAME OPEN_MODE CDB

ORA_CDB READ WRITE YES

Step 6: Check the compatibility of PDB in ( ORA_CDB).

SQL> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => ‘/tmp/noncdb.xml’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

Step 7: Check the violations of PDB in (ORA_CDB).

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name=’NONCDB’;

Step 8: Create pluggable database in container db (ORA_CDB).

SQL> create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY;

create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY

*ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file

‘/u01/app/oracle/oradata/NONCDB/datafile/o1_mf_temp_j0lhxlpy_.tmp’

Note: As tempfile is already there, so mention tempfile reuse tag, to avoid this error.

SQL> create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY tempfile reuse;

Step 9: Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 10: Startup the PDB and check the open mode PDB.

SQL> alter Pluggable database pdb1 open;

SQL> SELECT name, open_mode FROM v$pdbs;

The non-cdb database has been converted to CDB now successfully.

Category Archives: 12c database

Active (Current) and Inactive Redo Log Files

Add New redo log file

Add Member to an existing group

Drop member

Drop Logfile Group

Adding Two member

Log Switches and Log Sequence Numbers

Feb, 2021

Let us see how to create and configure ACLs in Oracle database.

Below Steps need to follow configure :-

Feb, 2021

Let us see how to create and configure ACLs in Oracle database.

Below Steps need to follow configure :-