Tag: controlfile

Check Your Database Size

Posted on by Osama Mustafa in Scripts
Datafiles : 

select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;

Tempfiles:

select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;

Your redo logs can also use up a large amount of disk space – especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).

Redologs:

select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database.  If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

Controlfiles:


select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.

Flash Recovery Area:


select * from v$recovery_file_dest; 
select * from v$flash_recovery_area_usage;  

These views will show sizing details and free space available.

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file


select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;

Files referenced by database directories or the utl_file_dir parameter:


Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables –

select a.owner||’.’||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;

(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;

Thank you 
osama Mustafa

RAC TO SINGLE NODE STEPS

Posted on by Osama Mustafa in Articles, Database
Take RMAN backup of the production RAC database

1-
RMAN> run{
allocate channel c1 type disk format ‘/tmp/%U’;
backup database;
backup archivelog all;
backup controlfile ;
}

2-Create Pfile from RAC for single Node Using SQLPLUS :
 SQL>create pfile ” from spfile ;

3-Open pfile that you create it and modify the following parameters manually:
%dest, control_files
log_archive_dest_1
cluster_database_instances

4-Use the pfile created above to STARTUP NOMOUNT the database on the new host
$ sqlplus / as sysdba
SQL> startup nomount;

5-rman target /
A-restore controlfile from ‘/tmp/’;
OR
  restore controlfile from ‘/media/moh_1509/MEPSLIVE_20110919_762231895_3885.ctl’




B-ALTER DATABASE MOUNT ;
C.catalog backuppiece  ‘.bkp’ repeat it for all of backupset .
D.catalog backuppiece  ‘.arch’ repeat it for all of ARCHIVELOG.

E.
run
{
set newname for datafile 1 to ”;
set newname for datafile 2 to  ”;
set newname for datafile 3 to ”;
set newname for datafile 4 to  ”;
set newname for datafile 5 to  ”;
set newname for datafile 6 to  ”;
     restore database;
     switch datafile all;
}
NOTE : datafile number must be taken from the production system .

F.
sqlplus / as sysdba

alter database rename file ” to ”;
alter database rename file ” to ”;
alter database rename file ” to ”;

G.
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

Then Delete unnecessary Redolog .

6.RMAN TARGET /
RMAN>RECOVER DATABASE ;

7.SQL> alter database open resetlogs;

If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:

_no_recovery_through_resetlogs=TRUE

8. 
SQL> select THREAD#, STATUS, ENABLED 2 from v$thread;

SQL> select group# from v$log where THREAD#=2;
SQL> alter database disable thread 2;

9.DROP REDO LOG FROM NEW SINGLE NODE BY :
 SQL> alter database clear unarchived logfile group ;  
SQL>alter database drop logfile group ;

After doing this you can remove undo tablespace for other instance 

10.To Remove it :
 SQL> show parameter undo;
SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;
SQL> drop tablespace UNDOTBS2 including contents and datafiles; 

Now you have single node instance 🙂

Thank you
Osama Mustafa