DB_UNKNOWN directory was created when using asmcmd pwcopy

DB_UNKNOWN directory was created when using asmcmd pwcopy:

'DB_UNKNOWN' directory was created when using asmcmd pwcopy (Doc ID 2329386.1)

1. Able to connect to primary RAC (RAC12C1 & RAC12C2) database as a sysdba user using scan name which indicates password file is working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RAC12C as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:11 2023

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

Last Successful login time: Thu Jun 22 2023 21:52:47 +05:30

Connected to:

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

SQL> 

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

2. Able to connect to primary RAC (RAC12C1 & RAC12C2) instance 1 as a sysdba user using scan name which indicates password file is working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RAC12C1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:17 2023

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

Last Successful login time: Mon Jul 03 2023 12:11:12 +05:30

Connected to:

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

SQL> 

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

3. Able to connect to primary RAC (RAC12C1 & RAC12C2) instance 2 as a sysdba user using scan name which indicates password file is working

[oracle@node1 ~]$ sqlplus sys/Mallik123@RAC12C2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:25 2023

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

Last Successful login time: Mon Jul 03 2023 12:11:18 +05:30

Connected to:

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

SQL> 

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

4. Unable to connect to Standby RAC (RACSB1 & RACSB2) database as a sysdba user using scan name which indicates password file is not working

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

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:11:33 2023

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

ERROR:

ORA-01017: invalid username/password; logon denied

ORA-17503: ksfdopn:2 Failed to open file +DATA/RACSB/PASSWORD/orapwracsb

ORA-15173: entry 'orapwracsb' does not exist in directory 'PASSWORD'

ORA-06512: at line 4

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679

ORA-06512: at line 2

Enter user-name:

5. Unable to connect to Standby RAC (RACSB1 & RACSB2) instance 1 as a sysdba user using scan name which indicates password file is not working

[oracle@oraclenode1 script]$ sqlplus sys/Mallik123@RACSB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:12:24 2023

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

ERROR:

ORA-01017: invalid username/password; logon denied

ORA-17503: ksfdopn:2 Failed to open file +DATA/RACSB/PASSWORD/orapwracsb

ORA-15173: entry 'orapwracsb' does not exist in directory 'PASSWORD'

ORA-06512: at line 4

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679

ORA-06512: at line 2

Enter user-name: 

6. Unable to connect to Standby RAC (RACSB1 & RACSB2) instance 2 as a sysdba user using scan name which indicates password file is not working

[oracle@oraclenode1 script]$ sqlplus sys/Mallik123@RACSB2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:12:32 2023

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

ERROR:

ORA-01017: invalid username/password; logon denied

ORA-17503: ksfdopn:2 Failed to open file +DATA/RACSB/PASSWORD/orapwracsb

ORA-15173: entry 'orapwracsb' does not exist in directory 'PASSWORD'

ORA-06512: at line 4

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679

ORA-06512: at line 2

Enter user-name: 

7. Verify the password file location 

[oracle@oraclenode1 script]$ srvctl config database -d RACSB

Database unique name: RACSB

Database name: RACSB

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

Oracle user: oracle

Spfile: +DATA/RACSB/PARAMETERFILE/spfileRACSB.ora

Password file: +DATA/RACSB/PASSWORD/orapwRACSB >>>> Password file location

Domain:

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: RACSB1,RACSB2

Configured nodes: oraclenode1,oraclenode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@oraclenode1 script]$

8. We has password file copied from Primary to standby on local file system. We need to copy that to ASM disk group using pwcopy command.

[oracle@oraclenode1 dbs]$ asmcmd -p

ASMCMD [+] > pwcopy '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1' '+DATA/RACSB/PASSWORD/orapwRACSB'

copying /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1 -> +DATA/RACSB/PASSWORD/orapwRACSB

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

Type      Redund  Striped  Time             Sys  Name

PASSWORD  UNPROT  COARSE   JUL 03 12:00:00  N    orapwRACSB => +DATA/DB_UNKNOWN/PASSWORD/pwddb_unknown.305.1141215603

ASMCMD [+] >

Note that if we don't us dbuniquename along with pwcopy then directory will created as DB_UNKNOWN

ASMCMD [+DATA] > rm -rf DB_UNKNOWN/

Note that below command failed due to environmental variable are pointing to GI home.

ASMCMD [+DATA] > pwcopy --dbuniquename RACSB '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1' '+DATA/RACSB/PASSWORD/orapwRACSB'

PRCD-1229 : An attempt to access configuration of database RACSB was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.2.0.1/dbhome_1.

copying /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1 -> +DATA/RACSB/PASSWORD/orapwRACSB

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

ASMCMD [+DATA] > 

[oracle@oraclenode1 dbs]$ . oraenv

ORACLE_SID = [+ASM1] ? RACSB1

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

[oracle@oraclenode1 dbs]$ asmcmd -p

ASMCMD [+] > pwcopy --dbuniquename RACSB '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwRACSB1' '+DATA/RACSB/PASSWORD/orapwRACSB'

ASMCMD [+] > exit

[oracle@oraclenode1 dbs]$

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

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:24:58 2023

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

Connected to:

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

SQL> exit

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

[oracle@oraclenode1 dbs]$ sqlplus sys/Mallik123@RACSB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:25:09 2023

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

Last Successful login time: Thu Jun 22 2023 21:52:47 +05:30

Connected to:

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

SQL> exit

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

[oracle@oraclenode1 dbs]$ sqlplus sys/Mallik123@RACSB2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 3 12:25:16 2023

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

Last Successful login time: Mon Jul 03 2023 12:25:09 +05:30

Connected to:

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

SQL> exit

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

[oracle@oraclenode1 dbs]$

Regards,

Mallik

ORA-29339: tablespace block size string does not match configured block sizes

ORA-29339: tablespace block size string does not match configured block sizes

Issue: 

Unable to create a Tablespace with larger block size.

Error:

ORA-29339: tablespace block size string does not match configured block sizes

Cause:

The block size of the tablespace to be created does not match the block sizes configured in the database.

Note:

Currently database running with 8K block size, we are trying to create tablespace with 16K block size.

Solution:

Configure the appropriate cache for the block size of this tablespace using below parameter.

db_2k_cache_size

db_4k_cache_size

db_8k_cache_size

db_16k_cache_size

db_32K_cache_size

Logs:

[root@oraclelab1 ~]# su - oracle

Last login: Tue Jul 25 08:06:22 IST 2023 on pts/0

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVDB

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

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 18:20:28 2023

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

SQL> select NAME from v$tablespace;

NAME

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

SYSAUX

SYSTEM

UNDOTBS1

USERS

TEMP

TEST2

TEST4

7 rows selected.

SQL> select NAME from v$datafile;

NAME

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

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_lcx8tlpt_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_lcx8votl_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_lcx8wgxt_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_lcx8wj14_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf

/u01/app/oracle/oradata/DEVDB/datafile/test4.dbf

6 rows selected.

SQL> set pages 100 lines 1000

SQL> set pages 1000 lines 1000

SQL> col tablespace_name format a16;

col file_name format a50;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024

FROM DBA_DATA_FILES;

TABLESPACE_NAME  FILE_NAME                                          BYTES/1024/1024

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

SYSTEM           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_syste            1020

                 m_lcx8tlpt_.dbf

SYSAUX           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysau             580

                 x_lcx8votl_.dbf

USERS            /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users               5

                 _lcx8wj14_.dbf

UNDOTBS1         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undot             390

                 bs1_lcx8wgxt_.dbf

TEST2            /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf               100

TEST4            /u01/app/oracle/oradata/DEVDB/datafile/test4.dbf               100

6 rows selected.

SQL> select NAME,BIGFILE from v$tablespace;

NAME                           BIG

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

SYSAUX                         NO

SYSTEM                         NO

UNDOTBS1                       NO

USERS                          NO

TEMP                           NO

TEST2                          NO

TEST4                          NO

7 rows selected.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

TABLESPACE_NAME  FILE_NAME                                          BYTES/1024/1024 AUT

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

SYSTEM           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_syste            1020 YES

                 m_lcx8tlpt_.dbf

SYSAUX           /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysau             580 YES

                 x_lcx8votl_.dbf

UNDOTBS1         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undot             390 YES

                 bs1_lcx8wgxt_.dbf

USERS            /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users               5 YES

                 _lcx8wj14_.dbf

TEST2            /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf               100 YES

TEST4            /u01/app/oracle/oradata/DEVDB/datafile/test4.dbf               100 NO

6 rows selected.

While creating Tablespace with 16k block size which failed

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384;

CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384

*

ERROR at line 1:

ORA-29339: tablespace block size 16384 does not match configured block sizes

Solution is to set 16k db_block size, for that I am setting db_16k_cache_size=112M

SQL> alter system set db_16k_cache_size=112M scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690985856 bytes

Fixed Size                  8903040 bytes

Variable Size             704643072 bytes

Database Buffers         2969567232 bytes

Redo Buffers                7872512 bytes

Database mounted.

Database opened.

SQL> show parameter db_16k_cache_size

NAME                                 TYPE        VALUE

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

db_16k_cache_size                    big integer 112M

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384;

Tablespace created.

SQL>

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME  BLOCK_SIZE

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

SYSTEM                 8192

SYSAUX                 8192

UNDOTBS1               8192

TEMP                   8192

USERS                  8192

TEST2                  8192

TEST4                  8192

TEST_16K_TS           16384

8 rows selected.

SQL>

Extra note:

A. How many default tablespaces are available in Oracle Database?

B. Can database user be part of Multiple Tablespaces in Oracle?

C. Can database user creates objects (example: Tables) into Multiple Tablespaces?

1. create an user and and user will default get assigned to default tablespace of a database (USERS)

SQL> create user mallik identified by mallik;

User created.

SQL> grant dba to mallik;

Grant succeeded.

SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='MALLIK';

USERNAME     ACCOUNT_STATUS                   DEFAULT_TABLESPACE    TEMPORARY_TABLESPACE

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

MALLIK       OPEN                             USERS                  TEMP

SQL>

2. Can user creates table or ojects into multiple tablespaces?

Answer: Yes 

create table EMP (SLNO number, NAME varchar2(15));

create table EMP_16K (SLNO number, NAME varchar2(15)) tablespace test_16k_ts;

SQL> conn mallik/mallik

Connected.

SQL> show user

USER is "MALLIK"

SQL> create table EMP (SLNO number, NAME varchar2(15));

Table created.

SQL> create table EMP_16K (SLNO number, NAME varchar2(15)) tablespace test_16k_ts;

Table created.

SQL>

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME like 'EMP%';

OWNER     TABLE_NAME     TABLESPACE_NAME

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

MALLIK    EMP            USERS

MALLIK    EMP_16K        TEST_16K_TS

SQL>

3. Can we move a object from one tablespace to another tablespace?

Answer: Yes

SQL> alter table MALLIK.EMP move tablespace TEST_16K_TS;

Table altered.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME like 'EMP%';

OWNER    TABLE_NAME TABLESPACE_NAME

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

MALLIK   EMP_16K    TEST_16K_TS

MALLIK   EMP        TEST_16K_TS

SQL>

Regards,

Mallik

Row chaining Vs Row migration

Row chaining Vs Row migration

Chained rows:

- Chained rows are records stored over multiple data blocks due to their excessive size. 

- Row piece size is grater than DB_BLOCK_SIZE then row piece will be spitted and spead accross the multiple blicks.

- Mainly caused due to INSERT statement

Migrated rows:

- Migrated rows occur when an UPDATE DML causes the rows to expand onto another data block.

- Mainly caused due to UPDATE statement

A migrated row is a special case of a chained row. 

A migrated row is a chained row, a chained row may or may not be a migrated row.

Chained rows:

Questions:

1. What is row chaining?

2. What is row migration?

3. How to identify the row chaining and row migration?

4. How to fix this row chaining and row migration?

Analyze the table to refresh the statistics:

analyze table EMP compute statistics;

This query will show how many chained (and migrated) rows each table has:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

Check for chained rows for perticuler table:

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

In order to a avoide the row chaining we need to create a tablespace with bigger block size and move the table:

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME  BLOCK_SIZE

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

SYSTEM                 8192

SYSAUX                 8192

UNDOTBS1           8192

TEMP                    8192

USERS                  8192

TEST_16K_TS      16384 >>> 16K block size

alter table MALLIK.EMP move tablespace TEST_16K_TS;

After table move we should rebuild the index since index become unusable:

alter index EMP.PK_EMPID rebuild;

After moved table analyze the table to refresh the statistics and and check for row chaining which will be resolved. 

analyze table EMP compute statistics;

select chain_cnt from all_tables where owner='MALLIK' and TABLE_NAME='EMP';

Migrated rows:

This will put the rows into the CHAINED_ROWS table which is created by the utlchain.sql script 

($ORACLE_HOME/rdbms/admin).

They create a table named CHAINED_ROWS in the schema of the user submitting the script.

SELECT * FROM chained_rows;

Example:

create table CHAINED_ROWS (

owner_name varchar2(30),

table_name varchar2(30),

cluster_name varchar2(30),

partition_name varchar2(30),

subpartition_name varchar2(30),

head_rowid rowid,

analyze_timestamp date

);

To see which rows are chained:

ANALYZE TABLE EMP LIST CHAINED ROWS;

select owner_name, table_name, head_rowid from chained_rows;

The following query can be used to identify tables with chaining problems:

TTITLE 'Tables Experiencing Chaining'

SELECT owner, table_name,

       NVL(chain_cnt,0) "Chained Rows"

  FROM all_tables

 WHERE owner NOT IN ('SYS', 'SYSTEM')

       AND NVL(chain_cnt,0) > 0

ORDER BY owner, table_name;

Conclusion

- Row migration is typically caused by UPDATE operation

- Row chaining is typically caused by INSERT operation.

- SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.

- To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view

- To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE

Regards,

Mallik