Tablespace missing in DBA_FREE_SPACE

Today i came across one of the interesting issue in my development database. Developer was loading the data and he got the ORA-01653: unable to extend table XXX.YYYYY by 128 in tablespace ZZZZ  error and data load got failed.  Immediately, he called me and reported this error.  I went and checked the DBA_FREE_SPACE and not able to find the tablespace... 

hmmm... interesting!

I found that tablespace will disappear in DBA_FREE_SPACE for the following scenario.

1. The tablespace has no free space. 
2. Tablespace is offline
3. Tablespace is temporary tablespace

Let us test the first case.  

 Create the tablespace.

SQL> create tablespace test

datafile  '/u02/oradata/test.dbf' size 10M;    2

Tablespace created.

Verify if the tablespace available in DBA_FREE_SPACE

SQL>

select tablespace_name from dba_free_space

where tablespace_name='TEST';  SQL>   2

TABLESPACE_NAME

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

TEST

Load the data  until it runs out of space

SQL>

create table gt_objects as select *

from dba_objects;

SQL>   2

Table created.

SQL>

SQL> alter table gt_objects move tablespace test;

Table altered.

SQL>

begin

for i in 1..1000000 loop

insert into gt_objects select * from dba_objects;

commit;

end  loop;

end;

/

SQL>   2    3    4    5    6    7  begin

*

ERROR at line 1:

ORA-01653: unable to extend table SYS.GT_OBJECTS by 128 in tablespace TEST

ORA-06512: at line 3

 Check if the tablespace disappeard in DBA_FREE_SPACE

SQL> SQL> select tablespace_name from dba_free_space

  2  where tablespace_name='TEST';

no rows selected

SQL>

Drop the table and see if it appears in DBA_FREE_SPACE

SQL> drop table gt_objects;

Table dropped.

SQL> select tablespace_name from dba_free_space

  2   where tablespace_name='TEST';

TABLESPACE_NAME

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

TEST

SQL>

Drop the tablespace

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL>

Conclusion :  The minimum space required for the tablespace to be reported in the DBA_FREE_SPACE is that of the extent size of the tablespace. We have to be careful if we use DBA_FREE_SPACE for tablespace monitoring query.

Oracle11g Data Guard manual switch over steps

The Manual Switch over steps were tested in oracle 11.2.0.4 and below steps were followed in Critical production database many times in the past and switch over were completed successfully!
We have primary and two standby database on this data guard environment. I tested the switch over between primary(devdb12) and first standby(devdb12_dg).
Step 1 Database verification

1. Verify the standby database and make sure standby database is in sync with primary database.
2. Tail the alert log on both primary and standby to monitor the database during the entire switch over time.  Use this command  :  tail -f  alert_$ORACLE_SID.log
3. Make sure below data guard parameters(log_archive_dest, log_archive_dest_state, log_archive_config, fal_client, fal_server etc) are correctly configured on standby site. This would help log  shipping go smooth after the switch over.

Let application team bring the application down.  Now DBA start switching over the database!
Step 2 Database Active Connection Verification

Make sure, no application connection on the database.
select count(*),username from v$session group by v$session
To be safer side, run the below script to kill any non local process to complete our switch over smoothly.
tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; echo $tokill;

tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; kill -9 $tokill;

Step 3 The primary and standby should show the status as below.

set linesize 200

col DB_UNIQUE_NAME form a30

col DATABASE_ROLE for a20

col OPEN_MODE for a30

col SWITCHOVER_STATU for a30

select DB_UNIQUE_NAME,Database_role,open_mode,switchover_status from v$database;

The Primary database SWITCHOVER_STATUS can be either TO STANDBY or SESSIONS ACTIVE.  The standby database SWITCHOVER_STATUS should be NOT ALLOWED.

Step 4: Convert Primary to Standby:

On Primary

Execute the below command on primary (devdb12)

alter database commit to switchover to physical standby with session shutdown;

At this stage, the primary database(devdb12) is not completely converted to standby.  The primary database is down and it is ready to covert for standby database. The standby database (devdb12_dg and devdb12_dr) are ready to convert to primary.  After completing Step 6, the primary(devdb12) will be turned to standby. Step 6 can be executed on this step. But it is always good practice to start standby after starting the primary to reduce the application down time. All three database status should show as below after running the above command.

The devdb12 SWITCHOVER_STATUS should be  RECOVERY NEEDED. The devdb12_dg,devdb12_dr  database SWITCHOVER_STATUS should be TO PRIMARY

Step 5:  Convert Standby to Primary

We are converting devdb12_dg to primary database. Hence login as devdb12_dg and run the below command.

alter database commit to switchover to primary with session shutdown;

alter database open;

After opening the database, the status should be as below. The switchover_status is FAILED_DESTINATION.

Now devdb12_dg becomes primary and ready to take the transactions.  Still it is not ready to ship the archive log file.  At this moment, DBA can ask application team to start the application. Now application is pointing to new primary(devdb12_dg). The old primary(devdb12) and second standby(devdb12_dr)  status should be as below. The  old primary(devdb12) switch over status is RECOVERY_NEEDED. The second standby(devdb12_dr) switch over status is NOT ALLOWED.

Step 6: - Convert Original Primary(devdb12)  to Standby
Login to old primary(devdb12) and run the below command.

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;
-- Wait for few minutes and make sure archive logs are shipping to standby database
alter database recover managed standby database cancel;

alter database open;

alter database recover managed standby database using current logfile disconnect;

Step 7  Verify the database mode for both primary and standby. Now devdb12 is standby and devdb12_dg is primary database. Devdb12_dr is still standby and no change on devdb12_dr.

Step 8 Verify that archive logs are shipping correctly.

Standby database UNNAMED file error issue

This Scenario was tested in Active Data Guard 11.2.0.4.

Some time, DBA create or add the data file on primary site with out verifying the file system size on standby site. In this case, standby file creation will fail and log shipping will break in standby database. 

Let us test this scenario. For this testing, devdb12 will be primary DB and devdb12_dg will be standby DB. The standby site /data01 file system size is lower than the primary site.

Let us add data file on primary site.

sys@devdb12> create tablespace test datafile

'/data01/oradata/devdb12/test.f01' size 3000m

/

Tablespace created.

sys@devdb12>

The data file was created successfully in primary. But it is failed in standby due to insufficient disk space. We get the below error message in standby alert log. MRP background process shutdown due to this issue.

The data file is created in primary site and not found in standby database.

sys@devdb12> select status,bytes/1024/1024 from
  2   v$datafile where name= '/data01/oradata/devdb12/test.f01';

STATUS                BYTES/1024/1024
--------------------- ---------------
ONLINE                           3000
sys@devdb12>

sys@devdb12_dg> select status,bytes/1024/1024 from
  2   v$datafile where name= '/data01/oradata/devdb12/test.f01';

no rows selected

sys@devdb12_dg>

However, the data file is created in standby site as below.

SQL> select name from v$datafile where name like '%UNNAME%';

NAME
----------------------------------------------------
/ora/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

SQL>

Now the Data Guard replication is broken and file was not able to ship to standby due to sufficient space on the /data01 file system.

How do we fix this issue?

Step 1  Add More space in standby /data01 file system and run the below command in standby database. If you don't have luxury to add space, then we can  map the file to different file system in standby database.

Step 2 Execute the below command in standby database. If you decided to create the data file in different file system, then modify the script accordingly. 

shutdown immediate;
startup nomount;
alter database mount standby database;
alter system set standby_file_management='MANUAL' scope=both;
alter database create datafile  '/ora/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/data01/oradata/devdb12/test.f01';
alter system set standby_file_management='AUTO' scope=both;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Step 3   Verify the datafile in standby database.

devdb12_dg> select status,bytes/1024/1024 from
v$datafile where name= '/data01/oradata/devdb12/test.f01';
  2
STATUS                BYTES/1024/1024
--------------------- ---------------
ONLINE                           3000

devdb12_dg>

Step 4 Monitor the alert log on primary and standby and  make sure no issues

How to move the Oracle temp file to new location?

The purpose of this topic is to explain how to move the temp files from one file system to another file system with out impacting the application. The post is tested in Oracle 11.2.0.4.

The temp files are located at /orasys/oradata The goal is to move this temp files to dedicated file system  /oratemp/oradata

Step1 Copy the parsed temp file to new location as non parsed file.

cp --sparse=never /orasys/oradata/temp01.dbf /oratemp/oradata/temp01.dbf

Step2 create the temp file with new temp files
alter tablespace temp add tempfile '/oratemp/oradata/temp01.dbf' reuse;

Step3 Drop the temp files on the original location.

ALTER DATABASE TEMPFILE '/orasys/oradata/temp01.dbf' DROP INCLUDING DATAFILES;

Pre allocate space for Oracle temp files

Temporary tablespaces use sparse files  meaning that the actual file created on disk is initially very small and grows to the specified size only as data is written to the file.

Let us say, the file system size is 100G and day shift DBA added 30G temp file on the file system.  But we all know that, temp file will create with very small size initially and  it will grow only if oracle started using the temp segment. 

Again, night shift DBA joined the duty and he saw that, the file system free space shows 99G free space. He added 3 data files each 30G size and he filled up 90G on the file system. A few hours later, oracle started using the temp segment and gradually growing the temp space and exceeding the file system size which puts Database under risk. We are cheated to believe that there is more than enough free disk space.

It is always best practice to use dedicated file system for temp files.  Especially, Data Guard, it is highly important to use dedicated file system. 

How do we resolve this issue?  How do we create non sparse temp files at the beginning of creating temp file?

Option 1

Step 1 Create the sparse temp file, say temp01.dbf

alter tablespace temp add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf' ;

Step 2 Execute the Cartesian join query as below to fill up the temp file

select * from dba_source a, dba_source b, dba_source c, dba_source d order by 1,2,3,4

Wait for ORA-01652:unable to extend temp segment by 128 in tablespace TEMP

Step 3 Copy the non-parsed temp file to another file called temp02.dbf

cp temp01.dbf temp02.dbf

Step 4 create the another temp file

Alter tablespace temp add tempfile ‘temp02.dbf’ reuse

Now we have no-parse temp files for both temp01.dbf and temp02.dbf


Option 2

Step 1 Create the sparse temp file, say temp01.dbf
alter tablespace temp add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf' ;

Step 2 copy the non-parsed temp file to another file called temp02.dbf
cp --sparse=never temp01.dbf temp02.dbf

Step 3 create the another temp file
Alter tablespace temp add tempfile ‘temp02.dbf’ reuse

Step 4 Drop the parsed temp file temp01.dbf
alter database tempfile ‘temp01.dbf’ drop including datafiles;


Repeat the step for adding another non-parsed temp files

Option 3

Create a normal datafile (via a transitory tablespace) and then add it to the temporary tablespace using the REUSE clause as follows:

create tablespace test_tbs  datafile '/oratemp/oradata/chfsdb20/temp01.dbf'  size 20480m;


Drop the tablespace without removing data files from file system

drop tablespace test_tbs;

alter tablespace TEMP add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf'   reuse;

How to activate the Oracle11g standby database

This exercise was tested in oracle11gR2 version. When primary database went down and database is not coming up with in the expected time frame.  In this scenario, we need to bring the standby up with READ/WRITE mode.

Here is the steps. Login to standby database and run the below steps.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL>

Once activated, then we need rebuild the old primary as a standby database or flash back the database and apply recent archive log files.

Cloning an existing ORACLE_HOME on Unix

Before we go further into this section, we need to understand the purpose of cloning and advantage of cloning the Oracle home binaries.

At what circumstance, cloning  oracle home is useful?

1) When you setup the standby database, you don't want to install the oracle binary from the scratch. You might install some extra component or you might miss some component which is not in primary database.  The best practice, it is always primary and standby should be Apple to Apple. Binary cloning will be appropriate for setting up the standby database server.

2) If you are performing multiple oracle database installations on several server as Production, Pre-production, Development, QA and Test, then you may want to use binary cloning to make sure you install same component across the environment.  This approach is much faster than installing from the scratch and then applying  PSU/CPU patch on each server.

Source Database : devdb12
Target Database : devdb13

The Binary version is 11.2.0.4
Oracle home : /ora/app/oracle/product/11.2.0/db_4

Step 1(Clone the binary in source database)

Login to source database:

cd /ora/app/oracle/product/11.2.0

tar -cvf oracle_110204_binaries.tar db_4

check  tar -tvf /ora/app/oracle/product/11.2.0/oracle_11204_binaries.tar |more

The above command ensure the binaries are zipped.

scp the oracle_11204_binaries.tar file to devdb13 under /ora/app/oracle/product/11.2.0/

On step1,  oracle binaries are cloned and copied to target server.

Step 2(Untar the binaries in target server)

Login to devdb13

Extract Oracle 11.2.0.4 binaries

cd /ora/app/oracle/product/11.2.0

tar -tvf oracle_11204_binaries.tar|more

tar -xvf oracle_11204_binaries.tar

Delete or move the oracle_11204_binaries.tar file if you need to.

cd /ora/app/oracle/product/11.2.0

mv oracle_11204_binaries.tar /work/exports

Step 3 (create the clone script)

cd /homo/oracle

cat clone.sh
#!/bin/sh
echo "Clone started at `date`" | tee -a clone.log
perl /ora/app/oracle/product/11.2.0/db_4/clone/bin/clone.pl ORACLE_BASE=/ora/app/oracle ORACLE_HOME=/ora/app/oracle/product/11.2.0/db_4 ORACLE_HOME_NAME=OraDb11g_home4 '-O"LOCAL_NODE=devdb13.acsrr.com"'
echo "Clone ended at `date`" | tee -a clone.log

Step 3 (Clone the binary)

nohup ./clone.sh &

Here is the cloning log screenshot, We need to execute root,sh as requested in the log file.

Unix Admin can run the  root.sh as a root user. Unix admin can
also provide sudo privilege to oracle account and DBA can run
this script.

Here is the log for executing root.sh.

sh /ora/app/oracle/product/11.2.0/db_4/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_4

Enter the full pathname of the local bin directory: [/usr/local/bin]:

   Copying dbhome to /usr/local/bin ...

   Copying oraenv to /usr/local/bin ...

   Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.