To recover the lost logs in oracle database

***

Oracle Database Recovery and Backup Task Documentation

This document describes the steps performed for deleting redo log files, performing an RMAN point-in-time recovery, and backing up the database with archived logs in an Oracle 12c environment with a multitenant container database (CDB) and pluggable database (PDB).

***

Environment and Initial Setup

- Oracle Database version: 12c Release 12.2.0.1.0 (64-bit Enterprise Edition)

- Working with Container Database (CDB) and Pluggable Database (PDB1)

- OS user: oracle

- Date and time of operations: Sep 20, 2025, around 18:30 to 18:50 +04 timezone

***

Step 1: Switch Container and Create Table

1. Set container to PDB1:

SQL> alter session set container=PDB1;

Session altered.

2. Verify PDB status:SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           READ WRITE NO

3. Create a simple table named `emp` with columns `id` (int) and `name` (varchar(100)):

SQL> create table emp(id int,name varchar(100));

Table created.

4. Insert one row into the `emp` table:

SQL> insert into emp values (1,'Muthu');

1 row created.

SQL> commit;

Commit complete.

5. Validate the data insertion:

SQL> select * from emp;

        ID        NAME

--------------------------------------------------------------------------------

         1         Muthu

6. Check the current System Change Number (SCN):

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    2779187

7. Confirm the current date/time from the OS shell:

SQL> !date

Sat Sep 20 18:32:15 +04 2025

***

Step 2: List and Remove Redo Log Files

1. List redo log members (files):

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/oradb/redo03.log

/u01/app/oracle/oradata/oradb/redo02.log

/u01/app/oracle/oradata/oradb/redo01.log

2. List the contents of Oracle data directory:

SQL> !ls -lrt /u01/app/oracle/oradata/oradb/

total 2498756

drwxr-x---. 2 oracle oinstall      4096 Sep  4  2020 pdbseed

drwxr-x---. 2 oracle oinstall       104 Sep  4  2020 pdb1

-rw-r-----. 1 oracle oinstall 142614528 Sep 20 18:20 temp01.dbf

-rw-r-----. 1 oracle oinstall 209715712 Sep 20 18:36 redo01.log

-rw-r-----. 1 oracle oinstall 209715712 Sep 20 18:37 redo02.log

-rw-r-----. 1 oracle oinstall 849354752 Sep 20 18:37 system01.dbf

-rw-r-----. 1 oracle oinstall 639639552 Sep 20 18:37 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 283123712 Sep 20 18:37 undotbs01.dbf

-rw-r-----. 1 oracle oinstall   5251072 Sep 20 18:37 users01.dbf

-rw-r-----  1 oracle oinstall 209715712 Sep 20 18:37 redo03.log

-rw-r-----. 1 oracle oinstall  18726912 Sep 20 18:38 control01.ctl

3. Remove all redo log files from directory:

SQL> !rm /u01/app/oracle/oradata/oradb/redo*

4. Confirm redo log files removal by listing directory again:

SQL> !ls -lrt /u01/app/oracle/oradata/oradb/

total 1884344

drwxr-x---. 2 oracle oinstall      4096 Sep  4  2020 pdbseed

drwxr-x---. 2 oracle oinstall       104 Sep  4  2020 pdb1

-rw-r-----. 1 oracle oinstall 142614528 Sep 20 18:20 temp01.dbf

-rw-r-----. 1 oracle oinstall 849354752 Sep 20 18:37 system01.dbf

-rw-r-----. 1 oracle oinstall 639639552 Sep 20 18:37 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 283123712 Sep 20 18:37 undotbs01.dbf

-rw-r-----. 1 oracle oinstall   5251072 Sep 20 18:37 users01.dbf

-rw-r-----. 1 oracle oinstall  18726912 Sep 20 18:38 control01.ctl

***

Step 3: Shutdown Database and Mount for Recovery

1. Shutdown the database immediately:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  801112064 bytes

Fixed Size                  8797872 bytes

Variable Size             423625040 bytes

Database Buffers          360710144 bytes

Redo Buffers                7979008 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

***

Step 4: RMAN Point-in-Time Recovery to a Specific Sequence

1. Start RMAN and connect to target database:

[oracle@muthu trace]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 18:40:06 2025

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB (DBID=2803495960, not open)

2. Run recovery commands:

RMAN> run

{

set until sequence 3;

restore database;

recover database;

alter database open resetlogs;

}2> 3> 4> 5> 6> 7>

executing command: SET until clause

Starting restore at 20-SEP-2025 18:40:21

using target database control file instead of recovery catalog

flashing back control file to SCN 2780743

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

skipping datafile 5; already restored to file /u01/app/oracle/oradata/oradb/pdbseed/system01.dbf

skipping datafile 6; already restored to file /u01/app/oracle/oradata/oradb/pdbseed/sysaux01.dbf

skipping datafile 8; already restored to file /u01/app/oracle/oradata/oradb/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oradb/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oradb/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oradb/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/oradb/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fra/oradb/ORADB/backupset/2025_09_20/o1_mf_nnndf_TAG20250920T183632_ndxgzjpn_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/oradb/ORADB/backupset/2025_09_20/o1_mf_nnndf_TAG20250920T183632_ndxgzjpn_.bkp tag=TAG20250920T183632

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_1_ndxgz7r8_.arc

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_2_ndxh0h3t_.arc

archived log file name=/u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_1_ndxgz7r8_.arc thread=1 sequence=1

archived log file name=/u01/app/oracle/fra/oradb/ORADB/archivelog/2025_09_20/o1_mf_1_2_ndxh0h3t_.arc thread=1 sequence=2

media recovery complete, elapsed time: 00:00:01

Finished recover at 20-SEP-2025 18:40:53

Statement processed

RMAN>

***

Step 5: Validate Recovery and Backup

[oracle@muthu trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 18:44:09 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ORADB     READ WRITE

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

SQL> alter session set container=PDB1;

Session altered.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    2782820

SQL> !date

Sat Sep 20 18:44:28 +04 2025

SQL> select * from emp;

        ID        NAME

--------------------------------------------------------------------------------

         1         Muthu

[oracle@muthu trace]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 18:47:20 2025

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB

RMAN> backup database plus archivelog;

Finished Control File and SPFILE Autobackup at 20-SEP-2025 18:48:26

Changing the hostname in Linux

(adsbygoogle = window.adsbygoogle || []).push({});

Changing the hostname in Linux depends on your distribution and whether you're using **systemd** (most modern distros do).

1. Check Current Hostname

[root@test ~]# hostnamectl status

   Static hostname: test.localdomain

         Icon name: computer-vm

           Chassis: vm

[root@test ~]# date

Thu Aug 14 15:53:54 +04 2025

[root@test ~]# uptime

 15:53:56 up 1 min,  1 user,  load average: 2.46, 0.70, 0.24

 

2. Change Hostname (Permanent)

Using `hostnamectl` (Recommended for systemd-based systems like Ubuntu, CentOS 7+, RHEL 7+, Debian 9+)

sudo hostnamectl set-hostname new-hostname

[root@test ~]# hostnamectl set-hostname muthu.org

> Replace `new-hostname` with your desired name.

3. Update `/etc/hosts`

Edit the file:

sudo nano /etc/hosts

[root@test ~] vi /etc/hosts

Find the line like:

127.0.1.1    old-hostname

And change it to:

127.0.1.1    new-hostname

This ensures local name resolution works properly.

4. Reboot or Re-login

Although the change is immediate, some services may require a reboot:

sudo reboot

[root@test ~]# reboot

##Viewing Alert Logs in Oracle Cloud Infrastructure (OCI) for Oracle Autonomous Databases

### Limited OS Access

For Oracle Autonomous Databases, direct access to the underlying operating system is restricted. Therefore, alert log monitoring must be done through SQL views and the OCI Console.

### SQL-Based Alert Log Access

#### Deprecated View

- **`V$DIAG_ALERT_EXT`**: Previously used to query alert logs directly via SQL.

- **Status**: **Removed** in recent Oracle ADB updates.

#### Replacement View

- **`V$CLIENT_ERRORS`**: Introduced as the new method to access alert log-related error data.

- **Purpose**: Displays XML-based error logs, including client connection issues and user errors like `ORA-4021`.

### Current Limitations of `V$CLIENT_ERRORS`

- **Session Dependency**: The view only shows error data **while the session is active**.

- **Post-Session Visibility**: If a session is terminated (e.g., killed due to resource limits), its error data **is no longer visible** in the view.

- **User Experience**: This behavior is understandably frustrating, as it limits post-mortem diagnostics.

### Enhancement in Progress

Oracle is actively working on enhancing `V$CLIENT_ERRORS` to improve its usability and persistence of error data beyond session lifetime. No official release date has been announced yet, but updates are expected soon.

we will keep posted about any recent update

 Goldengate cdc replication from Oracle to Postgres (DAA)

ADD EXTRACT PROCESS

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 10> dblogin USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD ******* , ENCRYPTKEY DEFAULT

Successfully logged into database CDB$ROOT.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 11> register extract EXT_E1_2 database container(PDB)

2025-08-12 20:22:02  INFO    OGG-02003  Extract group EXT_E1_2 successfully registered with database at SCN 11849257800002.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 12> add extract EXT_E1_2 integrated tranlog begin now

Integrated Extract added.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 13> add exttrail ./dirdat/ex  extract EXT_E1_2 megabytes 500

EXTTRAIL added.

ADD PUMP PROCESS

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 14> add extract PXT_P2 exttrailsource ./dirdat/ex

Extract added.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 15> add rmttrail ./dirdat/er extract PXT_P2 megabytes 500

RMTTRAIL added.

EXTRACT  PARAMETERS:

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 9> view params EXT_E1_2

EXTRACT EXT_E1_2
SETENV (ORACLE_SID='oradb')
USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD *******, ENCRYPTKEY DEFAULT
EXTTRAIL ./dirdat/ex
SOURCECATALOG PDB
DDL &
INCLUDE MAPPED
DDLOPTIONS REPORT
reportcount every 1 minutes, rate
REPORTROLLOVER AT 01:30 ON Sunday

LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
TABLE PDB.EMP.SALARY;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 10>

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 10> info EXT_E1_2

Extract    EXT_E1_2  Initialized  2024-08-12 00:34   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:02:45 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2024-08-12 00:34:17
                     SCN 0.0 (0)

PUMP  PARAMETERS;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 27> view params PXT_P2

EXTRACT PXT_P2
SETENV (ORACLE_SID='oradb')
USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD ******* , ENCRYPTKEY DEFAULT
rmthost 27-45-2-299, mgrport 7809
rmttrail ./dirdat/er
PASSTHRU
SOURCECATALOG PDB
TABLE PDB.EMP.SALARY;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT_E1_2    00:00:00      00:00:37
EXTRACT     STOPPED     PXT_P2    00:00:00      00:00:10

START EXTRACT CDC;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 17> start EXT_E1_2

Sending START request to Manager ...

Extract group EXT_E1_2 starting.

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 18> info EXT_E1_2

Extract    EXT_E1_2  Initialized  2025-08-12 20:22   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:49 ago)
Process ID           1135560
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2025-08-12 20:22:10
                     SCN 0.0 (0)

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 26> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT_E1_2    00:00:03      00:00:08
EXTRACT     STOPPED     PXT_P2    00:00:00      00:08:35

CDC PUMP PROCESS TO START;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 28>  info PXT_P2,detail
Extract    PXT_P2  Initialized  2025-08-12 20:22   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:10:16 ago)
Log Read Checkpoint  File ./dirdat/ex000000000

First Record  RBA 0
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  ./dirdat/er                                          0          0        500 RMTTRAIL

START CDC PUMP PROCESS;

GGSCI (node1.muthu.org as c##oggadmin@oradb/CDB$ROOT) 29> start PXT_P2

Sending START request to Manager ...

Extract group PXT_P2 starting.

GGSCI (node1.muthu.org) 3> info PXT_P2,detail

Extract    PXT_P2  Last Started 2025-08-12 22:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Process ID           1316914
Log Read Checkpoint  File ./dirdat/ex000000000
                     2025-08-12 21:17:05.000000  RBA 5298768
  Target Extract Trails:
  Trail Name                                       Seqno        RBA     Max MB Trail Type
  ./dirdat/er                                          0    5298846        500 RMTTRAIL

INITIAL LOAD:

INITIAL LOAD EXTRACT:

GGSCI (node1.muthu.org) 1> info all tasks

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
EXTRACT     STOPPED     ILT_03

SCN - Used here is from the REAL TIME EXTRACT PROCESS Registered SCN.

“ Extract group EXT_E1_2 successfully registered with database at SCN 11849257800002.”

GGSCI (node1.muthu.org) 24> view params ILT_03

EXTRACT ILT_03
USERID c##oggadmin@node1.muthu.org:1526/oradb, PASSWORD ******* , ENCRYPTKEY DEFAULT
RMTHOST 27-45-2-299,mgrport 7809, Compress
rmtfile /gg01/app/oracle/product/21.3.0/postgres/dirdat/m11/ta
SOURCECATALOG PDB
TABLE PDB.EMP.COUNTRY SQLPREDICATE 'AS OF SCN 11849257800002';

GGSCI (node1.muthu.org) > delete extract ILT_03

GGSCI (node1.muthu.org) 6> add extract ILT_03,sourceistable

Extract added.

GGSCI (node1.muthu.org) 7> info ILT_03
Extract    ILT_03   Initialized  2025-08-12 23:00   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
First Record         Record 0
Task                 SOURCEISTABLE

GGSCI (node1.muthu.org) 28> start ILT_03

Sending START request to Manager ...

Extract group ILT_03 starting.

GGSCI (node1.muthu.org) 29> info ILT_03

Extract    ILT_03   Initialized  2025-08-12 23:07   Status RUNNING
Checkpoint Lag       Not Available
Process ID           1437844
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

GGSCI (node1.muthu.org) 34> info ILT_03

Extract    ILT_03   Last Started 2025-08-12 23:08   Status RUNNING
Checkpoint Lag       Not Available
Process ID           1437844
Log Read Checkpoint  Table PDB.EMP.COUNTRY
                     2025-08-12 23:10:09  Record 12715001
Task                 SOURCEISTABLE

INITIAL LOAD REPLICAT

GGSCI (ip-27-45-2-299.us-east) 3> view params ILT_03

replicat ILT_03
SETENV (ODBCINI="/u01/app/oracle/product/21.3.0/postgres/odbc.ini" )
targetdb pgdbs, userid oggadmin PASSWORD *******
DISCARDFILE /gg01/app/oracle/product/21.3.0/postgres/dirprm/dsc_max.dsc, APPEND, MEGABYTES 200
BATCHSQL 
MAP PDB.EMP.COUNTRY,TARGET EMP.COUNTRY;

GGSCI (ip-27-45-2-299.us-east) 7> add replicat ILT_03,exttrail /u01/app/oracle/product/21.3.0/postgres/dirdat/m11/ta , NODBCHECKPOINT

Replicat added.

GGSCI (ip-27-45-2-299.us-east) 9> start ILT_03

Sending START request to Manager ...

Replicat group ILT_03 starting.

GGSCI (ip-27-45-2-299.us-east) 10> info ILT_03

Replicat   ILT_03   Last Started 2025-08-12 00:16   Status RUNNING
Checkpoint Lag       01:08:00 (updated 00:00:00 ago)
Process ID           1797
Log Read Checkpoint  File /u01/app/oracle/product/21.3.0/postgres/dirdat/m11/ta000000
                     2025-08-12 23:08:11.626226  RBA 901433

Once the initial load is completed then start cdc replication process.

GGSCI (ip-27-45-2-299.us-east) 2> add replicat REP_R3,exttrail  ./dirdat/er, NODBCHECKPOINT

Replicat added.

GGSCI (ip-27-45-2-299.us-east) 3> info REP_R3

Replicat   REP_R3  Initialized  2025-08-12 05:51   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File ./dirdat/er000000000
                     First Record  RBA 0

GGSCI (ip-27-45-2-299.us-east) 7> START REPLICAT REP_R3 AFTERCSN 11849257800002

Sending START request to Manager ...

Replicat group REP_R3 starting.

GGSCI (ip-27-45-2-299.us-east) 16> view params REP_R3

REPLICAT REP_R3
SETENV (ODBCINI="/u01/app/oracle/product/21.3.0/postgres/odbc.ini" )
targetdb pgdbs, userid oggadmin PASSWORD ******* 
DISCARDFILE /gg01/app/oracle/product/21.3.0/postgres/dirrpt/REP_R3.dsc
MAP PDB.EMP.SALARY,TARGET EMP.SALARY;

GGSCI (ip-27-45-2-299.us-east) 15> info REP_R3

Replicat   REP_R3  Last Started 2025-08-12 05:59   Status RUNNING
Checkpoint Lag       04:37:25 (updated 00:00:03 ago)
Process ID           8836
Log Read Checkpoint  File ./dirdat/er000000000
                     2025-08-12 01:25:40.013362  RBA 16962583

SCRIPTS USED TO DROP OBJECTS

Note : Login to respective user then execute this script

SELECT
'DROP TABLE ' || table_schema || '.' || table_name || ' CASCADE;'
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');

COUNTS;

SELECT
'SELECT COUNT(*) FROM ' || table_schema || '.' || table_name || ';'
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');

SCRIPT FOR TRIGGERS;

CREATE OR REPLACE FUNCTION set_replica_identity_on_create()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            objid::regclass AS full_table_name,
            n.nspname AS schema_name,
            c.relname AS table_name
        FROM pg_event_trigger_ddl_commands() AS e
        JOIN pg_class c ON c.oid = e.objid
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE e.command_tag = 'CREATE TABLE'
          AND n.nspname = 'entitystore'
          AND c.relkind IN ('r', 'p') -- regular and partitioned tables
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(r.schema_name) || '.' || quote_ident(r.table_name) || ' REPLICA IDENTITY FULL';
        RAISE NOTICE 'Set REPLICA IDENTITY FULL for table: %.%', r.schema_name, r.table_name;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER set_replica_identity_on_create

ON ddl_command_end

WHEN TAG IN ('CREATE TABLE')

EXECUTE FUNCTION set_replica_identity_on_create();

CREATE OR REPLACE FUNCTION set_replica_identity_on_alter()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            n.nspname AS schema_name,
            c.relname AS table_name,
            c.relreplident
        FROM pg_event_trigger_ddl_commands() e
        JOIN pg_class c ON c.oid = e.objid
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE e.command_tag = 'ALTER TABLE'
          AND c.relkind IN ('r', 'p')
          AND n.nspname = 'entitystore'
          AND c.relreplident != 'f'  -- Skip if already FULL
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(r.schema_name) || '.' || quote_ident(r.table_name) || ' REPLICA IDENTITY FULL';
        RAISE NOTICE 'Set REPLICA IDENTITY FULL for table: %.%', r.schema_name, r.table_name;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER set_replica_identity_on_alter
    ON ddl_command_end
    WHEN TAG IN ('ALTER TABLE')
    EXECUTE FUNCTION set_replica_identity_on_alter();

SELECT
    evtname AS trigger_name,
    evtevent AS event,
    evtowner AS owner_id,
    evtfoid::regprocedure AS function_name,
    evtenabled AS enabled_status
FROM pg_event_trigger;

Ads

Convert oracle standalone database to oracle ASM

Here is the sequence of operations to migrate an Oracle database from a file system to ASM (Automatic Storage Management) and register it with Oracle Clusterware.

1. Initial State & Failed Backup Attempt

First, the database instance is started in MOUNT mode. An initial attempt to back up the database using RMAN fails because the database is running in NOARCHIVELOG mode. An online backup cannot be performed on active datafiles in this state.

# Connect to the idle instance
[oracle@node1 dbs]$ sqlplus / as sysdba
Connected to an idle instance.

SQL> startup mount
Database mounted.

# Attempt backup using RMAN
[oracle@node1 dbs]$ rman target /
connected to target database: ORADB (DBID=2803372975, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
...
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/12/2025 14:20:46
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
...

---

2. Enabling ARCHIVELOG Mode

To resolve the backup failure, ARCHIVELOG mode must be enabled. An initial attempt fails with error ORA-00265, indicating that instance recovery is required due to the previous unclean state. A clean shutdown and restart in MOUNT mode resolve this, allowing ARCHIVELOG and FORCE LOGGING to be successfully enabled.

# Attempt to enable archivelog mode fails
SQL> alter database archivelog;
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

# Perform a clean shutdown and restart
SQL> shutdown immediate;
ORACLE instance shut down.

SQL> startup mount;
Database mounted.

# Successfully enable archivelog and force logging
SQL> alter database archivelog;
Database altered.

SQL> alter database force logging;
Database altered.

---

3. Successful Backup and Switch to ASM

With the database in ARCHIVELOG mode, the RMAN backup to the +DATA ASM disk group now succeeds. The BACKUP AS COPY command creates an image copy of the datafiles directly in ASM. Afterward, the SWITCH DATABASE TO COPY command updates the control file to point to these new datafile locations on ASM, effectively migrating the datafiles.

# RMAN backup to ASM

[oracle@node1 trace]$ rman target /
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 12-AUG-2025 14:27:48
...
output file name=+DATA/ORADB/DATAFILE/system.261.1208960879
...
Finished backup at 12-AUG-2025 14:29:15

# Switch database to use the new datafile copies in ASM
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/ORADB/DATAFILE/system.261.1208960879"
...

# Open the database
RMAN> ALTER DATABASE OPEN;
Statement processed

---

4. Relocating Redo Log Files to ASM

The final migration step is moving the online redo log files from the local file system to ASM. This is done by adding new log groups in ASM, switching log files until the old ones are no longer active, and then dropping the old log files.

-- Verify datafiles are now in ASM
SQL> SELECT name FROM V$DATAFILE;
NAME
--------------------------------------------------
+DATA/ORADB/DATAFILE/system.261.1208960879
+DATA/ORADB/DATAFILE/sysaux.262.1208960903
...

-- Add new redo log groups in the '+FRA' disk group
SQL> alter database add logfile group 4 ('+FRA/redo1.log') size 500M;
SQL> alter database add logfile group 5 ('+FRA/redo2.log') size 500M;
SQL> alter database add logfile group 6 ('+FRA/redo3.log') size 500M;

-- Switch logfiles to make old groups inactive
SQL> alter system switch logfile;

-- Drop old file system-based redo log groups
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

-- The current log group cannot be dropped immediately
SQL> alter database drop logfile group 1;
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance oradb (thread 1) - cannot drop

-- Switch again to make group 1 inactive, then drop it
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> alter database drop logfile group 1; 

5. Registering and Managing with Oracle Clusterware

Finally, the database is registered with Oracle Clusterware using the srvctl utility. This allows the cluster to manage the database startup, shutdown, and monitoring.

# Add the database and instance to the Clusterware configuration
[oracle@node1 ~]$ srvctl add database -d oradb -o /u01/app/oracle/product/19.0.0/db_1
[oracle@node1 ~]$ srvctl add instance -d oradb -i oradb1 -n node1

# Start the database using srvctl
[oracle@node1 dbs]$ srvctl start database -d oradb

# Verify the status
[oracle@node1 dbs]$ srvctl status database -d oradb
Instance oradb1 is running on node node1

# Final check of the running processes
[oracle@node1 ~]$ ps -ef|grep pmon
oracle   21502     1 0 15:57 ?        00:00:00 ora_pmon_oradb

Is C Still Relevant in the 21st Century?

Many programming languages have come and gone since Dennis Ritchie devised C in 1972, and yet C has not only survived three major revisions, but continues to thrive. Large chunks of Windows were written in C, along with most of Linux.

But aside from this incredible legacy, what keeps C atop the Tiobe Index? The number of jobs on Dice.com for C programmers is not huge, and many of those also include C++ and Objective-C. On Reddit, the C community, while one of the ten most popular programming communities, is half the size of the C++ group. (Of course, after more than four decades, maybe there’s not a whole lot of new material published about C!) (Aside from this article, of course.)

Despite being overshadowed by other languages, I believe C remains relevant for the following reasons:

It’s Easy to Learn

The only advanced features in C are pointers and function pointers. Once you’ve mastered those, you’ve pretty much learned the language. Knowing C provides a handy insight into higher-level languages—C++, Objective-C, Perl, Python, Java, PHP, C#, D and Go all have block syntax that’s derived from C. And reference variables in C# will be easier to understand because you know C pointers.

It’s Still Used

There is an immense amount of software written in C that’s still used, including Apache and NGINX Web servers, MySQL, PostgreSQL, SQLite, Ingres database, GIMP, CPython, Perl 5, PHP, Mathematica, MATLAB and most device drivers.

From the end of the 1980s until the early 2000s, developers relied on C to develop games, with C++ taking over after that. There’s so much C source code still around that learning to program games in C using the SDL library is not hard.

The Internet

The Internet is basically driven by C applications. Most browsers are written in C++, but C code is used for the infrastructure, mail sending utilities, DNS utilities, etc.

Some modern compilers generate C as an output stage. This saves the compiler-writer having to create a code generation stage for each platform.

Need for Tight Coding

The increased availability of low-cost processors with small amounts of RAM and ROM requires tight coding, and C fulfills that role perfectly.

It’s not been all rosy for C, especially with Internet-facing code; many of the vulnerabilities that have plagued Microsoft and other vendors are due to C functions that don’t do bounds-checking and end up called by buggy code. (Networked computers weren’t so commonplace back in the day, and no one predicted that malware writers working remotely would seek to exploit these unsafe functions.) These vulnerabilities have now been examined and a large number of C functions banned from use, replaced with safer versions that have an extra parameter (usually a limit value).

Newer C Compiler Support

Fifteen years on, the C99 standard is largely supported in compilers such as GCC and Clang, along with several commercial ones. The C11 standard, however, is still too new to be fully implemented, although it has partial support. It’s a reasonable guess that the most popular version of C is still C89 (also known as ANSI C). But with CPUs having greater numbers of cores, it’s likely that C11 will be a necessity in a few years because of its thread support with the threads library.

Is C Still Relevant?

Yes. It’s easy to learn, there’s a lot of it still in use, and plenty of free or open-source compilers. While it may not get you a job, it will give you an excellent grounding in low-level programming. It’s not growing in popularity… but it’s not going away anytime soon either.

By David Bolton | Dec 8, 2014