Month: May 2020

ORA-03113: end-of-file on communication channel

Error:

First check logfile to identify the cause !

[oracle@orcl19x ~]$ locate alert_orcl19x1.log
/apps01/base/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log
/apps01/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log
/data01/base/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log
[oracle@orcl19x ~]$ tail -f /apps01/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log
ORA-19502: write error on file "/data01/ORCL12X/archivelog/2021_01_17/o1_mf_1_299_j08yt23w_.arc", block number 26624 (block size=512)
ARCH: I/O error 19502 archiving log 2 to '/data01/ORCL12X/archivelog/2021_01_17/o1_mf_1_299_j08yt23w_.arc'
Sun Jan 17 13:00:34 2021
Errors in file /app01/oracle/product/base/diag/rdbms/orcl12x/orcl12x/trace/orcl12x_ora_15741.trc:
ORA-16038: log 2 sequence# 299 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 2 thread 1: '/app01/oracle/product/base/oradata/orcl12x/redo02.log'
USER (ospid: 15741): terminating the instance due to error 16038
Sun Jan 17 13:00:34 2021
System state dump requested by (instance=1, osid=15741), summary=[abnormal instance termination].
System State dumped to trace file /app01/oracle/product/base/diag/rdbms/orcl12x/orcl12x/trace/orcl12x_diag_15701_20210117130034.trc
Sun Jan 17 13:00:34 2021
Dumping diagnostic data in directory=[cdmp_20210117130034], requested by (instance=1, osid=15741), summary=[abnormal instance termination].
Sun Jan 17 13:00:34 2021
Instance terminated by USER, pid = 15741

While startup, the instance crashed due to multiple reasons because of space crunch in filesystem or ASM,FRA gets filled up and so on.

SQL> startup;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             419431360 bytes
Database Buffers          704643072 bytes
Redo Buffers               13852672 bytes
ORA-03113: end-of-file on communication channel
Process ID: 6215
Session ID: 1 Serial number: 46504


SQL> startup;
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE

SQL> !oerr ora 3113
03113, 00000, “end-of-file on communication channel”
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
// First, check for network problems and review the SQL*Net setup.
// Also, look in the alert.log file for any errors. Finally, test to
// see whether the server process is dead and whether a trace file
// was generated at failure time.

Solution:

Close your session and reopen sql*plus again

Sometimes due to network ipc waits,we get this kind of error

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 30 21:36:46 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             419431360 bytes
Database Buffers          704643072 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

There might be another reason that you face this error!!

Check if your mount point or ASM has sufficient space and not full because of archivelog files fill.

[oracle@orcl12x ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G   11G  7.5G  59% /
tmpfs           2.0G  1.1G  953M  52% /dev/shm
/dev/sda2        15G  8.4G  5.3G  62% /app01
/dev/sda5        12G  2.8G  8.1G  26% /data01

Then delete unwanted trace files and get rid of archivelogs files by backing them up to tape.

RMAN> crosscheck archivelog all;
RMAN> backup archivelog all delete input;
RMAN> delete expired archivelog all;

I will create test case for this error to occur by simulating ora 3113

Check the FRA total space

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data01
db_recovery_file_dest_size           big integer 6000M

Check filesystem mountpoint space

SQL> !df -h /data01
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        12G  9.6G  1.3G  89% /data01         <---------------

I manually fill up FRA space with archivelogs by populating data into table

SQL> create table t(name varchar2(10),id number(10));

Table created.

SQL> begin
  2  for i in 1 .. 10000000 loop
  3  insert into t values('xyz',i);
  4  end loop;
  5  commit;
  6  end;
  7  /

Archives flood up the FRA after insert of data


[oracle@orcl12x ~]$ tail -50f /app01/oracle/product/base/diag/rdbms/orcl12x/orcl12x/trace/alert_orcl12x.log|grep 'Archived Log'
Archived Log entry 217 added for thread 1 sequence 243 ID 0x748f1abe dest 1:
Archived Log entry 218 added for thread 1 sequence 244 ID 0x748f1abe dest 1:
Archived Log entry 219 added for thread 1 sequence 245 ID 0x748f1abe dest 1:
Archived Log entry 220 added for thread 1 sequence 246 ID 0x748f1abe dest 1:
Archived Log entry 221 added for thread 1 sequence 247 ID 0x748f1abe dest 1:
Archived Log entry 222 added for thread 1 sequence 248 ID 0x748f1abe dest 1:
Archived Log entry 223 added for thread 1 sequence 249 ID 0x748f1abe dest 1:
Archived Log entry 224 added for thread 1 sequence 250 ID 0x748f1abe dest 1:
Archived Log entry 225 added for thread 1 sequence 251 ID 0x748f1abe dest 1:
Archived Log entry 226 added for thread 1 sequence 252 ID 0x748f1abe dest 1:
Archived Log entry 227 added for thread 1 sequence 253 ID 0x748f1abe dest 1:
Archived Log entry 228 added for thread 1 sequence 254 ID 0x748f1abe dest 1:
Archived Log entry 229 added for thread 1 sequence 255 ID 0x748f1abe dest 1:
Archived Log entry 230 added for thread 1 sequence 256 ID 0x748f1abe dest 1:
Archived Log entry 231 added for thread 1 sequence 257 ID 0x748f1abe dest 1:
Archived Log entry 232 added for thread 1 sequence 258 ID 0x748f1abe dest 1:
Archived Log entry 233 added for thread 1 sequence 259 ID 0x748f1abe dest 1:
Archived Log entry 234 added for thread 1 sequence 260 ID 0x748f1abe dest 1:
Archived Log entry 235 added for thread 1 sequence 261 ID 0x748f1abe dest 1:
Archived Log entry 236 added for thread 1 sequence 262 ID 0x748f1abe dest 1:
Archived Log entry 237 added for thread 1 sequence 263 ID 0x748f1abe dest 1:
Archived Log entry 238 added for thread 1 sequence 264 ID 0x748f1abe dest 1:
Archived Log entry 239 added for thread 1 sequence 265 ID 0x748f1abe dest 1:
Archived Log entry 240 added for thread 1 sequence 266 ID 0x748f1abe dest 1:
Archived Log entry 241 added for thread 1 sequence 267 ID 0x748f1abe dest 1:

Archive logs are the high contributor of FRA space



SQL> select * from v$flash_recovery_area_usage order by PERCENT_SPACE_USED desc;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
----------------------- ------------------ -------------------------
NUMBER_OF_FILES     CON_ID
--------------- ----------
ARCHIVED LOG                         44.57                         0
             56          0

BACKUP PIECE                         34.66                     11.49
             21          0

CONTROL FILE                             0                         0
              0          0

As expected,there is no space to populate further data into FRA

[oracle@orcl12x ~]$ df -h /data01/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        12G   11G   34M 100% /data01

I abort the instance,startup again and there it is the weird error! although it is common


SQL> shu abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size             989859360 bytes
Database Buffers          637534208 bytes
Redo Buffers               13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6723
Session ID: 1 Serial number: 60575

After strace on the particular process id,it is clear that archivelogs are not being written in disk

[oracle@orcl12x cdmp_20210117042038]$ strace -p 15682
Process 15682 attached
write(1, "alter database open\n", 20)   = 20
write(1, "*\n", 2)                      = 2
lseek(4, 5120, SEEK_SET)                = 5120
read(4, "\r\0\351\0\0\0V\0\352\0\0\0\220\0\353\0\0\0\240\0\356\0\0\0\320\0\357\0\0\0\344\0"..., 512) = 512
write(1, "ERROR at line 1:\n", 17)      = 17  <-----------
write(1, "ORA-03113: end-of-file on commun"..., 48) = 48
write(1, "Process ID: 15741\n", 18)     = 18
write(1, "Session ID: 1 Serial number: 363"..., 35) = 35
write(1, "\n", 1)                       = 1
write(1, "\n", 1)                       = 1
write(1, "SQL> ", 5)                    = 5
read(0, 0x7f9c11fd3000, 1024)           = ? ERESTARTSYS (To be restarted if SA_RESTART is set)
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=15741, si_status=0, si_utime=16, si_stime=5} ---

Stack trace also clearly shows that archivelog fill up space due to space crunch in both FRA and Filesystem!

Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-16038: log 2 sequence# 299 cannot be archived
ORA-19502: write error on file "", block number  (block size=) <--------- 
ORA-00312: online log 2 thread 1: '/app01/oracle/product/base/oradata/orcl12x/redo02.log'
2021-01-17 13:00:34.545399 :kjzduptcctx(): Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+244<-kjzdssdmp()+321<-kjzduptcctx()+692<-kjzdicrshnfy()+992<-ksuitm()+5857<-kcfopd()+8296<-adbdrv_options()+1656<-adbdrv()+151<-opiexe()+20208<-opiosq0()+4554<-kpoal8()+1223<-opiodr()+1165<-ttcpip()+2699<-opitsk()+1734<-opiino()+945<-opiodr()+1165
<-opidrv()+587<-sou2o()+145<-opimai_real()+154<-ssthrdmain()+412
----- End of Abridged Call Stack Trace -----

*** 2021-01-17 13:00:34.580
USER (ospid: 15741): terminating the instance due to error 16038
ksuitm: waiting up to [5] seconds before killing DIAG(15701)

Start the database in mount stage,open rman and report all obsolete backups

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area    1644167168 bytes

Fixed Size                     2925024 bytes
Variable Size                989859360 bytes
Database Buffers             637534208 bytes
Redo Buffers                  13848576 bytes

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           1      12-DEC-20
  Backup Piece       1      12-DEC-20          /data01/1955551678-20201212-01vhsu9i_1_1
Backup Set           2      12-DEC-20
  Backup Piece       2      12-DEC-20          /data01/1955551678-20201212-02vhsu9m_1_1

Delete all obsolete backups

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK

Crosscheck the backups


RMAN> crosscheck backup;
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data01/ORCL12X/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T021408_j07ry2vp_.bkp RECID=144 STAMP=1062036850
crosschecked backup piece: found to be 'AVAILABLE'
.
RMAN> crosscheck archivelog all;
archived log file name=/data01/ORCL12X/archivelog/2021_01_17/o1_mf_1_298_j07txh61_.arc RECID=272 STAMP=1062038880
.
Crosschecked 70 objects

I dont need any backuppiece in disk as they already saved in tape,so i will delete them

It is always recommended to have a recent backup both in disk and tape

RMAN> delete noprompt backuppiece tag TAG20210117T021408;
.
.
backup piece handle=/data01/ORCL12X/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T021408_j07ry2vp_.bkp RECID=144 STAMP=1062036850
deleted backup piece
backup piece handle=/data01/ORCL12X/backupset/2021_01_17/o1_mf_nnndf_TAG20210117T021408_j07ry31l_.bkp RECID=145 STAMP=1062036850
Deleted 12 objects

Now i have sufficient space to open the database 🙂

[oracle@orcl12x archivelog]$ df -h /data01/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        12G  6.0G  5.0G  55% /data01

Archivelogs still spill up FRA

My FRA dont have enough room to hold the backup and archive

[oracle@orcl12x archivelog]$ du -sh *
4.0K    2020_12_20
4.0K    2020_12_21
4.0K    2020_12_25
4.0K    2020_12_26
4.0K    2020_12_27
4.0K    2021_01_13
3.3G    2021_01_17

Anyway i can open database without any issue

RMAN> alter database open;

Statement processed

Finally, take a full cold backup and delete all the archives from disk

ORA-00205: error in identifying control file on standby

I was trying to mount my DR system to synchronise after an activity.But i faced some issues where i was not able to mount the database because of missing controlfile.

I checked the alertlog and i found these errors on the trace file

Errors in file /oracle/base/diag/rdbms/orcldg/orcldg/trace/orcldg_asmb_6031.trc:
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Stopping background process RBAL

ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/orcldg/control.ctl'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcldg/control.ctl
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-205 signalled during: alter database mount...

I was clear that ASM was offline and cluster services were not up and running for any of the diskgroups

[oracle@orcldg ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               OFFLINE OFFLINE      orcldg                   STABLE
ora.DATA.dg
               OFFLINE OFFLINE      orcldg                   STABLE
ora.FRA.dg
               OFFLINE OFFLINE      orcldg                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       orcldg                   STABLE
ora.asm
               OFFLINE OFFLINE      orcldg                   Instance Shutdown,ST
                                                             ABLE
ora.ons
               OFFLINE OFFLINE      orcldg                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       orcldg                   STABLE
--------------------------------------------------------------------------------

Then i started the ASM resource manually.Then the services were all UP.

[oracle@orcldg ~]$ srvctl start asm
[oracle@orcldg ~]$  crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       orcldg                   STABLE
ora.DATA.dg
               ONLINE  ONLINE       orcldg                   STABLE
ora.FRA.dg
               ONLINE  ONLINE       orcldg                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       orcldg                   STABLE
ora.asm
               ONLINE  ONLINE       orcldg                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      orcldg                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       orcldg                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       orcldg                   STABLE
--------------------------------------------------------------------------------

Then i started the database and mounted in recovery mode.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             419431360 bytes
Database Buffers          704643072 bytes
Redo Buffers               13852672 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

The DR was in sync after starting the recovery mode.

MESSAGE
--------------------------------------------------------------------------------

Media Recovery Log +FRA/ORCLDG/ARCHIVELOG/2020_05_29/thread_1_seq_1331.522.10417
22827

Media Recovery Waiting for thread 1 sequence 1332 (in transit)

Root cause:

[root@orcldg oracle]# free -m
             total       used       free     shared    buffers     cached
Mem:          3696       2889        807       1710         44       2169
-/+ buffers/cache:        675       3020
Swap:         2095          0       2095

I didnt had sufficient memory to handle the ASM disk processes.

I had set hardcoded shell script on crontab to run for every 5 minutes.

*/5 * * * * /home/oracle/tblspce.sh > /dev/null

The script processes were piling up and stacked in a queue without getting released from the server.

[oracle@orcldg ~]$ ps -ef|grep oratbl.sh
oracle    6645  6519  0 23:50 pts/0    00:00:00 grep oratbl.sh
oracle    6647  6519  0 23:50 pts/0    00:00:00 grep oratbl.sh
oracle    6649  6519  0 23:50 pts/0    00:00:00 grep oratbl.sh
oracle    6651  6519  0 23:50 pts/0    00:00:00 grep oratbl.sh


After killing the processes i was able to start the ASM

This caused the ASM process to hung and not able to identify the controlfile.

ORA-00205: error in identifying control file on standby

locate command not working -solved

Cause:

locate is often used to search for the files on the linux server.

Sometimes we try to search some file and locate returns nothing.

we think the file is not present on the server though the file exist

[oracle@orcl ~]$ locate file
[oracle@orcl ~]$

Solution:

we have to use updatedb to update the locate repository file and it works like a charm!!

[oracle@orcl ~]$ updatedb
updatedb: can not open a temporary file for `/var/lib/mlocate/mlocate.db'
[oracle@orcl ~]$ su
Password:
[root@orcl oracle]# updatedb
[oracle@orcl ~]$ locate messages-20200209.gz
/var/log/messages-20200209.gz

How to generate awrdiff report

Performance issues often peepout if there are load on the database and bulk transactions pull the database back from moving front.It is a hectic task to identify the bottleneck of the issue.

So generating an AWRDIFF report would give us more insights on the bottom level of the database.It compares the two different time period between good and bad performance.

To generate an awrdiff report:

we can manually generate snapshots by using dbms_repository() package and generate the awr difference from script

Give the begin time snap id and end time snap id one at the time of performance issues and another at the normal working of database to compare the difference

Elapsed: 00:00:00.00
SQL> !uptime
 22:23:03 up  1:18,  1 user,  load average: 0.09, 0.12, 0.13

SQL> EXECUTE dbms_workload_repository.create_snapshot();
Elapsed: 00:00:00.85

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

Current Instance
~~~~~~~~~~~~~~~~

   DB Id       DB Id    DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 1559282684  1559282684 ORCL                1        1 orcl
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'

Type Specified:  html
Elapsed: 00:00:00.00


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1559282684        1 ORCL         orcl         orcl

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1559282684 for Database Id for the first pair of snapshots
Using          1 for Instance Number for the first pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL                47 24 May 2020 21:17      1
                                 48 24 May 2020 22:21      1
                                 49 24 May 2020 22:22      1
                                 50 24 May 2020 22:23      1
                                 51 24 May 2020 22:32      1



Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 47
First Begin Snapshot Id specified: 47

Enter value for end_snap: 51
First End   Snapshot Id specified: 51




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1559282684        1 ORCL         orcl         orcl




Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 1559282684 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days2: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL                47 24 May 2020 21:17      1
                                 48 24 May 2020 22:21      1
                                 49 24 May 2020 22:22      1
                                 50 24 May 2020 22:23      1
                                 51 24 May 2020 22:32      1



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 47
Second Begin Snapshot Id specified: 47

Enter value for end_snap2: 51
Second End   Snapshot Id specified: 51



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_47_1_47.html  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrdiff_1_47_1_47.html

</body></html>
Report written to awrdiff_1_47_1_47.html
[oracle@orcl ~]$ locate awrdiff_1_47_1_47.html
/home/oracle/awrdiff_1_47_1_47.html
[oracle@orcl ~]$ ls -lrt *awr*
-rw-r--r-- 1 oracle oinstall 1084151 May 24 22:59 awrdiff_1_47_1_47.html  <-----

Once it is generated export the html file using winscp to your local drive to analyse and interpret the performance stats of your database!!

Does moving datafile in primary database affect a physical standby replication?

Good day!!

There could be tough situations where we mistakes unknowingly in a critical production databases which make us think a lot.

Lets look at the oracle 12c new feature –moving a datafile online

The database is RAC with physical standby dataguard.

In this scenario,we are going to add a datafile to a database in prod(consideration).

On Primary:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/ORCL/DATAFILE/system.257.1031501823
+DATA/ORCL/DATAFILE/system.265
+DATA/ORCL/DATAFILE/sysaux.256.1031501729
+DATA/ORCL/DATAFILE/undotbs1.259.1031501899
+DATA/ORCL/DATAFILE/example.265.1031501993
+DATA/ORCL/DATAFILE/users.258.1031501899
+DATA/ORCL/DATAFILE/users.272.1040745563

7 rows selected.

SQL> alter tablespace USERS add datafile '+DATA' size 500M;

Tablespace altered.

On Standby:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/ORCLDG/DATAFILE/system.260.1031954441
+DATA/ORCLDG/DATAFILE/system.265.1032181503
+DATA/ORCLDG/DATAFILE/sysaux.264.1031954497
+DATA/ORCLDG/DATAFILE/undotbs1.270.1031954543
+DATA/ORCLDG/DATAFILE/example.269.1031954557
+DATA/ORCLDG/DATAFILE/users.268.1031954621
+DATA/ORCLDG/DATAFILE/users.259.1040846285
+DATA/ORCLDG/DATAFILE/users.258.1040915343

8 rows selected.

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcldg

users.258.1040915343 is the datafile added on USERS.

The datafile is replicated to DR with the same filename

standby_file_management should be set to AUTO to manage datafiles automatically by oracle

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

Now we move the datafile to different directory and rename the datafile online using oracle 12c feature

SQL> alter database move datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' to '+DATA/ORCL/users.new';

Database altered.

Archived Log entry 2553 added for thread 1 sequence 1304 ID 0x5cf12ffb dest 1:
Wed May 20 15:38:31 2020
TT00: Standby redo logfile selected for thread 1 sequence 1305 for destination LOG_ARCHIVE_DEST_2
Wed May 20 15:54:11 2020
alter database move datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' to '+DATA/ORCL/users.new'
Wed May 20 15:54:11 2020
Moving datafile +DATA/ORCL/DATAFILE/users.258.1031501899 (6) to +DATA/ORCL/users.new
Wed May 20 15:54:43 2020
Move operation committed for file +DATA/ORCL/users.new
Completed: alter database move datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' to '+DATA/ORCL/users.new'

After moving the datafile to new location,a thought comes to your mind.

On PROD:

ASMCMD [+DATA/ORCL] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileorcl.ora
users.new    <---------------------------------

The datafile was moved in PROD but not on DR.

On DR:

ASMCMD [+DATA/orcldg/DATAFILE] > ls
EXAMPLE.269.1031954557
SYSAUX.264.1031954497
SYSTEM.260.1031954441
SYSTEM.265.1032181503
UNDOTBS1.270.1031954543
USERS.258.1040915343
USERS.259.1040846285
USERS.268.1031954621 <----------------------------------------

“”” Oh ! I forgot about physical standby database which replicate from primary”””

Don’t worry moving the datafile online in primary doesn’t affect the standby log shipping.

The filename is not renamed on the standby,but it works fine without disruption.

I switch the log for couple of times and generate some manual archives on primary

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1307
Next log sequence to archive   1309
Current log sequence           1309 <------

On DR:

As you can see the archive shipping is running smooth with SCN sync and not interrupted due to datafile move

Recovery of Online Redo Log: Thread 1 Group 10 Seq 1308 Reading mem 0
  Mem# 0: +FRA/ORCLDG/ONLINELOG/group_10.263.1031954629
RFS[1]: Selected log 11 for thread 1 sequence 1309 dbid 1559282684 branch 1031501951
Wed May 20 16:05:34 2020
Archived Log entry 1263 added for thread 1 sequence 1308 ID 0x5cf12ffb dest 1:
Wed May 20 16:05:34 2020
Media Recovery Waiting for thread 1 sequence 1309 (in transit)
Wed May 20 16:05:34 2020
Recovery of Online Redo Log: Thread 1 Group 11 Seq 1309 Reading mem 0
  Mem# 0: +FRA/ORCLDG/ONLINELOG/group_11.262.1031954629

MESSAGE
--------------------------------------------------------------------------------
ARC3: Completed archiving thread 1 sequence 1307 (0-0)
Media Recovery Waiting for thread 1 sequence 1308 (in transit)
ARC1: Beginning to archive thread 1 sequence 1308 (7744674-7744677)
ARC1: Completed archiving thread 1 sequence 1308 (0-0)
Media Recovery Waiting for thread 1 sequence 1309 (in transit)

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            1304          1
ARCH      CLOSING            1308          1
ARCH      CONNECTED             0          0
ARCH      CLOSING            1307          1
RFS       IDLE               1309          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       1309   <----  1
 

“””alter database move datafile“””

The above awesome feature was not available on older versions which was really a difficult time for DR.

Thanks

kishan

Monitor MRP in physical standby

The physical standby database in a dataguard may lag from the archivelogs between primary and standby

This causes the primary database to stop shipping logs and causes MRP stuck issues

To monitor the lags between the primary and standby

Standby:

To start MRP in standby

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            1295          1
ARCH      CLOSING            1294          1
ARCH      CONNECTED             0          0
ARCH      CLOSING            1292          1
RFS       IDLE               1296          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG <---- 1296          1

9 rows selected.


The status of the standby should be applying_log and MRP should be active

Standby:

SQL> !ps -ef|grep -i mrp
oracle    6129     1 42 18:54 ?        01:01:13 ora_mrp0_orcldg

Heartbeat should be happening between primary and standby from dataguard_status

Standby:

SQL> select message from v$dataguard_status;
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: Beginning to archive thread 1 sequence 1290 (7385563-7485630)
ARC0: Completed archiving thread 1 sequence 1290 (7385563-7485630)
Error 12541 received logging on to the standby
Check whether the listener is up and running.

MESSAGE
--------------------------------------------------------------------------------
Error 12541 for archive log file 1 to 'orcldg'
Error 12541 received logging on to the standby
Check whether the listener is up and running.
FAL[server, ARC3]: Error 12541 creating remote archivelog file 'orcldg'
ARC3: Beginning to archive thread 1 sequence 1291 (7485630-7486952)
ARC3: Completed archiving thread 1 sequence 1291 (7485630-7486952)
TT00: Standby redo logfile selected for thread 1 sequence 1291 for destination L
OG_ARCHIVE_DEST_2

LNS: Beginning to archive log 1 thread 1 sequence 1291
LNS: Completed archiving log 1 thread 1 sequence 1291

MESSAGE
--------------------------------------------------------------------------------
TT00: Standby redo logfile selected for thread 1 sequence 1292 for destination L
OG_ARCHIVE_DEST_2

LNS: Beginning to archive log 2 thread 1 sequence 1292
ARC0: Standby redo logfile selected for thread 1 sequence 1068 for destination L
OG_ARCHIVE_DEST_2

ARC2: Archive log rejected (T-1.S-1096) at host 'orcldg'
ARC3: Archive log rejected (T-1.S-1097) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1128) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1160) at host 'orcldg'

MESSAGE
--------------------------------------------------------------------------------
ARC2: Archive log rejected (T-1.S-1161) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1190) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1191) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1221) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1222) at host 'orcldg'
ARC2: Archive log rejected (T-1.S-1246) at host 'orcldg'
ARC3: Beginning to archive thread 1 sequence 1292 (7486952-7491549)
LNS: Completed archiving log 2 thread 1 sequence 1292
TT00: Standby redo logfile selected for thread 1 sequence 1293 for destination L
OG_ARCHIVE_DEST_2


MESSAGE
--------------------------------------------------------------------------------
LNS: Beginning to archive log 3 thread 1 sequence 1293
ARC3: Completed archiving thread 1 sequence 1292 (7486952-7491549)
ARC0: Beginning to archive thread 1 sequence 1293 (7491549-7495011)
LNS: Completed archiving log 3 thread 1 sequence 1293
TT00: Standby redo logfile selected for thread 1 sequence 1294 for destination L
OG_ARCHIVE_DEST_2

LNS: Beginning to archive log 1 thread 1 sequence 1294
ARC0: Completed archiving thread 1 sequence 1293 (7491549-7495011)
ARC1: Beginning to archive thread 1 sequence 1294 (7495011-7498733)
LNS: Completed archiving log 1 thread 1 sequence 1294

MESSAGE
--------------------------------------------------------------------------------
TT00: Standby redo logfile selected for thread 1 sequence 1295 for destination L
OG_ARCHIVE_DEST_2

LNS: Beginning to archive log 2 thread 1 sequence 1295
ARC1: Completed archiving thread 1 sequence 1294 (7495011-7498733)

If applied archivelog sequence in standby is close to primary then both are in sync

Primary:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1295
Next log sequence to archive   1297
Current log sequence           1297

Standby:

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            1295          1
ARCH      CLOSING            1294          1
ARCH      CONNECTED             0          0
ARCH      CLOSING            1292          1
RFS       IDLE               1296          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       1296          1

Both are in sync !!!

“ORA-01653 error Tablespace full “

Tablespace is a logical entity in oracle database which stores the objects and metadatas.

If tablespace is full ,then the upcoming files have no room to store the data.

[oracle@orcl ~]$ oerr ora 01653
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

Solutions:

Here USERS tablespace has very low space.

1)add a datafile to the tablespace or resize the tablespace

check the space of the tablespace for which you get error and add a datafile to the tablespace!

SELECT TABLESPACE_NAME,SUM(BYTES)/1073741824 "FREE_SPACE(GB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                FREE SPACE(GB)
------------------------------ --------------
SYSAUX                             .052734375
UNDOTBS1                           .011657715
USERS                              .003234863    <------
SYSTEM                             .332702637
EXAMPLE                            .024108887

Check the datafile location on the disk

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
+DATA/ORCL/DATAFILE/users.258.1031501899
USERS


you can add a datafile if the extent size reached max for tablespace of 32G
If not, then resize the tablespace to high value if autoextend is off
SQL> alter database datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' RESIZE 500M;

Database altered.
SQL> alter tablespace USERS add datafile '+DATA' size 50M;

Tablespace altered.

Clearing space in filesystem

Times it is very essential to follow housekeeping of the filesystem in a Linux server

There are old junk and trace files that needs to erased intermittently to keep the server and the databases healthy

we can display the filesystem details to check if the space of filesystem is full

~] df -h

If ‘/’ root filesystem is full then you are into risk of crashing the server

In that case,you have to make wise decisions to either extend the filesystem space or delete the most unwanted files on the server

The kernel sends a signal called OOM (out of memory) to kill the server processes randomly and upon the demand of the applications running on the system.If there is no room for the other processes to accomodate

we can check the errors in /var/log/messages

To calculate the size of each files

~] du -sh filename

~] du -sh *

~] du -csh *

To find the files with a search pattern and delete the trace and logfiles

$ find /go/to/dir/ -type d -name "dirname" -exec rm -rf {} +

To remove a file manually

$ rm -rf filename

Hence clean up the filesystem regularly to avoid nightmares !!

DBLINK to copy SQL data

Be aware of what happens around you

Database link is an efficient way to copy data between two databases.It reduces time to copy small data faster. The link works using the tnsnames service of the source database.

Copy down the sql statement from prod to dev

SQL>select * from prod.tab1 where emp_id in ('101','102');

we check the service name of source database

SQL>show parameter service;

service_name PROD

Login to dev and create a database link

SQL>create database link prod connect to test identified by password using 'PROD';

you can check using below query

SQL>select * from dba_database_links;

check the db link connectivity using dual with the dblink name

SQL>select * from dual@prod;

use CTAS to copy the table from prod to dev

SQL>create table dev.tab1 as (select * from prod.tab1 where emp_id in ('101','102'));

check the rows in dev.tab1 table

SQL> select * from dev.tab1

How to establish ssh equivalency between two servers

Thoughts are powerful

To establish a passwordless ssh between servers,first generate a public/private RSA encryption key using the command:

step 1: The key of the host is updated on files ‘id_rsa’ and id_rsa.pub with key value pairs

oracle@orcl ~]$ssh-keygen -t rsa

Enter the passphrase:

step 2: Copy the symmetric keys to the authorized_keys file

oracle@orcl ~]$ cat id_rsa.pub | ssh hostname ‘cat >> /home/oracle/.ssh/authorized_keys

step 3: Try to ssh from one node to other and vice versa

oracle@orcl ~]$ ssh hostname1 && ssh hostname2