Splitting datafiles to new file system for restoration

For replication of database to new file system, we can use below script to split
production datafiles. It will generate set newname datafile output by evenly splitting based on available storage and checking & correcting duplicate file names.

Script input: List of file new file system name and size in GB for parameters
file_system_name (0)  and file_system_size (0)   in below script. Parameter  m_threshold := 100 will keep 100GB free space for each file system.

SET serveroutput on
SET serveroutput on size 1000000
SET LINESIZE 120

DECLARE
   TYPE str_array IS TABLE OF VARCHAR2 (100)
      INDEX BY BINARY_INTEGER;
   TYPE num_array IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
   file_system_name     str_array;
   file_system_size     num_array;
   path_selection       NUMBER    := 0;
   m_threshold          NUMBER    := 0;
   no_space_available   EXCEPTION;
   PROCEDURE rman_rename (
      str_array_in            str_array,
      num_array_in   IN OUT   num_array,
      threshold      IN       NUMBER
   )
   IS
   BEGIN
      FOR file_name1 IN
         (SELECT   file#, BYTES / 1024 / 1024 / 1024 AS gb, concat(RTRIM(file_name, '.dbf'),'.dbf') file_name
              FROM (SELECT   SUBSTR (NAME,
                                     INSTR (NAME, '/', -1, 1) + 1
                                    ) file_name
                        FROM v$datafile
                    GROUP BY SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1)
                      HAVING COUNT (1) = 1),
                   v$datafile
             WHERE SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1) = file_name
          UNION ALL
          SELECT   file#, BYTES / 1024 / 1024 / 1024 AS gb, file_name
              FROM (SELECT BYTES, file#,
                              SUBSTR
                                    (SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1),
                                     1,
                                       INSTR (SUBSTR (NAME,
                                                        INSTR (NAME, '/', -1,
                                                               1)
                                                      + 1
                                                     ),
                                              '.',
                                              1,
                                              1
                                             )
                                     - 1
                                    )
                           || '_'
                           || CHR
                                 (  96
                                  + ROW_NUMBER () OVER (PARTITION BY file_name ORDER BY file_name)
                                 )
                           || '.dbf' file_name
                      FROM (SELECT   COUNT (1),
                                     SUBSTR (NAME,
                                             INSTR (NAME, '/', -1, 1) + 1
                                            ) file_name
                                FROM v$datafile
                            GROUP BY SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1)
                              HAVING COUNT (1) > 1),
                           v$datafile
                     WHERE SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1) =
                                                                     file_name)
          ORDER BY 2 DESC)
      LOOP
         <<main_loop>>
        LOOP
            IF ((num_array_in (path_selection) - threshold) >= file_name1.gb
               )
            THEN
               num_array_in (path_selection) :=
                                 num_array_in (path_selection)
                                 - file_name1.gb;
               DBMS_OUTPUT.put_line (   'set newname for DATAFILE '
                                     || file_name1.file#
                                     || ' to '
                                     || ''''
                                     || str_array_in (path_selection)
                                     || file_name1.file_name
                                     || ''''
                                     || ';'
                                    --|| num_array_in (path_selection)
                                    -- || '-->'
                                    -- || file_name1.gb
                                    );
               IF (path_selection = str_array_in.COUNT - 1)
               THEN
                  path_selection := 0;
               ELSE
                  path_selection := path_selection + 1;
               END IF;
               EXIT main_loop;
            ELSE
               IF (path_selection < str_array_in.COUNT - 1)
               THEN
                  path_selection := path_selection + 1;
                  GOTO main_loop;
               ELSE
                  RAISE no_space_available;
               END IF;
            END IF;
         END LOOP;
      END LOOP;
      DBMS_OUTPUT.put_line ('Remaining Space');
      DBMS_OUTPUT.put_line ('---------------');
      FOR i IN 1 .. num_array_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line (   'PATH-->'
                               || str_array_in (i - 1)
                               || '      SPACE(GB)-->'
                               || num_array_in (i - 1)
                              );
      END LOOP;
   EXCEPTION
      WHEN no_space_available
      THEN
         DBMS_OUTPUT.put_line ('No Space Available !!');
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('OTHERS Exception');
   END rman_rename;
BEGIN
   file_system_name (0) := '/oradata/brmpp/data01/';
   file_system_size (0) := 9800.00;
   file_system_name (1) := '/oradata/brmpp/data02/';
   file_system_size (1) := 9800.00;
   file_system_name (2) := '/oradata/brmpp/data03/';
   file_system_size (2) := 9800.00;
   file_system_name (3) := '/oradata/brmpp/data04/';
   file_system_size (3) := 9800.00;
   m_threshold := 100;
   rman_rename (file_system_name, file_system_size, m_threshold);
END;
/

Oracle 10g onward tablespace point in time Recovery

When TSPITR is initiated with RMAN without specifying AUX instance then rman
create auxillary instance based on target database configuration.
In this scenerio restore channel information must be already configured in target database so aux instance can  use them in restore/recovery.

The location of the datafiles for the auxillary instance are specified using the
AUXILIARY DESTINATION clause
RECOVER TABLESPACE DATA_TBS   UNTIL LOGSEQ 2992 THREAD 1  AUXILIARY DESTINATION '/oracle/data/';
The tablespace is taken offline, restored from a backup, recovered to the specified point-in-time in the auxillary instance
and re-imported into the target database.
The tablespace in the target database should then be backed up and the tablespace brought back online.
BACKUP TABLESPACE DATA_TBS;
ALTER TABLESPACE DATA_TBS ONLINE;
On successful completion the auxillary instance will be cleaned up automatically.
example:
rman target /
run {
recover tablespace users until time “to_date(’2012-02-12 22:55:00′, ‘YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination = ‘/oracle/data/’;
backup tablespace users ;
sql ‘alter tablespace users  online’;
}

Database migration from ASM to filesystem and filesystem to ASM

Move database from file system to ASM
______________________________________
1)Prepare the init pfile from original pfile to move to ASM.
Instead of control_files parameter use db_create_file_dest and db_recovery_file_dest  to point to ASM diskgoup.
2)Note controlfile location(v$control) eg:/oracle/data/control.dbf
3)Backup database as RMAN copy
rman target /
run
{
backup as copy database format ‘+DATA’;
}
4)shutdown database after backup completion.
Startup nomount with new pfile to move to ASM and restore controlfile to ASM
rman target /
RMAN>restore controlfile from '/oracle/data/control.dbf';
RMAN>alter database mount;
RMAN>switch database to copy;
RMAN>recover database;
RMAN>alter database open;
5)Add new redolog file and new temporary tablespace in ASM diskgroup
 and drop the old redo logs and  temporary tablespace.
 Also create spfile to ASM using new pfile.

Moving database from ASM to file system
_______________________________________
1)Create a backup as copy of the database to a directory in the file system.

rman target /
run
{
 backup as copy database format ‘/oracle/oradata/%U’;
}

--> You can allocate multiple disk channel and specify format '‘/oracle/oradata/%b' to keep same file name under new directory structure.

2)Note the controlfile path on ASM and use this path to restore controlfile
eg:'+DATA/controlfile/current.112.734565445';
Shutdown database.
3)Prepare the init pfile with control_files pointing to file system
and startup nomount
rman target /
RMAN>restore controlfile from '+DATA/controlfile/current.112.734565445';
RMAN>alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
4)Add new redolog files and new temporary tablespace in file system
 and drop the old redo logs and  temporary tablespace from ASM.
 Also create spfile to file system using new pfile.

CSS Timeout Computation in Oracle Clusterware

The CSS misscount parameter represents the maximum time, in seconds, that a network heartbeat can be missed before entering into a cluster reconfiguration to evict the node. The following are the default values for the misscount parameter and their respective versions when using Oracle Clusterware* in seconds:
OS  10g (R1 &R2) 11g 
Linux 60       30
Unix 30 30
VMS 30 30
 Windows 30 30

*CSS misscount default value when using vendor (non-Oracle) clusterware is 600 seconds. This is to allow the vendor clusterware ample time to resolve any possible split brain scenarios.

CSS HEARTBEAT MECHANISMS AND THEIR INTERRELATIONSHIP
The synchronization services component (CSS) of the Oracle Clusterware maintains two heartbeat mechanisms
 1.) the disk heartbeat to the voting device and
 2.) the network heartbeat  across the interconnect which establish and confirm valid node membership in the cluster.
 Both of these heartbeat mechanisms have an associated timeout value. The disk heartbeat has an internal i/o timeout interval (DTO Disk TimeOut), in seconds, where an i/o to the voting disk must complete. The misscount parameter (MC), as stated above, is the maximum time, in seconds, that a network heartbeat  can be missed. The disk heartbeat i/o timeout interval is directly related to the misscount parameter setting. There has been some variation in this relationship
between versions as described below:
9.x.x.x
 NOTE, MISSCOUNT WAS A  DIFFERENT ENTITY IN THIS RELEASE
10.1.0.2
 No one should be on this version
10.1.0.3
 DTO = MC - 15 seconds
10.1.0.4
 DTO = MC - 15 seconds
10.1.0.4+Unpublished Bug 3306964
 DTO = MC - 3 seconds
10.1.0.4 with CRS II Merge patch
 DTO =Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration
10.1.0.5
 IOT = MC - 3 seconds
10.2.0.1 +Fix for unpublished Bug 4896338
 IOT=Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration

10.2.0.2
 Same as above (10.2.0.1 with Patch Bug:4896338

10.1 - 11.2
 During node join and leave (reconfiguration) in a cluster we need to reconfigure, in that particular case we use Short Disk TimeOut (SDTO) which is in all versions SDTO = MC – reboottime (usually 3 seconds)


Misscount drives cluster membership reconfigurations and directly effects the availability of the cluster. In most cases, the default settings for MC should be acceptable.  Modifying the default value of misscount not only influences the timeout interval for the i/o to the voting disk, but also influences the tolerance for missed network heartbeats across the interconnect.

Tuning using SQL Tuning advisor

Tuning for SQL_ID=d86a5g6dm7shd using SQL Tuning advisor
1) Use SQL_TEXT or SQL_ID to define tuning task
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      =>   'd86a5g6dm7shd',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 300,
                          task_name   => 'd86a5g6dm7shd_tuning_task',
                          description => 'Tuning task for statement d86a5g6dm7shd.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
2) Execute the tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task( task_name   => 'd86a5g6dm7shd_tuning_task');
3) Check status of tuning task
SELECT task_name, status FROM dba_advisor_log WHERE task_name='d86a5g6dm7shd_tuning_task';
4) Report of tuning task
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('d86a5g6dm7shd_tuning_task') AS recommendations FROM dual;
5) Drop the define tuning task
exec  DBMS_SQLTUNE.drop_tuning_task (task_name => 'd86a5g6dm7shd_tuning_task');

Another eg using sql text
1. Create the tuning task:
   declare
   stmt_task VARCHAR2(64);
   begin
   stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from service_subscriptions s2 where s2.group_code = ''MEH'' and  tss_id not in (''800'', ''93'', ''939'', ''9341'')', task_name => 'SRVC_SUB_TUNING');
   end;
2. Execute the tuning task:

   exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SRVC_SUB_TUNING');

3. After the execution finishes display result(Connect as system user):
   SET LONG 10000;
   SET PAGESIZE 1000
   SET LINESIZE 200
   SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SRVC_SUB_TUNING') from dual;

7 Habits of Highly Effective People

Habit 1: Be Proactive
              The Habit of Choice
Habit 2: BeginWith the End in Mind
               The Habit of Vision
Habit 3: Put First Things First
               The Habit of Integrity and Execution
Habit 4:ThinkWin-Win
               The Habit of Mutual Benefit
Habit 5: Seek First to Understand, Then to Be Understood
               The Habit of Mutual Understanding
Habit 6: Synergize
                The Habit of Creative Cooperation
Habit 7: Sharpen the Saw
                The Habit of Renewal

CVU IPMP Group Consistency Check Failure - 11.2.0.3

If you install Oracle Grid Infrastructure on a cluster that has Active-Passive IPMP configured, then the prerequisite check for Oracle Solaris IPMP group fail-over consistency may fail.

Solaris IPMP group fail-over consistency check - This is a check to verify the current selection of public and private network classifications is consistent with network interfaces in fail-over dependency of an IPMP group
  Check Failed on Nodes: [mps033,  mps039]  
Verification result of failed node: mps033 
 Details: 
 - 
PRVG-1509 : IPMP fail-over group "PRODUCTION" with interface list "nxge3,nxge7" on node "mps033" has interfaces "nxge7" which are not part of current public network classifications "nxge3/10.64.238.0"  - Cause:  Found an additional fail-over dependency on an interface in an IPMP group which is not classified as a public interface on the identified node.  - Action:  Ensure that all the identified non-participating network interfaces in the IPMP group are classified as public network interface on the identified node. Use command 'oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:public}' to classify the network interface as public. 
Back to Top  
Verification result of failed node: mps039 
 Details: 
 - 
PRVG-1509 : IPMP fail-over group "PRODUCTION" with interface list "nxge3,nxge7" on node "mps039" has interfaces "nxge7" which are not part of current public network classifications "nxge3/10.64.238.0"  - Cause:  Found an additional fail-over dependency on an interface in an IPMP group which is not classified as a public interface on the identified node.  - Action:  Ensure that all the identified non-participating network interfaces in the IPMP group are classified as public network interface on the identified node. Use command 'oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:public}' to classify the network interface as public.

Workaround: Ignore and continue installation. After installation as root:
If the interface is available on the server, subnet address can be identified by command:
oifcfg iflist
/oracle/sgwgrid/11.2.0.3/bin/crsctl/srvctl modify nodeapps -S '10.64.238.0/255.255.254.0/nxge3|nxge7'
oifcfg setif -global nxge3/10.64.238.0:public
oifcfg setif -global nxge7/10.64.238.0:public

Oracle Multimedia invalid after upgrade from 10.2.0.4 to 11.2.0.3

Oracle Multimedia  invalid after upgrade from 10.2.0.4 to 11.2.0.3

fix:

sqlplus / as sysdba

startup upgrade
/*replace the version 10.2.0.4 in the dbms_registry.loaded
command with the version from which the database has been upgraded
*/

EXECUTE dbms_registry.loaded('ORDIM','10.2.0.4');

spool Oracle_Multimedia.log

@<Oracle_11g_Home>/ord/im/admin/imdbmig.sql 

shutdown immediate

startup
?/rdbms/admin/utlrp.sql

check now status should be valid

while upgrade from 11.1.0.7 to 11.2.0.2
there was slowness in dbua taking more than 15 hours. After truncating SYS.AUD$
and noarchivelog mode , dbua completed in less than a hour.

Some possible reasons which will fix the issue:
- Truncate large SYS.AUD$ or SYS.FGA_LOG$ table
- database in archivelog mode(Upgrade in noarchivelog mode)
- flashback database logging on
- database triggers (i.e. if you do ddl auditing)
- maybe also disable change block tracking