Administration

Autoupgrade doesn’t Upgrade Timezone Version While RU Patching

Published on: by Mustafa Comment

Hi,

during some tests, I realized that autoupgrade tool (the magic tool for upgrade and patching) doesn’t upgrade timezone version of database. here is the environment:

Oracle Linux 8.6

ORACLE_HOME 1 :/u01/app/oracle/product/dbhome_1/dbhome_19_3
Database orcl created in 19.3 with default time zone version (V32)

ORACLE_HOME 2 :/u01/app/oracle/product/dbhome_1/dbhome_19_16
Time zone version is upgraded to V39.

this is autoupgrade config file:

as you see, “upg1.timezone_upg” is et to yes so it must be upgraded when “orcl” database is moved to 19.16 home but when autoupgrade completes its job. output of V$TIMEZONE_FILE shows that db time zone version is still v32. interesting part, there is no error in log files actually, on prechecks ( run autoupgrade in analyze mode first of course) it is not mentioning about time zone upgrade. Autoupgrade does not plan to upgrade time zone file and it didn’t 🙂

So I started to dig a little bit. I created a new database via dbca (using custom database template of course) on 19.16 home and timezone file version was v39 as expected. So, no problem about home.

Secondly, I  thought there might be some bugs/problems on 19.3 (base release) home and patched it to 19.8 recreate orcl database and run autoupgrade again but timezone version was still v32.

Thirdly, I installed an 18c home, created “orcl” database and run autoupgrade to move it on 19.16. Viola, it worked, after autoupgrade new timezone file version was v39. in prechecks this time, autoupgarde says that time zone will be updated and it did.

What is different between starting from 18c and 19.3? 18c has v31 as default time zone file and 19c has v32. maybe autoupgrade checks default time zone files first compare them (maybe hardcoded) and decide to make a timezone file upgrade. I don’t know just a speculation.

Or maybe autoupgrade does not upgrade timezone file while patching and only in upgrades! I asked this to Mike Dietrich and he told me this should be working and create an SR. So, I created an SR and a bug has been reported. I hope it will be fixed quickly. Be careful if you patching out of place and always check you time zone files.

wish you healthy, happy days.

EDIT: It turned out my guess is correct. autoupgrade is checking default timezone versions but while upgrading, it checks latest version and upgrade it too. that is why it doesn’t upgrade while RU patching, since both homes are 19c and default is 32 for both.

and again many thanks to Mike Dietrich, for his close interest and fast response. AU team already discussed the situation and will be solved soon.

Unique Columns For V$Sql

Published on: by Mustafa Comment

Hi,

Until yesterday I was saying SQL_ID and CHILD_NUMBER is enough to get a sql child cursor from V$SQL and today I know I was wrong.

Probably many times you needed the sql statement that a session is running. To do that you query V$session and find the session information and then use SQL_ID column to reach out V$Sql. if you write a query like this:

you can write any filter you want here but you will immediately realized that some session rows are multiplied. Why? Because you didn’t use “CHILD_NUMBER” while reaching v$sql. V$SQL is not main data source for unique sql statements in your database. V$SQLAREA is. So instead of v$sql, you can use v$sqlarea in this query but if you need specific information about the exact sql cursor that session is currently running then you must use v$sql.

Note: I will not go deep Parent-Child cursors here. basically, every sql statements has child cursors. they all same sql but for some reason they have different properties and your session is running a “child cursor”.

so your query should be like this:

and now you reached to exact correct sql child cursor that is running for that session. right? well, I did know so but I find out, it is not! On one of my clients system, I discovered that this query returns 2 rows from v$sql which means for same SQL_ID and CHILD_NUMBER there are 2 rows in v$sql. I couldn’t find anything on docs or google (maybe couldn’t search enough) and started to dig about this sql statements.

here is the necessary information about this sql:

almost everything is same but ADDRESS and CHILD_ADDRESS. CHILD_ADDRESS is the memory address that this cursor resides on so it must be different obviously. Only thing that create this difference is ADDRESS column. ADDRESS column in V$sql is the address of the PARENT cursor which is in V$SQLAREA view. So let’s check this view:

there is only one of them exists. So some of those child cursors in v$sql are not (I am not sure the term) “valid”. they might be used before (and they were in my example) but now they are remaining of a past sql. So which child cursor in v$sql is the sql statements that session is currently running? Now we need to use ADDRESS column too. SQL_ADDRESS column in v$session is the parent address of the sql statement. So, I should add it into my queries too:

thanks for reading. comments are welcome. wish you healthy happy days.

Should Run datapatch After DBCA

Published on: by Mustafa Comment

Yesterday, I was testing some stuff in database and I needed to create new homes/new databases (new life new beginning). at a point I realized that something is not right about database. One thing to another I found the reason and it was interesting to me.

I used DBCA to create database ( also I used General Purpose template which we shouldn’t use in a production environment. this was just a test and it was a virtual machine). starting from, I don’t know 12.2 I guess, dbca runs datapatch after database creation. datapatch will apply necessary sql patches into your database like RU (Revision Update) or one off patches. I waited dbca to complete its job and it completed without any error. if dbca says “it’s ok” then you don’t look more right? How many times did you check all log files that dbca produced? Anyway, during my actual tests, as I said before, I realized something is not right so I wanted to check if all patches are applied correctly. I checked CDB_REGISTRY_SQLPATCH view (this is a noncdb database by the way so I can use dba version of view too):

at first it seems OK to me but then I realized there were two “RU APPLY” rows with a small time difference. As you see all patch operations are done to container 0, They both applied to same container(which this is a noncdb database). Since it was around 02:40 AM, I went to bed and in the morning I run datapatch (by the way date of virtual machine is not correct). here is the output of datapatch:

pay attention on “Current state of release update SQL Patches:” section. it says for SQL registery: “Applied 19.16.0.0.0 Release_Update 220703022223 with errors on 02-NOV-22 07.04.56.062757 PM”. there is an error on update of this apply. I don’t know what that was and it also says 1 patch installed. Maybe I did something wrong during the installation. I rechecked registry:

here is a third row for 19.16 RU apply and from that point everything worked smoothly. So, there is no harm to run datapatch after creating a new database via dbca. You must run datapatch if you create your database with CREATE DATABASE command anyway. We must be careful out there, anything can happen 🙂 there is no harm run datapatch and see:

“Installation queue:
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied”

wish you all healthy, beautiful days.

APPLY with status WITH ERRORS in the PDB

Published on: by Mustafa Comment

Yesterday, I was doing some tests about noncdb to pdb conversions and during one of these tests I got a strange error. By the way, I am doing those tests because of a side effect of noncdb to pdb. I have already created an SR about it and write a post here later.

here is the environment in my test case; I installed Linux 8.6 on Virtual Box, Oracle 19c (19.16 RU) and two databases on same home, orcl and cdb19. orcl is noncdb database and cdb19 is the container database. I created both databases via dbca at the same time and dbca completed successfully (at least I thought so). Both databases are up and running. So I started noncdb to pdb conversion via autouptrade tool (latest version 22.5). I also tried, db link and unplug/plug methods too.

So after starting autoupgrade it completed successfully but when I login into database and open new pdb, it raises some errors. in pdb_plug_in_violations, I saw “APPLY with status WITH ERRORS in the PDB” error. For some reason, datapatch didn’t run correctly after dbca operation for orcl (noncdb) database. I run datapatch for orcl database again, it did some patching and re-run autoupgrade. After that operation completed and pdb created successfully.

by the way, my main problem still exists. this migrated pdb has its own data dictionary (which shouldn’t be because data dictionary is in cdb$root). anyway if you ever encounter with this error you can try datapatch again.

wish you all healthy happy days.

How to Check Empty Space in a Securefile LOB

Published on: by Mustafa Comment

Hi,

PS: please check last sql statement in this post for shorter and faster solution. first sql is for showing all information about the lob.

Yesterday, while checking segment space usage data in a client’s database and some of the LOB segments were absurdly big. So, I want to check what is in it and how much free space I can gain.

To do that, I cannot use just DBMS_LOB.GetLength function because for CLOB data type, it takes much more space than original data because of charsets (especially if you use AL32UTF8 charset) so I just want to check currently empty space in lob segment. if you need that kind of data then, DBMS_SPACE is the solution. DBMS_SPACE has a SPACE_USAGE procedure which checks a segment and  return space information but starting 12c, SECUREFILE is the default lob type and for securefile you cannot use same space_usage procedure with the one you use for tables.

space_usage has overloaded versions and second and third overloaded version (order in dbms_space package from top to bottom) is for SECUREFILE lob segments.

here is a sample script I wrote to check currently empty space in securefile lobs:

Caution: I check all LOBS in the database so, you might not want to do that, just modify sql and add necessary filters.

first with function block is to call dbms_space.space_usage procedure and return data in string, on second with clause (get_info) call the function and get lob information, by the way SEGMENT_TYPE for a lob segment is “LOB SEGMENT” but dbms_space.space_usage does not accept it as segment_Type, you must send “LOB” instead, then parse result as columns and list SEGMENT_SIZE_MB which is total size of LOB segment size in megabytes
USED_SIZE_MB which is actual used size in megabytes
EXPIRED_SIZE_MB which is expired data size (for versioning) in megabytes.

to calculate empty space just subtract used size from segment size. this is approximately empty size in your lob segment. if you have a partitioned table then you will get result for each partition (like mustafa.tmp table in the example). I hope this script helps in your job.

EDIT:

I forgot to mentioned why lob segments have empty spaces. biggest reason is delete operation of course. if you delete a big portion of the table (or lob segment) then deleted space will be considered as empty space. So, if you delete on a table, should you shrink the lob segment or table? NO! that space will be used by the table again. if this is your standard operation then empty spaces in table/lob segment will be reused but let’s say you inserted many rows by a mistake or because of a bug in your code and then deleted those rows. if you have enough disk space for the database then there is nothing to worry about (mostly). That table will get many inserts in time and it will use that space eventually but if too much space is reserved in the table / lob segment and maybe you have a disk space issues then you can reclaim it (by shrinking or moving table/lob segment). So, this is not a regular thing.

EDIT 2:

sql above is aiming to show all information about lob like used space, total space, expired space etc. probably , you will just need to see empty space in lob. so you can use this sql statement. this will work faster because no regexp usage and parsing operations. I also subtracted “unexpired bytes” from total size since this space is still in use.

 

 

thanks for reading, your comments are always welcome. wish you healthy days.

catcon.pl cheat sheet

Published on: by Mustafa Comment

Hello,

Oracle uses a container database architecture permanently anymore, you should be familiar with “catcon.pl” perl file. Let’s say you have container database with 10 different PDBs and you need to run a script file on all of those pdbs (maybe even in root and seed too) so what you need is catcon.pl.

catcon.pl will automatically execute your script file and run it on all or specified containers. Since it is a perl file it must be called with perl binary (perl in linux, perl.exe in windows) and you can find it under $ORACLE_HOME/perl/bin directory and catcon.pl itself is under $ORACLE_HOME/rdbms/admin .

so, you have a sql file to run under the /home/oracle/scripts directory called myfile.sql. let’s see how we can execute it in all pdbs:

that is it. myfile.sql file will be run in all containers (including root and seed) with the command above. Let’s dissect the command:

$ORACLE_HOME/perl/bin/perl => PERL binary to run catcon.pl
$ORACLE_HOME/rdbms/admin/catcon.pl => catcon.pl path
-d /home/oracle/scripts => directory where script is located.
-b MyScript => Prefix for the log files so you can separate them from other scripts logs.
myfile.sql => actual script name

actual script file must be the last parameter. It is that easy. in this example ORACLE_HOME and ORACLE_SID should be set before you run the command otherwise you cannot connect to the database. This is a local connection and by default it is a SYSDBA connection. So whatever you run will be run under SYS user.

To add some more functionality:

  1. -c : list of containers that you want to execute the script:
    ….catcon.pl -c ‘SALES PDB1 TEST’ => execute the script only at SALES, PDB1 and TEST pdbs.
  2. -C : list of containers that you don’t want to execute the script.
  3. -l : log directory
  4. -s : spool the output of script files.
  5. -S : (capital S) don’t execute the script in root and seed. (Edit: this option also don’t set “_oracle_scripts” parameter as true, so if you are creating a user it won’t be Oracle Maintained user)
  6. -u : specify username/password.
  7. -z : ez-connect string
  8. -n : number of workers. if you want to execute a script for 100 pdbs then you can increase the number of workers to execute script simultaneously on different pdbs. default is 1

Can we use catcon.pl to run a script on a remote server? Yes with a combination of -u and -z

run myfile.sql script under /home/oracle/scripts directory on CDBX database and only specified pdbs (PDB1…PDB8) which is on mydbserver.mydomain server using mustafa user and create 2 worker process so script will be run on 2 pdbs at a time and write log files under /home/oracle/logs.

catcon has more parameters but I just write about the ones I used most.

thanks for reading.

Audits are not Purged

Published on: by Mustafa Comment

Hello,

This week I realized that some of my databases’ audit data are not purged and audit tables get bigger. I already have a scheduler job to purge my audit records but somehow job has been completed successfully but audit records are not deleted. This is 19.15 version, NON-CDB databases.

I am using unified audit in all of my databases and purge audit records based on last archive timestamp. here is a sample code to purge unified audit records:

This code could belong to Tim Hall (www.oracle-base.com)  not sure. This basically sets a date to delete audit records which are before that date. So, to understand the problem I started to digging but didn’t find much (before starting a trace, I found it out).

I checked what the saved last time stamp is:

that is interesting because this is NON-CDB database but it has 2 different database_id for same AUDIT_TRAIL type. One of them is actual id (2158381708) of current database but other one is unknown (at first). Also, for the rows with 2158381708 database id, last_archive_ts is correct but the other one was pretty old. So, some of my databases has this not purged audit problems and all of them has double rows in this data dictionary view. So, I thought there might be a bug and maybe code gets an too many rows error. Simply I deleted foreign database id rows. base table of DBA_AUDIT_MGMT_LAST_ARCH_TS is SYS.DAM_LAST_ARCH_TS$. I deleted rows:

then I re-run audit purge code and it worked!

Of course, I didn’t stop at here. Why there were different rows on this table? while checking database id’s I realized that all databases I had this problem are duplicated databases. I duplicated PROD to create a TEST or ACP for example and every time I duplicate a database I always change database id with “nid” tool.

I learned that after nid, those table remain to store old rows. I deleted old database id rows from all databases and problem solved. purge operation is a success. this is something new that I will add to my duplicate database steps. if you have duplicated database and didn’t check audit rows, you should.

thanks for reading. wish you all healthy, happy, peaceful days.

lsnrctl or new db connection is too slow

Published on: by Mustafa Comment

Hello everyone,

I know this is not new but I haven’t seen this problem for a long time and it takes a while to find out it.

A team leader (who is an elder brother to me) from the company I started to my first job called me and asked about a problem on their customer. Their customer complained about too slow db connection and no problem after the connection. first things first, I wanted to check database availability but because of some PATH problems (this was a Windows server) it took some time. after fixing environment variables, I finally reached out to database via local sysdba connection and database is up and running. also, connection was fast. by the way database was an 11.2 version which is quite old.

So, I started to focus on LISTENER. I first checked the status but “lsnrctl status”  command was quite slow. then I stopped and started again but these two operations was also slow. I checked the logfile under: $ORACLE_BASE/diag/tnslsnr/<SERVER_NAME>/<LISTENER_NAME>/trace but everything seemed ok! When I tried to logon via listener on the database server (using the net service name) it took around 70 seconds.

I started a listener trace (adding tracing parameters into sqlnet.ora file and restarting the listener) and tried to make a connection again. as soon as I issue my connection command (sqlplus username/pass@db) trace file is created. this means network connection was fast but while reading the trace file, after passing the connection information, listener was waiting for around 60-70 seconds.

I understand that network has no issue, connection arrives immediately but db connections and lsnrctl commands were slow. as I said I already checked, listener trace directory because I know that if listener.log file under trace directory has a big size, this could cause a slow operations on listener but it was around few megabytes. I checked few more things and finally I realized that I never checked “alert” directory under diag ($ORACLE_BASE/diag/tnslsnr/<SERVER_NAME>/<LISTENER_NAME>/alert) and when I opened it, there were over 1900 log.xml file which is the xml version of listener.log file. I deleted them all and it worked like a charm. Listener was checking all xml files to decide to write log data in which xml file. that was the cause and we were able to solve the problem.

Always check your listener log files and keep their size minimum as much as possible.

Wish you all healthy days.

How to Convert LONG to Clob PART 2

Published on: by Mustafa Comment

Hi,

I just want to write a small post today. Some time ago, I shared a way to convert long to clob in a query directly: https://mustafakalayci.me/2020/04/06/how-to-convert-a-long-column-to-clob-in-a-query/ Even if this method works, it works really slow and anything slow annoys me!

So, I want to share another method which is much much faster than Sys_dburigen function but THIS IS UNDOCUMENTED and UNSUPPORTED! It is used by internally by oracle so it works fine but you must be careful. Since, no one uses LONG column in their application (right? you don’t! please don’t!) this kind of code is handy for mostly admins because for backward compatibility data dictionary has some LONG data type columns.

What we are going to use is a LIBRARY called UTL_XML_LIB . I almost never create objects under SYS user, so I will create this code under a privileged admin user. first we must grant privileges on UTL_XML_LIB library and then create an EXTERNAL procedure in plsql.

now create external procedure:

“kuxLong2Clob” is a function which returns void (basically it is a procedure) written in C by Oracle. this procedure takes a select statement for your LONG column and rowid. procedure returns CLOB data as an out parameter. I created my_long2clob procedure but to be able to call it via SQL statement, it must be a function, so:

to test it, I won’t create a new table with a LONG column but instead I will use one of in the data dictionary (which I use these kind of codes against). Let’s use SYS.TRIGGER$ table which is the base table for DBA_TRIGGERS view. ACTION# column is a LONG data type. of course we must first grant select privilege on SYS.TRIGGER$ to mustafa user.

I suppressed the output but here it is. really fast long to clob conversion in SQL.

as a CON, it will only work against tables not VIEWs! you cannot use it as “f_my_long2cloc(rowid, ‘DBA_TRIGGERS’, ‘TRIGGER_BODY’)”. Since it requires ROWID, you must be referring underlying table and its rowid.

beyond that, if you want to get LONG as VARCHAR2, similarly you can use “kpdbLong2Varchar2” procedure in DBMS_PDB_LIB library. as UTL_XML_LIB it is very fast. only difference is, it returns VARCHAR2 and trims the overflow part. Where is it in use? Check how DBA_VIEWS shows TEXT_VC column data 😉

I hope this helps to fight against LONG columns in your codes.

Wish you all healthy, happy days.

Zero Data Loss DISASTER Database for Standard Edition 2

Published on: by Mustafa Comment

Hi,

As you know Data Guard is an Enterprise Edition option and only accepted method to protect your data against a “DISASTER” (DR) is data guard. How about Standard Edition 2(SE2)? Is this possible? Well I did it so, king of yes 🙂 with some risk of course.

Warining: I don’t take any responsibility for wrong settings. Wrong actions can damage your database. Please be careful.

if you google Oracle SE2 Data guard or Manual Log Shipping, you will find many blog post and documents about it. Even youtube has videos about this but almost all of them (at least all I’ve seen and read) are not “Zero Data Loss”. Steps are quite easy:

  1. you have a production database based on SE2
  2. you use another server as your DR server. (it could be on the same server if you use duplicate snapshot method)
  3. copy all your backup
  4. restore database and keep it in MOUNT mode
  5. copy your archivelog ever X minutes to DR server
  6. recover database using rman or sqlplus.

And that is it. This is a valid method. Only GAP is in this, you are transferring your archive logs but not redo logs. This means, you will certainly loose some data in case of a disaster because changes in redo logs will be lost (if you cannot reach out the server). So, what can we do? basic answer is by copying redo logs too but how? Redo logs are changing constantly. Even if you copy redo logs with in a 1 minute interval, you are still be able to lost 1 minute data.

To achieve this, you must force database to write your both redo logs and archive logs onto DR server. Not copying files in an interval, force database to write it there. Probably, you already understood what I did. to use this kind of method you should (must) have a strong, reliable, stable, fast network connection between your production and disaster servers. Because, we will send redo logs and archivelogs instantly to DR server.

I am using 19.15 databases on Oracle Linux 8 (virtual box). Same things can be done in other operating systems too. Logic will be same.

Here are the steps that I am about to do:

  1. On the DR server, create a folder to share over network via NFS.
  2. On the PROD server, Mount this shared folder on PROD server.
  3. On the PROD server, Add an archive log destination to this shared folder.
  4. On the PROD server, Add redo log members for each group to this shared folder.
  5. On the DR server, create a copy of prod database via rman (not duplicate)
  6. On the DR server, catalog archive logs, which resides on shared folder and recover database.
  7. In a disaster situation, copy redo logs to their original location with original name and recover database.

that’s it. Let me demonstrate those steps.

I created 2 virtual machines. They have both Oracle DB Software and one of them (PROD) has a database and I want to use other virtual machine as DR.

  1. Oracle home is under /u01/app/oracle/product/19c/dbhome_1
  2. I (always) use OMF (Oracle Managed Files).
  3. data files are under /u01/app/oracle/oradata
  4. recovery area is under /u01/app/oracle/fast_recovery_area
  5. SID of database is cdb
  6. PROD server ip is 192.168.56.101
  7. DR server ip is       192.168.56.104

1- On the DR server, create a folder to share over network via NFS

As the oracle user (Oracle db software owner user), create necessary directories.

this is the folder that I will share over the network and mount it (map it) on the production server.

to share over network, as root user:

2- On the PROD server, Mount this shared folder on PROD server

as oracle user, create a directory to mount shared directory on DR. /u01/app/oracle/fast_recovery_area/DR_LOGS will be used for mount point. directories in it is for OMF.

as root user:

at this point if I put a file into /u01/app/oracle/fast_recovery_area/DR_LOGS directory on PROD server, it will be written to /u01/PROD_LOGS on DR server

3- On the PROD server, Add an archive log destination to the shared folder

By default for OMF, if there is no value for log_archive_dest_n parameters, then Oracle internally use log_archive_dest_1 parameter as LOCATION=USE_DB_RECOVERY_FILE_DEST. that way, archivelogs are written under /u01/app/oracle/fast_recovery_area directory with sub directory structure of course (/u01/app/oracle/fast_recovery_area(<SID>/archivelog/YYYY_MM_DD).

but! if you assign a value to any of those parameters, then it will just use that location to create archivelogs. so I will set 2 log_archive_dest_n parameter, one location for PROD server and one for shared folder.

by setting these 2 parameters (log_archive_dest 1 and 2) we make sure that oracle will create 2 copies at the same time to 2 different location and one of them is actually out DR server. As you see there is one more parameter which is very important. log_archive_min_succeed_dest, by default is 1 and if you don’t set this as 2, in an access problem situation (network might have issues or DR server could be shutdown), Oracle will just write the first location and pass the inaccessible location. this will cause not writing necessary archive logs to DR server. if we want “zero data loss” DR db then oracle must write archive log to dr server. (also you can use MANDATORY clause).

4- On the PROD server, Add redo log members for each group to the shared folder

as the archive logs, we need to do the same things for redo log too.

as log_archive_dest_n parameter, if we set any of db_create_online_log_dest_n parameter, we need to set all three of them. 2 locations are on PROD server and 1 location is on DR server. this is not a mandatory step actually but it is a good practice. Because, we can just add redo log members (last 3 command above) to the shared location and this is enough but if one day you need to create another log group and forget to add a log member to shared folder then, you could be exposed to data loss! so set it any way.

then, add 1 member for each log group to shared folder. I use standard naming here because I need to know the groups of files by their name (we will use this later).

lastly, I create pfile to move to DR server, so we will create our DR database.

5- On the DR server, create a copy of prod database via rman

on PROD server, database “cdb” is up and running. Let’s start:

I don’t want to go all the details about standard copy backups and restore a database so, I won’t put outputs here.

On PROD server, take a backup of database via RMAN and then copy everything to DR server via scp.

from shell:

copy all backups, archivelogs etc and also pfile.

then go to DR server and create necessary directories & restore your database:

these directories will be needed for database. restore database:

at this point we have a copy prod database on DR in MOUNT mode.

somet very important thing to do

DO THIS ON DR SERVER ONLY! we are deleting any copied redo logs because during recovery mode, there must not be any redo log!

6- On the DR server, catalog archive logs, which resides on shared folder and recover database

from now on, you can create a recover.sql script and call it ever n minute from cron to recover database regularly. content of recover.sql is:

we must catalog newly arrived archivelogs first and then recover database. I want to remind that /u01/PROD_LOGS directory is the one that we shared over network and map it on to prod server.

every recovery command will end up with an ERROR:

this is an expected behavior. since we don’t apply redo logs, Oracle is looking for the next archive log file in the sequence but couldn’t find it. our recovery is now working. after archive log with sequence 18 is arrived, recover command will raise the same error above for sequence 19.

7- In a disaster situation, copy redo logs to their original location with original name and recover database

from now on, our archive and redo log files are stored on DR server too. if there is a disaster situation, you must open the database with applying all logs including redo logs. this is the only moment that we will apply redo logs.

these are the redo log locations on PROD:

/u01/app/oracle/oradata/CDB/onlinelog
/u01/app/oracle/fast_recovery_area/CDB/onlinelog
/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog (this one is the network directory on DR server)

actual directory that redo files are written is /u01/PROD_LOGS on DR server. Those directories must be existed on DR server too (we already created them in scripts above). We must copy redo logs under /u01/PROD_LOGS to actual locations now. I deliberately named redo logs as redoN.log so that I can identify which redo file is belong to which redo group. a very simple sql script and shell script will copy redo logs to original locations:

calling put_redo_logs.sh file will run an sql script which will produce copy shell commands.

After that, you can run recover.sql one more time and this time RMAN will not raise any error and you will see “Recovery Completed” message. now you can open your database and use it.

Now, why did I delete copied redo log files via scp command on DR server? if you recover a redo log in mount mode, control file will raise some flags and all recover commands will start from the sequence of applied redo log, this will cause problems after some time because archivelogs will be deleted eventually because of retention policy. This also means, if you apply a redo log during recovery, to have a healthy copy of database, you must restore both control file & database all over again.

Pros

obviously, you have a DR db with full consistent data.

Cons

This approach contains high risk because if database cannot write archived or redo log to shared folder then production database will hang! You might have some issues about network or DR server might be restarted or shutdown. You might loose disk drives on DR server which shared folder resides on etc etc…

I will write another post about those situations and make some tests. if loosing some data for a small time then, still refreshable pluggable database would be a better option. I will also write a post about it.

if you don’t have a reliable network, this option could be catastrophic! So, try it carefully. I hope this helps in some way.

Wish you all healthy, happy days.