set lines 300
col RESOURCE_NAME format a30
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE
from v$resource_limit
where resource_name in ('processes','sessions');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------- ---------------
processes 1498 1500 1500 1500
sessions 1511 1517 2272 2272
Documented for Oracle 19c here
Monday, June 26, 2023
How to show current utilization of sessions, processes and other important settings for a database
Example:
Monday, June 19, 2023
Using the autoupgrade tool to migrate a non-multitenant database to a PDB on the same host
The autoupgrade tool can convert a non-cdb database to a PDB running in a container database.
If you need to switch to a multitenant architechture, this could be a way forward.
In my case, I had a non-cdb database of versjon 19c running on my server testserver1.oric.no, and I want to convert it to a PDB.
This is what I did to move away from the non-cdb architecture:
1. Download the latest version of autoupgrade from Oracle Support: AutoUpgrade Tool (Doc ID 2485457.1)
On your database server, make a backup of the existing $ORACLE_HOME/rdbms/admin/autoupgrade.jar file, then transfer the new version of the file you just downloaded from Oracle into the same folder.
2. create a container database.
You can execute the database configuration assistant silently to quickly set one up. Put this in a shell script and call it for example "cre_cdb.sh":
Put this in a file called "cre_sample_file.sh":
5. Analyze the database before converting.
Put the following in a script called "analyze_db.sh":
6. Finally, convert the database to a pdb.
Put the following into a file called "deploy.sh":
There are numerous ways to migrate from the non-container architecture to multitenant architecture, this is just one of them.
Things to notice:
* My non-cdb database was called "mydb" - notice the lower case of the name. During the process, this was ignored and the database was renamed to MYDB when it was recreated as a PDB. There seem to be no way to change this behaviour.
Credit to Tim Hall for the usual excellent work in his blog post about the same topic, but also involving upgrading to a higher version of the Oracle software.
The official Oracle 19c documenation about AutoUpgrade Configuration File for Non-CDB Upgrades on the Same System is essential reading
Lots of good stuff on the Mike Dietrich blog
If you need to switch to a multitenant architechture, this could be a way forward.
In my case, I had a non-cdb database of versjon 19c running on my server testserver1.oric.no, and I want to convert it to a PDB.
This is what I did to move away from the non-cdb architecture:
1. Download the latest version of autoupgrade from Oracle Support: AutoUpgrade Tool (Doc ID 2485457.1)
On your database server, make a backup of the existing $ORACLE_HOME/rdbms/admin/autoupgrade.jar file, then transfer the new version of the file you just downloaded from Oracle into the same folder.
2. create a container database.
You can execute the database configuration assistant silently to quickly set one up. Put this in a shell script and call it for example "cre_cdb.sh":
export ORACLE_SID=cdb dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname cdb -sid cdb -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword secret \ -systemPassword secret \ -createAsContainerDatabase true \ -numberOfPDBs 0 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/data01/oradata/" \ -redoLogFileSize 50 \ -emConfiguration NONE \ -ignorePreReqsExecute it:
chmod 755 cre_db.sh ./cre_db.sh3. Set some parameters in the cdb:
alter system set db_recovery_file_dest_size=10G; alter system set db_recovery_file_dest='/fra'; alter system set db_create_file_dest = '/data01/oradata'; shutdown immediate startup mount alter database archivelog; alter database open; exit4. When the container database is ready, create a configuration file.
Put this in a file called "cre_sample_file.sh":
$ORACLE_BASE/product/19c/jdk/bin/java \ -jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \ -create_sample_file config /sw/oracle/admin/mydb/sql/config.txt noncdbtopdbExecute it:
chmod 755 cre_sample_file.sh ./cre_sample_file.shThe output should be a file that you now open in an editor and edit. This is how mine looked:
upg1.log_dir=/sw/oracle/cfgtoollogs/autoupgrade/mydb upg1.sid=mydb upg1.source_home=/sw/oracle/product/19c upg1.target_cdb=cdb upg1.target_home=/sw/oracle/product/19c upg1.target_pdb_name=mydb upg1.start_time=NOW # Optional. 10 Minutes from now upg1.upgrade_node=testserver1.oric.no # Optional. To find out the name of your node, run the hostname utility. Default is ''localhost'' upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade upg1.target_version=19 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2There are many additional options that could potentially be relevant, but in my case the ones listed above was all that was needed.
5. Analyze the database before converting.
Put the following in a script called "analyze_db.sh":
$ORACLE_BASE/product/19c/jdk/bin/java \ -jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \ -config /sw/oracle/admin/mydb/sql/config.txt -mode analyzeExecute it:
chmod 755 analyze_db.sh ./analyze_db.shIn my case, this job returned very quickly, since there is no upgrade job do be done, we are simply converting from an non-cdb to a PDB!
6. Finally, convert the database to a pdb.
Put the following into a file called "deploy.sh":
$ORACLE_BASE/product/19c/jdk/bin/java \ -jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \ -config /sw/oracle/admin/mydb/sql/config.txt -mode deployExecute it:
chmod 755 deploy.sh ./deploy.shThe deploy phase goes through several steps and leaves you at the prompt. You can watch the progress by executing "lsj" at the prompt:
AutoUpgrade 23.1.230224 launched with default internal options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 Non-CDB(s) will be processed Type 'help' to list console commands upg> lsj +----+-------+-----------+---------+-------+----------+-------+-------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+-----------+---------+-------+----------+-------+-------------------+ | 101| mydb|NONCDBTOPDB|EXECUTING|RUNNING| 15:19:20| 1s ago|Compatibility check| +----+-------+-----------+---------+-------+----------+-------+-------------------+ Total jobs 1 upg> lsj +----+-------+-----------+---------+-------+----------+-------+------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+-----------+---------+-------+----------+-------+------------------+ | 101| mydb|NONCDBTOPDB|EXECUTING|RUNNING| 15:19:20| 3s ago|Executing describe| +----+-------+-----------+---------+-------+----------+-------+------------------+ Total jobs 1 . . . upg> lsj +----+-------+---------+---------+-------+----------+-------+-------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE| +----+-------+---------+---------+-------+----------+-------+-------+ | 101| mydb|DBUPGRADE|EXECUTING|RUNNING| 15:19:20| 8s ago|Running| +----+-------+---------+---------+-------+----------+-------+-------+ Total jobs 1 upg> Job 101 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs restored [0] Jobs pending [0] Please check the summary report at: /sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.html /sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.logLooking at the log file /sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.log I see that many of the stages were very quick to return:
==========================================
Autoupgrade Summary Report
==========================================
[Date] Mon Jun 19 15:28:58 CEST 2023
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name] mydb
[Version Before Upgrade] 19.19.0.0.0
[Version After Upgrade] 19.19.0.0.0
------------------------------------------
[Stage Name] PREUPGRADE
[Status] SUCCESS
[Start Time] 2023-06-19 15:19:20
[Duration] 0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/preupgrade
------------------------------------------
[Stage Name] DRAIN
[Status] SUCCESS
[Start Time] 2023-06-19 15:19:21
[Duration] 0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/drain
------------------------------------------
[Stage Name] NONCDBTOPDB
[Status] SUCCESS
[Start Time] 2023-06-19 15:19:21
[Duration] 0:08:51
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb
------------------------------------------
[Stage Name] DBUPGRADE
[Status] SUCCESS
[Start Time] 2023-06-19 15:28:12
[Duration] 0:00:42
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/dbupgrade
------------------------------------------
[Stage Name] POSTUPGRADE
[Status] SUCCESS
[Start Time] 2023-06-19 15:28:57
[Duration] 0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/postupgrade
------------------------------------------
[Stage Name] SYSUPDATES
[Status] SUCCESS
[Start Time] 2023-06-19 15:28:58
[Duration]
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/sysupdates
------------------------------------------
Summary: /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/dbupgrade/datapatch_summary.log
If I move into the /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb folder, I can see
ls -altrh total 480K -rw------- 1 oracle dba 6.9K Jun 19 15:20 mydb-MYDB.xml -rw------- 1 oracle dba 1.7K Jun 19 15:20 createpdb_mydb_MYDB.log -rw------- 1 oracle dba 323K Jun 19 15:28 noncdbtopdb_mydb_MYDB.log -rw------- 1 oracle dba 127K Jun 19 15:28 noncdb_to_pdb_mydb.log drwx------ 2 oracle dba 4.0K Jun 19 15:28 . drwx------ 8 oracle dba 4.0K Jun 19 15:28 ..This stage executes the command
create pluggable database "MYDB" using '/sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb/mydb-MYDB.xml' NOCOPY tempfile reuseto perform the actual plug-in operation of the old non-cdb database, so that it becomes a PDB. This was the only stage that took a bit of time to complete, almost 9 minutes.
There are numerous ways to migrate from the non-container architecture to multitenant architecture, this is just one of them.
Things to notice:
* My non-cdb database was called "mydb" - notice the lower case of the name. During the process, this was ignored and the database was renamed to MYDB when it was recreated as a PDB. There seem to be no way to change this behaviour.
Credit to Tim Hall for the usual excellent work in his blog post about the same topic, but also involving upgrading to a higher version of the Oracle software.
The official Oracle 19c documenation about AutoUpgrade Configuration File for Non-CDB Upgrades on the Same System is essential reading
Lots of good stuff on the Mike Dietrich blog
Thursday, June 15, 2023
How to list the content of a folder and get total size summary of the parent folder at the end
To list the size of all folders and get a summery at the end, use the --max-depth=1 switch with the du command.
In the listing below, I am following the examples of a recent blog post by Mike Dietrich, to measure the size of the subfolders in the .patch_storage folder, which is maintained by OPatch in case the DBA needs to rollback the patches later.
In the listing below, I am following the examples of a recent blog post by Mike Dietrich, to measure the size of the subfolders in the .patch_storage folder, which is maintained by OPatch in case the DBA needs to rollback the patches later.
cd $ORACLE_HOME/.patch_storage du -h --max-depth=1 100K ./29585399_Apr_9_2019_19_12_47 284K ./NApply 4.0K ./oracle-home-1681903491076105 434M ./34786990_Dec_6_2022_13_24_50 20K ./NRollback 436M ./35050341_Mar_17_2023_04_11_10 2.0G ./35042068_Apr_6_2023_15_25_04 91M ./backup_delete_inactive 2.9G .
Tuesday, June 6, 2023
How to use sqlcl with / as sysdba
Creds to the findings of Rodrigo Jorge:
Make sure the environment variable LD_LIBRARY_PATH is set, and if so, includes $ORACLE_HOME/lib:
Make sure the environment variable LD_LIBRARY_PATH is set, and if so, includes $ORACLE_HOME/lib:
oracle@testserver01.oric.no:[testdb01]# export LD_LIBRARY_PATH=$ORACLE_HOME/libThe following files are important for sqlcl to work:
cd $ORACLE_HOME/lib ls -la *ocijdbc19* -rw-r--r-- 1 oracle dba 166082 Apr 5 2019 libocijdbc19.a -rw-r--r-- 1 oracle dba 1812344 Apr 6 17:24 libocijdbc19_g.so -rw-r--r-- 1 oracle dba 153648 Apr 6 17:24 libocijdbc19.soConnect with sqlcl as sysdba, just the way you used to with sqlplus:
sql / as sysdba SQLcl: Release 21.4 Production on Tue Jun 06 08:46:27 2023 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0
Subscribe to:
Posts (Atom)