Oracledna

    Convert Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC)

    • oracledna
    • DataGuard
    • March 6, 2026

    This post demonstrates converting Snapshot Standby Database into Production in Oracle 19c (non-cdb & non-RAC).

    The Snapshot Standby Database helps developers for testing. To retain the transactions that occurred on Snapshot standby, they want to convert Snapshot standby into PRODUCTION/PRIMARY database.

    Note: Both forceful and graceful failover cannot be done on snapshot standby.

    1. Mount the snapshot standby database.

     

    SQL> startup mount
    ORACLE instance started.
    Total System Global Area 2113927120 bytes
    Fixed Size 8898512 bytes
    Variable Size 486539264 bytes
    Database Buffers 1610612736 bytes
    Redo Buffers 7876608 bytes
    Database mounted.

     

    SQL> select name,open_mode,database_role from v$database;

    NAME OPEN_MODE DATABASE_ROLE
    ———- ———- ——————
    PROD MOUNTED SNAPSHOT STANDBY

    2. Take backup of controlfile of snapshot database.

    SQL> alter database backup controlfile to trace;
    Database altered.

    To get the location of newly created controlfile backup, check the alert log

    SQL> oradebug setmypid
    Statement processed.


    SQL> oradebug tracefile_name;
    /u01/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_ora_14751.trc


    Alert log of snapshot standby:
    ======================
    alter database backup controlfile to trace
    2026-03-06T23:56:40.129646+05:30
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_ora_14751.trc
    Completed: alter database backup controlfile to trace

    3. Get the current control file location and shutdown the snapshot standby instance. Also, take the backup of controlfile.


    SQL> select name,open_mode,database_role from v$database;
    NAME OPEN_MODE DATABASE_ROLE
    ———- ———- ——————
    PROD MOUNTED SNAPSHOT STANDBY

     

    SQL> show parameter control_files
    NAME TYPE VALUE
    ———————————— ———– ——————————
    control_files string /u01/app/oracle/oradata/STDBY/
    control01.ctl, /u01/app/oracle
    /oradata/STDBY/control02.ctl


    SQL> shutdown immediate
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.


    Take the backup of controlfile at OS level==
    [oracle@stdby ~]$ cd /u01/app/oracle/oradata/STDBY/

     

    [oracle@stdby STDBY]$ mv control01.ctl control01.ctl_bkp
    [oracle@stdby STDBY]$ mv control02.ctl control02.ctl_bkp

     

    [oracle@stdby STDBY]$ ls -lrt control*
    -rw-r—– 1 oracle oinstall 10895360 Mar 7 00:13 control01.ctl_bkp
    -rw-r—– 1 oracle oinstall 10895360 Mar 7 00:13 control02.ctl_bkp

    4. Create a new control from the trace file.

    4.a Edit the trace control file and rename to control.ctl

    CREATE CONTROLFILE REUSE DATABASE “PROD” NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 ‘/u01/app/oracle/oradata/STDBY/redo01.log’ SIZE 200M BLOCKSIZE 512,
    GROUP 2 ‘/u01/app/oracle/oradata/STDBY/redo02.log’ SIZE 200M BLOCKSIZE 512,
    GROUP 3 ‘/u01/app/oracle/oradata/STDBY/redo03.log’ SIZE 200M BLOCKSIZE 512
    DATAFILE
    ‘/u01/app/oracle/oradata/STDBY/system01.dbf’,
    ‘/u01/app/oracle/oradata/STDBY/sysaux01.dbf’,
    ‘/u01/app/oracle/oradata/STDBY/undotbs01.dbf’,
    ‘/u01/app/oracle/oradata/STDBY/users01.dbf’
    CHARACTER SET AL32UTF8
    ;

    4.b Start the snapshot database in nomount state

    SQL> startup nomount
    ORACLE instance started.
    Total System Global Area 2113927120 bytes
    Fixed Size 8898512 bytes
    Variable Size 486539264 bytes
    Database Buffers 1610612736 bytes
    Redo Buffers 7876608 bytes

    4.c Create the controlfile from trace (created on step 2)

    SQL> @control.ctl
    Control file created.

    4.d check database role and whether mounted or not.

    SQL> select name,open_mode,database_role from v$database;
    NAME OPEN_MODE DATABASE_ROLE
    ——— ——————– —————-
    PROD MOUNTED PRIMARY

    The database is in mount state.

    Note: After control file creation, check the temp tablespace and tempfiles and add them accordingly.

    5. Open the database.

    SQL> alter database open;
    Database altered.


    SQL> select name,open_mode,database_role from v$database;
    NAME OPEN_MODE DATABASE_ROLE
    ——— ——————– —————-
    PROD READ WRITE PRIMARY

    Disclaimer:

    Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.

    Popular Posts
    Categories