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

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 ]

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

autoupgrade error TARGET_CDB_COMPATIBILITY

If you are converting your non-cdb to to a pdb using autoupgrade, you might run into this error:

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;

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:

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 file

The 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:

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:

sqlplus / as sysdba
create spfile from pfile;

Set the environment variable export ORA_RMAN_SGA_TARGET to the same (or a little more) as the auxiliary database's total SGA:

export ORA_RMAN_SGA_TARGET=7900

Run the rman script again and it should proceed past the mount-stage and start restoring the files.

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:

[ 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 file

Potential 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.

Workaround for error ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid

When trying to update your database's LOCAL_LISTENER parameter like this:

alter system set local_listener=LISTENER_CDB scope=both

and you get the following error stack:

ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'LISTENER_CDB'

The solution is to first change your $TNS_ADMIN/tnsadmin.ora to correspond to the value you wish to set the local_listener parameter to. For example, change the following line:

LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.oric.no)(PORT = 1521))

to

LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.oric.no)(PORT = 1521))

For more information about the local_listener parameter, see this earlier post