Renaming or Moving Oracle Files

original page : https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

Controlfiles

The current location of the controlfiles can be queried from the V$CONTROLFILE view, as shown below.

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL

3 rows selected.

SQL>

In order to rename or move these files we must alter the value of the control_files instance parameter.

SQL> show parameter control_files

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           C:\ORACLE\ORADATA\DB10G\CONTRO
                                                                      L01.CTL, C:\ORACLE\ORADATA\DB1
                                                                      0G\CONTROL02.CTL, C:\ORACLE\OR
                                                                      ADATA\DB10G\CONTROL03.CTL
SQL>

To move or rename a controlfile do the following.

• Alter the control_files parameter using the ALTER SYSTEM comamnd.
• Shutdown the database.
• Rename the physical file on the OS.
• Start the database.

The following SQL*Plus output shows how this is done for an instance using an spfile. For instances using a pfile replace the spfile manipulation steps with an amendment of the parameter in the init.ora file.

SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL' SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\ORADATA\DB10G\CONTROL01.CTL C:\ORACLE\ORADATA\DB10G\RENAME_CONTROL01.CTL

SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>

Repeating the initial query shows that the the controlfile has been renamed in the data dictionary.

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL

3 rows selected.

SQL>

Logfiles

The current location of the logfiles can be queried from the V$LOGFILE view, as shown below.

SQL> SELECT member FROM v$logfile;

MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG

3 rows selected.

SQL>

To move or rename a logfile do the following.

• Shutdown the database.
• Rename the physical file on the OS.
• Start the database in mount mode.
• Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
• Open the database.

The following SQL*Plus output shows how this is done.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG' -
>  TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

Repeating the initial query shows that the the logfile has been renamed in the data dictionary.

SQL> SELECT member FROM v$logfile;

MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG

3 rows selected.

SQL>

Datafiles

Online Move (12c)

Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

SQL>

RMAN

RMAN can be used to move files with less downtime by copying them in advance of the move, then recovering them as part of the move itself. First, log in to RMAN and list the current files.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/DB11G/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/DB11G/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/DB11G/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    370      TEMP                 32767       /u01/app/oracle/oradata/DB11G/temp01.dbf

RMAN>

Copy the file(s) to the new location.

RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/DB11G/soe.dbf';

Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.

RMAN> SQL 'ALTER TABLESPACE soe OFFLINE';

Switch to the new datafile copy(s) and recover the tablespace.

RMAN> SWITCH DATAFILE 8 TO COPY;
RMAN> RECOVER TABLESPACE soe;

Turn the tablespace online again.

RMAN> SQL 'ALTER TABLESPACE soe ONLINE';

Remove the old datafile(s).

RMAN> HOST 'rm /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf';

Listing the current files shows the move is complete.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/DB11G/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/DB11G/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/DB11G/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/oradata/DB11G/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    370      TEMP                 32767       /u01/app/oracle/oradata/DB11G/temp01.dbf

RMAN>

Moving the SYSTEM tablespace is possible using a similar method, but the database must be shutdown and mounted before the switch and recover can be done.

Manual (Almost Online)

For tablespaces other than the SYSTEM tablespace, you can move the datafiles while the database is online, provided you take the relevant tablespace offline during the rename operation.

ALTER TABLESPACE tablespace-name OFFLINE NORMAL;

-- Move/Rename the physical file.

ALTER TABLESPACE tablespace-name
    RENAME DATAFILE '/original/path/to/file1',
                    '/original/path/to/file2'
                 TO '/new/path/to/file1', 
                    '/new/path/to/file2';

ALTER TABLESPACE tablespace-name ONLINE;

An example of this is shown below.

SQL> SELECT name FROM v$datafile WHERE name LIKE '%users01%';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/users01.dbf

SQL>

ALTER TABLESPACE users OFFLINE NORMAL;

HOST mv /u01/app/oracle/oradata/cdb1/users01.dbf /u01/app/oracle/oradata/cdb1/users02.dbf

ALTER TABLESPACE users
    RENAME DATAFILE '/u01/app/oracle/oradata/cdb1/users01.dbf'
                 TO '/u01/app/oracle/oradata/cdb1/users02.dbf';

ALTER TABLESPACE users ONLINE;

SQL> SELECT name FROM v$datafile WHERE name LIKE '%users02%';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/users02.dbf

SQL>

The downtime associated with the tablespace rename is dependent on the length of time the physical rename/move takes. For a simple rename in place, it should happen immediately. If the file has to be moved to a new location, it will take as long as the file move takes to complete.

Thanks Noons for pointing out this glaring omission from the article.

Manual (Offline)

The process for manually renaming a datafile is the same as renaming a logfile, but for the same of clarity it is repeated below. The current location of the datafiles can be queried from the V$DATAFILE view, as shown below.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF

4 rows selected.

SQL>

To move or rename a datafile do the following.

• Shutdown the database.
• Rename the physical file on the OS.
• Start the database in mount mode.
• Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
• Open the database.

The following SQL*Plus output shows how this is done.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF' -
>  TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

Repeating the initial query shows that the the datafile has been renamed in the data dictionary.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF

4 rows selected.

SQL>

Recreating the Controlfile

For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace.

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

SQL>

The resulting trace file in the user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all datafiles and logfiles on one step.

This is quite a drastic step and it may affect the usefulness of existing backups, especially if the controlfile is being used as the recovery catlog.

Data Guard Environments

In Data Guard environments you have to be careful about renaming and moving files. The STANDBY_FILE_MANAGEMENT parameter determines how file changes on the primary server are applied to the standby server. When set to AUTO, files added or deleted under normal database use will be automatically created or deleted on the standby server. When set to MANUAL, this automatic maintenance will not happen.

If you are manually moving or renaming files in a Data Guard environment where STANDBY_FILE_MANAGEMENT=AUTO, you should first set STANDBY_FILE_MANAGEMENT=MANUAL, make your changes in the primary and standby environment, then set STANDBY_FILE_MANAGEMENT=AUTO again.

ORA-00020: maximum number of processes … exceeded

First check your sessions, processes and transactions system parameter.

Check Current Setting of Parameters

sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions

A basic formula for determining  these parameter values is as follows:

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

 

Then you have to check who use the connections

SQL> SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM;

MACHINE                        PROGRAM                          COUNT(*)
------------------------------ ------------------------------ ----------
ora_dbdev1                       ORACLE.EXE (SMON)                       1
ora_dbdev1                       ORACLE.EXE (CKPT)                       1
ora_dbdev1                       ORACLE.EXE (DBRM)                       1
ora_dbdev1                       ORACLE.EXE (SMCO)                       1
ora_dbdev1                       ORACLE.EXE (VKTM)                       1
ora_dbdev1                       ORACLE.EXE (PMON)                       1
ora_dbdev1                       ORACLE.EXE (J000)                       1
ora_dbdev1                       ORACLE.EXE (q001)                       1
ora_dbdev1                       ORACLE.EXE (RECO)                       1
ora_dbdev1                       ORACLE.EXE (LGWR)                       1
ora_dbdev1                       ORACLE.EXE (DIA0)                       1
ora_dbdev1                       ORACLE.EXE (MMON)                       1
ora_dbdev1                       ORACLE.EXE (J001)                       1
ora_dbdev1                       OMS                                   596
ora_dbdev1                       ORACLE.EXE (W000)                       1
ora_dbdev1                       ORACLE.EXE (q000)                       1
ora_dbdev1                       ORACLE.EXE (PSP0)                       1
AGPSVR01\ora_dbdev1              sqlplus.exe                             1
ora_dbdev1                       ORACLE.EXE (QMNC)                       1
ora_dbdev1                       ORACLE.EXE (CJQ0)                       1
ora_dbdev1                       ORACLE.EXE (DBW0)                       1
ora_dbdev1                       ORACLE.EXE (MMNL)                       1
ora_dbdev1                       ORACLE.EXE (MMAN)                       1
ora_dbdev1                       ORACLE.EXE (DIAG)                       1

24 rows selected.

In my case here OMS use 596 connections, for fast solution

Just stopped dbconsole and everything was fine.

How to list the most biggest directory on AIX

du command – To list the most biggest directory

To list the five most biggest directory, you have to perform the command below:

du -sk ./* | sort -rn | head -5

Output:

$ du -sk ./* | sort -rn | head -5
27921556  ./dir100d
1920392 ./dir200d
14036   ./sqllib
8       ./dir300d
5       ./dir400d

Explaining the command above:

du

• The du command displays the number of blocks used for files. If the File parameter specified is actually a directory, all files within the directory are reported on. If no File parameter is provided, the du command uses the files in the current directory.
• ./* – This is the location where the du command will be display the number of blocks used for files inside the ./* directory

•  Specifying the -s flag reports the total blocks for all specified files or all files in a directory.

•  Specifying the -k flag shows the size of blocks in Kilobytes
• Specifying the -g flag shows the size of blocks in Gigabytes

sort

• The sort command sorts lines in the files specified by the File  parameter and writes the result to standard output.
•  -r     Reverses the order of the specified sort.
•  -n   Sorts numeric fields by arithmetic value.

head

• The head command writes to standard output a specified number of lines or bytes of each of the specified files, or of the standard input. If no flag is specified with the head command, the first 10 lines are displayed by default.

Some Routine DB Checklist Queries

Mviews Not Refreshed from Last 7 Days:
select mview_name from user_mviews where LAST_REFRESH_DATE < sysdate – 7;

Total Number of Tables:
select count(1) from user_tables where table_name not like ‘%$%’

Total Number of Mviews:
select count(1) from user_mviews

Total Number of Indexes:
select count(1) from user_indexes where index_type in (‘FUNCTION-BASED NORMAL’,’NORMAL’)

Total Number of Invalid Objects:
select count(1) from user_objects where status = ‘INVALID’

Total Number of Objects Created in last 7 days:
select count(1) from user_objects where CREATED >= sysdate – 7

Total Database Size:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Size in GB”
from
dual

Oracle DBA Daily Checklist

Oracle DBA Daily Checklist

1. Check that all instances are up.

2. Monitor alert log entries (using tail -f)

3. Check that dbsnmp(SNMP subagent for Oracle DB) is running.

4. Check all last night backups were successful.

5. Check all database archiving are done.

6. Check tablespaces should not be used more that 95%.

7. Check all crons and Oracle Jobs are completed without any error

8. Verify resources for acceptable performance.

9. Identify bad growth of Segments.

10. Identify atleast 1 top resource consuming query

ORA-00020 maximum number of processes exceeded

ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under “Oracle Database Server Messages”. These messages are generated by the Oracle database server when running any Oracle program.

Reference: Oracle Documentation

How to increase PROCESSES initialization parameter:

1.    Login as sysdba
sqlplus / as sysdba

2. Check Current Setting of Parameters
sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions

3.    If you are planning to increase “PROCESSES” parameter you should also plan to increase “sessions and “transactions” parameters
A basic formula for determining  these parameter values is as follows:

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

4.    These paramters can’t be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;
sql> shutdown abort
sql> startup

 

http://nimishgarg.blogspot.co.id/2012/05/ora-00020-maximum-number-of-processes.html