Wednesday, May 10, 2023

Can the recyclebin be turned off for a container database but remain on in a pluggable database?

Yes, the multitenant architecture allows this.
SQL>select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                ISPDB
------------------------------ -------------------- -----
recyclebin                     OFF                  TRUE
Switch container to a PDB:
SQL>alter session set container=veg7;

Session altered.

SQL>show con_name

CON_NAME
------------------------------
VEG7

SQL>select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                ISPDB
------------------------------ -------------------- -----
recyclebin                     ON                   TRUE
Remember that the pluggable database must be closed and reopened if you toggle the recyclebin on/off:
SQL>alter session set container=veg7;

Session altered.

SQL>alter system set recyclebin=on container=current scope=spfile;

System altered.

SQL>alter pluggable database close;

Pluggable database altered.

cdb>SQL>alter pluggable database open;

Pluggable database altered.
If you want to toggle the recyclebin on/off for the container database, the procedure is the same except that the entire instance will have to be bounced.

Monday, May 1, 2023

How to use RMAN DUPLICATE FROM ACTIVE DATABASE to clone an entire CDB and all PDBs to a remote host



In this post I will show how to clone an entire cdb with all PDBs using the DUPLICATE FROM ACTIVE DATABASE command, which has been around for many years. 

You can still reuse your existing scripts for these operations, with some minor tweaks, even after you have moved to the multitenant architechture.





My target server is called prodserver
My auxiliary server is called testserver

On prodserver, the container database is called "cdb" and for the time being, there is only one pluggable database running there, called "pdbprod", as shown below:


orasoft@prodserver:[pdbprod] sqlplus / as sysdba
SYS@CDB$ROOT SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdbprod                        READ WRITE NO
On my auxiliary server testserver, I also have a container database called "cdb", and a PDB called "pdbtest":
orasoft@testserver:[pdbtest] sqlplus / as sysdba
SYS@CDB$ROOT SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdbtest                        READ WRITE NO
First, I recommend to configure a wallet, to avoid exposing your passwords in scripts or in on the Linux shell prompt.

Add a global user that exists in both container database and make sure it has SYSDBA privileges.
In this example, I add a user called c##cloneadmin and use the alias "cdbprod" and "cdbtest" for both:
mkstore -wrl $TNS_ADMIN/wallet -createCredential cdbprod c##cloneadmin
mkstore -wrl $TNS_ADMIN/wallet -createCredential cdbaux c##cloneadmin
Add the connect descriptions to your tnsnames.ora file, on both source and target.
Make sure the passwordless connections work before you attempt cloning. Test like this:
rman 
connect target /@cdbprod
connect auxiliary /@cdbaux
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
}
exit
Do not proceed until you connect successfully to both.

Create a minimal initcdb.ora file, containing only these two parameters:
*.db_name='cdb'
*.enable_pluggable_database=TRUE
Startup your auxiliary container database in nomount mode using the initcdb.ora file above:
sqlplus / as sysdba
startup nomount pfile=initcdb.ora
Create an RMAN script for your duplication, in my example I put it in a file called run_duplication.cmd:
connect target /@cdbprod
connect auxiliary /@cdbaux
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
debug io;
DUPLICATE DATABASE TO cdb
FROM ACTIVE DATABASE
USING BACKUPSET
SPFILE
 set db_file_name_convert='pdbprod','pdbtest'
 set audit_file_dest='/oraadmin/oracle/admin/cdb/adump'
 set core_dump_dest='/oraadmin/oracle/diag/rdbms/cdb/cdb/cdump'
 set control_files='/dbfiles01/oradata/CDB/control01.ctl','/dbfiles02/fra/CDB/control02.ctl'
 set sga_target='33621540864'
 set sga_max_size='33621540864'
 set pga_aggregate_target='12773752832'
 set shared_pool_size='2751463424'
 set streams_pool_size='268435456'
 set service_names='cdb,pdbtest'
 set db_recovery_file_dest_size='3221225472000'
NOFILENAMECHECK 
;
debug off;
}
exit
Remember that the NOFILENAMECHECK is required when using the same data file names but on different hosts. Without it, RMAN throws the error below and aborts your script:
RMAN-05001: auxiliary file name /dbfiles02/oradata/CDB/undotbs01.dbf conflicts with a file used by the target database
Create a shell script that calls the RMAN script, for example run_duplication.sh:
rman cmdfile='run_duplication.cmd' debug trace='duplicate.trc' log='duplicate.log'
Make the script executable, and execute it. If the source database is very large, and you expect the duplication to take more than 2-3 hours, you may want execute it in the background:
chmod 755 run_duplication.sh
nohup ./run_duplication.sh &
When the duplication was finished, the cdb + pdbprod was opened on the remote server. The last lines of the logfile states:
RMAN-06400: database opened
RMAN-06162: sql statement: alter pluggable database all open
RMAN-03091: Finished Duplicate Db at 29-APR-23
Sources:
Duplicating a CDB from the Oracle 19c documentation.

Friday, April 21, 2023

How to get verbose output in a call to mailx

Use the -vvv flag to debug your call to mailx:
root # echo "Test message" | mailx -S smtp="smtp.oric.no" -vvv -s "$(hostname) is up" monitoring@oric.no

Monday, April 17, 2023

How to deinstall Oracle 19c database software

If you are deinstalling Oracle on a Linux RHEL 8, set the variable CV_ASSUME_DISTID=OL7 to avoid the error outlined in Doc ID 2738856.1:
export CV_ASSUME_DISTID=OL7
Deinstall the binaries:
$ORACLE_HOME/deinstall/deinstall

At the end of the deinstallation, a summary is presented:
####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/oracle/product/19c' from the central inventory on the local node.
Failed to delete directory '/u01/oracle/product/19c' on the local node due to error : Either user has no permission to delete or file is in use.
Review the permissions and manually delete '/u01/oracle/product/19c' on local node.
Successfully deleted directory '/home/oracle/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.

Thursday, April 13, 2023

Workaround for message "Make sure that xdpyinfo exist under PATH variable" when launching OUI

Error:
oracle@myserver :[cdb]# ./runInstaller -createGoldImage -destinationLocation /stage/oracle/images
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
Cause: missing package:
[root@myserver ~]# yum list xorg-x11-utils
Updating Subscription Management repositories.
EPEL RHEL8 x86_64                                                                                                                        97 kB/s | 2.3 kB     00:00
Puppet7 RHEL8 x86_64                                                                                                                     72 kB/s | 1.7 kB     00:00
3rdparty RHEL8                                                                                                                           83 kB/s | 2.0 kB     00:00
Available Packages
xorg-x11-utils.x86_64                                                    7.5-28.el8                                                     rhel-8-for-x86_64-appstream-rpms
Solution: install package:
[root@myserver ~]# yum install xorg-x11-utils
Updating Subscription Management repositories.
EPEL RHEL8 x86_64                                                                                                                        89 kB/s | 2.3 kB     00:00
Puppet7 RHEL8 x86_64                                                                                                                     73 kB/s | 1.7 kB     00:00
3rdparty RHEL8                                                                                                                           83 kB/s | 2.0 kB     00:00
Dependencies resolved.
========================================================================================================================================================================
 Package                               Architecture                  Version                              Repository                                               Size
========================================================================================================================================================================
Installing:
 xorg-x11-utils                        x86_64                        7.5-28.el8                           rhel-8-for-x86_64-appstream-rpms                        135 k
Installing dependencies:
 libXcomposite                         x86_64                        0.4.4-14.el8                         rhel-8-for-x86_64-appstream-rpms                         29 k
 libXxf86dga                           x86_64                        1.1.5-1.el8                          rhel-8-for-x86_64-appstream-rpms                         26 k
 libdmx                                x86_64                        1.1.4-3.el8                          rhel-8-for-x86_64-appstream-rpms                         22 k

Transaction Summary
========================================================================================================================================================================
Install  4 Packages

Total download size: 212 k
Installed size: 418 k
Is this ok [y/N]: y
Downloading Packages:
(1/4): libXcomposite-0.4.4-14.el8.x86_64.rpm                                                                                            493 kB/s |  29 kB     00:00
(2/4): libdmx-1.1.4-3.el8.x86_64.rpm                                                                                                    302 kB/s |  22 kB     00:00
(3/4): xorg-x11-utils-7.5-28.el8.x86_64.rpm                                                                                             1.7 MB/s | 135 kB     00:00
(4/4): libXxf86dga-1.1.5-1.el8.x86_64.rpm                                                                                               709 kB/s |  26 kB     00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                   2.1 MB/s | 212 kB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                1/1
  Installing       : libXxf86dga-1.1.5-1.el8.x86_64                                                                                                                 1/4
  Installing       : libXcomposite-0.4.4-14.el8.x86_64                                                                                                              2/4
  Installing       : libdmx-1.1.4-3.el8.x86_64                                                                                                                      3/4
  Installing       : xorg-x11-utils-7.5-28.el8.x86_64                                                                                                               4/4
  Running scriptlet: xorg-x11-utils-7.5-28.el8.x86_64                                                                                                               4/4
  Verifying        : xorg-x11-utils-7.5-28.el8.x86_64                                                                                                               1/4
  Verifying        : libdmx-1.1.4-3.el8.x86_64                                                                                                                      2/4
  Verifying        : libXcomposite-0.4.4-14.el8.x86_64                                                                                                              3/4
  Verifying        : libXxf86dga-1.1.5-1.el8.x86_64                                                                                                                 4/4
Installed products updated.

Installed:
  libXcomposite-0.4.4-14.el8.x86_64           libXxf86dga-1.1.5-1.el8.x86_64           libdmx-1.1.4-3.el8.x86_64           xorg-x11-utils-7.5-28.el8.x86_64

Complete!
Relaunch OUI:
oracle@myserver :[cdb]# ./runInstaller -createGoldImage -destinationLocation /stage/oracle/images

Friday, February 24, 2023

Getting ORA-01722 when running adstats.sql after an EBS database upgrade to 19c

An old error from 2015 surfaced today, when we were trying to upgrade a 12.1 EBS database to 19c.

The problem occured during the running of adstats.sql, which should be executed at the end of the installation procedure, right before the database is converted to a PDB in a multitenant architecture:
sqlplus / as sysdba @adstats.sql apps
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 23 16:18:26 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Koblet til:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
Tilkoblet.
--------------------------------------------------
--- adstats.sql started at 2023-02-23 16:18:26 ---
Checking for the DB version and collecting statistics ...
declare
*
Error on line 1:
ORA-01722: invalid number
ORA-06512: on line 9
The solution is the same as in "ORA-01722 when running ausy1120.sql during preparation of new target database for EBS":

Modify the adstats.sql script slightly and correct a typo. In an editor, change the statement
select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;
to
select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;
Then rerun the script.

Sunday, February 19, 2023

How to identified active files/executables when using opatch to deinstall software

During a opatch rollback operation, we saw the following error message in the opatch logfile:
[Feb 19, 2023 2:29:51 PM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                                    The details are:


                                    Following active files/executables/libs are used by ORACLE_HOME :/sw/oracle/product/19.18
                                    /sw/oracle/product/19.18/lib/libclntsh.so.19.1
Reason:
Some processes are still using the /sw/oracle/product/19.18/lib/libclntsh.so.19.1.
Use the fuser utility with verbose output to find the process:
fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac
                     oracle    595787 ....m ggsci

There were two open processes using the file libclntsh.so.19.1: 1. the Golden Gate Manager 2. a utility called prometheus_oracle_exporter

Solution:
log in as the Golden Gate software owner
ggsci --> info all --> list all processes
stop mgr !
One of two processes quit its handler on the file:
 fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac

For the prometheus agent, we simply kill the agent, and the output from fuser now reveals that no file handlers are open:
kill 48439
fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1