The complete post-12c online procedure to move a file from file system to ASM (ORA-01110,ORA-01157,ORA-01187,ORA-12850,RMAN-03009)

In the Coronovirus-19 world, most of us would rather spend time learning about the Seattle Kraken, Max Ehrich, Roy Jones Jr and the Washington Football Team.  They'll probably eventually do what Prince did and just call themselves, "Symbol." Roy Jones and Max Ehrich will both get knocked out and the Blues will have one more team to beat before they get their next cup.  If you're reading this...you probably also care about Oracle databases...so let me tell you about a recent experience I had.

A client I work with had an issue the other day (actually 2 issues) in a container database.  This database has very strict uptime SLA's...so downtime for a bounce of an instance isn't an option.  The online process to move a datafile from file system to ASM is a 3 step process in 12c+...until now I've always thought it was a single "move datafile" command.

1. They hit a bug similar to the issue discussed in note 2558640.1, while doing a select from cdb_data_files, they got "ORA-12850: Could not allocate slaves on all specified instances: 3 needed, 0 allocated".  

2. This happened in a script that's used to add datafiles as needed.  Unfortunately, that script created a datafile with some of the error text instead of "+DATA"...so the file was created in the dbs directory on node 1 instead of putting it into ASM.  This is RAC, so the datafile was only able to be accessed on the first node.  RAC requires shared storage.

First order of business, get it working again.  This is 18.7, so it was easy to move the file into ASM without downtime.  The filename was ridiculous with special characters, and the file was on the file system on node 1, so from node 1 I did:

alter database move datafile 1654 to '+DATA' ;

...and that worked, now the datafile is in ASM's shared storage.  I kicked off a backup (which failed):

                RMAN-03009: failure of backup command on ch4 channel at 07/23/2020 11:23:24 
                ORA-01157: cannot identify/lock data file 1654 - see DBWR trace file
                ORA-01110: data file 1654: '+DATA/DB/DATAFILE/sysaux.22113.1246514439'

On node 1, this file is 100% working in ASM.  On other nodes, it was unavailable, giving ORA-01157's.  In gv$datafile I could see it was online and available on all nodes.  This made no sense to me.  I even double checked by going into asmcmd and doing an "ls" from one of the nodes that was failing...yep...it was there.  It was acting like it was offline, but reporting it was online.

I googled/checked metalink and didn't really find a good hit.  Finally I thought...there's no harm in onlining a file that says its online...so I did an:

ALTER DATABASE DATAFILE 1654 ONLINE;

The error changed from:
ORA-01157: cannot identify/lock data file 1654 
to:
ORA-01187 cannot read from file 1654 because it failed verification tests

It was still working on node 1, but getting ORA-01187 on the other nodes.  This time I found a solution:

ALTER SYSTEM CHECK DATAFILES;

...which I did on the nodes that were having issues.  After that, everything began to work.

With the solution and the original error, I went back on MOS and this time found the ultimate solution: (DOC 2499264.1) which states:

ORA-01157 error is expected to occur when running a query on dba_datafiles on a particular instance (of RAC database) for a file moved (just after creation) from Filesystem to ASM, without instance wide identification.
 
This problem was previously addressed in Bug 27268242 which was also closed as "Not a Bug" because this is an expected behavior.

(I think they meant cluster or database-wide identification.) 
<VENT> Come on, Oracle!  You shouldn't expect to get an error when running a correct query on a valid datafile.  This just causes more trouble and steps and after a datafile move, there's no reason to leave it in a partially working state.  If you're in RAC and somebody moves a file to ASM...let the other nodes know!  There's no conceivable situation where somebody would want to move a file in RAC and have it only available to a single instance...make the alter database datafile move check datafiles when its coming from the filesystem for us!  
<?VENT>

Until Larry starts listening to me...this means we have multiple steps.

That note also included a resolution:
alter system check datafiles;
alter system flush shared_pool; 

...so at this point, the datafile is working correctly on all nodes.

This means we have 3 statements to do when we move a file from the file system to ASM:
alter database move datafile 1654 to '+DATA';
alter system check datafiles;

alter system flush shared_pool; 

Now that its working, I can re-address problem #1 above...the root cause.

Note 2558640.1 discusses a similar problem when OEM is hitting the views that have the datafiles.  My issues isn't in OEM, but in every other way this matches up.  The work around:

alter system set "_px_cdb_view_enabled"=false comment='Work-around for internal view error and OEM-see bug 29595328';

This prevents the issue from happening again, but it also slows down the query (and others) hitting the cdb_data_files from 45 seconds (there's a lot of datafiles) to 90 seconds.  I suppose that's better than getting this error...I opened an SR to ask this be fixed in future releases.

I love the new feature in 12c+ to do online datafile moves...but it would be great if the process was documented when moving a datafile from the file system...all I've seen in the doc's is the basic move datafile command, not all 3 steps.

With the system working and in a state where this won't happen again...I can now return to C19's bad hair and toilet paper and coin shortages.

RHEL7 Control Groups on Oracle Database Servers

After some linux patching on some database servers I was surprised to see an entry in an alert log I hadn’t seen before…complete with a WARNING message.  After digging around I found it was due to Linux control groups.  In an effort to manage a server with multiple workloads, RHEL7 introduced control groups and they’ve increased features/abilities with each dot release.  On the affected  Oracle nodes in RHEL7 we see entries on startup in the alert log:

************************ Large Pages Information *******************

Parameter use_large_pages = ONLY

Per process system memlock (soft) limit = UNLIMITED

Large page usage restricted to processor group "user.slice"

Total Shared Global Region in Large Pages = 60 GB (100%)

WARNING:

  The parameter _linux_prepage_large_pages is explicitly disabled.

  Oracle strongly recommends setting the _linux_prepage_large_pages

  parameter since the instance  is running in a Processor Group. If there is

  insufficient large page memory, instance may encounter SIGBUS error

  and may terminate abnormally.

Large Pages used by this instance: 30721 (60 GB)

Large Pages unused in Processor Group user.slice = 1722 (3444 MB)

Large Pages configured in Processor Group user.slice = 120000 (234 GB)

Large Page size = 2048 KB

You may also see:

Large page usage restricted to processor group "system.slice/oracle-ohasd.service"

…this concerned me because if our large page usage is restricted and we can’t do a prepage, eventually the db_cache would warm up and allocate memory beyond the limitation, causing an instance crash.  Also, “WARNING” on startup is something to take a closer look at.  That undocumented parameter was most definitely NOT explicitly disabled.  This message appears in every database that’s being controlled by processor groups, so it appears prepage of large pages is disabled automatically.  There’s a note (2414778.1) that says this message can be ignored and its removed in 12.2+.

So…what amount of memory is our group being limited to by default?

>systemctl show user.slice|grep Mem

MemoryCurrent=18446744073709551615

MemoryAccounting=no

MemoryLimit=18446744073709551615

…which works out to be 17,179,869,184GB, (roughly 17 Exabytes) which is probably a sufficient amount of RAM (per node) for any database out there…so we’re basically being limited to slightly less than infinity….nothing to worry about.

Are control groups useful in a multi-database instance server environment?  To set up CPU limits per instance, you could use this or instance caging…I would prefer to set the cpu_count parameter and use instance caging.  Memory Limits?  Memory utilization for the SGA is set via sga_target/max_size…so I don’t think there’s a play for it here either.  Maybe the accounting features could be useful…if you wanted to see how much CPU one instance in a multiple instance db server was using historically?  There are awr tables that track that…but getting the information from CG might be easier….

One potentially useful way to use control groups for a database server with multiple instances is to throttle bandwidth on IO to prevent one instance from using up all the IO and affecting the performance of a different instance (assuming each instance is on its own asm disks or luns.)  There’s a note about how to get that to work on Metalink: 2495924.1.  This has been a useful feature in Exadata for years with IORM…now you can have similar functionality without Exadata.

One other interesting feature of control groups is the ability to look at the resources each group is using in real time (if you have accounting on) with the systemd-cgtop command.  If you don’t have accounting, you’ll see something like this, which is a summary of process counts, cpu, memory and IO for each group:

It took some time to find the details about what the new alert log entries were telling me with the new warning and processor group information.  Hopefully this information will save you some time.