Category Archives: Oracle

Oracle 12c – Use Online operation enhancements

April 2, 2016 Larry Catt Leave a comment

Online table redefinition allows to restructure table with little affect to table availability. The table is open for queries and DML for most of the redefine procedure, but is locked in exclusive mode for a short time in process. Redefinition is available in EM Cloud and through DBMS_REDEFINITION package.

Three enhancements in 12c for redefine:

Multiple Partitions – you can create multiple partitions in single redefinition execution. This reduces time for re-partitioning but requires more space, because an interim table is built for each partition creation.
Tables with VPD Policies – You can now redefine tables with Virtual Private Database policies. The copy_vpd_opt parameter of START_REDEF_TABLE procedure must set to handle VPD policies during process. Parameter can be set to:
1. CONS_VPD_NONE – This is the default, for use when VPD not in user, error will occur on a VPD table.
2. CONS_VPD_AUTO – VPD policies will be copied to new table during redefinition process.
3. CONS_VPD_MANUAL – VPD policies must be manually moved to new table, done when structural changes are made that affect VPD policies.
Lock Timeout for FINISH_REDEF_TABLE – You can specify a number of seconds that a redefine process will hold an exclusive lock for swapping interim table. If it is exceeded redefine will exit.
REDEF_TABLE – New procedure in the DBMS_REDEFINITION package, which allows for table redefinition in a single step to change the following:
1. Change the tablespace for a table, partition, index or LOB storage.
2. Compression changes including table, partition, index or LOBs
3. LOB column changes for SECUREFILE or BASICFILE storage.

Larry Catt

OCP

12c, CDB, listener, network, Oracle

Configure listener.ora for CDB

March 6, 2016 Larry Catt Leave a comment

With Oracle 12c multitenant databases connection is still normally performed through the Oracle networking files of listener.ora, tnsnames.ora, and sqlnet.ora. This articles shows the configuration of listener.ora file to support connection to a CDB database called cdb1.

Logon to your Oracle Server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon FEB 24 09:57:56 EDT 2016 on pts/0

[oracle@linux2 ~]$

Create the file listener.ora with the following statement:

echo -e “SID_LIST_LISTENER =\n” \

” (SID_LIST =\n” \

” (SID_DESC =\n” \

” (SID_NAME = “$ORACLE_SID”)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

” )\n” \

“\nLISTENER =\n” \

” (DESCRIPTION_LIST =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” ))\n” \

“\nADR_BASE_LISTENER = /opt/app/oracle\n” \

“\nLOGGING_LISTENER=ON\n” \

“\nTRACE_LEVEL_SERVER=SUPPORT\n” \

“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Execution example:

[oracle@linux2 admin]$ echo -e “SID_LIST_LISTENER =\n” \

> ” (SID_LIST =\n” \

> ” (SID_DESC =\n” \

> ” (SID_NAME = “$ORACLE_SID”)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

> ” )\n” \

> “\nLISTENER =\n” \

> ” (DESCRIPTION_LIST =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” ))\n” \

> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \

> “\nLOGGING_LISTENER=ON\n” \

> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \

> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Now start the listener with the command: lsnrctl start

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 10:58:06

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 24-FEB-2016 10:58:06

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

You can test the listener configuration by suppling the tnsping utility with the <machine_IP>:<Port>/<service_name> as below:

[oracle@linux2 admin]$ tnsping 10.30.15.75:1521/cdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 11:04:40

Used parameter files:

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=cdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

OK (10 msec)

[oracle@linux2 admin]$

This completes configuration of listener.ora file for CDB.

Larry Catt

OCP

12c, linux, Oracle

Part 10 – Building Oracle 12c development suite – Preparing Linux OS for 12c installation

June 8, 2015 Larry Catt Leave a comment

Preparing Linux OS for 12c installation
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration of Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your Linux Server as the root user and create the group “dba” and user account “oracle” to be your oracle software owner with the following commands:
groupadd dba
useradd oracle -g dba -G root
passwd oracle

3. Create the following directory structures to support your oracle software installation with the commands:
mkdir /opt/app
mkdir /opt/app/oracle
mkdir /opt/app/oracle_software
mkdir /opt/app/oracle_inventory
mkdir /opt/app/oracle_inventory/inventory

4. Change ownership of directory structures to the oracle software owner with the following commands:
chown oracle:dba /opt/app
chown oracle:dba /opt/app/oracle
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /opt/app/oracle_inventory
chown oracle:dba /opt/app/oracle_inventory/inventory

5. Change the ownership of the Oracle RDBMS media files downloaded in Part 9 of this serious to the oracle software owner with the following commands:
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /opt/app/oracle_software/*
chmod 777 /opt/app/oracle_software/*

6. Switch to the oracle software owners account and change directory back to /opt/app/oracle_software location.
[root@oel65 oracle_software]# su – oracle
[oracle@oel65 ~]$ cd /opt/app/oracle_software
[oracle@oel65 oracle_software]$

7. Unpack all the mediate files with the following command: “find . -name “*.zip” -exec unzip {} \;”
[oracle@oel65 oracle_software]$ find . -name “*.zip” -exec unzip {} \;
…
..
inflating: database/response/netca.rsp
inflating: database/response/dbca.rsp
inflating: database/response/db_install.rsp
[oracle@oel65 oracle_software]$

8. This completes the setup of the VM Enterprise Linux for Oracle 12c RDBMS installation.

Larry Catt, OCP

12c, linux, Oracle

Part 9 – Building Oracle 12c development suite – Media packages required for installation of Oracle RDBMS 12c

May 30, 2015 Larry Catt Leave a comment

Part 9 – Building Oracle 12c development suite.
Media packages required for installation of Oracle RDBMS 12c

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process of downloading the media required for installation of Oracle 12c RDBMS in a RHEL 6.5 environment.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Open an internet browser and download the following files from www.oracle.com and place them in the directory /opt/app/oracle_software.

p17694377_121020_Linux-x86-64_1of8.zip
p17694377_121020_Linux-x86-64_2of8.zip
p17694377_121020_Linux-x86-64_3of8.zip
p17694377_121020_Linux-x86-64_4of8.zip
p17694377_121020_Linux-x86-64_5of8.zip
p17694377_121020_Linux-x86-64_6of8.zip
p17694377_121020_Linux-x86-64_7of8.zip
p17694377_121020_Linux-x86-64_8of8.zip

3. Change directory to /opt/app/oracle_software with the command “cd /opt/app/oracle_software”

[root@OEL65 ~]# cd /opt/app/oracle_software/
[root@OEL65 oracle_software]#

4. Extract the zip files with the following command: “find . -name “*.zip” -exec unzip {} \;”

[root@OEL65 oracle_software]# find . -name “*.zip” -exec unzip {} \;
[root@OEL65 oracle_software]#

5. NOTE: This is a base version of the software and requires additional patches to bring it to current date.
6. This completes the setup of the VM network.

Larry Catt, OCP

12c, linux, Oracle

Part 8 – Building Oracle 12c development suite – Library setup for Oracle Enterprise Linux 6.5 for Oracle RDBMS 12c

April 15, 2015 Larry Catt Leave a comment

Library setup for Oracle Enterprise Linux 6.5 for Oracle RDBMS 12c

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration libraries for an Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your Linux VM as the root user.
3. Change directory to your yum repository with command: “cd /etc/yum.repos.d”

[root@oel65 ~]# cd /etc/yum.repos.d
[root@oel65 yum.repos.d]#

4. Extract the repository from the oracle public yum website with the command: “wget http://public-yum.oracle.com/public-yum-ol6.repo”

[root@OEL65 yum.repos.d]# wget http://public-yum.oracle.com/public-yum-ol6.repo
–2015-04-12 18:49:53– http://public-yum.oracle.com/public-yum-ol6.repo
Resolving public-yum.oracle.com… 70.186.29.33, 70.186.29.58
Connecting to public-yum.oracle.com|70.186.29.33|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 5046 (4.9K) [text/plain]
Saving to: “public-yum-ol6.repo.1”

100%[======================================>] 5,046 –.-K/s in 0s

2015-04-12 18:49:53 (225 MB/s) – “public-yum-ol6.repo.1” saved [5046/5046]

[root@OEL65 yum.repos.d]#

5. Edit the yum repo file with appropriate values with the following perl command: “perl -pi.bak -e ‘s/enabled=0/enabled=1/g’ /etc/yum.repos.d/public-yum-ol6.repo”

[root@OEL65 yum.repos.d]# perl -pi.bak -e ‘s/enabled=0/enabled=1/g’ /etc/yum.repos.d/public-yum-ol6.repo
[root@OEL65 yum.repos.d]#

6. Download necessary RPM file from oracle with command: “wget –no-check-certificate https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle”

[root@OEL65 yum.repos.d]# wget –no-check-certificate https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
–2015-04-12 18:50:29– https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
Resolving public-yum.oracle.com… 70.186.29.58, 70.186.29.33
Connecting to public-yum.oracle.com|70.186.29.58|:443… connected.
WARNING: no certificate subject alternative name matches
requested host name “public-yum.oracle.com”.
HTTP request sent, awaiting response… 200 OK
Length: 1011 [text/plain]
Saving to: “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle”

100%[======================================>] 1,011 –.-K/s in 0s

2015-04-12 18:50:30 (16.8 MB/s) – “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle” saved [1011/1011]

[root@OEL65 yum.repos.d]#

7. Use the following yum command to install the packages from oracle for 12c RDBMS install: “yum install oracle-rdbms-server-12cR1-preinstall”

[root@OEL65 yum.repos.d]# yum install oracle-rdbms-server-12cR1-preinstall
Loaded plugins: rhnplugin, security
This system is receiving updates from ULN.
Setting up Install Process
…
…
…
public_ol6_MySQL | 1.2 kB 00:00
libdmx.x86_64 0:1.1.3-3.el6 libstdc++-devel.x86_64 0:4.4.7-11.el6
xorg-x11-utils.x86_64 0:7.5-6.el6 xorg-x11-xauth.x86_64 1:1.0.2-7.1.el6

Complete!

8. This completes the setup of the VM libraries for Oracle 12c RDBMS.

Larry Catt, OCP

Oracle

Using explain plan from the SQLPLUS

February 15, 2015 Larry Catt Leave a comment

Oracle provides the ability to examine how a SQL statement will be parsed by oracle with explain plan.

1. Logon to sqlplus with a user that has DBA privileges.

[oracle@MYLINUX ~]$ sqlplus orcl_admin/*******@ORCL

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 13:20:51 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create the plan table with the oracle script $ORACLE_HOME/rdbms/admin/utlxplan.sql. NOTE: The ‘?’ sign in sqlplus refers to the variable ORACLE_HOME

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

Table created.

SQL>

3. Now you have the tables created to hold the parsing information, you can execute the explain command. NOTE: We use the ‘SET’ clause in this example to allow us to easily identify our statement.

SQL> explain plan

2 set statement_id=’TEST1′ for

3 select count(*) from nais.nais_tab;

Explained.

SQL>

4. Now we can query the table plan_table in our schema.

set pagesize 160

select plan_table_output from table(dbms_xplan.display(‘PLAN_TABLE’,’TEST1′,’TYPICAL’));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 1794364880

—————————————————————————————————————————-

——————————————————————————–

——————————————–

| 0 | SELECT STATEMENT | | 1 | 334K (1)| 01:18:02 | | | | | |

| 1 | SORT AGGREGATE | | 1 | | | | | | | |

| 2 | PX COORDINATOR | | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | | Q1,00 | P->S | QC (RAND) |

| 4 | SORT AGGREGATE | | 1 | | | | | Q1,00 | PCWP | |

| 5 | PX PARTITION RANGE ALL| | 554M| 334K (1)| 01:18:02 | 1 | 791 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS FULL | TEST_TAB| 554M| 334K (1)| 01:18:02 | 1 | 791 | Q1,00 | PCWP | |

—————————————————————————————————————————-

5. This completes the use of explain plan for Oracl

Oracle

Part 5 – Building Oracle 12c development suite – Network setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

January 15, 2015 Larry Catt Leave a comment

Network setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration network for an Oracle Enterprise Linux 6.5 installation on Oracle VirtualBox in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator, open a command prompt and execute the command IPCONFIG to gather current IP configuration for your host system.
C:\ >ipconfig
Windows IP Configuration
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . : C3CEN.Net
Link-local IPv6 Address . . . . . : fe80::b9b2:d070:d80f:82d1%11
IPv4 Address. . . . . . . . . . . : 10.30.15.90
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

Ethernet adapter VirtualBox Host-Only Network:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::bdca:1b67:c60c:7b1%19
IPv4 Address. . . . . . . . . . . : 10.30.15.127
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

C:\ >

NOTE: The following information:
HOST IP 10.30.15.90
Host Subnet 255.255.255.0
Gateway 10.30.15.1
Guest IP 10.30.15.127
Guest Subnet 255.s255.255.0

2. Open Oracle VirtualBox, from the main top menu of VirtualBox and Select File ? Preferences…

3. On the Left panel menu select Network and in the right hand panel Host-only Network.

4. Click the PLUS sign symbol to the right of the right hand panel to create a new Host-adaptor.

5. Once the new Adapter is created highlite it and select the screwdriver edit icon on the right hand side.

6. Select the enter a new IP address for your adapter and network mask under the Adapter tab, do not feel anything out under the DHCP Server tap and press OK and press OK at the original Network configuration screen.

7. On your host machine, open another command prompt and execute the “IPCONFIG” command to see your new adapter does exist.
C:\ >ipconfig
Windows IP Configuration
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . : C3CEN.Net
Link-local IPv6 Address . . . . . : fe80::b9b2:d070:d80f:82d1%11
IPv4 Address. . . . . . . . . . . : 10.30.15.93
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.30.15.1

Ethernet adapter VirtualBox Host-Only Network #2:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::c82c:7a2f:242c:fcfb%35
IPv4 Address. . . . . . . . . . . : 192.168.130.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

C:\ >
8. High light guest OS you created in Part 3 of this serious and select Settings tag from tool bar ? Network from left panel ? check the Enable Network Adapter ? Select Host-only Adapter and ensure the Adapter you just selected is in the Name: field from drop down button then press the OK button.
?

9. High light guest OS you created in Part 3 of this serious and select Settings tag from tool bar ? Network from left panel ? select Adapter 2 Tab ?check the Enable Network Adapter ? Select Internal Network from drop down button and ensure Promiscuous Mode is set to Allow All then press the OK button.
?

10. High light guest OS you created in Part 3 of this serious and select Settings tag from tool bar ? Network from left panel ? select Adapter 3 Tab ?check the Enable Network Adapter ? Select Bridge Adapter from drop down button with the name of the physical device and ensure Promiscuous Mode is set to Allow All then press the OK button.
?
11. Double click on the guest OS you created in Part 3 of this serious in the left hand panel of VirtualBox. In this instruction set the name is OEL_65.

12. Once the Oracle Linux Server prompt appear below logon as the user root with the password used during installation in Part 4 of this serious. NOTE: In this series, we installed a base Linux Server without desktop support. If your installation has desktop support, just open a terminal screen to perform the rest of this procedure.

13. Open the file /etc/hosts and add the host IP and hostname you wish to use.

[root@oel65 ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.130.1 oel65

14. As the root user navigate to the directory /etc/sysconfig/network-scripts and use VI editor to adjust the file ifcfg-eth0. Ensure that the ONBOOT is set to YES and

[root@oel65 ~]# cd /etc/sysconfig/network-scripts/
[root@oel65 network-scripts]# cat ifcfg-eth0
DEVICE=eth0
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=none
HWADDR=08:00:27:A2:02:20
IPADDR=10.30.15.127
PREFIX=24
GATEWAY=10.30.15.1
DNS1=10.10.15.21
DEFROUTE=yes
IPV4_FAILURE_FATAL=yes
IPV6INIT=no
NAME=”System eth0″
NETMASK=255.255.255.0
USERCTL=no

[root@oel65 network-scripts]#

15. Restart the network services with command “service network restart”

[root@oel65 network-scripts]# service network restart
Shutting down interface eth0: [ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface eth0: Determining if ip address 10.30.15.127 is already in use for device eth0…
[ OK ]
[root@oel65 network-scripts]#

16. Test your network connection to and from an outside machine with ping command:

17. This completes the setup of the VM network.

Larry Catt, OCP

Oracle

Part 4 – Building Oracle 12c development suite – Installation of Oracle Enterprise Linux 6.5

December 27, 2014 Larry Catt Leave a comment

Installation of Oracle Enterprise Linux 6.5

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Double click on the guest OS you created in Part 3 of this serious in the left hand panel of VirtualBox. In this instruction set the name is OEL_65.

3. Once the Oracle Linux Server 6.5 Installation screen appears, ensure that ‘Install or Upgrade existing system’ is highlighted and press the return button.

4. The screen will appear asking you to validate your installation media, use the error keys to select Skip button and press the Enter key.

5. At the Welcome screen, select the Next button and press the return key.

6. At the Language screen, select your language preference and press the Next Key.

7. At the keyboard selection screen, select your desired keyboard and press Next button.

8. At the devices installation screen, select Basic Storage Devices and press the Next button.

9. At the Storage Device Warning page, select Yes, discard any data.

10. At the name this computer screen, type in the name of your computer and press the Next button.

11. At the time zone screen select your nearest city and press the Next button.

12. At the root account password page, enter the password you wish to use for root access. NOTE: root access is the primary administrative account on a linux system.

13. If the Weak Password warning appears, either select cancel or use Anyways buttons.

14. At the type of installation page, select Replace Existing Linux System(s) and press the Next button.

15. If a Writing storage configuration to disk warning appears, select the Write changes to disk button.

16. A Default installation page, select Basic Server and all defaults and press the Next button.

17. Now the installation will proceed.
18. At the Congratulations screen, click the Reboot button.

19. This completes the installation of Oracle Enterprise Linux 6.5 on Oracle VirtualBox software.
Larry Catt

audit, error, Oracle

Resolving Oracle Error ORA-01653 by removing audit records

December 23, 2014 Larry Catt Leave a comment

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 4096 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 4096 in tablespace SYSTEM

1. Logon to the Oracle database as a sysdba user.

2. Validate that the system tablespace is out of space.

set pagesize 500
select a.tablespace_name,
(select sum(b.bytes)/1024/1024/1024 from dba_data_files b where a.tablespace_name=b.tablespace_name) as GB_TOT,
(select sum(c.bytes)/1024/1024/1024 from dba_free_space c where a.tablespace_name=c.tablespace_name) as GB_AVAIL,
100*(select sum(c.bytes) from dba_free_space c where a.tablespace_name=c.tablespace_name)/(select sum(b.bytes) from dba_data_files b where a.tablespace_name=b.tablespace_name) as PERC_FREE
from dba_tablespaces a where tablespace_name=’SYSTEM’;

TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 19.53125 9.1766357 49.184375

SQL>

3. The query above showes that the system tablespace is out of space. In this article we will purge records from the SYS.AUD$ table to address this error, additionally we
can add additional storage space to address the above error message. This alternative is addressed in another article.

4. Verify that AUD$ tables is indeed consuming a considerable amount of space.

SQL> select sum(bytes)/1024/1024 from dba_segments where SEGMENT_NAME=’AUD$’;

SUM(BYTES)/1024/1024
——————–
9694

SQL>

5. Purge this records with the following statement which remove all records older then 1 day.

SQL> truncate table sys.aud$;

Table truncated.

SQL> commit;

Commit complete.

SQL>

6. Validate that the SYSTEM tablespace has increased.

SQL>

7. This completes resolving Error ORA-01653

Larry Catt

error, Oracle

Resolving Oracle Error ORA-01653 by adding additional storage

December 21, 2014 Larry Catt Leave a comment

1. Logon to the Oracle database as a sysdba user.

2. Validate that the system tablespace is out of space.

TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 10.27125 .01766357 .024375

SQL>

3. The query above showes that the system tablespace is out of space. The quickest fix is to add additional space to the SYSTEM tablespace, additionally we
can address the amount of data stored in the AUD$ tables referenced in the above error message. This alternative is address in another article.

4. To add additional space to the system tablespace, find the current location of SYSTEM data files on your system with the following statement.

SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name=’SYSTEM’;

FILE_NAME BYTES/1024/1024
——————————— —————

/u01/oradata/UCOP/SYSTEM01.DBF 10000

SQL>

5. Verify that you have enough space available on your drives and add an additional datafile;

alter tablespace SYSTEM add datafile ‘/u01/oradata/UCOP/SYSTEM02.DBF’ size 10000m;

SQL> alter tablespace SYSTEM add datafile ‘/u01/oradata/UCOP/SYSTEM02.DBF’ size 10000m;

Tablespace altered.

SQL>

6. Validate that you know have available free space in the SYSTEM tablespace and re-execute the statement which caused your initial error.

TABLESPACE_NAME GB_TOT GB_AVAIL PERC_FREE
—————————— ———- ———- ———-
SYSTEM 19.53125 9.1766357 49.184375

SQL>

7. This completes resolving Error ORA-01653

Larry Catt

Catt Solutions

Category Archives: Oracle

Oracle 12c – Use Online operation enhancements

Configure listener.ora for CDB

Part 10 – Building Oracle 12c development suite – Preparing Linux OS for 12c installation

Part 9 – Building Oracle 12c development suite – Media packages required for installation of Oracle RDBMS 12c

Part 8 – Building Oracle 12c development suite – Library setup for Oracle Enterprise Linux 6.5 for Oracle RDBMS 12c

Using explain plan from the SQLPLUS

Part 5 – Building Oracle 12c development suite – Network setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Part 4 – Building Oracle 12c development suite – Installation of Oracle Enterprise Linux 6.5

Resolving Oracle Error ORA-01653 by removing audit records

Resolving Oracle Error ORA-01653 by adding additional storage

Oracle tips and tricks.