ERROR: Unable to get logical block size for spfile

Always make sure the referenced pfile or spfile exists and accessible.

Created 12.1.0.2 CDB/PDB database through DBCA, there are a lot of errors in alert.log.

...
..
.
ERROR: Unable to get logical block size for spfile '+DATA1/RACTEST/spfileRACTEST.ora'.
Mon Oct 24 23:33:11 2016
ERROR: Unable to get logical block size for spfile '+DATA1/RACTEST/spfileRACTEST.ora'.

Go to $ORACLE_HOME/dbs and check initRACTEST1.ora file, which was created by DBCA.

$cd $ORACLE_HOMEdbs
$cat initRACTEST1.ora
'+DATA1/RACTEST/spfileRACTEST.ora'

--- DBCA scripts
---
[RACTEST1] oracle@racnode1:/u01/app/oracle/admin/RACTEST/scripts$ cat RACTEST1.sql
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
ACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDE
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/orapwd file=+DATA1/RACTEST/orapwRACTEST force=y format=12 dbuniquename=RACTEST
host /u01/app/12.1.0.2/grid/bin/setasmgidwrap o=/u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add database -d RACTEST -pwfile +DATA1/RACTEST/orapwRACTEST -o /u01/app/oracle/product/12.1.0/dbhome_1 -p +DATA1/RACTEST/spfileRACTEST.ora -n RACTEST -a "DATA1,FRA"
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST1 -n racnode1
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST2 -n racnode2
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST3 -n racnode3
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl add instance -d RACTEST -i RACTEST4 -n racnode4
host /u01/app/oracle/product/12.1.0/dbhome_1/bin/srvctl disable database -d RACTEST
@/u01/app/oracle/admin/RACTEST/scripts/CreateDB.sql
@/u01/app/oracle/admin/RACTEST/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/RACTEST/scripts/CreateDBCatalog.sql
@/u01/app/oracle/admin/RACTEST/scripts/JServer.sql
@/u01/app/oracle/admin/RACTEST/scripts/context.sql
@/u01/app/oracle/admin/RACTEST/scripts/ordinst.sql
@/u01/app/oracle/admin/RACTEST/scripts/interMedia.sql
@/u01/app/oracle/admin/RACTEST/scripts/cwmlite.sql
@/u01/app/oracle/admin/RACTEST/scripts/spatial.sql
@/u01/app/oracle/admin/RACTEST/scripts/labelSecurity.sql
@/u01/app/oracle/admin/RACTEST/scripts/apex.sql
@/u01/app/oracle/admin/RACTEST/scripts/datavault.sql
@/u01/app/oracle/admin/RACTEST/scripts/CreateClustDBViews.sql
host echo "SPFILE='+DATA1/RACTEST/spfileRACTEST.ora'" > /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRACTEST1.ora
@/u01/app/oracle/admin/RACTEST/scripts/lockAccount.sql
@/u01/app/oracle/admin/RACTEST/scripts/postDBCreation.sql
@/u01/app/oracle/admin/RACTEST/scripts/PDBCreation.sql
@/u01/app/oracle/admin/RACTEST/scripts/plug_RACTESTPDB.sql
@/u01/app/oracle/admin/RACTEST/scripts/postPDBCreation_RACTESTPDB.sql

Check ASM files, there is no ‘+DATA1/RACTEST/spfileRACTEST.ora’ file existed.

ASMCMD> cd RACTEST/
ASMCMD> ls -l

Type Redund Striped Time Sys Name
 Y 3F9BD96544BC0349E053530F040A8320/
 Y 3F9C860784456287E053530F040ADB20/
 Y CONTROLFILE/
 Y DATAFILE/
 Y ONLINELOG/
 Y PARAMETERFILE/
 Y PASSWORD/
 Y TEMPFILE/
PASSWORD UNPROT COARSE OCT 24 15:00:00 N orapwractest=> +DATA1/RACTEST/PASSWORD/pwdractest.1427.926092135

ASMCMD> cd PARAMETERFILE/
ASMCMD> pwd
+data1/RACTEST/PARAMETERFILE
ASMCMD> ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE OCT 25 10:00:00 Y spfile.1438.926119495

SOLUTION

  1. Give right path and name for spfile in pfile. Get the right spfile name and path from alert.log when instance startup everytime.
$cat initRACTEST1.ora
'+data1/RACTEST/PARAMETERFILE/spfile.1438.926119495'

2. Or remove initRACTE1.ora from $ORACLE_HOME/dbs if pfile is not referenced at all.

How to Drop RAC Database

It is good practice to drop a database with “including contents and datafiles”.

A. Startup database in mount status.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
Fixed Size 7663544 bytes
Variable Size 3120562248 bytes
Database Buffers 1.4026E+10 bytes
Redo Buffers 25890816 bytes
Database mounted.

B. Failed to drop the database.

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

C. Make the database as a non-cluster database, and then mount the database in RESTRICT mode.

SQL> alter system set cluster_database=FALSE scope=spfile;
System altered.


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


SQL> startup mount restrict;
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 7663544 bytes
Variable Size 3120562248 bytes
Database Buffers 1.4026E+10 bytes
Redo Buffers 25890816 bytes
Database mounted.

D. Drop the database successfully.

SQL> drop database including contents and datafiles; 
Database dropped.

E. Remove the database from cluster.

$ srvctl status database -d RACTEST
Instance RACTEST1 is not running on node racnode1
Instance RACTEST2 is not running on node racnode2
Instance RACTEST3 is not running on node racnode3
Instance RACTEST4 is not running on node racnode4

$ srvctl remove database -d RACTEST
Remove the database RACTEST? (y/[n]) y

$ srvctl status database -d RACTEST
PRCD-1120 : The resource for database RACTEST could not be found.
PRCR-1001 : Resource ora.RACTEST.db does not exist

Copy AWR Snapshots Data to Different Database

Copy AWR snapshots data from production to test database, where performance comparison can be held.

EXTRACT AWR DATA FROM SOURCE

Connect to source database as SYS user.

@$ORACLE_HOME/rdbms/admin/awrextr.sql
......
.....
....
...
Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_92100_92110.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: awrdat_92100_92110

Using the dump file prefix: awrdat_92100_92110
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /u02/app/oracle/admin/TESTDB/dpdump/
| awrdat_92100_92110.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /u02/app/oracle/admin/TESTDB/dpdump/
| awrdat_92100_92110.log
|

End of AWR Extract

The script will prompt and ask for the following information :

  1. dbid
  2. The number of days of snapshots to choose from
  3. Begin Snapshot Id
  4. End Snapshot ID
  5. Value for directory_name
  6. Dump file name to be created ( prefix only ): awrdat_92100_92110

LOAD AWR DATA INTO TARGET

Connect to target database as SYS user.

@$ORACLE_HOME/rdbms/admin/awrload.sql

......
.....
....
...
... Creating AWR_STAGE_LOAD user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /tmp
| awrdat_92100_92110.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /tmp
| awrdat_92100_92110.log
|

The script will prompt and ask for :

  1. The name of directory object.
  2. The name of dump file.
  3. The staging schema name to load AWR data into. ( will be dropped automatically after AWR loading ).
  4. The default tablespace name for staging schema.
  5. The temporary tablespace name.

Change AWR Retention and Interval

Use “dbms_workload_repository.modify_snapshot_settings” to change AWR retention and interval.

Check current Retention and Interval

RETENTION = 8 days ( 11520 Mins ) ( Default )
INTERVAL = 1 hour ( 60 Mins ) ( Default )

SQL> desc dba_hist_wr_control
 Name          Null?     Type
 ------------- --------- --------------------------
 DBID          NOT NULL NUMBER
 SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION NOT NULL     INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                VARCHAR2(10)

SQL> select dbid from v$database;

DBID
----------
2920212463

SQL> col SNAP_INTERVAL format a30
SQL> col RETENTION format a30
SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL       RETENTION
---------- ------------------- ------------------------------
2920212463 +00000 01:00:00.0   +00008 00:00:00.0

SQL> col SNAP_INTERVAL_MINS format a40
SQL> col RETENTION_MINS format a40
SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
            RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL_MINS            RETENTION_MINS
---------- ----------------------------  -----------------------------
2920212463 +000000060 00:00:00.000000000 +000011520 00:00:00.000000000

Change Retention and Interval

Retention from 8 days (  11520 Mins )  —-> 31 days ( 44640 Mins )
Interval from 1 hour ( 60 Mins ) —-> 0.5 hour ( 30 Mins )

SQL>exec dbms_workload_repository.modify_snapshot_settings
                                 (retention=>44640,
                                  interval=>30,
                                      dbid=>2920212463);

PL/SQL procedure successfully completed.

Check New Retention and Interval

SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

DBID S     NAP_INTERVAL      RETENTION
---------- ----------------- ---------------------
2920212463 +00000 00:30:00.0 +00031 00:00:00.0

SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
                     RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID SNAP_INTERVAL_MINS                   RETENTION_MINS
---------- ------------------------------- ----------------------------
2920212463 +000000030 00:00:00.000000000   +000044640 00:00:00.000000000

Check SYSAUX Tablespace Usage

SQL> SELECT occupant_name, space_usage_kbytes/1024 MBS 
       FROM V$SYSAUX_OCCUPANTS 
   order by 2;

OCCUPANT_NAME             MBS
------------------------- ----------
TSM                         0
EXPRESSION_FILTER           0
ULTRASEARCH_DEMO_USER       0
...
..
.
SM/ADVISOR                 69.3125
AUDIT_TABLES              136.1875
SM/OPTSTAT                406.4375
SM/AWR                   1633.3125

31 rows selected.

Change ASM Instance SYS ASMSNMP User Password

Change ASM instance SYS ASMSNMP user password by using ASM command “passwd”. If ASM password file is stored and shared in ASM diskgroup, then SQL “alter user” can still be used to change ASM user password.

Here is the way to change ASM instance SYS/ASMSNMP user password in 12c.

$ asmcmd
ASMCMD> pwget --asm
+OCR_VOTE/orapwASM

ASMCMD> cd +OCR_VOTE
ASMCMD> ls -l orapwASM
Type     Redund Striped Time           Sys Name
PASSWORD UNPROT COARSE  AUG 08 17:00:00 N  orapwASM => +OCR_VOTE/ASM/PASSWORD/pwdasm.256.919359249

ASMCMD> cd +OCR_VOTE/ASM/PASSWORD
ASMCMD> ls -l
Type     Redund Striped Time            Sys Name
PASSWORD UNPROT COARSE  AUG 08 17:00:00 Y   pwdasm.256.919359249
ASMCMD>

To see users in password file:

ASMCMD> lspwusr
Username sysdba sysoper sysasm
 SYS     TRUE   TRUE    TRUE
 ASMSNMP TRUE   FALSE   FALSE

ASMCMD> lspwusr --suppressheader
 SYS     TRUE TRUE  TRUE
 ASMSNMP TRUE FALSE FALSE
ASMCMD>

Change user password:

ASMCMD> passwd
usage: passwd <user>
help: help passwd
ASMCMD> help passwd
passwd
 Changes the password of a user.

Synopsis
 passwd <user>

Description
 The option for the passwd command is described below.

user - Name of the user.

An error is raised if the user does not exist in the Oracle ASM
 password file. The user is first prompted for the current password,
 then the new password. The command requires the SYSASM privilege to run

Examples
 This example changes the password of the oracle2 user.

ASMCMD [+] > passwd oracle2
 Enter old password (optional):
 Enter new password: ******


ASMCMD> passwd sys
Enter old password (optional):
Enter new password: *********
ASMCMD>

We can still use “alter user ” to change sys and asmsnmp user password :

SQL> alter user sys identified by "XXXXXXXX";
alter user sys identified by "XXXXXXXX"
 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect / as sysasm
Connected.
SQL> alter user sys identified by "XXXXXXXX";

User altered.