Friday, June 21, 2024

Flashback database to Guarantee Restore Point or Restore Point

Flashback database to Guarantee Restore Point or Restore Point after Upgrade from 12c to 19c

How to Perform Flashback Database after a Failed Database Upgrade (Doc ID 2882218.1)

How to Downgrade to 12c After Upgrade To 19c Using Flashback database (Doc ID 2887982.1)

We can flashback database at SQL command prompt as well as RMAN command prompt

Here in this blog we are falshing back to GRP after the database upgrade fron 12c to 19c.

- GRP restore point was created in 12c and once we flashback database to GRP after 19c upgrade it will automatically downgrade to 12c.

High Level Steps:

=================

On 19c Home

SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to restore point GRP1;

SQL> shutdown immediate;

on 12c Home

SQL> startup mount

SQL> alter database open resetlogs;

[root@hostnode1 ~]# ps -ef|grep smon

oracle 3022 1 0 10:49 ? 00:00:00 ora_smon_TEST1

root 9504 9238 0 15:51 pts/0 00:00:00 grep --color=auto smon

root 13767 1 1 May25 ? 00:27:10 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 14088 1 0 May25 ? 00:00:01 asm_smon_+ASM1

oracle 25219 1 0 11:40 ? 00:00:00 ora_smon_UAT1

oracle 26493 1 0 10:45 ? 00:00:00 ora_smon_DEV1

[root@hostnode1 ~]# su - oracle

Last login: Sun May 26 15:41:45 IST 2024

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEV1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1

The Oracle base has been set to /u01/app/oracle

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl status database -d DEV

Instance DEV1 is running on node hostnode1

Instance DEV2 is running on node hostnode2

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl config database -d DEV

Database unique name: DEV

Database name: DEV

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/DEV/PARAMETERFILE/spfile.319.1169935561

Password file: +DATA/DEV/PASSWORD/pwddev.269.1169935335

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: DEV1,DEV2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl stop database -d DEV

[oracle@hostnode1 ~]$ srvctl status database -d DEV

Instance DEV1 is not running on node hostnode1

Instance DEV2 is not running on node hostnode2

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 26 15:54:52 2024

Version 19.17.0.0.0

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3690985856 bytes

Fixed Size 8903040 bytes

Variable Size 989855744 bytes

Database Buffers 2684354560 bytes

Redo Buffers 7872512 bytes

Database mounted.

SQL>

SQL> set pages 1000 lines 1000

col TIME for a35

col RESTORE_POINT_TIME for a30

col NAME for a30

select SCN,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,TIME,RESTORE_POINT_TIME,NAME,PDB_RESTORE_POINT from v$restore_point; 2

SCN DATABASE_INCARNATION# GUA TIME RESTORE_POINT_TIME NAME PDB

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

1550262 2 YES 26-MAY-24 09.12.24.000000000 AM BEFORE_UPGRADE NO

SQL>

SQL> flashback database to restore point BEFORE_UPGRADE;

Flashback complete.

SQL> shut immediate;

Database dismounted.

ORACLE instance shut down.

SQL>

[root@hostnode1 ~]# tail -100f /u01/app/oracle/diag/rdbms/dev/DEV1/trace/alert_DEV1.log

2024-06-05T23:15:53.393655+05:30

flashback database to restore point BEFORE_UPGRADE

2024-06-05T23:15:55.041279+05:30

Flashback Restore Start

2024-06-05T23:16:03.045947+05:30

Flashback Restore Complete

Flashback Media Recovery Start

Started logmerger process

2024-06-05T23:16:04.213698+05:30

Parallel Media Recovery started with 2 slaves

Flashback Media Recovery Log +RECO/DEV/ARCHIVELOG/2024_05_26/thread_1_seq_3.309.1169976533

2024-06-05T23:16:04.649010+05:30

ALTER SYSTEM SET remote_listener=' hostscan.localdomain.com:1521' SCOPE=MEMORY SID='DEV1';

2024-06-05T23:16:04.651212+05:30

ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='DEV1';

Flashback Media Recovery Log +RECO/DEV/ARCHIVELOG/2024_05_26/thread_2_seq_1.306.1169976533

2024-06-05T23:16:05.142464+05:30

Incomplete Recovery applied until change 1550263 time 05/26/2024 09:12:31

2024-06-05T23:16:05.146121+05:30

Flashback Media Recovery Complete

2024-06-05T23:16:05.436014+05:30

Completed: flashback database to restore point BEFORE_UPGRADE

[oracle@hostnode1 ~]$ srvctl config database -d DEV

Database unique name: DEV

Database name: DEV

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/DEV/PARAMETERFILE/spfile.319.1169935561

Password file: +DATA/DEV/PASSWORD/pwddev.269.1169935335

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: DEV1,DEV2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEV1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0.1/dbhome_1

The Oracle base has been set to /u01/app/oracle

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 5 23:30:27 2024

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 5 23:36:39 2024

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

Database mounted.

Database opened.

SQL>

SQL> column comp_name format a40

coluSQL> mn version format a12

column staSQL> tus format a15

select comp_name,SQL> version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 12.2.0.1.0 VALID

Oracle Database Packages and Types 12.2.0.1.0 VALID

JServer JAVA Virtual Machine 12.2.0.1.0 VALID

Oracle XDK 12.2.0.1.0 VALID

Oracle Database Java Packages 12.2.0.1.0 VALID

OLAP Analytic Workspace 12.2.0.1.0 VALID

Oracle Real Application Clusters 12.2.0.1.0 VALID

Oracle XML Database 12.2.0.1.0 VALID

Oracle Workspace Manager 12.2.0.1.0 VALID

Oracle Text 12.2.0.1.0 VALID

Oracle Multimedia 12.2.0.1.0 VALID

COMP_NAME VERSION STATUS

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

Spatial 12.2.0.1.0 VALID

Oracle OLAP API 12.2.0.1.0 VALID

Oracle Label Security 12.2.0.1.0 VALID

Oracle Database Vault 12.2.0.1.0 VALID

15 rows selected.

SQL> exit

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl downgrade database -d DEV -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -targetversion 12.2.0.1.0

[oracle@hostnode1 ~]$ env |grep ORA

ORACLE_SID=DEV1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl config database -d DEV

Database unique name: DEV

Database name: DEV

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile: +DATA/DEV/PARAMETERFILE/spfile.319.1169935561

Password file: +DATA/DEV/PASSWORD/pwddev.269.1169935335

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: DEV1,DEV2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 ~]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 00:35:43 2024

Connected to an idle instance.

SQL>

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initDEV1.ora_backup';

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

SQL> create spfile='+DATA/DEV/PARAMETERFILE/spfileDEV.ora' from pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initDEV1.ora_backup';

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

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

[oracle@hostnode1 dbs]$ srvctl modify database -d DEV -spfile '+DATA/DEV/PARAMETERFILE/spfileDEV.ora'

[oracle@hostnode1 dbs]$ srvctl start database -d DEV

[oracle@hostnode1 dbs]$ srvctl status database -d DEV

Instance DEV1 is running on node hostnode1

Instance DEV2 is running on node hostnode2

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ . oraenv

ORACLE_SID = [DEV1] ? TEST1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl status database -d TEST

Instance TEST1 is running on node hostnode1

Instance TEST2 is running on node hostnode2

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$ srvctl stop database -d TEST

[oracle@hostnode1 dbs]$ env |grep ORA

ORACLE_SID=TEST1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 6 00:57:52 2024

Version 19.17.0.0.0

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 3690985856 bytes

Fixed Size 8903040 bytes

Variable Size 1191182336 bytes

Database Buffers 2483027968 bytes

Redo Buffers 7872512 bytes

RMAN> exit

Recovery Manager complete.

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 6 01:00:19 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> set pages 1000 lines 1000

col TIME for a35

col RESTORE_POSQL> SQL> INT_TIME for a30

col NAME for a3SQL> 0

select SCN,DATABASE_INCARNATIOSQL> N#,GUARANTEE_FLASHBACK_DATABASE,TIME,RESTORE_POINT_TIME,NAME,PDB_RESTORE_POINT from v$restore_point;

SCN DATABASE_INCARNATION# GUA TIME RESTORE_POINT_TIME NAME PDB

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

1565819 2 YES 26-MAY-24 09.50.46.000000000 AM GRP_1716697245868 NO

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@hostnode1 dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 6 01:00:39 2024

Version 19.17.0.0.0

connected to target database: TEST (DBID=2468082526, not open)

RMAN>

RMAN> flashback database to restore point GRP_1716697245868;

Starting flashback at 06-JUN-24

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 instance=TEST1 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file +DATA/TEST/ARCHIVELOG/2024_05_26/thread_1_seq_3.324.1169978063

archived log for thread 2 with sequence 1 is already on disk as file +DATA/TEST/ARCHIVELOG/2024_05_26/thread_2_seq_1.321.1169977865

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

Finished flashback at 06-JUN-24

RMAN>

[oracle@hostnode1 ~]$ cd /u01/app/oracle/diag/rdbms/test/TEST1/trace/

[oracle@hostnode1 trace]$ tail -f alert_TEST1.log

Flashback Media Recovery Start

Started logmerger process

2024-06-06T01:01:33.460764+05:30

Parallel Media Recovery started with 2 slaves

2024-06-06T01:01:33.613604+05:30

ALTER SYSTEM SET remote_listener='hostscan.localdomain.com:1521' SCOPE=MEMORY SID='TEST1';

2024-06-06T01:01:33.615983+05:30

ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='TEST1';

2024-06-06T01:01:40.947588+05:30

Flashback Media Recovery Log +DATA/TEST/ARCHIVELOG/2024_05_26/thread_1_seq_3.324.1169978063

2024-06-06T01:01:42.005664+05:30

Flashback Media Recovery Log +DATA/TEST/ARCHIVELOG/2024_05_26/thread_2_seq_1.321.1169977865

2024-06-06T01:01:42.372236+05:30

Incomplete Recovery applied until change 1565820 time 05/26/2024 09:50:53

2024-06-06T01:01:42.376499+05:30

Flashback Media Recovery Complete

Completed: RMAN flashback database to before scn 1565820 in incarnation 2

RMAN> shutdown immediate;

database dismounted

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ . oraenv

ORACLE_SID = [TEST1] ?

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0.1/dbhome_1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ env |grep ORA

ORACLE_SID=TEST1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl downgrade database -d TEST -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -targetversion 12.2.0.1.0

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:05:55 2024

Connected to an idle instance.

SQL>

SQL> create pfile from spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora';

File created.

SQL> exit

Disconnected

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl modify database -d TEST -spfile ''

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile:

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$

[oracle@hostnode1 trace]$ asmcmd -p

ASMCMD [+] > asmcmd -p

ASMCMD [+] > cd +DATA/TEST/PARAMETERFILE

ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l

Type Redund Striped Time Sys Name

PARAMETERFILE UNPROT COARSE JUN 06 01:00:00 Y spfile.274.1169981285

PARAMETERFILE UNPROT COARSE JUN 06 01:00:00 N spfileTEST.ora => +DATA/TEST/PARAMETERFILE/spfile.274.1169981285

ASMCMD [+DATA/TEST/PARAMETERFILE] > rm spfile.274.1169981285

ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l

ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA/TEST/'

ASMCMD [+DATA/TEST/PARAMETERFILE] >

ASMCMD [+DATA/TEST] > exit

[oracle@hostnode2 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:16:49 2024

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initTEST1.ora';

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

SQL> create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' from pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initTEST1.ora';

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> exit

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ cat > initTEST1.ora

spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora'

[oracle@hostnode1 dbs]$ pwd

/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs

[oracle@hostnode1 dbs]$

[oracle@hostnode2 dbs]$ cat > initTEST2.ora

spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora'

[oracle@hostnode2 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:20:13 2024

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl start database -d TEST

[oracle@hostnode1 dbs]$ srvctl status database -d TEST

Instance TEST1 is running on node hostnode1

Instance TEST2 is running on node hostnode2

[oracle@hostnode1 dbs]$

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [TESt1] ? TEST1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0.1/dbhome_1

The Oracle base has been set to /u01/app/oracle

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:32:38 2024

Connected to:

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

SQL> column comp_name format a40

column version format a12

columSQL> SQL> n status format a15

select comp_SQL> name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 12.2.0.1.0 VALID

Oracle Database Packages and Types 12.2.0.1.0 VALID

JServer JAVA Virtual Machine 12.2.0.1.0 VALID

Oracle XDK 12.2.0.1.0 VALID

Oracle Database Java Packages 12.2.0.1.0 VALID

OLAP Analytic Workspace 12.2.0.1.0 VALID

Oracle Real Application Clusters 12.2.0.1.0 VALID

Oracle XML Database 12.2.0.1.0 VALID

Oracle Workspace Manager 12.2.0.1.0 VALID

Oracle Text 12.2.0.1.0 VALID

Oracle Multimedia 12.2.0.1.0 VALID

COMP_NAME VERSION STATUS

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

Spatial 12.2.0.1.0 VALID

Oracle OLAP API 12.2.0.1.0 VALID

Oracle Label Security 12.2.0.1.0 VALID

Oracle Database Vault 12.2.0.1.0 VALID

15 rows selected.

SQL> exit

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

[oracle@hostnode1 ~]$

Thursday, June 20, 2024

Manually Drop RAC database using command line

We have 2 RAC database called DEVCDB and TESTCDB

DEVCBD (DEVCBD1 & DEVCBD2)

TESTCDB (TESTCDB1 & TESTCDB2)

We will drop these RAC databases using manual method in command line.

1. Check RAC database instance status

[root@hostnode1 ~]# ps -ef|grep smon

root 2047 1865 0 20:24 pts/2 00:00:00 grep --color=auto smon

oracle 2622 1 0 May10 ? 00:00:26 ora_smon_DEVDB1

oracle 15043 1 0 May12 ? 00:00:29 ora_smon_TESTCDB1

root 16358 1 1 May05 ? 07:15:10 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 17256 1 0 May05 ? 00:00:26 asm_smon_+ASM1

oracle 20885 1 0 May12 ? 00:00:24 ora_smon_DEVCDB1

[root@hostnode1 ~]#

2. login to oracle user and stop the RAC database

[root@hostnode1 ~]# su - oracle

Last login: Fri May 24 20:13:08 IST 2024

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVCDB1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1

The Oracle base has been set to /u01/app/oracle

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl stop database -d DEVCDB

3. Start the database in nomount and set cluster_database=false so that we can start the RAC database instance in exclusive mode

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 24 20:27:15 2024

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1644167168 bytes

Database Buffers 2030043136 bytes

Redo Buffers 7634944 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

4. Start database in mount exclusive restrict mode

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1644167168 bytes

Database Buffers 2030043136 bytes

Redo Buffers 7634944 bytes

Database mounted.

5. Drop database

SQL> drop database;

Database dropped.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@hostnode1 ~]$

6. Remove database from the cluster configuration

[oracle@hostnode1 ~]$ srvctl status database -d DEVCDB

Instance DEVCDB1 is not running on node hostnode1

Instance DEVCDB2 is not running on node hostnode2

[oracle@hostnode1 ~]$ srvctl remove database -d DEVCDB

Remove the database DEVCDB? (y/[n]) y

[oracle@hostnode1 ~]$

7. Similarly follow the above step #1 to step #6 for dropping the TESTCDB database.

[root@hostnode2 ~]# ps -ef|grep smon

root 3673 1 1 May01 ? 08:49:44 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 4112 1 0 May01 ? 00:00:33 asm_smon_+ASM2

oracle 7539 1 0 May12 ? 00:00:33 ora_smon_TESTCDB2

oracle 10041 1 0 May10 ? 00:00:31 ora_smon_DEVDB2

oracle 18734 1 0 May12 ? 00:00:26 ora_smon_DEVCDB2

root 32313 32171 0 20:24 pts/0 00:00:00 grep --color=auto smon

[root@hostnode2 ~]#

[root@hostnode2 ~]# su - oracle

Last login: Fri May 24 18:03:45 IST 2024

[oracle@hostnode2 ~]$

[oracle@hostnode2 ~]$ . oraenv

ORACLE_SID = [oracle] ? TESTCDB2

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1

The Oracle base has been set to /u01/app/oracle

[oracle@hostnode2 ~]$ srvctl stop database -d TESTCDB

[oracle@hostnode2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 24 20:27:15 2024

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1073741824 bytes

Database Buffers 2600468480 bytes

Redo Buffers 7634944 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1073741824 bytes

Database Buffers 2600468480 bytes

Redo Buffers 7634944 bytes

Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> exit

^[[A^[[A[oracle@hostnode2 ~]$ srvctl status database -d TESTCDB

Instance TESTCDB1 is not running on node hostnode1

Instance TESTCDB2 is not running on node hostnode2

[oracle@hostnode2 ~]$ srvctl remove database -d TESTCDB

Remove the database TESTCDB? (y/[n]) y

[oracle@hostnode2 ~]$

Regards,

Mallik

Sunday, June 2, 2024

Copy password file from FS to ASM diskgroup

Copy password file from FS to ASM diskgroup:

1. Verify passwordile available on FS

[oracle@oraclenode1 ~]$ ll /tmp/orapwRAC12C

-rw-r----- 1 oracle oinstall 6144 Jun 2 11:01 /tmp/orapwRAC12C

2. set the enviroment and copy the password file from FS to ASM diskgroup

[oracle@oraclenode1 tmp]$ . oraenv

ORACLE_SID = [RACSb1] ? RACSB1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclenode1 tmp]$

[oracle@oraclenode1 tmp]$ asmcmd -p

ASMCMD [+] > pwcopy --dbuniquename RACSB '/tmp/orapwRAC12C' '+DATA/RACSB/PASSWORD/orapwRACSB'

ASMCMD [+] >

3. Verify the password file on ASM diskgroup

[oracle@oraclenode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

The Oracle base has been set to /u01/app/oracle

[oracle@oraclenode1 ~]$ asmcdm -p

ASMCMD [+] > cd +DATA/RACSB/PASSWORD/

ASMCMD [+DATA/RACSB/PASSWORD] > ls -l

Type Redund Striped Time Sys Name

PASSWORD UNPROT COARSE JUN 02 11:00:00 N orapwracsb => +DATA/RACSB/PASSWORD/pwdracsb.309.1170587277

PASSWORD UNPROT COARSE JUN 02 11:00:00 Y pwdracsb.309.1170587277

ASMCMD [+DATA/RACSB/PASSWORD] >

4. Verify the database connection using password file

[oracle@oraclenode1 ~]$ sqlplus sys/Mallik123#@RACSB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 2 11:10:38 2024

Last Successful login time: Sun Jun 02 2024 11:00:35 +05:30

Connected to:

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

SQL>

Mallik034

Friday, June 21, 2024