How to enable ArchiveLog mode in Oracle Database RAC Environment?

Steps to enable Archivelog mode in RAC environment:

The following steps need to be taken to enable archive logging in a RAC database environment:

1. Shutdown immediate all database instances
$ srvctl stop database -d <db_unique_name>

2. Startup database in mount mode
$ srvctl start database -d <db_unique_name> -o mount

3. Enable archive logging
$ sqlplus / as sysdba
sql> alter database archivelog;
sql> exit;

4. Stop database
$ srvctl stop database -d <db_unique_name>

5.Start all database instances
$ srvctl start database -d <db_unique_name>

6.Verify archiving is enabled/disabled 
sql> archive log list;

Oracle Database ASM Features: REBALANCING

ASM has the ability to rebalance data across the disks whenever a disk is added,dropped or replaced.
The performance of rebalance operation is controlled by initialization parameter ASM_POWER_LIMIT.

ASM_POWER_LIMIT Parameter

Default value 1

Range of Values 0 to 11 (Prior to 11gR2)

Range of Values 0 to 1024(From 11gR2)

For 10g Databases:

SQL> alter diskgroup DG1 rebalance power 12;
alter diskgroup DG1 rebalance power 12
                                              *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup DG1 rebalance power 11;

Diskgroup altered.


For 11g AND 12c Databases

SQL> alter diskgroup DG1 rebalance power 1025;
alter diskgroup DG1 rebalance power 1025
                                      *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup DG1 rebalance power 1024;

Diskgroup altered.

The higher the value of rebalancing power, faster would be rebalancing operation.

12C New Feature: Invisible Columns

Starting from 12c, Oracle allows columns to be hidden from application.
Columns can be made invisible in the CREATE TABLE statement or  using an ALTER TABLE statement.By default columns are visible. Invisible columns can be made visible again using an ALTER TABLE statement.Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns.

General characteristics for Invisible Columns:

 1.  Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns.
 2.  The database usually stores columns in the order in which they were listed in the CREATE TABLE statement. If you add a new column to a table, then the new column becomes the last column in the table's column order. When a table contains one or more invisible columns, the invisible columns are not included in the column order for the table.
3.Invisible columns are not seen unless specified explicitly in the SELECT list.
 
 
Create Table with INVISIBLE column

SQL> CREATE TABLE test1 (a INT, b INT INVISIBLE, c INT Not NULL);

Table created.

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
C NOT NULL NUMBER(38)


SQL> select COLUMN_NAME,COLUMN_ID,HIDDEN_COLUMN from user_tab_cols where table_name ='TEST1';

COLUMN_NAME COLUMN_ID HIDDEN_COLUMN
--------------- ---------- ---------------
C               2          NO
B                          YES
A               1          NO


 The invisible column wont be displayed in a DESCRIBE statement. The column id for an invisible (or hidden ) column will be NULL.



Changing the column to visible / invisible using the ALTER TABLE statement

SQL> ALTER TABLE test1 MODIFY (b VISIBLE);

Table altered.

While inserting data into table with INVISIBLE Columns, proper care need to be taken.
Otherwise,data will be inserted to unexpected columns.

Query to Find Free Space in a Tablespace

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) "Allocated Used/Unused MB",
round(Ftbs.mb) "Allocated_Free MB",
round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
Ftbs.MaxBytes "MaxChunk MB"
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)
/