Amit's Oracle DBA Blog

Saturday, 4 May 2024

TABLE FRAGMENTATION IN ORACLE DATABASE

What is Oracle Table Fragmentation?
If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

What are the reasons to reorganization of table?

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to find Table Fragmentation?

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.

Steps to Check and Remove Table Fragmentation:-
=============================================
1. Gather table stats:
———————
To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’);

2. Check Table size:
——————-
Now again check table size using and will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

3. Check for Fragmentation in table:
———————————–
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.

set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/
Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. How to reset HWM / remove fragemenation?
—————————————
We have four options to reorganize fragmented tables:

1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
(Depends upon the free space available in the tablespace)
2. Export and import the table:- (difficult to implement in production environment)
3. Shrink command (fron Oracle 10g)
(Shrink command is only applicable for tables which are tablespace with auto segment space management)

Here, I am following Options 1 and 3 option by keeping table availability in mind.

Option: 1 Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
——————————————————————————————
Collect status of all the indexes on the table:-
———————————————-
We will record Index status at one place, So that we get back them after completion of this exercise,

select index_name,status from dba_indexes where table_name like ‘&table_name’;

Move table in to same or new tablespace:
—————————————-----------------
In this step we will move fragmented table to same tablespace or from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from dba_segments and check if same or any other tablespace has same free space available. So, that we can move this table to same or new tablespace.

Steps to Move table in to same tablespace:
—————————————–-----------------------
alter table <table_name> move; ——> Move to same tablespace

Steps to Move table in to new tablespace:
—————————————----------------------
alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

Now,Rebuild all indexes:
———————–-------------
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME
——– ——————————
UNUSABLE INDEX_NAME ——-> Here, value in status field may be valid or unusable.

SQL> alter index <INDEX_NAME> rebuild online; ——-> Use this command for each index
Index altered.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME
——– ——————————
VALID INDEX_NAME ——-> Here, value in status field must be valid.

Gather table stats:
——————----------
SQL> exec dbms_stats.gather_table_stats(‘&owner_name’,’&table_name’);
PL/SQL procedure successfully completed.

Check Table size:
—————–---------
Now again check table size using and will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

Check for Fragmentation in table:
——————————–----------------
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/

Option: 3 Shrink command (fron Oracle 10g):-
——————————————

Shrink command:
————–------------
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table <table_name> enable row movement;
Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:
———————————–———————————–
Part 1: Rearrange (All DML’s can happen during this time)
SQL> alter table <table_name> shrink space compact;
Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table <table_name> shrink space;
Table altered.

2. Directly reset the HWM:
————————-———————————–
SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.

Advantages over the conventional methods are:
——————————————–———————————–
1. Unlike “alter table move ..”,indexes are not in UNUSABLE state.After shrink command,indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.

Saturday, 9 March 2024

How to Diagnose Wrong Time -Wrong Time Zone- Oracle database

How to Diagnose Wrong Time

Issue Details:-

We had faced an issue of Wrong time zone in RAC environment after switchover done and application team informed that time was lagging behind by 5 hours as this application uses TZ=Europe/Berlin time zone but it was taking TZ=America/New_York

We started investigation:-

ORCLDB:

SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

DBTIME SESSIONTIMEZONE

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

+00:00 +01:00

sqlplus sys/******@ORCLDB as sysdba

[oracle@bms04dbaora02t admin]$ sqlplus sys/******@ORCLDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 10:49:12 2024

Version 19.10.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

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

07-MAR-24 10.49.30.206771 AM +01:00

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS') TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')

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

07/03/2024 04:50:42 07/03/2024 04:50:42 -05:00 ------------------ >>>> Issue

We have below two nodes in RAC

Node 1 :- ora04dba05p

Node 2 :- ora04dba06p

I went to below location and checked s_crsconfig_ora04dba05p_env.txt file and could see the TZ=America/New_York, this was the issue ..

cd /applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba05p install]$ cat s_crsconfig_ora04dba05p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

# characterset used for messages. For example, a new value can be

# configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

# the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

# can be specified by modifying the appropriate entries.

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=America/New_York

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

[grid@ora04dba06p install]$ cat s_crsconfig_ora04dba06p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=America/New_York

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

Solution :-

1) Modify the files with TZ=Europe/Berlin time

2) Restart the clusterware one by one

/applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba05p install]$ cat s_crsconfig_ora04dba05p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

# characterset used for messages. For example, a new value can be

# configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

# the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

# can be specified by modifying the appropriate entries.

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

root#ora04dba05p] crsctl stop crs

root#ora04dba05p] crsctl start crs

ora04dba06p:-

/applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba06p install]$ cat s_crsconfig_ora04dba06p_env.txt
#########################################################################
#This file can be used to set values for the NLS_LANG and TZ environment
#variables and to set resource limits for Oracle Clusterware and
#Database processes.
#1. The NLS_LANG environment variable determines the language and
#   characterset used for messages. For example, a new value can be
#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
#2. The Time zone setting can be changed by setting the TZ entry to
#   the appropriate time zone name. For example, TZ=America/New_York
#3. Resource limits for stack size, open files and number of processes
#   can be specified by modifying the appropriate entries.
#
#Do not modify this file except as documented above or under the
#direction of Oracle Support Services.
#########################################################################
TZ=Europe/Berlin
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
CRS_LIMIT_STACK=2048
CRS_LIMIT_OPENFILE=65536
CRS_LIMIT_NPROC=65536
TNS_ADMIN=


root#ora04dba06p] crsctl stop crs

root#ora04dba06p] crsctl start crs

now we are getting correct value

[oracle@bms04dbaora02t admin]$ sqlplus sys/*****#1234@ORCLDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 11:27:07 2024

Version 19.10.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS') TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')

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

07/03/2024 11:27:12 07/03/2024 11:27:12 +01:00 -------------------->>>> Issue Resolved

How to Diagnose Wrong Time ( SYSDATE and SYSTIMESTAMP) After DST Change , Server Reboot , Database Restart or Installation When Connecting to a Database on an Unix Server (Doc ID 1627439.1)

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

select current_timestamp from dual;

select systimestamp from dual;

SELECT DBTIMEZONE FROM DUAL;

SELECT SESSIONTIMEZONE FROM DUAL;

SELECT DBTIMEZONE FROM DUAL

SELECT SESSIONTIMEZONE FROM DUAL

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file - Temp file issue

We were facing below error :-

SQL> select file_name from dba_temp_files;

select file_name from dba_temp_files

ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf'

SQL> select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name

ERROR at line 1:

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf'

Above datafile is dedicated for Tivoli user at database level.

SQL> select username, temporary_tablespace, default_tablespace from dba_users where username='TIVOLI';

username temporary_tabespace default_tablespace

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

TIVOLI TIVOILTEMPPT TIVOLIORTS

Before proceeding let's create new temporary tablespace for TIVOLI user:-

SQL> create temporary tablespace IVOLITEMPT1 tempfile '+DATA/' size 2G;

Tablespace created.

Assigned IVOLITEMPT1 temp tablespace to user TIVOLI:-

SQL> alter user tivoli temporary tablespace TIVOLITEMPT1;

User altered.

You can confirm by executing below query:-

SQL> select temporary_tablespace from dba_users where username='TIVOLI';

TEMPORARY_TABLESPACE

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

TIVOLITEMPT1

1 row selected.

SQL> select file#,status,name from v$tempfile;

FILE# STATUS NAME

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

1 ONLINE +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

2 ONLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

3 ONLINE +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL> select username,TEMPORARY_TABLESPACE,DEFAULT_TABLESPACE from dba_users where username='TIVOLI';

USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE

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

TIVOLI TIVOLITEMPT1 TIVOLIORTS

tivoliortstempt01.dbf --This is culprit datafile so let's drop it

SQL> alter database tempfile '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf' offline ;

Database altered.

SQL> select file#,status,name from v$tempfile;

FILE# STATUS NAME

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

1 ONLINE +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

2 OFFLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

3 ONLINE +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL>

SQL> select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME FILE_NAME

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

TEMP +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

TIVOLITEMPT1 +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

TIVOLITEMPT +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

3 rows selected.

SQL> select file#,status,name from v$tempfile;

FILE# STATUS NAME

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

1 ONLINE +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

2 OFFLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

3 ONLINE +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL>

SQL> alter database tempfile '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf' drop including datafiles;

Database altered.

SQL>

SQL> select file_name from dba_temp_files;

FILE_NAME

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

+DATA/ORCLDB/TEMPFILE/temp.308.1160136011

+DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

2 rows selected.

SQL>

SQL> select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED INST_ID

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

TEMP 3.4084E+10 65011712 3.4069E+10 SHARED

TIVOLITEMPT1 2147483648 1048576 2146435072 SHARED

2 rows selected.

SQL>

Monday, 22 January 2024

Oracle error from auxiliary database: ORA-01275 - Dataguard duplication

We had completed our Standby duplication but we received below errors.

Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: warning: All redo log files were not defined properly.

Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: warning: All redo log files were not defined properly.

Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: warning: All redo log files were not defined properly.

Finished Duplicate Db at 18-JAN-24

released channel: CH1

released channel: CH2

Recovery Manager complete.

[oracle@dms04dbaorcl05p ORCLDB1_clone]$

[oracle@dms04dbaorcl05p ORCLDB1_clone]$ sqlplus / as sysdba

bash: sqlplus: command not found...

[oracle@dms04dbaorcl05p ORCLDB1_clone]$

[oracle@dms04dbaorcl05p ORCLDB1_clone]$ ps -ef | grep pmon

grid 60809 1 0 Jan17 ? 00:00:09 asm_pmon_+ASM1

oracle 272728 1 0 Jan18 ? 00:02:32 ora_pmon_ORCLDB1

oracle 371879 371331 0 05:22 pts/0 00:00:00 grep --color=auto pmon

[oracle@dms04dbaorcl05p ORCLDB1_clone]$

[oracle@dms04dbaorcl05p ORCLDB1_clone]$ . oraenv

ORACLE_SID = [oracle] ? ORCLDB1

The Oracle base has been set to /applications/oracle/d19.3.0/oracle_base

[oracle@dms04dbaorcl05p ORCLDB1_clone]$

[oracle@dms04dbaorcl05p ORCLDB1_clone]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 05:23:03 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> SELECT ARCH.THREAD# "Thread",

ARCH.SEQUENCE# "Last Sequence Received",

APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4 5 6 7 8

no rows selected

SQL>

SQL> col host_name for a15;

set lines 200 pages 200;

col INSTANCE_NAME for a15;

col PROTECTION_MODE for a20;

col STATUS for a8;

col INSTANCE_ROLE for a18;

col DATABASE_STATUS for a15;

col DATABASE_ROLE for a18;

col VERSION for a15;

col VERSION_FULL for a15

select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;

INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE

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

ORCLDB1 dms04dbaorcl05p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.21.0.0.0 PHYSICAL STANDBY

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTE 0

DGRD 0 ALLOCATE 0

ARCH 0 CONNECTE 0

6 rows selected.

SQL>

Starting MRP

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 CONNECTED 0

ARCH 0 CONNECTED 0

MRP0 1 WAIT_FOR_GAP 74461

DGRD 0 ALLOCATED 0

8 rows selected.

SQL>

[grid@dms04dbaorcl05p ~]$ . oraenv

ORACLE_SID = [grid] ? +ASM1

The Oracle base has been set to /applications/oracle/g19.3.0/grid_base

[grid@dms04dbaorcl05p ~]$

[grid@dms04dbaorcl05p ~]$ asmcmd

ASMCMD>

ASMCMD> lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 512000 511860 0 511860 0 N ARCH/

MOUNTED EXTERN N 512 512 4096 4194304 14680064 3644996 0 3644996 0 N DATA01/

MOUNTED NORMAL N 512 512 4096 4194304 30720 29684 10240 9722 0 Y OCRVD/

ASMCMD>

ASMCMD> cd

usage: cd <dir>

help: help cd

ASMCMD>

ASMCMD> cd DATA01/

ASMCMD>

ASMCMD> ls

ORCLDB/

ASMCMD>

ASMCMD> cd ORCLDB/

ASMCMD>

ASMCMD> ls -lrt

WARNING: option 'r' is deprecated for 'ls'

please use 'reverse'

Type Redund Striped Time Sys Name

Y CONTROLFILE/

Y DATAFILE/

N DATAGUARDCONFIG/

N ORCLDBREDO/

N ORCLDBSRL/

N AAA_AT/

N AAA_BE/

N AAA_CH/

N AAA_ES/

N AAA_NL/

N AAA_TR/

Y ONLINELOG/

CONTROLFILE UNPROT FINE JAN 18 14:00:00 N control01.ctl => +DATA01/ORCLDB/CONTROLFILE/current.256.1158588415

CONTROLFILE UNPROT FINE JAN 18 14:00:00 N control02.ctl => +DATA01/ORCLDB/CONTROLFILE/current.257.1158588431

DATAFILE UNPROT COARSE JAN 19 05:00:00 N AAA_PL01.dbf => +DATA01/ORCLDB/DATAFILE/AAA_PL.269.1158588449

DATAFILE UNPROT COARSE JAN 19 05:00:00 N fom_se_01.dbf => +DATA01/ORCLDB/DATAFILE/FOM_SE.261.1158588447

DATAFILE UNPROT COARSE JAN 19 05:00:00 N gg_tbs.dbf => +DATA01/ORCLDB/DATAFILE/GG_TBS.270.1158588453

DATAFILE UNPROT COARSE JAN 19 05:00:00 N MC_D01.dbf => +DATA01/ORCLDB/DATAFILE/MC_D.273.1158588457

DATAFILE UNPROT COARSE JAN 19 05:00:00 N returns01.dbf => +DATA01/ORCLDB/DATAFILE/RETURNS.272.1158588457

DATAFILE UNPROT COARSE JAN 19 05:00:00 N RAT_01.dbf => +DATA01/ORCLDB/DATAFILE/RAT.274.1158588459

DATAFILE UNPROT COARSE JAN 19 05:00:00 N sysaux01.dbf => +DATA01/ORCLDB/DATAFILE/SYSAUX.262.1158588447

DATAFILE UNPROT COARSE JAN 19 05:00:00 N sysaux02.dbf => +DATA01/ORCLDB/DATAFILE/SYSAUX.263.1158588447

DATAFILE UNPROT COARSE JAN 19 05:00:00 N system01.dbf => +DATA01/ORCLDB/DATAFILE/SYSTEM.271.1158588457

DATAFILE UNPROT COARSE JAN 19 05:00:00 N undotbs01.dbf => +DATA01/ORCLDB/DATAFILE/UNDOTBS1.258.1158588445

DATAFILE UNPROT COARSE JAN 19 05:00:00 N undotbs02.dbf => +DATA01/ORCLDB/DATAFILE/UNDOTBS2.265.1158588447

DATAFILE UNPROT COARSE JAN 19 05:00:00 N users01.dbf => +DATA01/ORCLDB/DATAFILE/USERS.275.1158588461

ASMCMD>

ASMCMD> cd ORCLDBREDO/

ASMCMD>

ASMCMD> ls -lrt

WARNING: option 'r' is deprecated for 'ls'

please use 'reverse'

Type Redund Striped Time Sys Name

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo01.redo => +DATA01/ORCLDB/ONLINELOG/group_1.276.1158643477

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo02.redo => +DATA01/ORCLDB/ONLINELOG/group_2.277.1158643479

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo03.redo => +DATA01/ORCLDB/ONLINELOG/group_3.278.1158643483

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo04.redo => +DATA01/ORCLDB/ONLINELOG/group_4.279.1158643485

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo05.redo => +DATA01/ORCLDB/ONLINELOG/group_5.280.1158643487

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo06.redo => +DATA01/ORCLDB/ONLINELOG/group_6.281.1158643491

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo07.redo => +DATA01/ORCLDB/ONLINELOG/group_7.282.1158643493

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo08.redo => +DATA01/ORCLDB/ONLINELOG/group_8.283.1158643495

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo09.redo => +DATA01/ORCLDB/ONLINELOG/group_9.284.1158643499

ONLINELOG UNPROT COARSE JAN 19 05:00:00 N redo10.redo => +DATA01/ORCLDB/ONLINELOG/group_10.285.1158643501

ASMCMD>

ASMCMD> cd ../ORCLDBSRL/

ASMCMD>

ASMCMD> ls -lrt

WARNING: option 'r' is deprecated for 'ls'

please use 'reverse'

ASMCMD>

ASMCMD> exit

[grid@dms04dbaorcl05p ~]$

[grid@dms04dbaorcl05p ~]$ ls -lrt

total 140

-rw-r--r--. 1 grid oinstall 11412 Jan 16 12:27 cvuqdisk-1.0.10-1.rpm

-rw-r--r--. 1 grid oinstall 27092 Jan 16 12:27 oracleasmlib-2.0.17-1.el8.x86_64.rpm

-rw-r--r--. 1 grid oinstall 99852 Jan 16 12:27 oracleasm-support-2.1.12-1.el8.x86_64.rpm

drwxr-xr-x. 9 grid oinstall 142 Jan 16 12:58 CVU

[grid@dms04dbaorcl05p ~]$

[grid@dms04dbaorcl05p ~]$ exit

logout

[oracle@dms04dbaorcl05p ORCLDB1_clone]$

[oracle@dms04dbaorcl05p ORCLDB1_clone]$ cd $ORACLE_HOME

[oracle@dms04dbaorcl05p oracle_home]$

[oracle@dms04dbaorcl05p oracle_home]$ cd dbs

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 36

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r----- 1 oracle oinstall 2560 Jan 18 12:23 orapwdORCLDB1_old

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 orapwORCLDB1

-rw-r----- 1 oracle asmadmin 9728 Jan 18 14:07 spfileORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 05:24 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ mv orapwORCLDB1 orapwORCLDB1_clone_created

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ mv orapwdORCLDB1_old orapwdORCLDB1

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 05:32:53 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 19 05:33:29 2024

Version 19.21.0.0.0

connected to target database: ORCL (DBID=2127777777, not open)

Note:- Checked the count of database files with production database.

RMAN> report schema;

using target database control file instead of recovery catalog

RMAN-06139: warning: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name ORCLDB

List of Permanent Datafiles

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

File Size(MB) Tablespace RB segs Datafile Name

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

1 1580 SYSTEM *** +DATA01/ORCLDB/system01.dbf

2 500 MC_D *** +DATA01/ORCLDB/MC_D01.dbf

3 32767 SYSAUX *** +DATA01/ORCLDB/sysaux01.dbf

4 32767 UNDOTBS1 *** +DATA01/ORCLDB/undotbs01.dbf

5 23424 UNDOTBS2 *** +DATA01/ORCLDB/undotbs02.dbf

7 5 USERS *** +DATA01/ORCLDB/users01.dbf

8 1024 RETURNS *** +DATA01/ORCLDB/returns01.dbf

9 543260 FOM_SE *** +DATA01/ORCLDB/fom_se_01.dbf

10 2048 GG_TBS *** +DATA01/ORCLDB/gg_tbs.dbf

11 1557844 AAA_CH *** +DATA01/ORCLDB/AAA_CH/AAA_CH_01.dbf

12 2481044 AAA_ES *** +DATA01/ORCLDB/AAA_ES/AAA_ES_01.dbf

13 1024 RAT *** +DATA01/ORCLDB/RAT_01.dbf

14 793647 AAA_TR *** +DATA01/ORCLDB/AAA_TR/AAA_TR_01.dbf

15 2011217 AAA_AT *** +DATA01/ORCLDB/AAA_AT/AAA_AT_01.dbf

16 5727 SYSAUX *** +DATA01/ORCLDB/sysaux02.dbf

17 1972610 AAA_NL *** +DATA01/ORCLDB/AAA_NL/AAA_NL_01.dbf

18 1549342 AAA_BE *** +DATA01/ORCLDB/AAA_BE/AAA_BE01.dbf

19 4058 AAA_PL *** +DATA01/ORCLDB/AAA_PL01.dbf

List of Temporary Files

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

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 23306 TEMP 32767 +DATA01/ORCLDB/temp01.dbf

2 2349 TEMP 31744 +DATA01/ORCLDB/temp02.dbf

RMAN> exit

Recovery Manager complete.

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 05:37:52 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL>

SQL> set line 200

SQL> col MEMBER for a35

SQL> select thread#, group#, sequence#, bytes, archived, s.status , blocksize, member FROM v$standby_log s join v$logfile using (GROUP#) order by thread#, group#;

SQL> col MEMBER for a35

SQL>

THREAD# GROUP# SEQUENCE# BYTES ARC STATUS BLOCKSIZE MEMBER

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

1 13 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl13.redo

1 14 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl14.redo

1 15 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl15.redo

1 16 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl16.redo

1 17 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl17.redo

1 18 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl18.redo

2 20 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl20.redo

2 21 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl21.redo

2 22 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl22.redo

2 23 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl23.redo

2 24 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl24.redo

2 25 0 2147483648 YES UNASSIGNED 512 +DATA01/ORCLDB/ORCLDBSRL/srl25.redo

12 rows selected.

As you can see standby is unassigned status means it is not used by standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter system set standby_file_management=manual scope=spfile;

System altered.

SQL>

SQL> alter system set standby_file_management=manual scope=both;

System altered.

SQL>

SQL> alter database clear logfile group 13;

Database altered.

SQL> alter database clear logfile group 14;

alter database clear logfile group 15;

alter database clear logfile group 16;

alter database clear logfile group 17;

alter database clear logfile group 18;

alter database clear logfile group 20;

alter database clear logfile group 21;

alter database clear logfile group 22;

alter database clear logfile group 23;

alter database clear logfile group 24;

alter database clear logfile group 25;

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL> SQL> SQL>

SQL>exit

[oracle@dms04dbaorcl05p ~]$ su - grid

Password:

[grid@dms04dbaorcl05p ~]$

[grid@bms04dbafom01p ~]$ . oraenv

ORACLE_SID = [grid] ? +ASM1

The Oracle base has been set to /applications/oracle/g19.3.0/grid_base

[grid@dms04dbaorcl05p ~]$ asmcmd

ASMCMD>

ASMCMD> lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED NORMAL N 512 512 4096 4194304 409600 195272 0 97636 0 N ARCH/

MOUNTED NORMAL N 512 512 4096 4194304 25600000 3271112 1024000 1123556 0 N DATA01/

MOUNTED NORMAL N 512 512 4096 4194304 30720 29668 10240 9714 0 Y OCRVD/

ASMCMD>

ASMCMD> cd DATA01/

ASMCMD>

ASMCMD> ls

ORCL/

redo09.log

redo10.log

redo11.log

redo12.log

redo13.log

redo14.log

snapcf_ORCL1.f

ASMCMD>

ASMCMD> cd ORCLDB/

ASMCMD>

ASMCMD> ls

CHANGETRACKING/

CONTROLFILE/

DATAFILE/

DATAGUARDCONFIG/

ORCLDBREDO/

ORCLDBSRL/

AAA_AT/

AAA_BE/

AAA_CH/

AAA_ES/

AAA_NL/

AAA_PL01.dbf

FOM_SE_01.dbf

AAA_TR/

MC_D01.dbf

ONLINELOG/

PARAMETERFILE/

PASSWORD/

RETURNS01.dbf

RAT_01.dbf

TEMPFILE/

bct/

control01.ctl

control02.ctl

gg_tbs.dbf

redo01.log

redo02.log

redo03.log

redo04.log

redo05.log

redo06.log

redo07.log

redo08.log

sysaux01.dbf

sysaux02.dbf

system01.dbf

temp01.dbf

temp02.dbf

undotbs01.dbf

undotbs02.dbf

users01.dbf

ASMCMD> cd ORCLDBSRL/

ASMCMD>

ASMCMD> ls

srl13.redo

srl14.redo

srl15.redo

srl16.redo

srl17.redo

srl18.redo

srl20.redo

srl21.redo

srl22.redo

srl23.redo

srl24.redo

srl25.redo

SQL>

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

After starting MRP also we are getting the ORA error in DG alert logfile

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

PR00 (PID:403960): FAL: Error 16191 connecting to ORCL for fetching gap sequence

2024-01-19T08:59:50.148440+01:00

Errors in file /applications/oracle/d19.3.0/oracle_base/diag/rdbms/ORCLDB/ORCLDB1/trace/ORCLDB1_pr00_403960.trc:

ORA-16191: Primary log shipping client not logged on standby

2024-01-19T09:00:04.314910+01:00

PR00 (PID:403960): Error 1017 received logging on to the standby

PR00 (PID:403960): -------------------------------------------------------------------------

PR00 (PID:403960): Check that the source and target databases are using a password file

PR00 (PID:403960): and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

PR00 (PID:403960): and that the SYS password is same in the password files,

PR00 (PID:403960): returning error ORA-16191

PR00 (PID:403960): -------------------------------------------------------------------------

PR00 (PID:403960): FAL: Error 16191 connecting to ORCL for fetching gap sequence

2024-01-19T09:00:04.315258+01:00

Errors in file /applications/oracle/d19.3.0/oracle_base/diag/rdbms/ORCLDB/ORCLDB1/trace/ORCLDB1_pr00_403960.trc:

ORA-16191: Primary log shipping client not logged on standby

2024-01-19T09:00:18.519421+01:00

PR00 (PID:403960): Error 1017 received logging on to the standby

PR00 (PID:403960): -------------------------------------------------------------------------

PR00 (PID:403960): Check that the source and target databases are using a password file

PR00 (PID:403960): and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

PR00 (PID:403960): and that the SYS password is same in the password files,

PR00 (PID:403960): returning error ORA-16191

PR00 (PID:403960): -------------------------------------------------------------------------

PR00 (PID:403960): FAL: Error 16191 connecting to ORCL for fetching gap sequence

2024-01-19T09:00:18.519855+01:00

Errors in file /applications/oracle/d19.3.0/oracle_base/diag/rdbms/ORCLDB/ORCLDB1/trace/ORCLDB1_pr00_403960.trc:

ORA-16191: Primary log shipping client not logged on standby

2024-01-19T09:00:32.713383+01:00

PR00 (PID:403960): Error 1017 received logging on to the standby

PR00 (PID:403960): -------------------------------------------------------------------------

Then we have again checked password file and error was still persist in prod

[oracle@bms04dbafom01p ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 08:57:53 2024

Version 19.14.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.14.0.0.0

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

THREAD# DEST_ID STATUS

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

ERROR FAIL_SEQUENCE

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

1 1 VALID

1 2 VALID

1 3 ERROR

ORA-01033: ORACLE initialization or shutdown in progress 0

THREAD# DEST_ID STATUS

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

ERROR FAIL_SEQUENCE

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

2 1 VALID

2 2 VALID

2 3 ERROR

ORA-01033: ORACLE initialization or shutdown in progress 0

6 rows selected.

SQL>

After this, we have cancelled the recovery and again copy the password file from production

From :- Production

ASMCMD>

ASMCMD> pwcopy pwdORCL.256.1097485033 /home/grid

copying +DATA01/ORCL/PASSWORD/pwdORCL.256.1097485033 -> /home/grid/pwdORCL.256.1097485033

ASMCMD>

[grid@bms04dbafom01p ~]$ ls -lrt

total 16

drwxr-xr-x 25 grid oinstall 4096 Feb 22 2022 cuv

-rw-r--r-- 1 grid oinstall 200 Feb 25 2022 patch_list_gihome.txt

-rw-r----- 1 grid oinstall 2560 Jan 18 12:18 orapwdORCL

-rw-r----- 1 grid oinstall 2560 Jan 19 09:38 pwdORCL.256.1097485033

SCP the password file

[grid@bms04dbafom01p ~]$ scp pwdORCL.256.1097485033 grid@192.168.1.39:/tmp

grid@192.168.1.39's password:

pwdORCL.256.1097485033 100% 2560 1.3MB/s 00:00

[grid@bms04dbafom01p ~]$ exit

logout

[oracle@bms04dbafom01p dbs]$

on DR Side :--------

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ rm -rf orapwORCLDB1 orapwdORCLDB

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 36

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r----- 1 oracle oinstall 2560 Jan 18 12:23 orapwdORCLDB1

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 orapwORCLDB1_clone_created

-rw-r----- 1 oracle asmadmin 9728 Jan 19 08:35 spfileORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 08:59 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ cksum orapwdORCLDB1

667708259 2560 orapwdORCLDB1

[oracle@dms04dbaorcl05p dbs]$/tmp pwdORCL.256.1097485033 $ORACLE_HOME

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$mv pwdORCL.256.1097485033 orapwdORCLDB1

then start the recover .....recovery process started

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 1 WAIT_FOR_GAP 74461

RFS 2 IDLE 0

RFS 1 IDLE 0

RFS 1 IDLE 74643

RFS 2 IDLE 61985

11 rows selected.

You can see that there is huge archive log GAP here

so we decided to resolve this issue using Service Name

THEN NEXT STEP IS PROCEED FOR RECOVERY USING SERVICE NAME

[oracle@dms04dbaorcl05p tmp]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 10:34:56 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL>

SQL> col host_name for a15;

set lines 200 pages 200;

col INSTANCE_NAME for a15;

col PROTECTION_MODE for a20;

col STATUS for a8;

col INSTANCE_ROLE for a18;

SQL> col DATABASE_STATUS for a15;

col DATABASE_ROLE for a18;

col VERSION for a15;

col VERSION_FULL for a15

select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SP2-0734: unknown command beginning "SQL> col D..." - rest of line ignored.

SQL> SQL> SQL> SQL>

INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE

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

ORCLDB1 dms04dbaorcl05p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.21.0.0.0 PHYSICAL STANDBY

SQL>

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 19 10:35:54 2024

Version 19.21.0.0.0

connected to target database: ORCL (DBID=2127777777, not open)

RMAN>

RMAN> run

{

allocate channel C1 device type disk;

allocate channel C2 device type disk;

RECOVER STANDBY DATABASE FROM SERVICE ORCL;

};2> 3> 4> 5> 6>

using target database control file instead of recovery catalog

allocated channel: C1

channel C1: SID=510 device type=DISK

allocated channel: C2

channel C2: SID=1012 device type=DISK

Starting recover at 19-JAN-24

Oracle instance started

Total System Global Area 118648468256 bytes

Fixed Size 18379552 bytes

Variable Size 13421772800 bytes

Database Buffers 104958263296 bytes

Redo Buffers 250052608 bytes

contents of Memory Script:

{

restore standby controlfile from service 'ORCL';

alter database mount standby database;

}

executing Memory Script

Starting restore at 19-JAN-24

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1011 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output file name=+DATA01/ORCLDB/control01.ctl

output file name=+DATA01/ORCLDB/control02.ctl

Finished restore at 19-JAN-24

released channel: ORA_DISK_1

Statement processed

Executing: alter system set standby_file_management=manual

contents of Memory Script:

{

recover database from service 'ORCL';

}

executing Memory Script

Starting recover at 19-JAN-24

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=5538 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: NMDA Oracle v19.7.1.0

allocated channel: ORA_SBT_TAPE_2

channel ORA_SBT_TAPE_2: SID=508 device type=SBT_TAPE

channel ORA_SBT_TAPE_2: NMDA Oracle v19.7.1.0

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1012 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1515 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=2018 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=5037 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=4533 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=5038 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=5539 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=8 device type=DISK

allocated channel: ORA_DISK_9

channel ORA_DISK_9: SID=509 device type=DISK

allocated channel: ORA_DISK_10

channel ORA_DISK_10: SID=1013 device type=DISK

RMAN-06900: warning: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

RMAN-06901: warning: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows

Oracle error from target database:

ORA-19922: there is no parent row with id 0 and level 2

channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore

channel ORA_SBT_TAPE_1: using network backup set from service ORCL

destination for restore of datafile 00001: +DATA01/ORCLDB/system01.dbf

channel ORA_SBT_TAPE_2: starting incremental datafile backup set restore

channel ORA_SBT_TAPE_2: using network backup set from service ORCL

destination for restore of datafile 00002: +DATA01/ORCLDB/MC_D01.dbf

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

destination for restore of datafile 00003: +DATA01/ORCLDB/sysaux01.dbf

channel ORA_DISK_2: starting incremental datafile backup set restore

channel ORA_DISK_2: using network backup set from service ORCL

destination for restore of datafile 00004: +DATA01/ORCLDB/undotbs01.dbf

channel ORA_DISK_3: starting incremental datafile backup set restore

channel ORA_DISK_3: using network backup set from service ORCL

destination for restore of datafile 00005: +DATA01/ORCLDB/undotbs02.dbf

channel ORA_DISK_4: starting incremental datafile backup set restore

channel ORA_DISK_4: using network backup set from service ORCL

destination for restore of datafile 00007: +DATA01/ORCLDB/users01.dbf

channel ORA_DISK_5: starting incremental datafile backup set restore

channel ORA_DISK_5: using network backup set from service ORCL

destination for restore of datafile 00008: +DATA01/ORCLDB/returns01.dbf

channel ORA_DISK_6: starting incremental datafile backup set restore

channel ORA_DISK_6: using network backup set from service ORCL

destination for restore of datafile 00009: +DATA01/ORCLDB/fom_se_01.dbf

channel ORA_DISK_7: starting incremental datafile backup set restore

channel ORA_DISK_7: using network backup set from service ORCL

destination for restore of datafile 00010: +DATA01/ORCLDB/gg_tbs.dbf

channel ORA_DISK_8: starting incremental datafile backup set restore

channel ORA_DISK_8: using network backup set from service ORCL

destination for restore of datafile 00011: +DATA01/ORCLDB/AAA_CH/AAA_CH_01.dbf

channel ORA_DISK_9: starting incremental datafile backup set restore

channel ORA_DISK_9: using network backup set from service ORCL

destination for restore of datafile 00012: +DATA01/ORCLDB/AAA_ES/AAA_ES_01.dbf

channel ORA_DISK_10: starting incremental datafile backup set restore

channel ORA_DISK_10: using network backup set from service ORCL

destination for restore of datafile 00013: +DATA01/ORCLDB/RAT_01.dbf

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07

channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore

channel ORA_SBT_TAPE_1: using network backup set from service ORCL

destination for restore of datafile 00014: +DATA01/ORCLDB/AAA_TR/AAA_TR_01.dbf

channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:00:06

channel ORA_SBT_TAPE_2: starting incremental datafile backup set restore

channel ORA_SBT_TAPE_2: using network backup set from service ORCL

destination for restore of datafile 00015: +DATA01/ORCLDB/AAA_AT/AAA_AT_01.dbf

channel ORA_DISK_4: restore complete, elapsed time: 00:00:05

channel ORA_DISK_4: starting incremental datafile backup set restore

channel ORA_DISK_4: using network backup set from service ORCL

destination for restore of datafile 00016: +DATA01/ORCLDB/sysaux02.dbf

channel ORA_DISK_5: restore complete, elapsed time: 00:00:06

channel ORA_DISK_5: starting incremental datafile backup set restore

channel ORA_DISK_5: using network backup set from service ORCL

destination for restore of datafile 00017: +DATA01/ORCLDB/AAA_NL/AAA_NL_01.dbf

channel ORA_DISK_6: restore complete, elapsed time: 00:00:05

channel ORA_DISK_6: starting incremental datafile backup set restore

channel ORA_DISK_6: using network backup set from service ORCL

destination for restore of datafile 00018: +DATA01/ORCLDB/AAA_BE/AAA_BE01.dbf

channel ORA_DISK_7: restore complete, elapsed time: 00:00:05

channel ORA_DISK_7: starting incremental datafile backup set restore

channel ORA_DISK_7: using network backup set from service ORCL

destination for restore of datafile 00019: +DATA01/ORCLDB/AAA_PL01.dbf

channel ORA_DISK_2: restore complete, elapsed time: 00:00:16

channel ORA_DISK_10: restore complete, elapsed time: 00:00:12

channel ORA_DISK_3: restore complete, elapsed time: 00:00:34

channel ORA_DISK_4: restore complete, elapsed time: 00:00:28

channel ORA_DISK_1: restore complete, elapsed time: 00:00:55

channel ORA_DISK_7: restore complete, elapsed time: 00:01:26

channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:02:49

channel ORA_DISK_6: restore complete, elapsed time: 00:03:07

channel ORA_DISK_8: restore complete, elapsed time: 00:04:01

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:39

channel ORA_DISK_5: restore complete, elapsed time: 00:07:37

channel ORA_DISK_9: restore complete, elapsed time: 00:14:31

starting media recovery

archived log for thread 2 with sequence 61987 is already on disk as file +ARCH/ORCLDB/ARCHIVELOG/2024_01_19/thread_2_seq_61987.285.1158663141

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

Finished recover at 19-JAN-24

Executing: alter system set standby_file_management=auto

Finished recover at 19-JAN-24

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found ";": expecting one of: "advise, allocate, alter, analyze, associate statistics, audit, backup, begin, @, call, catalog, change, comment, commit, configure, connect, convert, copy, create, create catalog, create global, create or replace global, create or replace script, create script, crosscheck, declare, delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback, flashback table, grant, grant catalog, grant register, host, import, insert, list, lock, merge, mount, noaudit, open, print, purge, quit, recover, register, release"

RMAN-01007: at line 0 column 2 file: standard input

RMAN>

RMAN> exit

Recovery Manager complete.

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 10:53:38 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> show parameter standby

NAME TYPE VALUE

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

standby_file_management string AUTO

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

ARCH 2 CLOSING 61987

RFS 1 IDLE 74645

RFS 1 IDLE 0

RFS 2 IDLE 0

RFS 2 RECEIVING 61988

MRP0 2 APPLYING_LOG 61988

11 rows selected.

SQL> col host_name for a15;

set lines 200 pages 200;

col INSTANCE_NAME for a15;

col PROTECTION_MODE for a20;

col STATUS for a8;

col INSTANCE_ROLE for a18;

col DATABASE_STATUS for a15;

col DATABASE_ROLE for a18;

col VERSION for a15;

col VERSION_FULL for a15

select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL>

INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE

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

ORCLDB1 dms04dbaorcl05p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.21.0.0.0 PHYSICAL STANDBY

You can check the Archive log gap now

SQL>

SQL> SELECT ARCH.THREAD# "Thread",

ARCH.SEQUENCE# "Last Sequence Received",

APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74644 74644 0

2 61987 61987 0

SQL>

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74644 74644 0

2 61987 61987 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74647 74647 0

2 61990 61990 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74647 74647 0

2 61990 61990 0

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p tmp]$

SQL> select PROCESS,STATUS,SEQUENCE# from v$managed_standby;

select thread#, group#, sequence#, bytes, archived, s.status , blocksize, member FROM v$standby_log s join v$logfile using (GROUP#) order by thread#, group#;

SELECT thread#, group#, sequence#, bytes, archived ,l.status, blocksize, member FROM v$log l join v$logfile f using (GROUP#) ORDER BY thread#, group#;

select to_char(TIMESTAMP,'dd-mon-yyyy hh24:mi:ss'),MESSAGE from v$dataguard_status where DEST_ID=3;

SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

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

Need to add second node in the cluster for ORCLDB

oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ clear

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ cd $ORACLE_HOME/dbs

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 36

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r----- 1 oracle asmadmin 9728 Jan 19 10:52 spfileORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 10:54 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ cp initORCLDB1.ora initORCLDB1.ora_before_clone

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ cp spfileORCLDB1.ora spfileORCLDB1.ora_after-clone-sp

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 52

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r----- 1 oracle asmadmin 9728 Jan 19 10:52 spfileORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 10:54 hc_ORCLDB1.dat

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ strings spfileORCLDB1.ora

*.audit_file_dest='/applications/oracle/d19.3.0/oracle_base/admin/ORCLDB/adump'

*.audit_trail='db'

*.cluster_database=FALSE

*.compatible='19.0.0'

*.control_file_record_keep_time=15

*.control_files='+DATA01/ORCLDB/control01.ctl','+DATA01/ORCLDB/control02.ctl'#Restore Controlfile

*.core_dump_dest='/applications/oracle/d19.3.0/oracle_base'

*.cursor_sharing='FORCE'

*.db_block_size=8192

*.db_file_name_convert='+DATA01/ORCL','+DAT

A01/ORCLDB','+DATA01/ORCL/AAA_AT','+DATA01/ORCLDB/AAA_AT','+DATA01/ORCL/AAA_BE','+DATA01/ORCLDB/AAA_BE','+DATA01/ORCL/AAA_CH','+DATA01/ORCLDB/AAA_CH','+DATA01/ORCL/AAA_ES','+DATA01/ORCLDB/AAA_ES','+DATA01/ORCL/FOM_N','+DATA01/ORCLDB/AAA_NL','+DATA01/ORCL/AAA_TR','+DATA01/ORCLDB/AAA_TR'

*.db_name='ORCL'

*.db_unique_name='ORCLDB'

*.dg_broker_config_file1='+DATA01/ORCLDB1/DATAGUARDCONFIG/ORCLdg_config01.dat'

*.dg_broker_config_file2='+DATA01/ORCLDB1/DATAGUARDCONFIG/ORCLdg_config02.dat'

*.dg_broker_start=TRUE

*.diagnostic_dest='/applications/oracle/d19.3.0/oracle_base'

*.dispatchers='(protocol=TCP)(dispatchers=50)(service=FOM_SH)'

ORCL1.dispatchers='(protocol=TCP)(dispatchers=50)(service=FOM)'

ORCL2.dispatchers='(protocol=TCP)(dispatchers=50)(service=FOM)'

*.enable_goldengate_replication=TRUE

*.fal_client='ORCLDB'

*.fal_server='ORCL'

family:dw_helper.instance_mode='read-only'

ORCL2.instance_number=2

ORCL1.instance_number=1

*.job_queue_processes=3000

*.local_listener

='-oraagent-dummy-'

*.log_archive_config='DG_CONFIG=(ORCL,fomdg,ORCLDB)'

*.log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLDB'

*.log_archive_dest_2='service="fomdg"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="fomdg" net_timeout=30','valid_for=(online_logfile,all_roles)'

*.log_archive_dest_3='service=ORCLDB async NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=ORCLDB'

*.log_arc

hive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_dest_state_3='ENABLE'

*.log_archive_format='%t_%s_%r.arch'

*.log_file_name_convert='+DATA01/ORCL/ORCLREDO','+DATA01/ORCLDB/ORCLDBREDO/','+DATA01/ORCL/ORCLSRL','+DATA01/ORCLDB/ORCLDBSRL'

*.max_shared_servers=2000

*.nls_language='AMERICAN'

*.nls_numeric_characters=',.'

*.nls_terRATory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=37664m

*.processes=4000

*.remote_login_passwordfile='exclusive'

*.resource_

manager_plan='DEFAULT_PLAN'

*.sessions=6032

ORCL1.sga_max_size=274877906944

ORCL2.sga_max_size=274877906944

*.sga_target=112990m

ORCL1.sga_target=274877906944

ORCL2.sga_target=274877906944

*.shared_servers=500

ORCL1.shared_servers=1000

ORCL2.shared_servers=1000

*.standby_file_management='AUTO'

ORCL2.thread=2

ORCL1.thread=1

*.transactions=6635

ORCL1.undo_tablespace='UNDOTBS1'

ORCL2.undo_tablespace='UNDOTBS2'

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 52

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r----- 1 oracle asmadmin 9728 Jan 19 10:52 spfileORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 10:54 hc_ORCLDB1.dat

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 11:32:08 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> show parameter broker

NAME TYPE

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

VALUE

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

connection_brokers string

((TYPE=DEDICATED)(BROKERS=1)),

((TYPE=EMON)(BROKERS=1))

dg_broker_config_file1 string

+DATA01/ORCLDB1/DATAGUARDCONFI

G/ORCLdg_config01.dat

dg_broker_config_file2 string

+DATA01/ORCLDB1/DATAGUARDCONFI

G/ORCLdg_config02.dat

NAME TYPE

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

VALUE

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

dg_broker_start boolean

TRUE

use_dedicated_broker boolean

FALSE

SQL>

SQL> show parameter db_u

NAME TYPE

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

VALUE

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

db_ultra_safe string

OFF

db_unique_name string

ORCLDB

db_unrecoverable_scn_tracking boolean

TRUE

SQL>

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 52

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r----- 1 oracle asmadmin 9728 Jan 19 10:52 spfileORCLDB1.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 11:34 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$ rm -rf spfileORCLDB1.ora

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 40

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 11:34 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 11:35:33 2024

Version 19.21.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1.6106E+11 bytes

Fixed Size 18372720 bytes

Variable Size 2.0401E+10 bytes

Database Buffers 1.4012E+11 bytes

Redo Buffers 518496256 bytes

SQL>

SQL> alter database mount

2 ;

Database altered.

SQL>

SQL> show parameter file spfile;

NAME TYPE

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

VALUE

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

audit_file_dest string

/applications/oracle/d19.3.0/o

racle_base/admin/ORCLDB/adump

control_file_record_keep_time integer

control_files string

+DATA01/ORCLDB/control01.ctl,

+DATA01/ORCLDB/control02.ctl

db_create_file_dest string

NAME TYPE

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

VALUE

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

db_file_multiblock_read_count integer

128

db_file_name_convert string

+DATA01/ORCL, +DATA01/ORCLDB,

+DATA01/ORCL/AAA_AT, +DATA01/F

OMPDB/AAA_AT, +DATA01/ORCL/FOM

_BE, +DATA01/ORCLDB/AAA_BE, +D

ATA01/ORCL/AAA_CH, +DATA01/FOM

NAME TYPE

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

VALUE

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

PDB/AAA_CH, +DATA01/ORCL/FOM_E

S, +DATA01/ORCLDB/AAA_ES, +DAT

A01/ORCL/FOM_N, +DATA01/ORCLDB

/AAA_NL, +DATA01/ORCL/AAA_TR,

+DATA01/ORCLDB/AAA_TR

db_files integer

200

db_flash_cache_file string

NAME TYPE

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

VALUE

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

db_performance_profile string

db_recovery_file_dest string

db_recovery_file_dest_size big integer

db_securefile string

PREFERRED

dg_broker_config_file1 string

NAME TYPE

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

VALUE

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

+DATA01/ORCLDB/DATAGUARDCONFIG

/ORCLdg_config01.dat

dg_broker_config_file2 string

+DATA01/ORCLDB/DATAGUARDCONFIG

/ORCLdg_config02.dat

file_mapping boolean

FALSE

fileio_network_adapters string

NAME TYPE

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

VALUE

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

filesystemio_options string

none

ifile file

log_file_name_convert string

+DATA01/ORCL/ORCLREDO, +DATA01

/ORCLDB/ORCLDBREDO/, +DATA01/F

OMP/ORCLSRL, +DATA01/ORCLDB/FO

MPDBSRL

NAME TYPE

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

VALUE

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

max_dump_file_size string

unlimited

pdb_file_name_convert string

remote_login_passwordfile string

EXCLUSIVE

remote_recovery_file_dest string

session_max_open_files integer

NAME TYPE

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

VALUE

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

spfile string

standby_file_management string

AUTO

standby_pdb_source_file_dblink string

standby_pdb_source_file_directory string

NAME TYPE

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

VALUE

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

tracefile_identifier string

SQL> show parameter show parameter file

SQL>

SQL> show parameter pfile

NAME TYPE

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

VALUE

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

spfile string

SQL> !pwd

/applications/oracle/d19.3.0/oracle_home/dbs

SQL>

SQL> !ls -lrt

total 40

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 11:36 hc_ORCLDB1.dat

SQL>

SQL> create spfile='+DATA01/ORCLDB/PARAMETERFILE/spfileORCLDB.ora' from pfile='/applications/oracle/d19.3.0/oracle_home/dbs/initORCLDB1.ora';

File created.

SQL>

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 40

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 11:42 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ mv initORCLDB1.ora initORCLDB1.ora_onceagain

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ vi initORCLDB1.ora

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 11:44:04 2024

Version 19.21.0.0.0

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.6106E+11 bytes

Fixed Size 18372720 bytes

Variable Size 2.0401E+10 bytes

Database Buffers 1.4012E+11 bytes

Redo Buffers 518496256 bytes

SQL>

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

System altered.

SQL>

SQL> show parameter cluster_database

NAME TYPE

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

VALUE

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

cluster_database boolean

FALSE

cluster_database_instances integer

SQL>

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1,6106E+11 bytes

Fixed Size 18372720 bytes

Variable Size 2,0401E+10 bytes

Database Buffers 1,4012E+11 bytes

Redo Buffers 518496256 bytes

SQL>

SQL> show parameter cluster_database

NAME TYPE

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

VALUE

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

cluster_database boolean

TRUE

cluster_database_instances integer

SQL>

SQL> shut immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ps -ef | grep pmon

grid 60809 1 0 Jan17 ? 00:00:10 asm_pmon_+ASM1

oracle 424578 395360 0 11:53 pts/4 00:00:00 grep --color=auto pmon

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl status database -db ORCLDB

PRCD-1120 : The resource for database ORCLDB could not be found.

PRCR-1001 : Resource ora.ORCLDB.db does not exist

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl add database -d ORCLDB -o /applications/oracle/d19.3.0/oracle_home

[oracle@dms04dbaorcl05p dbs]$ srvctl status database -db ORCLDB

Database is not running.

[oracle@dms04dbaorcl05p dbs]$ srvctl add instance -d ORCLDB -i ORCLDB1 -n dms04dbaorcl05p

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl add instance -d ORCLDB -i ORCLDB2 -n dms04dbaorcl06p

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl start database -d ORCLDB -o mount

[oracle@dms04dbaorcl05p dbs]$ srvctl stop database -d ORCLDB

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl start database -d ORCLDB -o mount

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 12:00:07 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CONNECTED 0

DGRD ALLOCATED 0

ARCH CONNECTED 0

6 rows selected.

SQL> show parameter db_name

NAME TYPE

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

VALUE

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

db_name string

ORCL

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CONNECTED 0

DGRD ALLOCATED 0

ARCH CONNECTED 0

MRP0 APPLYING_LOG 61992

7 rows selected.

SQL> col host_name for a15;

set lines 200 pages 200;

col INSTANCE_NAME for a15;

col PROTECTION_MODE for a20;

col STATUS for a8;

col INSTANCE_ROLE for a18;

col DATABASE_STATUS for a15;

col DATABASE_ROLE for a18;

col VERSION for a15;

col VERSION_FULL for a15

select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE

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

ORCLDB1 dms04dbaorcl05p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.21.0.0.0 PHYSICAL STANDBY

SQL>

SQL> SELECT ARCH.THREAD# "Thread",

ARCH.SEQUENCE# "Last Sequence Received",

APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4 5 6 7 8

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74647 74648 -1

1 74648 74647 1

1 74647 74647 0

1 74648 74648 0

2 61991 61991 0

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 12:03:34 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select PROCESS,STATUS,SEQUENCE# from v$managed_standby;

PROCESS STATUS SEQUENCE#

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

ARCH CONNECTED 0

DGRD ALLOCATED 0

ARCH CONNECTED 0

MRP0 APPLYING_LOG 74649

RFS IDLE 0

RFS IDLE 61992

RFS IDLE 0

RFS IDLE 74649

11 rows selected.

SQL>

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 1 APPLYING_LOG 74649

RFS 1 IDLE 0

RFS 2 RECEIVING 61992

RFS 1 IDLE 0

RFS 1 IDLE 74649

11 rows selected.

SQL>

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl config database -d ORCLDB

Database unique name: ORCLDB

Database name:

Oracle home: /applications/oracle/d19.3.0/oracle_home

Oracle user: oracle

Spfile:

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA01,ARCH

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oper

Database instances: ORCLDB1,ORCLDB2

Configured nodes: dms04dbaorcl05p,bms04dbafom06p

CSS cRATical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 44

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora_onceagain

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

-rw-r--r-- 1 oracle oinstall 60 Jan 19 11:43 initORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 12:01 hc_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$ pwd

/applications/oracle/d19.3.0/oracle_home/dbs

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ cd /tmp

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ ls -lrt

total 60

drwx------ 3 root root 17 Jan 16 13:10 systemd-private-6e06ff0530fe4d009d4cd95360193b59-rtkit-daemon.service-MXp9YR

drwx------ 3 root root 17 Jan 16 13:10 systemd-private-6e06ff0530fe4d009d4cd95360193b59-ModemManager.service-0oI1Ie

drwx------ 3 root root 17 Jan 16 13:10 systemd-private-6e06ff0530fe4d009d4cd95360193b59-colord.service-hFN6Nu

drwxr-xr-x. 2 grid oinstall 28 Jan 16 13:18 cvutrace_grid

-rw------- 1 grid oinstall 1776 Jan 16 16:09 tmpacfsLnpI

-rw------- 1 grid oinstall 18070 Jan 16 16:10 tmpacfsJlqD

-rw------- 1 grid oinstall 1768 Jan 16 16:20 tmpacfs8pf6

drwxr-xr-x 4 grid oinstall 305 Jan 16 17:18 GridSetupActions2024-01-16_04-02-11PM

-rw------- 1 grid oinstall 1776 Jan 17 12:24 tmpacfsHAPm

-rw------- 1 grid oinstall 15774 Jan 17 12:24 tmpacfs0kTE

-rw------- 1 grid oinstall 1768 Jan 17 12:32 tmpacfsHHwZ

-rw------- 1 root root 1027 Jan 17 12:46 ahf_install_235000_55857_2024_01_17-12_46_30.log

drwxr-xr-x 2 root root 6 Jan 17 12:50 hsperfdata_root

drwxr-xr-x 3 oracle oinstall 63 Jan 17 13:17 ORACLE-19-21

drwxr-x--- 2 oracle oinstall 34 Jan 17 13:35 OraInstall2024-01-17_01-35-55PM

drwxr-xr-x 3 oracle oinstall 26 Jan 17 13:35 InstallActions2024-01-17_01-35-54PM

drwx------ 3 root root 17 Jan 17 14:17 systemd-private-6e06ff0530fe4d009d4cd95360193b59-chronyd.service-BemRlW

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:39 orapwORCLDB1

drwxr-xr-x 2 oracle oinstall 6 Jan 19 12:04 hsperfdata_oracle

drwxr-xr-x. 2 grid oinstall 45 Jan 19 12:05 hsperfdata_grid

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ pwd

/tmp

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ rm -rf orapwORCLDB1

[oracle@dms04dbaorcl05p tmp]$

[oracle@dms04dbaorcl05p tmp]$ cd -

/applications/oracle/d19.3.0/oracle_home/dbs

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ls -lrt

total 2092

-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora

-rw-r--r-- 1 oracle oinstall 3133 Jan 18 12:55 initORCLDB1.ora_onceagain

-rw-r----- 1 oracle oinstall 6144 Jan 18 13:23 coorapwORCLDB1_clone_created

-rw-r----- 1 oracle oinstall 2560 Jan 19 09:41 orapwORCLDB1

-rw-r--r-- 1 oracle oinstall 3133 Jan 19 11:28 initORCLDB1.ora_before_clone

-rw-r----- 1 oracle oinstall 9728 Jan 19 11:29 spfileORCLDB1.ora_after-clone-sp

-rw-r--r-- 1 oracle oinstall 60 Jan 19 11:43 initORCLDB1.ora

-rw-rw---- 1 oracle asmadmin 1544 Jan 19 12:01 hc_ORCLDB1.dat

-rw-r----- 1 oracle asmadmin 2097152 Jan 19 12:06 id_ORCLDB1.dat

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ cp orapwORCLDB1 /tmp

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ su - grid

Password:

[grid@dms04dbaorcl05p ~]$

[grid@dms04dbaorcl05p ~]$ . oraenv

ORACLE_SID = [grid] ? +ASM1

The Oracle base has been set to /applications/oracle/g19.3.0/grid_base

[grid@dms04dbaorcl05p ~]$

[grid@dms04dbaorcl05p ~]$ asmcmd

ASMCMD>

ASMCMD> pwcopy --dbuniquename ORCLDB /tmp/orapwORCLDB1 +DATA01/ORCLDB/PASSWORD/orapwORCLDB

copying /tmp/orapwORCLDB1 -> +DATA01/ORCLDB/PASSWORD/orapwORCLDB

ASMCMD-9453: failed to register password file as a CRS resource

ASMCMD> ls -lrt

WARNING: option 'r' is deprecated for 'ls'

please use 'reverse'

State Type Rebal Name

MOUNTED NORMAL N OCRVD/

MOUNTED EXTERN N DATA01/

MOUNTED EXTERN N ARCH/

ASMCMD>

ASMCMD> cd DATA01/

ASMCMD>

ASMCMD> ls

ORCLDB/

ASMCMD> cd ORCLDB/

ASMCMD>

ASMCMD> ls

CONTROLFILE/

DATAFILE/

DATAGUARDCONFIG/

ORCLDBREDO/

ORCLDBSRL/

AAA_AT/

AAA_BE/

AAA_CH/

AAA_ES/

AAA_NL/

AAA_TR/

ONLINELOG/

PARAMETERFILE/

PASSWORD/

control01.ctl

control02.ctl

AAA_PL01.dbf

fom_se_01.dbf

gg_tbs.dbf

MC_D01.dbf

returns01.dbf

RAT_01.dbf

sysaux01.dbf

sysaux02.dbf

system01.dbf

undotbs01.dbf

undotbs02.dbf

users01.dbf

ASMCMD>

ASMCMD> cd PASSWORD/

ASMCMD>

ASMCMD> ls

orapwORCLDB

pwdORCLDB.298.1158667873

ASMCMD>

ASMCMD> exit

[grid@dms04dbaorcl05p ~]$

[grid@dms04dbaorcl05p ~]$ exit

logout

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl modify database -d ORCLDB -pwfile DATA01/ORCLDB/PASSWORD/orapwORCLDB

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl config database -d ORCLDB

Database unique name: ORCLDB

Database name:

Oracle home: /applications/oracle/d19.3.0/oracle_home

Oracle user: oracle

Spfile:

Password file: DATA01/ORCLDB/PASSWORD/orapwORCLDB

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA01,ARCH

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oper

Database instances: ORCLDB1,ORCLDB2

Configured nodes: dms04dbaorcl05p,bms04dbafom06p

CSS cRATical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@dms04dbaorcl05p dbs]$ srvctl modify database -d ORCLDB -pwfile +DATA01/ORCLDB/PASSWORD/orapwORCLDB

[oracle@dms04dbaorcl05p dbs]$ srvctl config database -d ORCLDB

Database unique name: ORCLDB

Database name:

Oracle home: /applications/oracle/d19.3.0/oracle_home

Oracle user: oracle

Spfile:

Password file: +DATA01/ORCLDB/PASSWORD/orapwORCLDB

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA01,ARCH

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oper

Database instances: ORCLDB1,ORCLDB2

Configured nodes: dms04dbaorcl05p,bms04dbafom06p

CSS cRATical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl stop database -d ORCLDB

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ ps -ef | grep pmon

grid 60809 1 0 Jan17 ? 00:00:10 asm_pmon_+ASM1

oracle 430334 395360 0 12:15 pts/4 00:00:00 grep --color=auto pmon

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ srvctl start database -d ORCLDB -o mount

[oracle@dms04dbaorcl05p dbs]$

[oracle@dms04dbaorcl05p dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 19 12:17:23 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

6 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

6 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

6 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

6 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

6 rows selected.

SQL>

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

6 rows selected.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select PROCESS,thread#,STATUS,SEQUENCE# from v$managed_standby;

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL>

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

7 rows selected.

SQL> /

PROCESS THREAD# STATUS SEQUENCE#

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

ARCH 0 CONNECTED 0

DGRD 0 ALLOCATED 0

ARCH 0 CONNECTED 0

MRP0 2 APPLYING_LOG 61992

RFS 1 IDLE 0

RFS 2 RECEIVING 61994

RFS 1 IDLE 0

RFS 1 RECEIVING 74651

11 rows selected.

SQL> SELECT ARCH.THREAD# "Thread",

ARCH.SEQUENCE# "Last Sequence Received",

2 3 APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THRE 4 AD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.T 5 HREAD#; 6 7 8

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74647 74648 -1

1 74648 74647 1

1 74647 74647 0

1 74648 74648 0

2 61991 61991 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74647 74648 -1

1 74648 74647 1

1 74647 74647 0

1 74648 74648 0

2 61991 61991 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74647 74648 -1

1 74648 74647 1

1 74647 74647 0

1 74648 74648 0

2 61991 61991 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74650 74650 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74650 74650 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74650 74650 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74650 74650 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74650 74650 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74651 74651 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74651 74651 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74651 74651 0

2 61993 61993 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74653 74653 0

2 61996 61996 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74653 74653 0

2 61996 61996 0

SQL> /

Thread Last Sequence Received Last Sequence Applied Difference

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

1 74653 74653 0

2 61996 61996 0

SQL>

SQL> exit

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

Version 19.21.0.0.0

[oracle@dms04dbaorcl05p dbs]$

Amit's Oracle DBA Blog

Disclaimer

Saturday, 4 May 2024

TABLE FRAGMENTATION IN ORACLE DATABASE

TABLE FRAGMENTATION IN ORACLE DATABASE

Saturday, 9 March 2024

How to Diagnose Wrong Time -Wrong Time Zone- Oracle database

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file - Temp file issue

Monday, 22 January 2024

Oracle error from auxiliary database: ORA-01275 - Dataguard duplication

Data Guard Standby Resynchronization Using RMAN RECOVER FROM SERVICE (300+ Archive Lag)

Labels