How to check why a PostgreSQL server won't start

journalctl -u postgresql-15.service

Example output:

Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service entered failed state.
Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service failed.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Starting PostgreSQL 15 database server...
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no postmaster[65528]: 
2024-12-27 06:22:01.703 GMT [65528] 
LOG:  syntax error in file "/path/to/file/postgresql.auto.conf" line
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service: main process exited, code=exited, status=1/FAILURE
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Failed to start PostgreSQL 15 database server.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service entered failed state.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service failed.

In this case, the error seems to be in an error in the file postgresql.auto.conf.

How to use rsync between two servers

For example, to syncronize two servers so they get identical file structures: 

• Source server: server1.oric.no 
• Destination server: server2.oric.no 
• Execute as: root

target=server2.oric.no && rsync -vaz --delete /u01/app/myfiles ebsuser@$target:/u01/app

This will sync the entire "/u01/app/myfiles" folder + all subfolders.

The --delete flag in rsync ensures that files in the destination directory (ebsuser@$target:/u01/app) that are not present in the source directory (/u01/app/inst) are deleted. This helps keep the destination directory synchronized with the source directory.

Error: ORA-16831: operation not allowed on this member

When trying to add a standby database to your configuration, I received

DGMGRL>  add database 'cdb_stby1' as connect identifier is 'cdb_stby1';
Error: ORA-16831: operation not allowed on this member

This message means that the database you are trying to add, in this case cdb_stby1, is not ready to become a standby database in your configuration.

It is easy to verify by querying the "database_role" column in the v$database view.

It needs to be opened with a role that is NOT primary, for example, the PHYSICAL STANDBY role

select database_role, open_mode,name from v$database;

DATABASE_ROLE    OPEN_MODE            NAME
---------------- -------------------- ------------------------------
PHYSICAL STANDBY MOUNTED              CDB

If your database is opened with the role PRIMARY, you need to clone it from your target, for example by using RMANs DUPLICATE ... FOR STANDBY FROM ACTIVE DATABASE

How to use autoconfig to create a sample config file for non-cdb to pdb conversions

java -jar autoupgrade.jar -create_sample_file config /tmp/autoconfig_example.txt noncdbtopdb

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