Create Database with 32K Block Size Oracle 12c

This parameter in the init.ora is the most important. This can be done only during creation time. If you have already created the Database you cannot change this value. You will have to re-create the Database with a different size.

When you start to think about larger block sizes, remember that a 32KB undo block size can be a source of wasted I/O.

This block size is used for the SYSTEM tablespace and by default in other tablespaces.

Solaris Sparc 64-bit
Oracle 12c

SQL> conn / as sysdba
Connected.
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     32768


SQL>

Fix for ORA-03113: end-of-file on communication channel

While startup the database getting error.

SQL> startup
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instace;
select instance_name from v$instace
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 43880
Session ID: 170 Serial number: 5



Solution:


SQL> alter database recover database until cancel;

database altered


SQL> alter system set db_recovery_file_dest_size=8000m scope=both;

database altered


SQL> alter database open resetlogs;

database altered

ORA-19809: limit exceeded for recovery files tips

SQL> alter database open resetlogs;



ERROR at line 1
ORA-19809: Limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 10 limit




SQL> show parameter db_recovery



NAME                                            TYPE            VALUE
---------------------------------------------------------------------------------------------------------------------

db_recovery_file_dest                     string               /ora/oracle/oracle12/fast_recovery_file_dest_size
db_recovery_file_dest_Size             big integer         10


SQL> alter system set db_recovery_file_dest_size=8000m scope=both



SQL> alter database open resetlogs;

database altered.

12c Grid Infrastructure diskmon Will be Offline by Default in Non-Exadata Environment

As Grid Infrastructure daemon diskmon.bin is used for Exadata fencing, started from 11.2.0.3, resource ora.diskmon will be offline in non-Exadata environment. This is expected behaviour change.

$ crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       pricedev1-lnx            Started             
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.crf
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.crsd
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.cssd
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.cssdmonitor
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.ctssd
      1        ONLINE  ONLINE       pricedev1-lnx            OBSERVER            
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.drivers.acfs
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.evmd
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.gipcd
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.gpnpd
      1        ONLINE  ONLINE       pricedev1-lnx                                
ora.mdnsd
      1        ONLINE  ONLINE       pricedev1-lnx  

Refer:
11.2.0.3 Grid Infrastructure diskmon Will be Offline by Default in Non-Exadata Environment (Doc ID 1346881.1)

RESIZE Datafiles and Tempfile IN ORACLE 12c

SQL> alter database datafile '/ora/app/oracle/oradata/inforln/undotbs01.dbf' resize 10G;

Database altered.




SQL> alter database tempfile '/ora/app/oracle/oradata/inforln/temp01.dbf' resize 10G;

Database altered.

SQL> alter database datafile '/ora/app/oracle/oradata/inforln/system01.dbf' resize 5G;

Database altered.

RESIZE REDOLOG FILE IN ORACLE 12c

SQL>  select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2   14 52428800 YES INACTIVE
3   15 52428800 YES INACTIVE

SQL> alter database add logfile group 4 '/ora/app/oracle/oradata/inforln/redo04.log' size 6G;

Database altered.

SQL> alter database add logfile group 5 '/ora/app/oracle/oradata/inforln/redo05.log' size 6G;

Database altered.

SQL> alter database add logfile group 6 '/ora/app/oracle/oradata/inforln/redo06.log' size 6G;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2   14 52428800 YES INACTIVE
3   15 52428800 YES INACTIVE
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.



SQL> alter database drop logfile group 2;




Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
3   15 52428800 YES INACTIVE
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

SQL> alter database add logfile group 2 '/ora/app/oracle/oradata/inforln/redo02.log' size 6G;
alter database add logfile group 2 '/ora/app/oracle/oradata/inforln/redo02.log' size 6G
*
ERROR at line 1:
ORA-00301: error in adding log file
'/ora/app/oracle/oradata/inforln/redo02.log' - file cannot be created
ORA-27038: created file already exists
Additional information: 1


$ rename the redo file

$ mv redo02.log redo02.old.log


SQL> alter database add logfile group 2 '/ora/app/oracle/oradata/inforln/redo02.log' size 6G;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2    0 6442450944 YES UNUSED
3   15 52428800 YES INACTIVE
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.



SQL> alter database drop logfile group 3;

Database altered.


$ rename the redo file

$ mv redo03.log redo03.old.log

SQL> alter database add logfile group 3 '/ora/app/oracle/oradata/inforln/redo03.log' size 6G;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2    0 6442450944 YES UNUSED
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.



SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 YES ACTIVE
2   17 6442450944 NO  CURRENT
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.




SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance inforln (thread 1)
ORA-00312: online log 1 thread 1: '/ora/app/oracle/oradata/inforln/redo01.log'


SQL> alter system checkpoint global;

System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 YES INACTIVE
2   17 6442450944 NO  CURRENT
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

$ rename the redo file

$ mv redo01.log redo01.old.log

SQL> alter database add logfile group 1 '/ora/app/oracle/oradata/inforln/redo01.log' size 6G;

Database altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1    0 6442450944 YES UNUSED
2   17 6442450944 NO  CURRENT
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.

SQL>

ORA-00845: MEMORY_TARGET not supported on this system

While converting Non-RAC to RAC database getting below error.




[oracle@rac01 bin]$ ./rconfig contorac.xml
Converting Database "oratech" to Cluster Database. Target Oracle Home: /ora/orac                         le/product/12.1.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Trace files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Setting Fast Recovery Area
Updating Oratab
Creating Password file(s)
Configuring related CRS resources
Starting Cluster Database
<?xml version="1.0" ?>
<RConfig version="1.1" >
<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="1" >
          Got Exception
        </Result>
       <ErrorDetails>
             oracle.sysman.assistants.rconfig.engine.CRSStartupException: PRCR-1079 : Failed to start resource ora.oratech.db
CRS-5017: The resource action "ora.oratech.db start" encountered the following error:
ORA-00845: MEMORY_TARGET not supported on this system
. For details refer to "(:CLSN00107:)" in "/ora/grid/log/rac02/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.oratech.db' on 'rac02' failed
CRS-2632: There are no more servers to try to place resource 'ora.oratech.db' on that would satisfy its placement policy
Operation Failed. Refer logs at /ora/oracle/oracle12/cfgtoollogs/rconfig/rconfig_12_28_16_18_43_14.log for more details.


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

Solution:





[oracle@rac01 bin]$ df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G   18G  994M  95% /
tmpfs           2.0G  1.1G  901M  55% /dev/shm
/dev/sda2        96G   27G   66G  29% /ora
/dev/sda3        20G   60M   19G   1% /tmp

# umount tmpfs
# mount -t tmpfs shmfs -o size=4G /dev/shm


# /etc/fstab

# Created by anaconda on Tue Dec 20 17:07:44 2016
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=db2938b3-db31-4abf-ac1e-9e6869f825c9 /                       ext4    defaults        1 1
UUID=ce2cece9-fa18-458c-a384-3fec9b507b2e /ora                    ext4    defaults        1 2
UUID=99e7a8dc-a14a-4a85-ad67-b2459043af3d /tmp                    ext4    defaults        1 2
UUID=c8915969-3315-4ba1-a941-0cc32240707d swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   size=4G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

#reboot