Monthly Archives: December 2014

Oracle

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

Leave a comment

Installation of Oracle Enterprise Linux 6.5

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 Installation of Oracle Enterprise Linux 6.5 using your personal desktop/laptop and Oracle VirtualBox.

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

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.

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 19.1766357 98.184375

SQL>

7. This completes resolving Error ORA-01653

Larry Catt

error, Oracle

Resolving Oracle Error ORA-01653 by adding additional storage

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 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.

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>

7. This completes resolving Error ORA-01653

Larry Catt

linux

Disable Linux Firewall

Leave a comment

When preparing your RHEL server for an Oracle RDBMS or other application software installations, it is normally a good idea to temporarily disable the OS layer firewall to prevent possible communication errors. This can be performed with the command CHKCONFIG. Use the following procedures to check and disable or enable the RHEL firewall.

1. Logon as the root user.
Using username “root”.
Last login: Mon Jun 2 08:57:14 2014 from 192.168.10.1
[root@linuxpc ~]#
2. Check the current status of RHEL firewall with command ‘SERVICE’ command.
[root@linuxpc ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all — 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 ACCEPT icmp — 0.0.0.0/0 0.0.0.0/0
3 ACCEPT all — 0.0.0.0/0 0.0.0.0/0
4 ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
5 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 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@linuxpc ~]#

3. As shown above the firewall is currently active, You can disable now with the ‘SERVICE’ command.
[root@linuxpc ~]# service iptables stop
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Unloading modules: [ OK ]
[root@linuxpc ~]#

4. Or you can use the ‘CHKCONFIG’ command to disable the RHEL firewall, which shuts the firewall down on reboot. This is done by adding references to the appropriate /etc/rc.d directory to either kill the service or start the service at given run level.
[root@linuxpc ~]# chkconfig iptables off
[root@linuxpc ~]#

Larry Catt, OCP