Our business website uses WordPress. We have a theme bought from the market place and Elementor for page designs. The whole setup costed us something below 200$ & we are happy with the way the site delivers all our needs (not forgetting dozens of plugins that we use for basic features)
Recently we noticed “Elementor” throwing few errors while the pages were getting loaded. Errors were like “Deprecated: _register_skins is deprecated since version“. A quick Google search landed us on this discussion & rolled back Elementor to a previous version as suggested by one of the users and expected no further issues.
We were wrong. We started getting serious issues related to Jetpack, a plugin that is provided by “Automatic”, WordPress’s own repository. Our pages started showing some weird messages while loading and we were completely denied WordPress Admin page access. Our hosting company renamed the plugin folder & we were able to log on and disable Jetpack plugin, which was not recommended by many.
Much of the Jetpack related errors were like get_name(), get_title(),get_description(),get_long_description(),get_features(),get_pricing_for_ui(),get_manage_url() should not be abstract in & Warning: Cannot modify header information…
Jetpack error(s)
Then I remembered about a PHP related warning I have noticed during the last admin panel visit. WordPress was recommending PHP upgrade as they were in the process of stop supporting PHP 5.x versions. So I approached our hosting & asked them to upgrade the available PHP options for us. Once after they installed the latest available versions, we switched to PHP 7.4 (7.3 is deprecated already) from 5.6 . Proceeded with enabling Jetpack and upgraded Elementor to latest.
Everything started working as expected. So if you started facing Elementor or Jetpack related errors all of a sudden, before breaking your head anywhere else, check the version of PHP & give a try by upgrading it to one of the not deprecated versions.
Few weeks back one DBA told me there is no need to setup HugePages as we are not using 40+GB SGA & we had it enabled from the initial days of 11g migration from 10g (10.2.0.3). Well, here is how you can setup HugePages for your Oracle database instance.
A brief explanation that might help you to decide whether you should enable HugePages or not is quoted from Tim Hall’s article below.
“For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle 11g systems running on x86-64 architectures.“
Recently I had a short Twitter exchange with Tim Hall about the subject & He mentioned that he always enables it.
Please refer the article from below link, it is by Tim Hall, a geek in this field and the owner of https://oracle-base.com
Exercises explained in this article is applicable to 19c also (verified as working). Please make sure that your version of Oracle database supports these settings before attempting. Please do not copy this to PRODUCTION environments without enough testing.
We’ve our Oracle EBS instance hosted from Linux environment. However I post much for the Windows OS as I see there are not many articles targeting Oracle on Windows. I think for better view counts, I should consider posting the articles for both OS.
In my last post about restoring 1g RMAN online backup to 19c non CDB database, I said my ultimate goal was to convert the upgraded database from non CDB to PDB, that is plugged on to an existing CDB. Let us see how to achieve this goal this time.
Before start, there are few mandatory checks you must do to insure a successful migration from Non CDB to PDB. I will list few of them those came as hurdles for me.
Character Set. Make sure that your CDB and the Non CDB database have the same character set. There are few possible conversions. Please check 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1) for more details. My CDB was using Arabic character set and the upgraded non CDB database was using a noncompatible set. So I dropped the CDB and created a new CDB without PDB(s). Dropping a CDB will not be always possible because other PDBs are attached to it.
Available storage space. This could be a real concern incase if you are planning to create a PDB, cross verify & then delete the non CDB database. You will need approximately the same size of your non CDB datafiles size storage available to do this activity. My database was less than 5 GB in size, hence it never occurred to me. If your database has a bigger size, plan accordingly.
Let’s do it now.
From and elevated command prompt (Windows Terminal is not yet matured enough to handle such “heavy” tasks) source the non CDB. Shutdown and startup mount exclusive.
C:\Windows\system32>set ORACLE_SID=KAZEMA
C:\Windows\system32>sqlplus / as sysdba
SQL> shu immediate
SQL> startup mount exclusive
SQL> alter database open read only;
Create manifest file for the non CDB now.
SQL> exec dbms_pdb.describe (pdb_descr_file=>'D:\Oracle19c\kazema_manifest_file.xml');
SQL> shu immediate
Source the CDB now.
C:\Windows\system32>set ORACLE_SID=KAZEMACDB
C:\Windows\system32>sqlplus / as sysdba
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
Now, we need to see whether non CDB meets the compatibility requirements. There could be multiple elements those you may have to fix before, the non CDB is ready for a successful migration as PDB. You will have to refer PDB_PLUG_IN_VIOLATIONS view for these details. Go ahead, execute the below code block.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
Compatible CONSTANT VARCHAR2(3) :=CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY
(pdb_descr_file => 'D:\Oracle19c\kazema_manifest_file.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
YES
My case I didn’t have incompatibilities blocking possible migration. Your case could be different. If the output is “NO”, run the following query (use SQL Developer for better view)
select * from PDB_PLUG_IN_VIOLATIONS where name='<your non CDB name>';
Fix each and every other ERROR & you can safely ignore the “WARNINGS” for now.
Proceed with creating a new PDB from the manifest file that we have created in the 1st step.
SQL> CREATE PLUGGABLE DATABASE KAZEMA USING 'D:\Oracle19c\kazema_manifest_file.xml' COPY FILE_NAME_CONVERT=('D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE','D:\ORACLE19C\DB\ORADATA\KAZEMA');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 KAZEMA MOUNTED
Once the PDB has been created, confirm the same & proceed with balance activities. Run “noncdb_to_pdb.sql”
SQL> alter session set container=KAZEMA;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Based on the resource available, this might take a while for the script to complete execution. Once completed you can try to open the PDB. Check for Errors and Warnings in the PDB_PLUG_IN_VIOLATIONS view. If there are new ERRORS & WARNINGS fix them.
A typical scenario could list you many things like this (reference image only)
After fix attempts, I am still left with one Warning about sys lock issues. This is mainly due to the password file format 12.2. I hope to find a solution soon for it. Status column states whether the Error/Warning was taken care off.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
KAZEMA
READ WRITE
1 row selected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 KAZEMA READ WRITE NO
SQL>
Now you have a non CDB and PDB with the same SID. You can proceed with dropping the non CDB database after cross verifying the concerns.
Foot Note: If you are plugging a database that was hosted in another server/computer after converting, make sure to verify the “local_listener” parameter for the PDB exist.
D:\Oracle19c>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 10 11:18:33 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_KAZEMACDB
SQL> alter session set container="KAZEMA";
Session altered.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_KAZEMACDB
SQL>
If the local listener is not reachable, PDB level services will fail to register with listener and the applications those depend on such services will fail to establish connections. This applies mostly to Oracle E-Business Suite environments and could be mapped to sophisticated applications that use specific service names to establish connections to the database.
Is it possible to restore previous version RMAN database backup to a later version? Depending upon the supported upgrade paths, the lowest version I managed to restore to 19c (19.14) was 11g R2 (11.2.0.4)
Upgrade Path for Oracle database 19c
Please note, my final goal is to convert this NON CDB database to PDB and attach to an existing CDB.
What you need?
Intact RMAN online backup
Enough memory and storage space available on your computer.
Time and patience
Oracle support access, for many of the documents mentioned later in the article.
Scenario (fictitious): I’m given a full RMAN backup without any other details. I don’t know the source server details other than that it was windows & the version of the database release was 11.2.0.4.
Step#1
Using DBCA created a new non CDB database with the target database name, eg “KAZEMA”. After shutting down database, stopped listener keeping Windows Service for Oracle database running. Physically deleted all data files including control, redo logs.
(You can create an empty instance using ORADIM. That means ORADIM will create Windows services necessary for Oracle. You will have to create the necessary folders. Not just that, you need create a parameter file (initDATABASENAME.ora) to start the instance. Refer the below example)
D:\Oracle19c>oradim -new -sid KAZEMA -syspwd Oracle123 -startmode auto
Enter password for Oracle service user:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
Instance created.
Considering the fact that my target database name is “KAZEMA”, I have modified a copy of parameter file that I had. One of the most important factor you should pay attention here is to create all the physical paths mentioned in the parameter file. Example D:\Oracle19c\db\admin\KAZEMA\adump, D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE. Failing to find these physical paths might cause errors or your database will not start at all.
D:\Oracle19c\db\admin\KAZEMA has few more folders, like shown in the image. Please create them manually before starting the database.
Add *.max_string_size=’STANDARD’ to the parameter file, necessary for the upgrade. You will have to change this parameter once again after upgrade completes. We will discuss about it later in the article.
SQL> startup nomount (pfile=D:\Oracle19c\database\initKAZEMA.ora # if instance was created using ORADIM)
ORACLE instance started.
Total System Global Area 2147482424 bytes
Fixed Size 9030456 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7745536 bytes
From another elevated command prompt started RMAN & restored control file from backup that I had in “D:\BAHRMAN” folder.
D:\BAHRMAN>set ORACLE_SID=KAZEMA
D:\BAHRMAN>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:52:14 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: KAZEMA (DBID=964058362, not open)
RMAN>restore controlfile from 'D:\BAHRMAN\CTL_C-964058362-20210927-02';
RMAN>alter database mount;
RMAN>report schema; #This will populate the data file details
Shutdown the database now. Restart the database in “nomount” mode using parameter file.
Once database shutdown and restarted, exit RMAN and connect again. We’ll duplicate database using the RMAN backup now using “NOOPEN” switch that is supported from versions 12.
D:\BAHRMAN>rman auxiliary /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:56:42 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: KAZEMA (not mounted)
run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "KAZEMA" NOOPEN backup location 'D:\BAHRMAN' nofilenamecheck
db_file_name_convert=('D:\ORACLE\ORADATA\KAZEMA\','D:\Oracle19c\db\oradata\KAZEMA\DATAFILE\')
LOGFILE
GROUP 1 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01b.log'
) SIZE 1200M ,
GROUP 2 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02b.log'
) SIZE 1200M ,
GROUP 3 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03b.log'
) SIZE 1200M ,
GROUP 4 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04b.log'
) SIZE 1200M ;
# 19c & later, no need to exclusively release channels. If you restoring to other versions, make sure to release the channels.
# https://www.thegeekdiary.com/oracle-database-19c-rman-06012-channel-d1-not-allocated/
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
allocated channel: c1
channel c1: SID=498 device type=DISK
allocated channel: c2
channel c2: SID=619 device type=DISK
allocated channel: c3
channel c3: SID=742 device type=DISK
allocated channel: c4
channel c4: SID=862 device type=DISK
Starting Duplicate Db at 23-MAR-22
searching for database ID
found backup of database ID 964058362
contents of Memory Script:
.....Removed........
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\LC01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\BAHAR01.DBF
Leaving database unopened, as requested
Finished Duplicate Db at 23-MAR-22
#You will see the below errors for 19c and later while trying to release the channels exclusively. Remove the release channel instructions from the RMAN scripts to avoid seeing these messages.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of release command at 03/23/2022 10:07:32
RMAN-06012: channel: c1 not allocated
RMAN>
Ignore errors by the end of RMAN process, if they are related to releasing channels. If there are errors of other nature, you must resolve them. Now we will open the database resetting logs and ready to upgrade.
D:\Oracle19c>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 23 10:09:51 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> alter database open resetlogs upgrade;
Database altered.
As we have restored a RMAN backup, it is obvious that the source database had archive logs enabled. Let us disable archive logging from our new database. Shutdown the database and open it in mount mode.
SQL> startup mount pfile=D:\Oracle19c\database\initKAZEMA.ora
ORACLE instance started.
Total System Global Area 2147482424 bytes
Fixed Size 9030456 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7745536 bytes
SQL> alter database noarchivelog;
This is a long process for many reasons. First of all, no prerequisites were executed and recommended actions were performed against the source 11G database for 19c upgrade. This will cause upgrade process to fix prerequisites during upgrade & brace for few errors. Without MAX_STRING_SIZE set as ‘STANDARD’ for 11g, upgrade will fail. I have started the database instance using parameter file ONLY for this reason. I’m not sure whether RMAN restore crated SPFILE copies this value. So, I didn’t take chances.
Argument list for [D:\Oracle19c\rdbms\admin\catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = D:\Oracle19c\
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.14.0.0.0DBRU_WINDOWS.X64_211224.3]
D:\Oracle19c\rdbms\admin\orahome.exe = [D:\Oracle19c]
D:\Oracle19c\bin\orabasehome.exe = [D:\Oracle19c]
catctlGetOraBaseLogDir = [D:\Oracle19c]
Analyzing file D:\Oracle19c\rdbms\admin\catupgrd.sql
Log file directory = [D:\Oracle19c]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [D:\Oracle19c\catupgrd_catcon_8056.lst]
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd_*.lst] files for spool files, if any
Number of Cpus = 8
Database Name = KAZEMA
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [KAZEMA]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_03_23 10:18:27]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [KAZEMA] Files:1 Time: 273s
*************** Catalog Core SQL ***************
Serial Phase #:1 [KAZEMA] Files:5 Time: 31s
Restart Phase #:2 [KAZEMA] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [KAZEMA] Files:19 Time: 11s
Restart Phase #:4 [KAZEMA] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [KAZEMA] Files:7 Time: 14s
***************** Catproc Start ****************
Serial Phase #:6 [KAZEMA] Files:1 Time: 11s
***************** Catproc Types ****************
Serial Phase #:7 [KAZEMA] Files:2 Time: 6s
Restart Phase #:8 [KAZEMA] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [KAZEMA] Files:70 Time: 11s
Restart Phase #:10 [KAZEMA] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [KAZEMA] Files:1 Time: 42s
Restart Phase #:12 [KAZEMA] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [KAZEMA] Files:95 Time: 4s
Restart Phase #:14 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:15 [KAZEMA] Files:122 Time: 6s
Restart Phase #:16 [KAZEMA] Files:1 Time: 0s
Serial Phase #:17 [KAZEMA] Files:25 Time: 2s
Restart Phase #:18 [KAZEMA] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [KAZEMA] Files:32 Time: 8s
Restart Phase #:20 [KAZEMA] Files:1 Time: 0s
Serial Phase #:21 [KAZEMA] Files:3 Time: 8s
Restart Phase #:22 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:23 [KAZEMA] Files:25 Time: 157s
Restart Phase #:24 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:25 [KAZEMA] Files:12 Time: 96s
Restart Phase #:26 [KAZEMA] Files:1 Time: 0s
Serial Phase #:27 [KAZEMA] Files:1 Time: 0s
Serial Phase #:28 [KAZEMA] Files:4 Time: 2s
Serial Phase #:29 [KAZEMA] Files:1 Time: 0s
Restart Phase #:30 [KAZEMA] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [KAZEMA] Files:1 Time: 0s
Restart Phase #:32 [KAZEMA] Files:1 Time: 0s
Serial Phase #:34 [KAZEMA] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [KAZEMA] Files:297 Time: 19s
Serial Phase #:36 [KAZEMA] Files:1 Time: 0s
Restart Phase #:37 [KAZEMA] Files:1 Time: 1s
Serial Phase #:38 [KAZEMA] Files:10 Time: 3s
Restart Phase #:39 [KAZEMA] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [KAZEMA] Files:3 Time: 40s
Restart Phase #:41 [KAZEMA] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [KAZEMA] Files:13 Time: 91s
Restart Phase #:43 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:44 [KAZEMA] Files:11 Time: 7s
Restart Phase #:45 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:46 [KAZEMA] Files:3 Time: 1s
Restart Phase #:47 [KAZEMA] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [KAZEMA] Files:1 Time: 9s
Restart Phase #:49 [KAZEMA] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [KAZEMA] Files:1 Time: 15s
************ Upgrade Component Start ***********
Serial Phase #:51 [KAZEMA] Files:1 Time: 0s
Restart Phase #:52 [KAZEMA] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [KAZEMA] Files:2 Time: 363s
***************** Upgrading XDB ****************
Restart Phase #:54 [KAZEMA] Files:1 Time: 0s
Serial Phase #:56 [KAZEMA] Files:3 Time: 22s
Serial Phase #:57 [KAZEMA] Files:3 Time: 1s
Parallel Phase #:58 [KAZEMA] Files:10 Time: 2s
Parallel Phase #:59 [KAZEMA] Files:25 Time: 3s
Serial Phase #:60 [KAZEMA] Files:4 Time: 4s
Serial Phase #:61 [KAZEMA] Files:1 Time: 0s
Serial Phase #:62 [KAZEMA] Files:32 Time: 2s
Serial Phase #:63 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:64 [KAZEMA] Files:6 Time: 5s
Serial Phase #:65 [KAZEMA] Files:2 Time: 14s
Serial Phase #:66 [KAZEMA] Files:3 Time: 59s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [KAZEMA] Files:1 Time: 0s
Serial Phase #:69 [KAZEMA] Files:1 Time: 1s
Parallel Phase #:70 [KAZEMA] Files:2 Time: 25s
Restart Phase #:71 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:72 [KAZEMA] Files:2 Time: 1s
Serial Phase #:73 [KAZEMA] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [KAZEMA] Files:1 Time: 0s
Serial Phase #:76 [KAZEMA] Files:1 Time: 29s
Serial Phase #:77 [KAZEMA] Files:2 Time: 1s
Restart Phase #:78 [KAZEMA] Files:1 Time: 1s
Serial Phase #:79 [KAZEMA] Files:1 Time: 14s
Restart Phase #:80 [KAZEMA] Files:1 Time: 0s
Parallel Phase #:81 [KAZEMA] Files:3 Time: 28s
Restart Phase #:82 [KAZEMA] Files:1 Time: 0s
Serial Phase #:83 [KAZEMA] Files:1 Time: 4s
Restart Phase #:84 [KAZEMA] Files:1 Time: 0s
Serial Phase #:85 [KAZEMA] Files:1 Time: 8s
Restart Phase #:86 [KAZEMA] Files:1 Time: 1s
Parallel Phase #:87 [KAZEMA] Files:4 Time: 56s
Restart Phase #:88 [KAZEMA] Files:1 Time: 0s
Serial Phase #:89 [KAZEMA] Files:1 Time: 1s
Restart Phase #:90 [KAZEMA] Files:1 Time: 0s
Serial Phase #:91 [KAZEMA] Files:2 Time: 7s
Restart Phase #:92 [KAZEMA] Files:1 Time: 0s
Serial Phase #:93 [KAZEMA] Files:1 Time: 0s
Restart Phase #:94 [KAZEMA] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [KAZEMA] Files:1 Time: 18s
Restart Phase #:96 [KAZEMA] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [KAZEMA] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [KAZEMA] Files:1 Time: 61s
******************* Migration ******************
Serial Phase #:99 [KAZEMA] Files:1 Time: 30s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [KAZEMA] Files:1 Time: 1s
Serial Phase #:101 [KAZEMA] Files:1 Time: 0s
Serial Phase #:102 [KAZEMA] Files:1 Time: 80s
***************** Post Upgrade *****************
Serial Phase #:103 [KAZEMA] Files:1 Time: 49s
**************** Summary report ****************
Serial Phase #:104 [KAZEMA] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [KAZEMA] Files:1 Time: 1s
Serial Phase #:106 [KAZEMA] Files:1 Time: 0s
Serial Phase #:107 [KAZEMA] Files:1 Time: 41s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_23 10:48:42]
------------------------------------------------------
Grand Total Time: 1847s
*** WARNING: ERRORS FOUND DURING UPGRADE ***
1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Rerun the upgrade when the problem is resolved
REASON:
ERRORS FOUND: During Upgrade
FILENAME: D:\Oracle19c\catupgrd0.log AT LINE NUMBER: 1124126
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-13509: error encountered during updates to a AWR table ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
(actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [BEGIN
/* Perform the required AWR catalog operations for this release */
dbms_swrf_internal.refresh_catalog;
END;]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
(actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = ["SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30) (actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [(actual: , maximum: ) ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524 ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR = [ORA-06512: at line 3]
STATEMENT = [as above]
------------------------------------------------------
LOG FILES: (D:\Oracle19c\catupgrd*.log)
Upgrade Summary Report Located in:
D:\Oracle19c\upg_summary.log
Grand Total Upgrade Time: [0d:0h:30m:47s]
I didn’t remove lines from the output to give you an overview of what happened during the initial “catupgrd.sql” call without MAX_STRING_SIZE set as “STANDARD“. As you can see, it has generated few errors & we need to fix them.
ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column “SYS”.”OBJ$”.”SUBNAME” (actual: 31, maximum: 30) is pretty easy to fix. This is mainly due to strings lengths & follow the below steps to fix it.
Move to %ORACLE_HOME%\rdbms\admin folder.
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;
Once the script runs successfully, shutdown the database once again and start in upgrade mode. Rerun the “catupgrd.sql” as instructed above. Let the upgrade finish. Insure there are no errors. A successfully finished upgrade process will look like below. I mentioned earlier, this process could be pretty time taking based on the size of the database you are upgrading. My entire database was less than 5GB in size & I’ve NVMe+SSD based work laptop running i7 10th generation processor & 32GB memory. The waiting was frustrating.
Serial Phase #:101 [KAZEMA] Files:1 Time: 0s
Serial Phase #:102 [KAZEMA] Files:1 Time: 78s
***************** Post Upgrade *****************
Serial Phase #:103 [KAZEMA] Files:1 Time: 23s
**************** Summary report ****************
Serial Phase #:104 [KAZEMA] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [KAZEMA] Files:1 Time: 1s
Serial Phase #:106 [KAZEMA] Files:1 Time: 0s
Serial Phase #:107 [KAZEMA] Files:1 Time: 36s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_23 15:15:34]
------------------------------------------------------
Grand Total Time: 1209s
LOG FILES: (D:\Oracle19c\catupgrd0\catupgrd*.log)
Upgrade Summary Report Located in:
D:\Oracle19c\catupgrd0\upg_summary.log
Grand Total Upgrade Time: [0d:0h:20m:9s]
I came across the errors while I called the upgrade and went ahead and fixed them. Kept the same in the article so that the search engine could pick up the errors for someone else out there. Now it is time for to recompile all invalid objects after upgrade.
SQL>@utlrp.sql
Unless you have a huge number of PL/SQL libraries and other, this should complete in few minutes. A count for invalid objects will be provided to you & you can proceed to fix them once after a database restart. Keep a tight watch on the alert log file and address additional errors, if any.
Finally create SPFILE from current parameter file, shutdown and restart the database. You should be good to go now. Check for the Temporary files, Redo Log files and other vitals.
These invalid objects are from custom schema & could be fixed/dropped if not needed. Make sure there are no system level invalid objects.
Another area of interest could be OLAP.
Check and confirm OLAP objects are not invalid. OLAP catalog was deprecated after 11g. You may proceed removing it if needed.
One of the major annoyances with Oracle EBS CDB/PDB environment settings I found that, I cannot connect to the PDB (for EBS) from the CDB environment (default). I always have to source the PDB environment in order to connect the PDB and do my stuffs.
As usual, I am doing this for my LAB & you may not copy this to your PRODUCTION environment.
So this what I get when I try to connect to PDB from CDB environment.
oracle@erp-devp.xyz.com:/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin>tnsping DEVP
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 14:59:03
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora
TNS-03505: Failed to resolve name
The easiest solution was to add an additional entry for the PDB within the $TNS_ADMIN tnsnames.ora file. Let us see how to. Please note these might get overwritten when you run autoconfig.
Save the file & you are ready to go. Notice carefully the service name and instance name values for “DEVP” and “DEVPCDB”. The PDB will have own name for the service, however the instance name will point towards the CDB name & this is the way it should be.
oracle@erp-devp.xyz.com:/home/oracle>tnsping DEVP
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 15:06:20
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)(HOST=erp-devp.xyz.com)(PORT=1526)) (CONNECT_DATA = (SERVICE_NAME=DEVP)(INSTANCE_NAME=DEVPCDB)))
OK (0 msec)
oracle@erp-devp.xyz.com:/home/oracle>
That’s all. Regardless whether it is EBS or not, you can keep adding the PDB details into the tnsnames.ora file & if the service is registered with the listener, will able to connect without sourcing the specific environment variables.
Few “years” back, while it was almost impossible to install Oracle 10g on Windows 7/8, I made some attempts & managed to install 32Bit versions of 10g 10.2.0.3.x successfully. A particular release 10.2.0.4 had helluva of issues, mainly TNS related & I can see many visits to that thread even today! I believe, those visitors are not coming over there for 10g, instead for TNS.
On Windows, the below is a typical situation & we’ll see the primary reasons for the same.
Microsoft Windows [Version 10.0.22000.556]
(c) Microsoft Corporation. All rights reserved.
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 21 07:59:29 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
On Windows, everything is another service. So we’ll start with checking whether the “Oracle” service for the database instance has started.
I’m using Oracle database on my laptop mostly for learning purposes, hence the service startup is set as “Manual”. This gives me control over the resources consumed by the database. Not just the service, I have setup the instance to start manual to insure Oracle service doesn’t open the database automatically. Latest Windows OS that use quick boot will save the state of the service, hence you might find your database readily available once after a reboot, although you set everything as manual :)
Let’s see what happens after starting the “Oracle Service”.
Well, that fixes one of the many reasons for the dreaded ORA-12560: TNS:protocol adapter error! As soon I am hit with another, will amend this post.
I think I have mentioned few times already that I am not a certified (Certifitted as we call it sarcastically due to the pure ignorance of much of them) DBA, yet I love executing experiments with Oracle database technology and come up with some hacks, tricks etc. As usual, whatever you read ahead is NOT recommended for PRODUCTION environment and if you end up setting up something that works finally for your PRODUCTION instance, well you are sole responsible for it.
For this experiment, we will use one 19c database with multitenant structure installed on Windows 10/11. Before you attempt the below, please make sure that you have taken the backup of control file trace, spfile copy as pfile and a full backup of database using RMAN
Control file backup trace.
SQL>alter database backup controlfile to trace as 'D:\folder_name\control.txt';
SPfile to Pfile (This file should be available at $ORACLE_HOME\database\ folder)
SQL>create pfile from spfile;
Creating pfile is not mandatory incase if you are restoring the backup to same host and the paths are intact. Pfile becomes useful when you are restoring the database to another host with same directory structure.
Shutdown the database & delete the datafile from paths. For example, my datafiles were in the following paths.
There were three paths from which I needed to delete the data files. From CDB root, PDB seed and PDB itself. You should get a clear idea of the exact locations for your data files from the control file trace export.
Once after deleting the files, start the database at nomount state. The latest version of databases allow you to execute many SQL commands from RMAN itself, however, I always prefer SQL prompt for SQL activities. Suite yourself.
SQL>startup nomount
From another command/terminal window connect to RMAN
D:\RMAN>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 12 11:47:15 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: SCTCDB (not mounted)
Restore the control file from latest backup
RMAN> restore controlfile from 'D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312';
Starting restore at 12-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=498 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\ORACLE19C\DB\ORADATA\SCTCDB\CONTROL01.CTL
output file name=D:\ORACLE19C\DB\ORADATA\SCTCDB\CONTROL02.CTL
Finished restore at 12-MAR-22
Now try to mount the database.
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
Once the database mounted, let us see what the schema reprorts
RMAN> report schema;
Populate catalog from the backup.
RMAN> catalog start with 'D:\RMAN';
searching for all files that match the pattern D:\RMAN
List of Files Unknown to the Database
=====================================
File Name: D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312
Once the above completes successfully, we can proceed with restoring the backup.
RMAN> run
2> {allocate channel ch1 device type disk;
3> restore database;
4> switch datafile all;
5> switch tempfile all;
6> release channel ch1;
7> }
allocated channel: ch1
channel ch1: SID=252 device type=DISK
Starting restore at 12-MAR-22
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00004 to D:\ORACLE19C\DB\ORADATA\SCTCDB\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_040O6TG6_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_040O6TG6_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_020O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_020O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00005 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_050O6TG9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_050O6TG9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00009 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_030O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_030O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00011 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_080O6TH9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_080O6TH9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00010 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_060O6TH2_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_060O6TH2_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00007 to D:\ORACLE19C\DB\ORADATA\SCTCDB\USERS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_0B0O6THG_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_0B0O6THG_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00008 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_090O6THA_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_090O6THA_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00012 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\USERS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_0C0O6THH_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_0C0O6THH_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:02
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00006 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_070O6TH9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_070O6TH9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_010O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_010O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:35
Finished restore at 12-MAR-22
released channel: ch1
With the above restore, I used only one channel, please adjust based on available hardware resources. Once the backup completes, we can recover the database using the available archive logs. For that you need to know what is the latest available sequence number.
RMAN> list backup of archivelog all;
From the image above, you could see that the latest Sequence number for the SCN is 20, however the listing is kind of confusing (remember I am not a DBA, however eventually I will figure out why the listing is messed up). Once the latest sequence number identified, we can try to recover until last sequence + 1
RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> allocate channel ch3 device type disk;
5> allocate channel ch4 device type disk;
6> recover database until sequence 21;
7> release channel ch1;
8> release channel ch2;
9> release channel ch3;
10> release channel ch4;
11> }
allocated channel: ch1
channel ch1: SID=252 device type=DISK
allocated channel: ch2
channel ch2: SID=128 device type=DISK
allocated channel: ch3
channel ch3: SID=375 device type=DISK
allocated channel: ch4
channel ch4: SID=499 device type=DISK
Starting recover at 12-MAR-22
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file D:\ORA\ARCH\SCT0000000019_1096219042_0001.ARC
archived log for thread 1 with sequence 20 is already on disk as file D:\ORA\ARCH\SCT0000000020_1096219042_0001.ARC
archived log file name=D:\ORA\ARCH\SCT0000000019_1096219042_0001.ARC thread=1 sequence=19
archived log file name=D:\ORA\ARCH\SCT0000000020_1096219042_0001.ARC thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-MAR-22
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4
Finally, we can open the database resetting online redo log files
RMAN> alter database open resetlogs;
Database MUST open without creating any issues at this stage. Continue checking for the database vitals.
Let us see how we can create and give a group write permission for a folder on Linux environment. This becomes helpful when you want to keep downloaded files accessible to multiple users. Very helpful if you are dealing with Oracle EBS like applications that has different users for both database and application.
[root@erp-test /]# mkdir -p orapatches
--Change group ownership for the newly created folder.
[root@erp-test /]# chgrp -R oinstall /orapatches
--Finally change the mode so that all the members in this group could write to this folder.
[root@erp-test /]# chmod g+w /orapatches
We recently migrated from Oracle 11gR2 to 19c for our Oracle Applications (12.2.10) & decided to continue with old RMAN plans. Run a shell script, call RMAN as “Oracle” user, copy current day backups to a remote server & finalizing the activities with email sent.
Our previous database compressed RMAN backups were a maximum of 142GB per day, that grew to 190-192GB (few datafiles were added) after the upgrade. This made us to reduce the retention period from 7 to 5 and next day, while going through the RMAN logs, I found the following error.
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c4 channel at 02/10/2022 22:24:51
ORA-19571: archived log RECID 416 STAMP 1095631214 not found in control file
RMAN>
Recovery Manager complete.
So, I went through the previous days logs and found this error started being logged from 9th of Feb and rest were all okay.
I checked database “control_file_record_keep_time” parameter and found the value set as “7” which was more than our current backup retention period.
So the next factor to check was the RMAN script that we were using over a period of 6-7 years without a single failure.
export PATH=$ORACLE_HOME/bin:$PATH
export LAST_RMANLOG_FILE="rman_dailyfull`date +%d%m%y`.log"
echo "Starting Full Daily Database Backup of oracle ebsr12 PROD database"
date
rman target / << EOF >/u03/RMAN/log/rman_dailyfull`date +%d%m%y`.log
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
crosscheck archivelog all;
crosscheck backup;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u03/RMAN/DAILYBKP/rman_comp_%d_lvl0_%U' TAG "dailyfull_db_lvl0_bkp" INCLUDE CURRENT CONTROLFILE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '/u03/RMAN/DAILYBKP/archive_%d_lvl0_%U';
DELETE NOPROMPT archivelog all completed before'sysdate-7';
backup current controlfile format '/u03/RMAN/DAILYBKP/bkpcontrol_file.ctl_%d_%T';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 5 DAYS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
EOF
After a close reading, I found the typo. I was deleting the obsolete backups within a window of 5 days & trying to delete logs which were “7” days old.
DELETE NOPROMPT archivelog all completed before'sysdate-5';
That’s all. No more RMAN-03009, ORA-19571, or archived log RECID <NNN> STAMP <NNNNNNNNNN> not found in control file errors.
So how many Oracle VirtualBox VM (Virtual Machine)s you do have?
I’ve many. I try many new software & not to break production box, I try them using Virtual Machines. I use Oracle VirtualBox (mostly) for both Windows and Linux machines (and VMWare ESXi for Production servers), as you could see with the above image. VirtualBox and VMWare supply additional software called “Guest Additions” that synchronize the Guest Virtual Machines with Host machine seamlessly. Like Mouse integration that allows your mouse to travel between both host and guest machines without any difficulties.
On Linux OS, installing the Guest Additions could be sometimes challenging. Especially with Oracle Linux that uses UEK kernels. Let us see how to “fix” the common error messages that appears when installing the Guest Additions on Oracle Linux 7 and 8.
The best approach is to update your Linux OS with latest patches before trying the below. I consider you already updated your Linux box, if yes then
yum install kernel-uek-devel
is enough to install the devel package, suffice the kernel headers missing error. Install the package, reboot the box and try to install VirtualBox Guest Additions. This time module should get installed properly. You may need make, gcc & perl already installed for a successful installation of Guest Additions. You will find instructions to install them later in the article.
Now we have another situation when guest add-ons are required, however updating the new Linux box is not possible due to many reasons. Here we will use the Linux OS ISO image. Please note, the ISO file that you are going to use must be the same ISO that you used to install OS and OS was not updated at all. That means your Linux box is as it is after the OS installation.
Attach the ISO file to your Linux box & Switch to
Packages folder for Oracle Linux 7 and “BaseOS\Packages” for Linux 8
Now locate the kernel-uek-devel-xxxxxxxxxxxxxxxxxxxx package and install it. For example, I will execute the following for Linux 8
That’s it. Restart the box and try to install the guest add-ons once again and you must be through without other issues. You might need to install gcc, make & perl also to get the guest add-ons installed.
If you are connected to internet, you can install the above packages by issuing the command
yum install gcc make perl -y
Or go through the packages available in the Packages repo, install them.
