System & Database Related Solutions

Tuesday 13 November 2018

Step by Step Enable Archive Log in Oracle 12c

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 244249
Current log sequence

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string miscdb
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0100E+10 bytes
Fixed Size 3721224 bytes
Variable Size 1778386936 bytes
Database Buffers 8287944704 bytes
Redo Buffers 29831168 bytes
Database mounted.
SQL>

Space Assign for Fast Recovery Area:

SQL> alter system set db_recovery_file_dest_size = 200G;

System altered.

Choose the destination:

SQL> alter system set db_recovery_file_dest='/u02/app/oracle/FRA/miscdb';

System altered.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02/app/oracle/FRA/miscdb
db_recovery_file_dest_size big integer 200G
recovery_parallelism integer 0
SQL>

Monday 12 November 2018

ORA-00600: internal error code, arguments: [4193], [31580], [31585], [], [], [], [], [], [], [], [], []

Problem: The following error coming from alert log

Errors in file /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/bacdb_m001_26439.trc (incident=1129768) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [4193], [31580], [31585], [], [], [], [], [], [], [], [], []
Incident details in: /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/incident/incdir_1129768/bacdb_m001_26439_i1129768.trc

Solution:

idle@SYS> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE

idle@SYS>

1. Create pfile from spfile to edit
SQL> Create pfile='/export/home/oracle/pfile081018.ora' from spfile;

2. Shutdown the instance

3. set the following parameters in the pfile /tmp/initsid.ora
undo_management = manual
event = '10513 trace name context forever, level 2'

4. SQL>>startup restrict pfile='/export/home/oracle/pfile081018.ora'

startup restrict pfile='/export/home/oracle/pfile081018.ora'

5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

idle@SYS> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
UNDOTBS1 ONLINE _SYSSMU33_748006928$
UNDOTBS1 ONLINE _SYSSMU34_2282707298$
UNDOTBS1 ONLINE _SYSSMU35_3873732304$
UNDOTBS1 ONLINE _SYSSMU36_2146687221$
UNDOTBS1 ONLINE _SYSSMU37_2465056056$
UNDOTBS1 ONLINE _SYSSMU38_941211847$
UNDOTBS1 ONLINE _SYSSMU39_113148174$
UNDOTBS1 ONLINE _SYSSMU40_2428414494$
UNDOTBS1 ONLINE _SYSSMU41_3210694757$
UNDOTBS1 ONLINE _SYSSMU42_1725270345$

.
..
.
.
.
.

This is critical - we are looking for all undo segments to be offline - System will always be online.

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.

If all offline then continue to the next step

6. Create new undo tablespace - example
SQL>create undo tablespace datafile size 2000M;

CREATE UNDO TABLESPACE UNDO2 DATAFILE '+DATA/bacdb/undo2tbs01_dbf' SIZE 10240M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

7. Drop old undo tablespace
SQL>drop tablespace including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles;

8. SQL>shutdown immediate;

9 SQL>startup nomount; --> Using your Original spfile

10. Modify the spfile with the new undo tablespace name

SQL> Alter system set undo_tablespace = '' scope=spfile;

Alter system set undo_tablespace = 'UNDO2' scope=spfile;

11. SQL>shutdown immediate;

12. SQL>startup; --> Using spfile

Monday 17 September 2018

step by step to configure NTP client in Soalris

How to Set Up an NTP Client in Soalris

Step 1: Login as root user

Step 2: Create the ntp.conf file.

# cd /etc/inet

-bash-4.1$ ls -lrt

total 203

-r--r--r-- 1 root sys 3262 Oct 6 2015 dhcpd.conf.example

-rw-r--r-- 1 root sys 5832 Oct 6 2015 slp.conf.example

-rw-r--r-- 1 root sys 4570 Oct 6 2015 services

-rw-r--r-- 1 root sys 1882 Oct 6 2015 protocols

-rw-r--r-- 1 root sys 462 Oct 6 2015 networks

-rw-r--r-- 1 root sys 487 Oct 6 2015 netmasks

-r--r--r-- 1 root sys 2088 Oct 6 2015 ipsecinit.sample

-rw-r--r-- 1 root sys 2039 Oct 6 2015 ipsecalgs

-rw-r--r-- 1 root sys 528 Oct 6 2015 ipaddrsel.conf

-rw-r--r-- 1 root sys 812 Oct 6 2015 inetd.conf

-r--r--r-- 1 root sys 22 Oct 6 2015 datemsk.ndpd

-r--r--r-- 1 root sys 4001 Oct 6 2015 ntp.server

-r--r--r-- 1 root sys 4122 Oct 6 2015 ntp.client

-r--r--r-- 1 root sys 3290 Oct 6 2015 wanboot.conf.sample

drwxr-xr-x 4 ikeuser sys 8 Jul 18 2016 ike

drwx------ 3 root sys 6 Jul 18 2016 secret

lrwxrwxrwx 1 root root 7 Jul 18 2016 ipnodes -> ./hosts

-rw-r--r-- 1 root root 63 Jul 18 2016 static_routes-DefaultFixed

-rw-r--r-- 1 root sys 252 Feb 5 2018 hosts

-bash-4.1$

# cp ntp.client ntp.conf

Edit the ntp.conf file.

#vi /etc/inet/ntp.conf

# Many ISP's also provide NTP servers for use by their customers.

# server server_name1 iburst

# server server_name2 iburst

# server server_name3 iburst

server

# Always configure the drift file. It can take days for ntpd to completely

# stabilize and without the drift file, it has to start over on a reboot

# of if ntpd restarts.