Managing Availibility of Tablespaces in Oracle

Taking tablespaces Offline or Online

You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

We usually take tablespaces offline for maintenance purposes.

To alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

To Take a Tablespace Offline give the following command

SQL>alter tablespace ica offline;

To again bring it back online give the following command.

SQL>alter tablespace ica online;

To take individual datafile offline type the following command

SQL>alter database datafile ‘/u01/oracle/ica/ica_tbs01.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/oracle/ica/ica_tbs01.dbf’ online;

Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode.  If the datafile has become corrupt or missing when the database is running in NOARCHIVELOG mode then you can only drop it by giving the following command

SQL>alter database datafile ‘/u01/oracle/ica/ica_tbs01.dbf’ offline for drop;

Making a Tablespace Read only.

Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user’s update privilege level.

To make a tablespace read only

SQL>alter tablespace ica read only

Again to make it read write

SQL>alter tablespace ica read write;

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace:

ALTER TABLESPACE users RENAME TO usersts;

The following affect the operation of this statement:

• The COMPATIBLE parameter must be set to 10.0 or higher.
• If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.
• If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.

Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely

To drop a tablespace give the following command.

SQL> drop tablespace ica;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it anyhow then add the following keyword

SQL>drop tablespace ica including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.

But If  you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace ica including contents and datafiles;

Disabling ARCHIVELOG mode

To disable ARCHIVELOG mode status, complete the following:

Procedure

1. Log in as OS user, oracle and enter the following commands:

   $ export ORACLE_SID=<MYDB>

   where <MYDB> is the name of the database

   $ sqlplus /nolog
   SQL> connect / as sysdba

2. To enable ARCHIVELOG mode status, enter the following SQL commands:

   SQL> Shutdown
   SQL> Startup mount
   SQL> Alter database noarchivelog;
   SQL> alter database open;

3. To check the ARCHIVELOG mode status, enter the following SQL command:

   SQL> archive log list;
   Database log mode 						No Archive Mode
   Automatic archival 					Disabled
   Archive destination 					/oradump/<MYDB>
   Oldest online log sequence 		7
   Current log sequence 					9

Enabling ARCHIVELOG mode

To enable ARCHIVELOG mode status, complete the following steps:

1. Log in as user oracle and enter the following commands:

   $ export ORACLE_SID=<MYDB>

   where <MYDB> is the name of the database

   $ sqlplus /nolog
   SQL> connect / as sysdba

2. To enable ARCHIVELOG mode status, enter the following SQL commands:

   SQL> Shutdown
   SQL> Startup mount
   SQL> Alter database archivelog;
   SQL> alter database open;

3. To check the ARCHIVELOG mode status, enter the following SQL command:

   SQL> archive log list;
   Database log mode 						Archive Mode
   Automatic archival 					Enabled
   Archive destination 					/oradump/<MYDB>
   Oldest online log sequence 		7
   Next log sequence to archive 	7
   Current log sequence 					9

Results

The database is now in ARCHIVELOG mode.

Find redo log members / redo log file size / redo log status

Find redo log file members.

SQL> col member format a50
SQL> select GROUP#,TYPE,MEMBER from v$logfile;

GROUP#       TYPE     MEMBER
----------  -------  --------------------------------------------------
 3           ONLINE  /oracle/openprod/origlogA/openprod/redo03a.log
 3           ONLINE  /oracle/openprod/mirrlogA/openprod/redo03b.log
 2           ONLINE  /oracle/openprod/origlogA/openprod/redo02a.log
 2           ONLINE  /oracle/openprod/mirrlogA/openprod/redo02b.log
 1           ONLINE  /oracle/openprod/origlogA/openprod/redo01a.log
 1           ONLINE  /oracle/openprod/mirrlogA/openprod/redo01b.log

To find redo log file size.

SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,
MEMBERS,STATUS from v$log;

GROUP#      THREAD#   SEQUENCE#  BYTES/1024/1024 MEMBERS   STATUS
---------- ---------- ---------- --------------- ---------- ----------------
 1              1        10        100                 2       INACTIVE
 2              1        11        100                 2       INACTIVE
 3              1        12        100                 2       CURRENT

Combined view of both v$logfile & v$log.

ORA-27140: attach to post/wait facility failed or ORA-27301: OS failure message: Not owner

After you installed Oracle, did you run the $ORACLE_HOME/root.sh script as the root user? If after running this script, you still have a problem, then do the following:

cd $ORACLE_HOME/bin
ls -l oracle

Your permissions should appear as follows:

-rwsr-s--x 61M Jun 21 2018 oracle 

If not, then issue the following as ‘oracle’:

chmod 6751 oracle

Steps for Enabling/Disable ARCHIVELOG mode for SAP with Oracle

SQL> select log_mode from v$database;

LOG_MODE|
————
NOARCHIVELOG

• Take a trace of controlfile for backup.

SERVER: sidadm 1> sqlplus “/as sysdba”

SQL> alter database backup controlfile to trace;
Database altered.
SQL> exit

• Now stop sap and oracle database normally. Listener process need not to be stopped.

SERVER:sidadm 2> stopsap

• Now we have to start oracle in mount stage.

  SERVER:orasid 1> sqlplus “/as sysdba”
  SQL> startup mount;

• We will now change to archivelog mode for oracle and then will open the database.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered. 

• Archivelog mode can be verified by command select log_mode from v$database
  SQL> select log_mode from v$database;
  LOG_MODE
  ————
  ARCHIVELOG
• For enabling automatic archival we can use the following SQL commands
  SQL> alter system archive log start;
  System altered.
• Now we can see that automatic archival is enabled

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/SID/oraarch/SIDarch
Oldest online log sequence 28151
Next log sequence to archive 28154
Current log sequence 28154

• Now we can start sap system.
  SERVER:sidadm 1> startsap

How to find table size?

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments where segment_type=’TABLE’ and segment_name='<yourtablename>’;

Tablespace layout

The tablespace layout contains the following tablespaces:

• SYSTEM (tablespace for the Oracle dictionary)

• SYSAUX (additional tablespace as of Oracle database 10g)

• PSAPTEMP (default tablespace for database sort operations)

• PSAPUNDO/PSAPROLL (default tablespace for segment undo/rollback)

• PSAP<SCHEMA_ID> (default tablespace for all SAP objects of the ABAP stack)

• PSAP<SCHEMA_ID>DB (default tablespace for all SAP objects of the Java stack)

• PSAP<SCHEMA_ID>USR (default tablespace for all customer objects)

• PSAP<SCHEMA_ID><REL> (default tablespace for release-dependent data)

• PSAP<SCHEMA_ID<NAME> (Additional tablespace for example, for large tables)

Introduction to LOBs

What exactly is a LOB? LOB is the acronym for Large OBject. A table can have a column of the type LOB, this column holds unstructured data like documents, images, spreadsheets, xml files … When you create a LOB column for a table, you are actually creating a separate LOB segment. And when creating an LOB segment, also a LOB index is created together with it, that helps to find the data of the LOB, which is stored in different places in a tablespace.

This is how the LOB segments look like:

SYS_LOBXXXXXXXXXXCXXXXX$$, where XXXX is a hexadecimal number

And the index name of the LOB segement:

SYS_ILXXXXXXXXXXXCXXXXX$$, where XXXX is a hexadecimal number.

The hexadecimal numbers for both the LOB segment and the LOB index are the same.

There are several types of LOBs that can be found in the Oracle database:

BLOB: Binary LOBs
CLOB: Character LOBs
NCLOB: National Character LOBs
BFILE: Binary File

But in ABAP dictionary the datatype represented by LOBs are:

RATRING BLOB
STRING CLOB

LOBs can be stored as basicfiles or securefiles. Basicfiles are the old way of storing these segments, the recommended way of storing LOB segments is now securefile. The main reasons for this would be performance, additional features and security. So it is really recommended in case you still have LOB segments stored as basicfiles, to convert them into securefiles. This can be done easily, as a reorganization option via brtools.
It is possible to check the LOB segments that are not stored as secure LOBs via the command:

SELECT DISTINCT TC.TABLE_NAME, 
TE.SECUREFILE
FROM
DBA_TAB_COLUMNS TC, DBA_TABLES T, DBA_LOBS TE
WHERE TC.TABLE_NAME = T.TABLE_NAME and TC.TABLE_NAME = TE.TABLE_NAME and TE.TABLE_NAME = T.TABLE_NAME
and TC.OWNER LIKE 'SAP%' and 
(TC.DATA_TYPE = 'BLOB' OR TC.DATA_TYPE = 'CLOB') and SECUREFILE = 'NO';

These segments can be reorganized as secure LOBs with brtools via this path:

BRTOOLS => Segment management -> Reorganize tables -> choose reorganization option “lob2lob”

In Oracle 11 and 12 there is also a parameter name db_securefile that specifies how should a LOB segment be created by default.
These are the possible values according to Oracle documentation:

” NEVER

Any LOBs that are specified as SecureFiles are created as BasicFiles LOBs. All SecureFiles-specific storage options and features (for example, compress, encrypt, deduplicate) will cause an exception. The BasicFiles LOB defaults will be used for storage options not specified.

PERMITTED

LOBs are allowed to be created as SecureFiles.

PREFERRED

All LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is a Manual Segment Space Management tablespace. When PREFERRED is set, cases where BASICFILE would otherwise be inherited from the partition or column level LOB storage are ignored; the LOBs will be created as SecureFiles instead.

ALWAYS

Attempts to create all LOBs as SecureFiles LOBs but creates any LOBs not in an Automatic Segment Space Managed (ASSM) tablespace as BasicFiles LOBs, unless SECUREFILE is explicitly specified. Any BasicFiles LOB storage options that are specified will be ignored and the SecureFiles LOB defaults will be used for all storage options not specified.

IGNORE

The SECUREFILE keyword and all SecureFiles options are ignored “

But SAP mentions in the note 1426979 for Oracle 11g only these options: permitted, never, always and ignore and clearly states that for SAP no other value is allowed, except “permitted”, that is also the default value. This means that a new created LOB, that has no specific options added under Oracle 11g, will be created by default as basicfile and this can be converted to securefile anytime.
But what about Oracle 12c? The problem is that SAP has not updated this information for 12c, but from what I know, is that the new default for 12c is the value PREFFERED which means that LOBs are now created as securefiles directly, if there is nothing else specifically mentioned in the creation clause and it is also the only accepted value by SAP for Oracle 12c.

From my experience I had a lot of situations when LOB segments increased very large and had to be reorganized. I recommend that these are regularly checked and reorganized when feasible. Here are some commands that will for sure help during the process. And remember, do not panic if after the reorganization you will not find that LOB segment in the database, this is recreated during the reorganization with another name.

How to check out the LOBs from a table and the mapped columns:

select SEGMENT_NAME,owner,table_name,column_name from dba_lobs where TABLE_NAME='table_name';

Vice versa you can also check what table belongs to a lob segment:

select owner, table_name, column_name FROM dba_lobs WHERE segment_name = 'lob_segment';

How to check the size of the LOB:

select sum(bytes)/1024/1024 from dba_extents where segment_name='XXXXXXX';

How to get an approximation of how much really does a LOB segment occupy and check if it fragmented:

SELECT 'table_name' TABLE_NAME, 'column_name' COLUMN_NAME, ROUND(SUM(DBMS_LOB.GETLENGTH("column name"))/ 1024 / 1024) "NET (MB)"
FROM "object_owner"."table_name";

Important SAP Notes about this topic:

563359 – Performance optimization for tables with LOB columns
2384534 – LOB conversion and table compression with BRSPACE 7.40 for Oracle 12c
2142497 – What to do for large lobsegment on Oracle database?
500340 – FAQ: LOBS
1426979 – Oracle 11g: SecureFiles – The new way to store LOB data

How to fix ORA-12547 TNS lost contact when try to connect to Oracle

There can be different reasons of this issue, but usually the problem is in wrong privileges/ownership of some Oracle binaries located in $ORACLE_HOME/bin directory. Perhaps, Oracle database binaries were installed / linked not correctly or something or somebody has changed the files’ ownership.

Solution

1. 1. Ensure the DB is up and running and you can connect locally AS SYSDBA to the database using Oracle binaries owner (usually oracle:oinstall Unix / Linux user). If it does not work, probably you encounter a different problem.
   2. Check privileges of an Oracle file on Unix / Linux host where database is running:

cd $ORACLE_HOME/bin
ls -ltr oracle
-rwxr-xr-x    1 oracle   oinstall       136803483 Mar 16 20:32 oracle

Change permissions as below:
chmod 6751 oracle
ls -ltr oracle
-rwsr-s–x 1 oracle oinstall 136803483 Mar 16 20:32 oracle