java -jar autoupgrade.jar -create_sample_file config /tmp/autoconfig_example.txt noncdbtopdb
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Wednesday, October 30, 2024
How to use autoconfig to create a sample config file for non-cdb to pdb conversions
Tuesday, October 29, 2024
Lock and expire users
An anonymous PL/SQL block to lock and expire open accounts:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /oracle/admin/mydb01/scripts/lockAccount.log append BEGIN FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ( 'SYS','SYSTEM') ) LOOP IF item.AUTHENTICATION_TYPE='PASSWORD' THEN dbms_output.put_line('Locking and Expiring: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' password expire account lock' ; ELSE dbms_output.put_line('Locking: ' || item.USERNAME); execute immediate 'alter user ' || sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) || ' account lock' ; END IF; END LOOP; END; / spool off
Tuesday, October 15, 2024
Arguments that can be passed to dbca - usage output
This can certainly come in handy one day...
-createDatabase - Command to Create a database. -responseFile | (-gdbName,-templateName) -responseFile --gdbName -templateName [-useWalletForDBCredentials Specify true to load database credentials from wallet] -dbCredentialsWalletLocation [-dbCredentialsWalletPassword ] [-characterSet ] [-dvConfiguration Specify true to configure and enable database vault.] -dvUserName -dvUserPassword [-dvAccountManagerPassword ] [-dvAccountManagerName ] [-datafileDestination ] [-datafileJarLocation ] [-runCVUChecks ] [-sid ] [-redoLogFileSize ] [-registerWithDirService ] -dirServiceUserName [-databaseCN ] [-dirServiceCertificatePath ] [-dirServicePassword ] [-dirServiceUser ] [-ldapDirectoryAccessType ] [-useSYSAuthForLDAPAccess ] [-walletPassword ] [-systemPassword ] [-nodelist ] [-oracleHomeUserPassword ] [-sysPassword ] [-enableArchive Specify true to enable archive>] [-archiveLogMode ] [-archiveLogDest ] [-memoryMgmtType ] [-variables ] [-listeners ] [-olsConfiguration Specify true to configure and enable Oracle Label Security.] [-configureWithOID This flag configures Oracle Label Security with OID.] [-createAsContainerDatabase ] [-pdbName ] [-numberOfPDBs ] [-pdbStorageMAXSizeInMB ] [-pdbStorageMAXTempSizeInMB ] [-useLocalUndoForPDBs Specify false to disable local undo tablespace for PDBs.] [-pdbAdminPassword ] [-pdbOptions ] [-recoveryAreaDestination ] [-recoveryAreaSize ] [-createListener ] [-useOMF Specify true to use Oracle-Managed Files.] [-memoryPercentage | -totalMemory] [-memoryPercentage ] [-totalMemory ] [-dbOptions ] [-sampleSchema ] [-variablesFile ] [-customScripts ] [-databaseType ] [-oracleHomeUserName ] [-initParams ] [-initParamsEscapeChar ] [-policyManaged | -adminManaged] [-policyManaged ] -serverPoolName [-pqPoolName ] [-createServerPool ] [-pqPoolName ] [-forceServerPoolCreation ] [-pqCardinality ] [-cardinality ] [-adminManaged ] [-nationalCharacterSet ] [-storageType < FS | ASM >] -datafileDestination [-asmsnmpPassword ] [-databaseConfigType ] [-RACOneNodeServiceName ] [-emConfiguration ] [-dbsnmpPassword ] [-emPassword ] [-emUser ] [-emExpressPort ] [-omsHost ] [-omsPort ] [-emExpressPortAsGlobalPort ]
Monday, October 14, 2024
How to add a post-creation script to your dbca createDatabase statement
Given that you have saved your responsefile in the oracle user's home directory, use the -customScripts to point out a script that will be run *after* the cdb is ready. I have found this useful in situations where you want a non-default standard that Oracle simply denies you every time you create a new cdb.
dbca -createDatabase -responsefile/home/oracle/${ORACLE_SID}.rsp -customScripts /home/oracle/post_cdb_creation.sql -silent
Thursday, October 10, 2024
autoupgrade error TARGET_CDB_COMPATIBILITY
If you are converting your non-cdb to to a pdb using autoupgrade, you might run into this error:
The database you are converting to a pluggable database is installed with more options than the cdb you are trying to plug it into.
Solution:
Recreate your cdb so that it has the same options installed as the database you are trying to convert to a pdb.
Check the installed options in both databases like this:
2024-10-10 14:19:51.697 INFO build.version:24.6.240905 build.hash:0ca273885 build.date:2024/09/05 11:30:40 -0400 build.max_target_version:23 build.MOS_NOTE:2485457.1 build.supported_target_versions:12.2,18,19,21,23 build.type:production build.MOS_LINK:https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1 build.label:(HEAD, tag: v24.6, origin/stable_devel, stable_devel) build.hash_date:2024/09/05 11:23:06 -0400 2024-10-10 14:19:51.850 INFO No preupgrade user action defined 2024-10-10 14:20:13.184 INFO Analyzing mydb01, 62 checks will run using 8 threads 2024-10-10 14:20:39.765 INFO File /u01/oracle/cfgtoollogs/autoupgrade/mydb01/mydb01/104/prechecks/mydb01_preupgrade.html not found 2024-10-10 14:20:39.767 INFO File /u01/oracle/cfgtoollogs/autoupgrade/mydb01/mydb01/104/prechecks/mydb01_preupgrade.log not found 2024-10-10 14:20:39.768 INFO File /u01/oracle/cfgtoollogs/autoupgrade/mydb01/mydb01/104/dbupgrade/datapatch_summary.log not found 2024-10-10 14:20:39.805 ERROR The following checks have ERROR severity and no auto fixup is available or the fixup failed to resolve the issue. Fix them before continuing: mydb01 TARGET_CDB_COMPATIBILITY 2024-10-10 14:20:39.925 ERROR Dispatcher failed: The following checks have ERROR severity and no auto fixup is available or the fixup failed to resolve the issue. Fix them before continuing: mydb01 TARGET_CDB_COMPATIBILITY oracle.upgrade.autoupgrade.utils.errors.AutoUpgException: The following checks have ERROR severity and no auto fixup is available or the fixup failed to resolve the issue. Fix them before continuing: mydb01 TARGET_CDB_COMPATIBILITY at oracle.upgrade.autoupgrade.dbchecks.standard.checks.ChecksController.evaluateBlockerChecks(ChecksController.java:310) at oracle.upgrade.autoupgrade.dbchecks.standard.checks.ChecksController.executeChecks(ChecksController.java:166) at oracle.upgrade.autoupgrade.dispatcher.facade.subsystems.ExecuteChecks.executeStage(ExecuteChecks.java:95) at oracle.commons.dispatcher.JobStage.executeStage(JobStage.java:80) at oracle.commons.dispatcher.RunJobDefinition.runJob(RunJobDefinition.java:144) at oracle.upgrade.autoupgrade.dispatcher.v2.def.RunAutoUpgradeJob.executeDispatcher(RunAutoUpgradeJob.java:62) at oracle.upgrade.autoupgrade.dispatcher.AutoUpgDispatcher.run_(AutoUpgDispatcher.java:262) at oracle.upgrade.autoupgrade.dispatcher.AutoUpgDispatcher.run(AutoUpgDispatcher.java:245) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750)Cause:
The database you are converting to a pluggable database is installed with more options than the cdb you are trying to plug it into.
Solution:
Recreate your cdb so that it has the same options installed as the database you are trying to convert to a pdb.
Check the installed options in both databases like this:
set lines 200 pages 100 col comp_name format a40 col status format a20 SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;
Wednesday, October 9, 2024
Workaround for ORA-27069 attempt to do I/O beyond the range of the file during RMAN clone from active database
This is the message I received when starting an active database duplication using RMAN:
Usa a pfile to start the auxiliary instance, and *not* an spfile. If you do not have one, log onto the not-yet-mounted instance and create one:
Set the environment variable export ORA_RMAN_SGA_TARGET to the same (or a little more) as the auxiliary database's total SGA:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 10/09/2024 10:40:19 RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the fileThe rman script:
connect target sys/pwd@mydb1 connect auxiliary sys/pwd@mydb2 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 TARGET DATABASE TO mydb2 FROM ACTIVE DATABASE USING COMPRESSED BACKUPSET; debug off; }Solution:
sqlplus / as sysdba create spfile from pfile;
export ORA_RMAN_SGA_TARGET=7900Run the rman script again and it should proceed past the mount-stage and start restoring the files.
Thursday, October 3, 2024
Workaround for RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file when using dbca
Late night error when trying to create a cdb out of the same ORACLE_HOME as an older, non-cdb database:
dbca reports:
Potential solution: scale your total memory up. If necessary, adjust hugepages to fit the extra instance.
dbca reports:
[ 2024-10-03 18:34:35.876 CEST ] Prepare for db operation DBCA_PROGRESS : 10% [ 2024-10-03 18:34:35.956 CEST ] Copying database files DBCA_PROGRESS : 40% DBCA_PROGRESS : 100% [ 2024-10-03 18:35:04.332 CEST ] [FATAL] Recovery Manager failed to restore datafiles. Refer logs for details. DBCA_PROGRESS : 10% DBCA_PROGRESS : 0%Detailed log file shows:
RMAN-03015: error occurred in stored script Memory Script RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the filePotential cause: your memory is too small to hold the extra instance you are attempting to create.
Potential solution: scale your total memory up. If necessary, adjust hugepages to fit the extra instance.
Subscribe to:
Posts (Atom)