user ddl

set head off
REM set long 150
set linesize 150
set longchunksize 150
set pages 0
set long 99999999
set feedback off
set echo off
spool /home/oracle/user_MIGRATE.sql
SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) || ';' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists (select 'x' from dba_role_privs drp where drp.grantee=upper('USERNAME'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists (select 'x' from dba_sys_privs drp where drp.grantee=upper('USERNAME'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',USERNAME) || ';' DDL
FROM DBA_USERS
where exists(select 'x' from dba_tab_privs drp where drp.grantee=upper('USERNAME'))
/
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';'from DBA_ROLE_PRIVS
/
spool off


ddl username

createUser.sql

set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual 

creation of partition table

SQL> CREATE TABLE “SH”.”PART_TABLE”
2     (       “PARAMETER” VARCHAR2(32) NOT NULL ENABLE,
3     “TIMESTAMP” NUMBER NOT NULL ENABLE,
4     “VALUESUM” NUMBER NOT NULL ENABLE,
5     “VALUECOUNT” NUMBER DEFAULT 1 NOT NULL ENABLE,
6      CONSTRAINT “PK_INDEX01? PRIMARY KEY (“PARAMETER”, “TIMESTAMP”) ENABLE,
7      CONSTRAINT “VALUECOUNT_POS” CHECK ( valuecount > 0 ) ENABLE
8     ) ORGANIZATION INDEX COMPRESS 1 PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING
9    TABLESPACE “TS1?
10    STORAGE(
11    BUFFER_POOL DEFAULT)
12   PCTTHRESHOLD 50
13    PARTITION BY RANGE (“TIMESTAMP”)
14   (PARTITION “PART_1?  VALUES LESS THAN (1277596800001)
15    PCTFREE 10 INITRANS 2 MAXTRANS 255
16    STORAGE(INITIAL 50331648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18    TABLESPACE “TS1? );

Table created.

SQL> select segment_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select partition_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select table_name,tablespace_name from dba_tables where table_name=’PART_TABLE’;

TABLE_NAME                     TABLESPACE_NAME
—————————— ——————————
PART_TABLE

SQL> select partition_name,tablespace_name from dba_tab_partitions where partition_name=’PART_1';

PARTITION_NAME                 TABLESPACE_NAME
—————————— ——————————
PART_1

SQL> select index_name,partition_name,subpartition_count,status from dba_ind_partitions where tablespace_name=’TS1';

INDEX_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT STATUS
—————————— —————————— —————— ——–
PK_INDEX01                     PART_1                               0 USABLE

SQL> select owner,table_name,index_name,status from dba_indexes where index_name=’PK_INDEX01';

OWNER      TABLE_NAME                     INDEX_NAME              STATUS
———- —————————— ——————————
SH         PART_TABLE                     PK_INDEX01              N/A

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1' group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             1

SQL> ALTER TABLE SH.PART_TABLE ADD
2    PARTITION “PART_2?  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1? ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1' group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             2

Insert some  data

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971

$exp SH/SH@RACDB file=part3.dmp log=part3_exp.log tables=SH.PART_TABLE:PART_2 feedback=10000 statistics=none constraints=n

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: constraints on tables will not be exported

About to export sSHcified tables via Conventional Path …
. . exporting table PART_TABLE
. . exporting partition              PART_2
…………………………………………………………………
…..
809310 rows exported
Export terminated successfully without warnings.

SQL> alter table SH.PART_TABLE drop partition PART_2;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

==================================================================================================================================

sql>select Period,count(1) from arch_fct.sc_products_sum where period between '01-JAN-2001' and '01-DEC-2005' GROUP by period;

==================================================================================================================================

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

SQL>    alter table SH.PART_TABLE add
2    PARTITION “PART_2?  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1? ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

$ imp SH/SH@RACDB file=part3.dmp log=part3_imp.log fromuser=SH buffer=200000 recordlength=6400 tables=PART_TABLE:PART_2 feedback=10000 ignore=y constraints=n statistics=none

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SH, not by you

import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SH’s objects into SH
. . importing partition “PART_TABLE”:”PART_2?
…………………………………………………………………
…..
809310 rows imported
Import terminated successfully without warnings.

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971






command to move table, index, table partition, index partition of owner = MSC
o/p like
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_0 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_1023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_2023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_SUPPLIES move partition ALLOC_SUPPLIES_0 tablespace APPS_TS_TX_DATA;


SQL> create table test1 tablespace test as select *from dict ;

Table created.

SQL> create table test2 tablespace test as select *from cat ;

Table created.

SQL> create table test3 tablespace test as select *from cat ;




select partition_name from dba_tab_partitions where tablespace_name='COST_REV_FACT_DATA' and table_name='PA_UNBLLD_UNRND_FCT';

select partition_name,owner,segment_name from dba_segments where tablespace_name='COST_REV_FACT_DATA';







select partition_name, high_value
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;


select partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name='TEST_TBL'
order by partition_name, subpartition_position;




http://gavinsoorma.com/2015/03/oracle-12c-partitioning-new-features/


SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='ORDERS_PART';


We can see that for the partitions where indexing is not enabled, the index has been created as UNUSABLE.

SQL> SELECT PARTITION_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME='SALES_12C_IND';




Find if you need to add new partitions
                                                 Last update (2010-07-09 21:11:28)
                                                                                                                   Date added (2008-02-29 08:44:19)
Summary
If you have partitions in your database it is very common in the future to need a method knowing in which partition you are currently inserting data and if this is the last available partition. If yes, then you must add very soon new partitions to the table.

In this example partitions have been setup for tables in AX, AR, GL schemas of an E-Business Suite ERP. The algorith is very simple. It checks if rows exist in the last partition of the table, if yes then it creates the ddl sql to add the new partition.

Connect as sys from sqlplus and set
sql>set serveroutput on

DECLARE
CURSOR c1 IS
SELECT 'select count(*) from '|| a.table_owner || '.' || a.table_name ||' partition('||a.partition_name||')' s1,a.*
FROM dba_tab_partitions a,dba_part_tables b
WHERE a.table_owner IN ('AX','AR','GL')
AND a.table_name NOT LIKE 'HZ%'
AND a.table_name NOT LIKE 'AX_DOC%'
AND a.partition_position = b.partition_count
AND b.owner = a.table_owner
AND b.table_name = a.table_name;

CURSOR c2(v_owner VARCHAR2,v_tname VARCHAR2) IS
SELECT high_value,partition_name
   FROM (SELECT * FROM dba_tab_partitions
             WHERE table_owner = v_owner 
             AND   table_name  = v_tname
         ORDER BY partition_position DESC) WHERE ROWNUM <3;


CURRENT_MAX_VALUE_NUMBER NUMBER;
CURRENT_MAX_VALUE_NUMBER2 NUMBER;
v_statement   VARCHAR2(200);
v_rec        dba_tab_partitions%ROWTYPE;
v_oldtname   dba_tab_partitions.table_name%TYPE := NULL;
v_maxhvalue  dba_tab_partitions.high_value%TYPE;
v_minhvalue  dba_tab_partitions.high_value%TYPE;
v_newhvalue  dba_tab_partitions.high_value%TYPE;
v_newpname   dba_tab_partitions.partition_name%TYPE;
BEGIN
FOR i IN c1 LOOP
v_statement           := i.s1;
    EXECUTE IMMEDIATE v_statement INTO CURRENT_MAX_VALUE_NUMBER;
    IF CURRENT_MAX_VALUE_NUMBER > 0 THEN
   FOR j IN c2(i.table_owner,i.table_name) LOOP
       IF i.table_name = v_oldtname THEN
      v_minhvalue := j.high_value;
   ELSE
      v_maxhvalue := j.high_value; 
   END IF;
   v_oldtname := i.table_name;
   END LOOP;
   v_newhvalue := TO_NUMBER(v_maxhvalue) + (TO_NUMBER(v_maxhvalue)-TO_NUMBER(v_minhvalue));
       v_newpname  := SUBSTR(i.partition_name,1,LENGTH(i.partition_name)-3)||
       LTRIM(TO_CHAR(SUBSTR(i.partition_name,LENGTH(i.partition_name)-2)+1,'099'));
       DBMS_OUTPUT.PUT_LINE('The table '|| i.table_owner || '.'|| i.table_name||' needs partition');
   DBMS_OUTPUT.NEW_LINE();
       DBMS_OUTPUT.PUT_LINE('Alter table '||i.table_name||' add partition '||v_newpname ||
       ' VALUES LESS THAN ('||v_newhvalue||')');
   DBMS_OUTPUT.PUT_LINE(' LOGGING NOCOMPRESS TABLESPACE '||i.tablespace_name);
   DBMS_OUTPUT.PUT_LINE(' PCTFREE '||i.pct_free);
   DBMS_OUTPUT.PUT_LINE(' INITRANS '||i.ini_trans||' MAXTRANS '||i.max_trans);
   DBMS_OUTPUT.PUT_LINE(' STORAGE    (INITIAL '||i.initial_extent);
   DBMS_OUTPUT.PUT_LINE(' NEXT '||i.next_extent);
   DBMS_OUTPUT.PUT_LINE(' MINEXTENTS '||i.min_extent);
   DBMS_OUTPUT.PUT_LINE(' MAXEXTENTS '||i.max_extent);
   DBMS_OUTPUT.PUT_LINE(' PCTINCREASE '||i.pct_increase);
   DBMS_OUTPUT.PUT_LINE(' BUFFER_POOL      DEFAULT)');
   DBMS_OUTPUT.NEW_LINE();
    END IF;
END LOOP;
END;
Tip:It works for partitions based on NUMBER columns, not DATE

To check the parition value

select a.table_owner, a.table_name, a.partition_name, a.high_value from dba_tab_partitions a
where a.table_name in ('DAILY_INVENTORY_FACTS')
and partition_name in (select max(partition_name) from dba_tab_partitions b
    where b.table_owner = a.table_owner  and b.table_name = a.table_name )
order by a.table_name;

___________________________________________________________________

Create on temp machine.


CREATE TABLE DBMON.TEST_PARTITION_TEMP AS
(
SELECT 1 AS PARTITION_POSITION, TABLE_OWNER, TABLE_NAME, SUBSTR(PARTITION_NAME, 1, (LENGTH(PARTITION_NAME)-3))    AS PARTITION
_NAME,
TO_NUMBER(SUBSTR(PARTITION_NAME, (LENGTH(PARTITION_NAME)-2),3)) AS PARTITION_NUMBER, PARTITION_NAME AS VALIDATION,
TO_LOB(HIGH_VALUE) AS HIGH_VALUE
FROM DBA_TAB_PARTITIONS
WHERE
( TABLE_NAME,PARTITION_POSITION ) IN
(
SELECT    TABLE_NAME, MAX(PARTITION_POSITION)
FROM DBA_TAB_PARTITIONS
WHERE
TABLE_OWNER = 'EDIADMIN'
GROUP BY TABLE_NAME
)
);



CREATE TABLE DBMON.TEST_PARTITION AS
SELECT PARTITION_POSITION, TABLE_OWNER, TABLE_NAME, PARTITION_NAME, PARTITION_NUMBER, VALIDATION,
TO_DATE(SUBSTR(HIGH_VALUE,10,11), 'YYYY-MM-DD') AS HIGH_VALUE
FROM DBMON.TEST_PARTITION_TEMP;



SELECT TEMP1.TABLE_OWNER, TEMP1.TABLE_NAME, TEMP1.PARTITION_NAME, TEMP1.HIGH_VALUE, TEMP2.HIGH_VALUE
FROM  DBMON.TEST_PARTITION TEMP1, DBMON.TEST_PARTITION_TEMP TEMP2
WHERE
TEMP1.TABLE_NAME = TEMP2.TABLE_NAME
ORDER BY 2;

_________________________________________________________________________
prepare_daily_inventory_facts.sh

script to partitions.


#!/bin/ksh

if [[ $# -ne 1 ]]
then
print "Usage: prepare_daily_inventory_facts.sh  <days>"
print "Example: prepare_daily_inventory_facts.sh 1 \n"
exit -1
fi

typeset -i WEEK=$1
typeset -i x=0
SCRIPT_FILE=daily_inventory_facts.sql

cat header.txt > ${SCRIPT_FILE}

while [[ $x -ne $WEEK ]];
do

##################################
# Create the partitions
#
sqlplus /nolog @prepare_daily_inventory_facts.sql > prepare_daily_inventory_facts.sql.out

cat prepare_daily_inventory_facts.sql.out | grep  "ALTER"  | grep -v TABLE_OWNER >> ${SCRIPT_FILE}
print "Partitions for day: ${x} as added."

x=$x+1


done

cat tail.txt >> daily_inventory_facts.sql


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

prepare_daily_inventory_facts.sql

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME ||
       ' ADD PARTITION ' || PARTITION_NAME || TO_CHAR(PARTITION_NUMBER+1)  ||
       ' VALUES LESS THAN (TO_DATE(' || '''' || ' '|| to_char(HIGH_VALUE+1, 'YYYY-MM-DD HH24:MI:SS') || ''
'' ||
       ', ' || '''' || 'SYYYY-MM-DD HH24:MI:SS' || '''' || ', ' || '''' || 'NLS_CALENDAR=GREGORIAN' || '''
' || '));'
FROM DBMON.TEST_PARTITION
where TABLE_NAME = 'DAILY_INVENTORY_FACTS';

UPDATE DBMON.TEST_PARTITION SET PARTITION_NUMBER = PARTITION_NUMBER+1, HIGH_VALUE=HIGH_VALUE+1
WHERE TABLE_NAME = 'DAILY_INVENTORY_FACTS';

COMMIT;

quit

______________________________________________________________________

header .txt

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

_________________________________________________

tail.txt

quit;

_________________________-

daily_inventory_facts.sql  

ALTER TABLE DSSADMIN.DAILY_INVENTORY_FACTS ADD PARTITION DIF_PART05859 VALUES LESS THAN (TO_DATE(' 2019-07-23 00:00:0

0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

ALTER TABLE DSSADMIN.DAILY_INVENTORY_FACTS ADD PARTITION DIF_PART05860 VALUES LESS THAN (TO_DATE(' 2019-07-24 00:00:0

0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

Change No archivelog to Archive log in Rac

srvctl stop database -d TEST

srvctl start database -d TEST -o mount


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            +RECO_DG01
Oldest online log sequence     9
Current log sequence           12
SQL>  select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
TEST1          MOUNTED
TEST2          MOUNTED

SQL> alter database archivelog;

Database altered.

[oracle@OMPR preupgrade]$ srvctl stop database -d TEST
[oracle@OMPR preupgrade]$ srvctl start database -d TEST

SELECT log_mode FROM gv$database;

ACL

SQL> begin
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => '/sys/acls/AddressDoc.xml',
description=>'ACL for UTL_MAIL',
principal=>'STAT_CUSTGOV',
is_grant=>TRUE,
privilege=>'connect');
COMMIT;
End;
/  2    3    4    5    6    7    8    9   10

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => '/sys/acls/AddressDoc.xml',
host => '*');
COMMIT;
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> BEGIN
        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
          acl   => '/sys/acls/AddressDoc.xml',
          principal => 'STAT_CUSTGOV',
          is_grant  => TRUE,
          privilege => 'connect');
END;
/ 

PL/SQL procedure successfully completed.

Partition DDL

Partitions


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||'  PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='OR_LINE_FACTS';



select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('PO_RECEIVING_FACTS','PRIORITY_FACTS','SALES_FACTS','SALES_PROD_SUPP_ORG_MONTH_AGG','SALE_IBT_INVOICE_FACTS','SLS_PRD_SP_ORG_MO_AGG');


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F','W_RP_MED_ASSIGNMENT_DETAILS_F','W_RP_MED_ASSIGNMENT_F');


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('AR_BALANCE_STG_HIST','CISP_ALLOCATED_REV_COSTS_HIST','CISP_ALLOCATED_REV_COSTS_HIST','DISCRETE_HIST','INSBRN_HIST','INSBRN_NK_HIST','INSBRN_NK_HIST','KVI_SUMMARY_HIST','MED_KVI_SUMMARY','OHTLNE_HIST','OHTLNE_HIST','ORSLNE_HIST','ORSLNE_HIST','WFCTOTAL_HIST');


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('SALES_F','ZIU_DAILY_PLANNED_ORDER_FACTS','Z_INTPART_TAB','Z_INTPART_TAB','Z_TEST');

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('SALES_FACTS_NEW');

SUB partitions
____________

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move SUBPARTITION '||SUBPARTITION_NAME||' compress; '  from all_tab_subpartitions WHERE table_name in ('KVI_SUMMARY_WBREAKS_FINAL_STG','KVI_SUMMARY_WBREAKS_STG','OMSTG.MED_KVI_SUMMARY','OMSTG.KVI_SUMMARY_WBREAKS_STG');


Advance compression query
________________________

 select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COM
PRESS ADVANCED NOLOGGING PARALLEL 4; '  from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F');

recompile objects

set pagesize 0 head off feedb off echo off
spool validate_all.sql

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where object_type in ('PROCEDURE','FUNCTION','VIEW','TRIGGER','MATERIALIZED VIEW')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile package;'
from dba_objects where object_type in ('PACKAGE')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects where object_type in ('PACKAGE BODY')
and status='INVALID' order by owner
/
select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects where object_type = 'JAVA SOURCE' and status = 'INVALID';
/
select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID';
/
spool off

srvctl commands

1. STOP DATABASE :

SYNTAX – srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort

e.g

srvctl stop database -d PRODB -o normal
srvctl stop database -d PRODB -o immediate
srvctl stop database -d PRODB -o transactional
srvctl stop database -d PRODB -o abort

2. START DATABASE
SYNTAX – srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default)

e.g

srvctl start database -d PRODB -o nomount
srvctl start database -d PRODB -o mount
srvctl start database -d PRODB -o open

3. STOP AN INSTANCE

SYNTAX – srvctl stop instance -d db_unique_name [-i “instance_name_list”]} [-o stop_options] [-f]
e.g

srvctl stop instance -d PRODB -i PRODB

4. START AN INSTANCE

SYNTAX – srvctl start instance -d db_unique_name [-i “instance_name_list”} [-o start_options]
e.g

srvctl start instance -d PRODB -i PRODB1

5. REMOVING DB FROM CRS:
SYNTAX – srvctl remove database -d db_unique_name [-f] [-y] [-v]
e.g

srvctl remove database -d PRODB -f -y

6. ADDING DB IN CRS :
SYNTAX – srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]
e.g

srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora

7. REMOVING AN INSTANCE
SYNTAX – srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
e.g

srvctl remove instance -d PRODB – I PRODB1

8.ADDING AN INSTANCE
SYNTAX – srvctl add instance –d db_unique_name –i inst_name -n node_name
e.g

srvctl add instance -d PRODB – i PRODB1 -n rachost1

9. Enable/disable auto restart of the instance

srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME

10. Enable/disable auto restart of the database

 srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME
11. ADDING A SERVICE:

SYNTAX – srvctl add servicec -d {DB_NAME} -s {SERVICE_NAME} -r {“preferred_list”} -a {“available_list”} [-P {BASIC | NONE | PRECONNECT}]

e.g

12.REMOVING A SERVICE:

SYNTAX – srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}
e.g

srvctl remove service -d PREDB -s PRDB_SRV

13. START A SERVICE
SYNTAX– srvctl start servicec -d {DB_NAME} -s {SERVICE_NAME}
e.g

srvctl start service -d PREDB -s PRDB_SRV

14. START A SERVICE

SYNTAX– srvctl stop servicec -d {DB_NAME} -s {SERVICE_NAME}
e.g

 srvctl stop service -d PREDB -s PRDB_SRV
15. RELOCATE A SERVICE

SYNTAX – srvctl relocate service -d {database_name} -s {service_name} -i {old_inst_name} -r {new_inst_name}

EXAMPLE: (Relocating service PRDB_SRV from PREDB2 to PREDB1)

srvctl relocate service –d PREDB –s PRDB_SVC –i PREDB2 –t PREDB1
16. Check the status of service
SYNTAX – srvctl status service -d {database_name} -s {service_name}
srvctl status service -d PREDB -s PRDB_SVC
17. Check the configuration of service
SYNTAX – srvctl config service -d {database_name} -s {service_name}

Rac commands

Cluster Related     Commands
---------------         --------
crs_stat -t        Shows HA resource status (hard to read)
crsstat            Ouptut of crs_stat -t formatted nicely
ps -ef|grep d.bin    crsd.bin evmd.bin ocssd.bin
crsctl check crs    CSS,CRS,EVM appears healthy
crsctl stop crs        Stop crs and all other services
crsctl disable crs*    Prevents CRS from starting on reboot
crsctl enable crs*    Enables CRS start on reboot
crs_stop -all        Stops all registered resources
crs_start -all        Starts all registered resources

NOTE
----
* These commands update the file /etc/oracle/scls_scr/<node>/root/crsstart which contains the string “enable” or “disable” as appropriate.

Database Related Commands
-------------------------
srvctl start instance -d <db_name> -i <inst_name>    Starts an instance
srvctl stop instance -d <db_name> -i <inst_name>    Stops an instance
srvctl status instance -d <db_name> -i <inst_name>    Checks an individual instance

srvctl start database -d <db_name>            Starts all instances
srvctl stop database -d <db_name>            Stops all instances, closes database
srvctl status database -d <db_name>            Checks status of all instances

srvctl start service -d <db_name> -s <service_name>    Starts a service
srvctl stop service -d <db_name> -s <service_name>    Stops a service
srvctl status service -d <db_name>            Checks status of a service

examples

srvctl stop service -d DWDB -s DWDB_SRI_ETLUSER
srvctl add service -d DWDB -s DWDB_SRI_ETLUSER -r DWDB1,DWDB2 -a DWDB1,DWDB2
srvctl add service -d DWDB -s DWDB_SRI_ETLUSER -r DWDB1 -a DWDB2
srvctl add service -d DWDB -s DWDB_export_import -r DWDB1 -a DWDB2
srvctl modify service -db DWDB -service DWDB_SRI_USER -n -preferred DWDB1,DWDB2
srvctl relocate service -s DWDB_SRI_USER -d DWDB -i DWDB1 -t DWDB2
srvctl modify service -db DWDB -service DWDB_SRI_USER -n -preferred DWDB1,DWDB2 -a DWDB1,DWDB2
srvctl config service -db DWDB
srvctl modify service -db DWDB -service DWDB_sri_user -modifyconfig -available DWDB1,DWDB2


relocating services


[oracle@rac1 ~]$ srvctl status service -d orcl -s myservice
Service myservice is running on instance(s) orcl1


[oracle@rac1 ~]$ srvctl relocate service -d orcl -s myservice -i orcl1 -t orcl2

[oracle@rac1 ~]$ srvctl relocate service -d orcl -s myservice -i orcl1 -t orcl2
-i represents an old instance

-t represents a new instance

Now check the status of service.


[oracle@rac1 ~]$ srvctl status service -d orcl -s myservice
Service myservice is running on instance(s) orcl2
[oracle@rac1 ~]$

srvctl start nodeapps -n <node_name>            Starts gsd, vip, listener, and ons
srvctl stop nodeapps -n <node_name>            Stops gsd, vip and listener

_________________________________________________________________________

BACKGROUND PROCESSES
----------------------------------
There are three main background processes you can see when doing a ps –ef|grep d.bin.  They are normally started by init during the operating system boot process.  They can be started and stopped manually by issuing the command /etc/init.d/init.crs {start|stop|enable|disable}

/etc/rc.d/init.d/init.evmd
/etc/rc.d/init.d/init.cssd
/etc/rc.d/init.d/init.crsd

SERVICES
------------
Once the above processes are running, they will automatically start the following services in the following order if they are enabled.  This list assumes you are using ASM and have a service set up for TAF/load balancing.

1.The nodeapps (gsd, VIP, ons, listener) are brought online.
2.The ASM instances are brought online.
3.The database instances are brought online.
4.Any defined services are brought online.





























Node Startup Sequence:


Operating System > Oracle Clusterware > ASM > Instance > Listener > Service


================================================================================================
CRSCTL (Clusterware Control utility)                    (crsctl)
CRS_STAT (Cluster Ready Services Statistics)            (crs_stat -h)
OCRCHECK (Oracle Cluster Registry Check Utility)        (ocrcheck -help)
OCRCONFIG (Oracle Cluster Registry Config Utility)      (ocrconfig -help)
CLSCFG (Clusterware Config Tool)                        (clscfg -help)
CLUVFY (Clusterware Verification Utility)               (cluvfy)
================================================================================================

Node Verification:
------------------

olsnodes Clusterware command is used to list the node name and their numbers configured in a cluster:

olsnodes -n
olsnodes -p
olsnodes -i

================================================================================================

Use the crs_stat command to verify the status and state of the cluster sources such as gsd, ons, and vip. Ensure all resources Target and State are flagged as ONLINE

crs_stat -t

================================================================================================

OS-level background processes that belong to the cluster

ps -ef | grep init.d

================================================================================================

cluster stack daemon processes that are running at the OS level

ps -ef | grep d.bin


================================================================================================

For init deamon processess

cd /etc/init.d
pwd
ls -l ini*


================================================================================================

Ensure that the following entries exist in the /etc/inittab OS file to facilitate the Clusterware auto startup and shutdown operations at system (node) restarts. These entries are added at the time of the root.sh script execution.


cat /etc/inittab

# Run xdm in runlevel 5
x:5:respawn:/etc/X11/prefdm -nodaemon
h1:35:respawn:/etc/init.d/init.evmd run >/dev/null 2>&1 </dev/null
h2:35:respawn:/etc/init.d/init.cssd fatal >/dev/null 2>&1 </dev/nul l
h3:35:respawn:/etc/init.d/init.crsd run >/dev/null 2>&1 </dev/null

================================================================================================

To display Oracle High Availability Services automatic startup configuration:
crsctl config crs

crsctl stop cluster   (for clusterstack)
crsctl stop crs       (for HA Services)
crsctl start cluster   (for clusterstack)
crsctl start crs       (for HA Services)

crsctl enable/disable crs       (for HA Services)


crsctl check crs      (for specific Node)
crsctl check cluster
crsctl check cluster -all   (for all Nodes)


crsctl query css votedisk


crsctl query crs activeversion
crsctl query crs softwareversion [hostname]


cluvfy comp crs -n all -verbose  (checks the complete Cluster Stack)


For OCR Check:
--------------


ocrcheck -config
ocrcheck -local

(OR)

NOTE:  The location of the OCR file can be determined by using the cat /etc/oracle/ocr.loc command. Because these files are always located on shared storage, the command can be executed from any node.


To check the integrity of the OCR file:
---------------------------------------

$ ocrcheck
$ cluvfy comp ocr –n all –verbose


To chek Voting Disk integrity or Voting Disk Issues:
----------------------------------------------------

To check the integrity of the voting disks, examine ocssd.log. Errors with the voting disks appear in the log. The following is a snippet of the output that indicates what an error may look like:

$ grep voting ocssd.log

[    CSSD]2008-09-09 10:47:09.711 [100494224] >ERROR:   clssnmvReadFatal: voting device corrupt (0x00000000/0x00000000/1//dev/sda6)
[    CSSD]2008-09-09 10:47:09.711 [3082128272] >ERROR:   clssnmvReadFatal: voting device corrupt (0x00000000/0x00000000/2//dev/sda7)


Locating OCR Backup:
--------------------

ocrconfig -showbackup auto
ocrconfig -manualbackup
ocrconfig –showbackup manual


Virtual IP (VIP) host name, VIP address, VIP subnet mask and VIP interface name:
--------------------------------------------------------------------------------

srvctl config nodeapps -a
srvctl status nodeapps -n host01


Public, private, and storage interfaces for Oracle Clusterware:
---------------------------------------------------------------

oifcfg getif


current IP address for the VIP address:
---------------------------------------
srvctl config vip -n host01


To view SCAN LISTENER configuration:
------------------------------------

srvctl config scan_listener


To view SCAN VIP configuration:
-------------------------------

srvctl config scan




NOTE:  srvctl -help   (OR)   srvctl  -h


================================================================================================

onsctl ping
onsctl start


SELECT name FROM v$database;
SELECT instance_name FROM v$instance;
SELECT instance_name FROM gv$instance;
SELECT instance_name, host_name FROM gv$instance;

To verify that instances are running, on any node:

SELECT * FROM V$ACTIVE_INSTANCES;

SELECT * FROM gv$cluster_interconnects;


srvctl -h
srvctl verb noun -h


srvctl config database -d orcl  (to display current policy of the Database orcl)
srvctl status database -d orcl -v
srvctl getenv database –d racdb
srvctl status instance -d orcl -i orcl1, orcl2
srvctl status -o oracle_home
srvctl status asm -a
srvctl status asm -n host01
srvctl config asm -n host02
srvctl status service -d orcl


srvctl stop instance -d db_unique_name -i inst_name -n node_name
srvctl start/stop/config/status -d db_unique_name

srvctl start instance -d orcl -i orcl1,orcl2
srvctl stop instance -d orcl -i orcl1,orcl2
srvctl start database -d orcl -o open
srvctl stop database -d orcl
srvctl stop instance -d orcl -i orcl1


srvctl modify database -d <dbname> -y AUTOMATIC|MANUAL
srvctl modify database -d orcl -y MANUAL;


==============================================================



SQL> select instance_name, host_name from gv$instance;

 col host_name for a20

select instance_name, host_name from gv$instance;


ps -ef | grep ora_ | grep orcl2  current running oracle process


– stop the execution of all rdbms processes (by sending the STOP signal)

[root@host02 ~]#  ps -ef | grep ora_ | grep orcl2 | awk ‘{print $2}’ | while read PID
                               do
                               kill -STOP $PID
                               done

rac command to stop and start cluster

[oracle@OMPRLEXD001 grid]$ oifcfg getif
em1 10.96.32.0 global public
em2 192.168.0.0 global cluster_interconnect,asm
[oracle@OMPRLEXD001 grid]$

[root@racnode1 bin]# olsnodes
racnode1
racnode2
Olsnodes is especially useful using the following options:
-i - includes VIP information
-n - includes node number
-p - includes private interconnect information
-v - verbose mode
[root@racnode1 bin]# olsnodes -i -n -p
racnode1 1 racnode1-priv racnode1-vip
racnode2 2 racnode2-priv racnode2-vip

[root@hseau376 ~]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online



Show CRS stat verbosely.
[grid@primary01 ~]$ crs_stat -v
NAME=ora.DATA.dg

NAME=ora.hsuuat.db
TYPE=ora.database.type
GEN_START_OPTIONS@SERVERNAME(hseau375)=open
GEN_START_OPTIONS@SERVERNAME(hseau376)=open
GEN_USR_ORA_INST_NAME@SERVERNAME(hseau375)=hsuuat1
GEN_USR_ORA_INST_NAME@SERVERNAME(hseau376)=hsuuat2
RESTART_ATTEMPTS=2
RESTART_COUNT=0
USR_ORA_INST_NAME@SERVERNAME(hseau375)=hsuuat1
USR_ORA_INST_NAME@SERVERNAME(hseau376)=hsuuat2
FAILURE_THRESHOLD=1
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375

NAME=ora.hsuuat.ismuatservice1.svc
TYPE=ora.service.type
RESTART_ATTEMPTS=0
RESTART_COUNT=0
FAILURE_THRESHOLD=0
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375

NAME=ora.net1.network
TYPE=ora.network.type
RESTART_ATTEMPTS=5
RESTART_COUNT=0
FAILURE_THRESHOLD=
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375

NAME=ora.oc4j
TYPE=ora.oc4j.type
RESTART_ATTEMPTS=1
RESTART_COUNT=0
FAILURE_THRESHOLD=2
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375

NAME=ora.ons
TYPE=ora.ons.type
RESTART_ATTEMPTS=3
RESTART_COUNT=0
FAILURE_THRESHOLD=
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375

NAME=ora.registry.acfs
TYPE=ora.registry.acfs.type
RESTART_ATTEMPTS=5
RESTART_COUNT=0
FAILURE_THRESHOLD=
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375

NAME=ora.scan1.vip
TYPE=ora.scan_vip.type
RESTART_ATTEMPTS=0
RESTART_COUNT=0
FAILURE_THRESHOLD=0
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau376

NAME=ora.scan2.vip
TYPE=ora.scan_vip.type
RESTART_ATTEMPTS=0
RESTART_COUNT=0
FAILURE_THRESHOLD=0
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau376

NAME=ora.scan3.vip
TYPE=ora.scan_vip.type
RESTART_ATTEMPTS=0
RESTART_COUNT=0
FAILURE_THRESHOLD=0
FAILURE_COUNT=0
TARGET=ONLINE
STATE=ONLINE on hseau375


[root@hseau376 ~]# ps -ef | grep lsnr | grep -v 'grep' | grep -v 'ocfs' | awk '{print $9}'
LISTENER_SCAN2
LISTENER
LISTENER_SCAN1





















*****************************************************************************







http://satya-racdba.blogspot.in/2009/12/srvctl-commands.html

Refer :


How to Stop/Start RAC components

In this article, I am going to explain how to stop/start rac components. Here is the my system:

My db version : 11.2.0.3
My Operating System : AIX 7.1
My servers hostname : node1-node2
My database name : TEST01
My instance name : TEST011-TEST012

Here is the some basic  commands, for commands details&options  please review Reference docs:


Checking CRS Status
[oracle@node1]</home/oracle> crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[oracle@node2]</home/oracle> crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Checking Node Status

[oracle@node1]</home/oracle> srvctl status nodeapps

VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP 192.168.100.101 is enabled
VIP 192.168.100.101 is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

[oracle@node2]</home/oracle> srvctl status nodeapps

VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP 192.168.100.101 is enabled
VIP 192.168.100.101 is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

Checking Clusterware Resource Status
[oracle@node1]</home/oracle> crsctl status resource -t

I will not paste result because output is not clear in that page

You can use below command which is not recommended for 11g and which is depreciated

[oracle@node1]</home/oracle> crs_stat -t
Name Type Target State Host
————————————————————
ora….DATA.dg ora….up.type ONLINE ONLINE node1
ora….ER.lsnr ora….er.type ONLINE ONLINE node1
ora….N1.lsnr ora….er.type ONLINE ONLINE node1
ora….N2.lsnr ora….er.type ONLINE ONLINE node2
ora.ORADATA.dg ora….up.type ONLINE ONLINE node1
ora.asm ora.asm.type ONLINE ONLINE node1
ora.cvu ora.cvu.type ONLINE ONLINE node2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora….SM1.asm application ONLINE ONLINE node1
ora….11.lsnr application ONLINE ONLINE node1
ora….b11.gsd application OFFLINE OFFLINE
ora….b11.ons application ONLINE ONLINE node1
ora….b11.vip ora….t1.type ONLINE ONLINE node1
ora….SM2.asm application ONLINE ONLINE node2
ora….12.lsnr application ONLINE ONLINE node2
ora….b12.gsd application OFFLINE OFFLINE
ora….b12.ons application ONLINE ONLINE node2
ora….b12.vip ora….t1.type ONLINE ONLINE node2
ora….network ora….rk.type ONLINE ONLINE node1
ora.oc4j ora.oc4j.type ONLINE ONLINE node2
ora.ons ora.ons.type ONLINE ONLINE node1
ora.test01.db ora….se.type ONLINE ONLINE node1
ora….int.svc ora….ce.type ONLINE ONLINE node2
ora….int.svc ora….ce.type ONLINE ONLINE node2
ora….kis.svc ora….ce.type ONLINE ONLINE node2
ora….est.svc ora….ce.type ONLINE ONLINE node1
ora….ry.acfs ora….fs.type ONLINE ONLINE node1
ora.scan1.vip ora….ip.type ONLINE ONLINE node1
ora.scan2.vip ora….ip.type ONLINE ONLINE node2

Oracle High Availability Services

— disable/enable Oracle HAS.
Use the “crsctl enable/disable has” command to disable automatic startup of the Oracle High Availability Services stack when the server boots up.

To can see current settings for Oracle High Availability Services stack when the server boots up, follow:

[root@node1]crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

or

[root@node1]cat /etc/oracle/scls_scr/node1/root/ohasdstr
enable

So as you can see my current setting is enable.If your system shown disable than :

For Disable:
[root@node1]crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.

[root@node1] crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.

# cat /etc/oracle/scls_scr/node1/root/ohasdstr
disable

For Enable:
[root@node1]crsctl enable has
CRS-4621: Oracle High Availability Services autostart is enabled.

Check new setting:

[root@node1] crsctl config has
CRS-4621: Oracle High Availability Services autostart is enabled.

[root@node1] cat /etc/oracle/scls_scr/node1/root/ohasdstr
enable

Stop the Oracle clusterware stack

You can use below commands:

With root user:

crsctl stop crs or crsctl stop has

[root@node1]crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘node1'
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘node1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘node1'
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.test01.db’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘node1'
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘node1'
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.node1.vip’ on ‘node1'
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘node1'
CRS-2677: Stop of ‘ora.node1.vip’ on ‘node1' succeeded
CRS-2672: Attempting to start ‘ora.node1.vip’ on ‘node2'
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘node1' succeeded
CRS-2672: Attempting to start ‘ora.scan2.vip’ on ‘node2'
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘node1' succeeded
CRS-2672: Attempting to start ‘ora.scan3.vip’ on ‘node2'
CRS-2676: Start of ‘ora.node1.vip’ on ‘node2' succeeded
CRS-2677: Stop of ‘ora.test01.db’ on ‘node1' succeeded
CRS-2676: Start of ‘ora.scan2.vip’ on ‘node2' succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN2.lsnr’ on ‘node2'
CRS-2676: Start of ‘ora.scan3.vip’ on ‘node2' succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN3.lsnr’ on ‘node2'
CRS-2676: Start of ‘ora.LISTENER_SCAN2.lsnr’ on ‘node2' succeeded
CRS-2676: Start of ‘ora.LISTENER_SCAN3.lsnr’ on ‘node2' succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.eons’ on ‘node1'
CRS-2677: Stop of ‘ora.ons’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘node1'
CRS-2677: Stop of ‘ora.net1.network’ on ‘node1' succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘node1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘node1' has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.cssdmonitor’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘node1'
CRS-2677: Stop of ‘ora.cssdmonitor’ on ‘node1' succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘node1' succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘node1' succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘node1' succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘node1'
CRS-2677: Stop of ‘ora.cssd’ on ‘node1' succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘node1'
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘node1'
CRS-2677: Stop of ‘ora.gipcd’ on ‘node1' succeeded
CRS-2677: Stop of ‘ora.diskmon’ on ‘node1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Start the Oracle clusterware stack

You can use below commands:

With root user:
crsctl start crs or crsctl start has

[root@node1] crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Start the Oracle Database
To start all Oracle RAC instances for a database:
[oracle@node1]</home/oracle> $ORACLE_HOME/bin/srvctl start database -d db_name

PS: db_name is the name of the databasethis command is starting all the instances

Stop the Oracle Database
To shut down all Oracle RAC instances for a database:
[oracle@node1]</home/oracle> $ORACLE_HOME/bin/srvctl stop database -d db_name

PS: db_name is the name of the databasethis command is starting all the instances

Start the Oracle Instance:

[oracle@node1]</home/oracle> $ORACLE_HOME/bin/srvctl start instance –d db_name –i instance_name

Stop the Oracle Instance:

[oracle@node1]</home/oracle> $ORACLE_HOME/bin/srvctl stop instance –d db_name –i instance_name
Stop/Start Listener-SCAN_LISTENER
–
srvctl stop/start listener -n node1
srvctl stop/start listener -n node2
srvctl stop scan_listener

Stop ASM

srvctl stop asm [-o stop_options] [-f]
srvctl stop asm -n node1

**************************************************************************************************************************

service status

Load Balancing Advisory and Connection Load Balancing in RAC
Oracle Net Services provides the ability to balance client connections across the instances in an Oracle RAC configuration. There are two types of load balancing that you can implement:

Client-side load balancing. Client-side load balancing balances the connection requests across the listeners. Set via LOAD_BALANCE parameter in the the net service name within a TNSNAMES.ORA file, it is just a random selection of the addresses in the address_list section of the net service name.

Server-side load balancing .With server-side load balancing, the listener directs a connection request to the best instance currently providing the service. Set via REMOTE_LISTENER in SPFILE, each instance registers with the TNS listeners running on all nodes within the cluster. By default this load balance is done on the instance on node. This can be changed to session based if required.

From 10g release 2 the service can be setup to use load balancing advisory. This mean connections can be routed using SERVICE TIME and THROUGHPUT.  Connection load balancing  means the goal of a service can be changed, to reflect the type of connections using the service.


Configuring the Load Balancing Advisory

GOAL

None(0):When set to 0(NONE), this disables the ONS notifications to the Client as to the load of the various nodes.

Service Time(1)Attempts to direct work requests to instances according to response time. So if one node takes longer to do the same work, the client can be informed of this load difference, so it can now
direct further work to the node that is taking less time.Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service.

Example to modify a service called ORCL and setup the service  to use SERVICE_TIME

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'ORCL' 
, goal => DBMS_SERVICE.GOAL_SERVICE_TIME - 
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

Throughput(2):Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. Instead of figuring out how long something takes, it is the frequency this work occurs that is used.
So if node one is able to handle 10  transactions, while node two can handle 12, in the same amount of time, then the client will be told to go to node two. So even if node two will take longer to handle a specific job, it can handle more jobs at one time then node.

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'ORCL' -
        , goal => DBMS_SERVICE.GOAL_THROUGHPUT -
       , clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);
You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views

Configuring Connection Load Balancing

CLB_GOAL.

Long(2). Use the LONG connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. It does not matter if GOAL is set or not for this condition as the point behind this setting is to balance based on  number of sessions. LONG is the default connection load balancing goal.

Example to modify service ORCL and set CLB_GOAL long

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'ORCL'
        , clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);
Short(1).Use the SHORT connection load balancing method for applications that have short-lived connections. The database uses first the GOAL setting to have PMON tell the Listener
which node to prefer

Example to modify service ORCL and set CLB_GOAL short

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'ORCL'
, CLB_GOAL => DBMS_SERVICE.CLB_GOAL_SHORT);

Please note for 11.2 onwards for DBMS_SERVICE.MODIFY_SERVICE >> This procedure is deprecated in databases managed by Oracle Clusterware and Oracle Restart with Release 11.2. While the procedure remains available in the package, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes.


status of service

[oracle@pdwddm01 ~]$ srvctl status service -d PDWDB -s PDWDB_SRI_USER
Service PDWDB_SRI_USER is running on instance(s) PDWDB1,PDWDB2

[oracle@pdwddm01 ~]$ srvctl status service -d PDWDB -s PDWDB_SRI_ETLUSER
Service PDWDB_SRI_ETLUSER is running on instance(s) PDWDB1,PDWDB2


### Changing an available instance to a preferred:

srvctl modify service -d DB2 -s DB2_UK_SEARCH -i DB21 -r
### Moving a service member from one instance to another (move and change configuration):

srvctl modify service -d DB2 -s DB2_UK_SEARCH -i DB21 -t DB22
### Relocate service (not changing configuration):

srvctl relocate service -d DB2 -s DB2_OPERATIONS_SERVICE -i DB21 -t DB23
### Enabling service:

srvctl enable service -d DB2 -s DBS_UK_PROPAGATE_SEARCH_CACHE_SERVICE3
### Disabling service:

srvctl disable service -d DB2 -s DBS_UK_PROPAGATE_SEARCH_CACHE_SERVICE3
### Start service:

srvctl start service -d DB2 -i DB21 -s DBS_UK_PROPAGATE_SEARCH_CACHE_SERVICE3
### Stop service:

srvctl stop service -d DB2 -i DB21 -s DBS_UK_PROPAGATE_SEARCH_CACHE_SERVICE3
### Remove Service:

srvctl remove service -d DB2 -s DB2_UK_SEARCH -i DB25

services in rac

srvctl stop service -d DWDB -s DWDB_SRI_ETLUSER



srvctl add service -d DWDB -s DWDB_SRI_ETLUSER -r DWDB1,DWDB2 -a DWDB1,DWDB2



srvctl add service -d DWDB -s DWDB_SRI_ETLUSER -r DWDB1 -a DWDB2



srvctl add service -d DWDB -s DWDB_export_import -r DWDB1 -a DWDB2



srvctl modify service -db DWDB -service DWDB_SRI_USER -n -preferred DWDB1,DWDB2





srvctl relocate service -s DWDB_SRI_USER -d DWDB -i DWDB1 -t DWDB2





srvctl modify service -db DWDB -service DWDB_SRI_USER -n -preferred DWDB1,DWDB2 -a DWDB1,DWDB2



srvctl config service -db DWDB





srvctl modify service -db DWDB -service DWDB_sri_user -modifyconfig -available DWDB1,DWDB2

Migrate and upgrade from 11g to 18c

http://dbmentors.blogspot.com/2013/05/restore-rman-rac-database-backup-to.html

-MGMTDB:/oraclebase/app/oracle/product/18.0.0/grid:N

test:/oraclebase/app/oracle/product/18.0.0/db11g_home:N

11G HOME --/oraclebase/app/oracle/product/18.0.0/db11g_home

export ORACLE_SID=test1

alter system set cluster_database = False scope = SPFILE sid='*';

create pfile from spfile;

1.       Stop the db service

srvctl stop database -d test

2.        start the database in mount exclusive mode:

startup mount exclusive restrict

select instance_name,status,logins from v$Instance;

\

[oracle@ompl ~]$ srvctl status database -d test

Instance test1 is running on node ompl

Instance test2 is not running on node ompl002

create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

drop database

SQL> startup nomount pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

ORACLE instance started.

Total System Global Area 7.9369E+10 bytes

Fixed Size                  2236848 bytes

Variable Size            7516196432 bytes

Database Buffers         7.1672E+10 bytes

Redo Buffers              178704384 bytes

SQL> create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

SQL> select dbid from v$database;

      DBID

----------

3447057297

______________________________________________________

node 2

[oracle@ompl002 dbs]$ cat inittest2.ora_bk

SPFILE='+DATA_DG01/test/spfiletest.ora'

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30012: undo tablespace 'UNDOTBS02' does not exist or of wrong type

Process ID: 112727

Session ID: 667 Serial number: 3

startup with pfile

starup mount pfile='/orace/initdwpx.ora'

alter database open;

alter tablespace undotbs2 rename to undotdbs02;

exit

srvctl stop database -d test

check the spfile location on both the side

crsctl stat res -t

[oracle@ompl002 admin]$ srvctl status database -d test

Instance test1 is running on node ompl001

Instance test2 is running on node ompl002

srvctl stop database -d test

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

stop the cluster on second node

[oracle@ompl002 dbs]$ sqlplus sys/***@pdrl-scan

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 9 15:07:54 2019

Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

ERROR:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

test =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prddl-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test)

    )

  )

oragrid$ srvctl stop LISTENER -n OMPRD001

oragrid$srvctl stop LISTENER -n oracledb1

oragrid$srvctl stop LISTENER -n oracledb2

srvctl stop scan_listener

srvctl start scan_listener

-____________________________________________________________________________________

set dbid=344705297

RMAN_test_CTL_2019_07_02_01_43.bkp

run

{

allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testadm02,CvInstanceName=Instance001)";

restore controlfile from 'c-3447057297-20190712-00';

}

output file name=+DATA_DG01/test/controlfile/current.267.1013439113

output file name=+DATA_DG02/test/controlfile/current.256.1013439113

sql> alter database mount;

SQL> select name from v$controlfile;

NAME

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

output file name=+DATA_DG01/test/controlfile/current.260.1013403753

output file name=+DATA_DG02/test/controlfile/current.256.1013403755

+DATA_DG01/test/controlfile/current.287.1013473803

+DATA_DG02/test/controlfile/current.279.1013473805

run {

allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_02_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_03_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_04_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_05_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_06_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_07_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_08_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_09_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_10_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

#set until time "TO_DATE('2019-07-07 11:44','yyyy-mm-dd hh24:mi:ss')";

set newname for datafile 1 to '+DATA_DG03';

set newname for datafile 2 to '+DATA_DG03';

set newname for datafile 3 to '+DATA_DG03';

set newname for datafile 4 to '+DATA_DG03';

set newname for datafile 5 to '+DATA_DG03';

set newname for datafile 6 to '+DATA_DG03';

set newname for datafile 7 to '+DATA_DG01';

set newname for datafile 9 to '+DATA_DG01';

set newname for datafile 10 to '+DATA_DG02';

set newname for datafile 11 to '+DATA_DG01';

set newname for datafile 14 to '+DATA_DG03';

set newname for datafile 16 to '+DATA_DG02';

set newname for datafile 18 to '+DATA_DG02';

set newname for datafile 19 to '+DATA_DG02';

set newname for datafile 20 to '+DATA_DG02';

set newname for datafile 21 to '+DATA_DG02';

set newname for datafile 22 to '+DATA_DG02';

set newname for datafile 23 to '+DATA_DG03';

set newname for datafile 24 to '+DATA_DG03';

set newname for datafile 25 to '+DATA_DG03';

set newname for datafile 26 to '+DATA_DG03';

set newname for datafile 29 to '+DATA_DG03';

set newname for datafile 168 to '+DATA_DG03';

set newname for datafile 200 to '+DATA_DG02';

# restore the database and switch the datafile names

restore database;

switch datafile all;

recover database;

release channel tape_01_test;

release channel tape_02_test;

release channel tape_03_test;

release channel tape_04_test;

}

Restore archive

__________________________________________________________________________________________________

rman target / catalog=rmanadmin1/****@RMADB

connect catalog rmanadm/*****@RMADB

run

{

allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_02_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

set archivelog destination to '+RECO_DG01/archive';

restore archivelog from logseq=136422 until logseq=136640 thread=1;

release channel tape_01_test;

release channel tape_02_test;

}

restore archivelog from logseq=121835 until logseq=122150 thread=2;

recover database using backup controlfile until cancel;

AUTO

select 'alter database rename file '''||member||''' to '''||replace(member,'&sourcedb','&targetdb')||''';' from v$logfile;

alter database open RESETLOGS;

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-19751: could not create the change tracking file

ORA-19750: change tracking file:

'+DATA_XDW/test/changetracking/ctf.281.816780643'

ORA-17502: ksfdcre:1 Failed to create file

+DATA_XDW/test/changetracking/ctf.281.816780643

ORA-17501: logical block size 4294967295 is invalid

ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted

ORA-17503: ksfdopn:2 Failed to open file

+DATA_XDW/test/changetracking/ctf.281.816780643

ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted

ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted

SQL> alter database disable block change tracking;

Database altered.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

recover database until available redo;

select name from v$tempfile;

select member from v$logfile;

select name, open_mode, dbid from v$database;

NAME      OPEN_MODE                  DBID

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

test    READ WRITE           34470572097

SELECT NAME FROM V$TEMPFILE;

SHUT immediate;

startup mount;

nid target=/   or nid target=sys/ChangeMe2019

starup mount;

alter database open resetlogs;

alter system set cluster_database = True scope = SPFILE sid='*';

make changes in pfile;

Add the cluster parameter

create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

create pfile='/home/dba/initdwpxw.ora' from spfile;

check the pfile parameter

test2.thread=2

test1.thread=1

test2.instance_number=2

test1.instance_number=1

test2.undo_tablespace='UNDOTBS2'

test1.undo_tablespace='UNDOTBS1'

*.remote_listener='omprl-scan:1521'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.cluster_database=true

startup nomount pfile='/home/dba/initdwpxw.ora';

create spfile='+DATA_DG01' from pfile='/home/dba/initdwpxw.ora';

SHUT IMMEDIATE

ASMCMD> cd +DATA_DG01/test/parameterfile

ASMCMD> ls -l

check the spfile

on node 1

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs

[oracle@rac1 dbs]$ cat initdwpxw1.ora

SPFILE='+DATA_DG01/test/parameterfile/spfile.267.924478703'

[oracle@rac1 dbs]$

rm spfile*

On node 2

[oracle@rac2 dbs]$ cat initdwpxw1.ora

SPFILE='+DATA_DG01/test/parameterfile/spfile.267.924478703'

[oracle@rac2 dbs]$

rm spfile*

node 1

. oraenv

test1

startup nomount;

show parameter pfile

alter database mount;

alter database open;

select name, open_mode from gv$database;

follow the above step on node 2 and start the db.

startup mount;

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA_DG01/test/spfiletest

                                                 .ora

SQL> show parameter cluster;

NAME                                 TYPE        VALUE

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

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

cluster_interconnects                string

select name,open_mode from gv$database;

select MEMBER from gv$logfile;

ADD DATABASE TO CLUSTER

crsctl check cluster -all

crsctl stat res -t

[oracle@rac1 ~]$ which srvctl

/oraclebase/app/oracle/product/18.0.0/dbhome_1/bin/srvctl

[oracle@rac1 ~]$ srvctl add database -d test -o /oraclebase/app/oracle/product/18.0.0/dbhome_1

srvctl config database -d test -a

 srvctl add instance -d test -i test1 -n ompl

 srvctl add instance -d test -i test2 -n ompl002

 srvctl config database -d test -a

database is already started in sqlplus

srvctl start database -d test

crsctl stat res ora.test.db -t

// some time disk may not appear just restart the db in cluster.

srvctl stop database -d test

srvctl start database -d test

add service

[oracle@rac1 ~]$ srvctl add service -d test -s test_SRV -r test1 -a test2

[oracle@rac1 ~]$ srvctl start service -d test -s test_SRV

[oracle@rac1 ~]$ srvctl status service -d test -s test_SRV

______________________________________________________________________________________________

change the archive to noarchive before compressing. else delete the archives.

[oracle@OMPRLE001 advance_compression]$ more check_what_to_execute

nohup ksh run_advance_compression.ksh > run_advance_compression.ksh.out &

[oracle@OMPRLD001 advance_compression]$ more run_advance_compression.ksh

nohup sqlplus /nolog @Advanced_compress_01.sql > Advanced_compress_01.sql.out &

nohup sqlplus /nolog @Advanced_compress_02a.sql > Advanced_compress_02a.sql.out &

nohup sqlplus /nolog @Advanced_compress_02b.sql > Advanced_compress_02b.sql.out &

nohup sqlplus /nolog @Advanced_compress_03a.sql > Advanced_compress_03a.sql.out &

nohup sqlplus /nolog @Advanced_compress_03b.sql > Advanced_compress_03b.sql.out &

nohup sqlplus /nolog @Advanced_compress_04.sql > Advanced_compress_04.sql.out &

nohup sqlplus /nolog @Advanced_compress_05.sql > Advanced_compress_05.sql.out &

nohup sqlplus /nolog @Advanced_compress_06.sql > Advanced_compress_06.sql.out &

nohup sqlplus /nolog @Advanced_compress_07.sql > Advanced_compress_07.sql.out &

nohup sqlplus /nolog @Advanced_compress_08.sql > Advanced_compress_08.sql.out &

__________________________________________________________________________________

++++++++++++++++++++++++++++++++++++++++++++++++++

uncompress

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select name from v$database

alter table AZIwU.SALES_F move   nocompress;

alter table AZIwU.ZIU_DAILY_PLANNED_ORDER_FACTS move     nocompress;

alter table AZIwU.Z_INTPART_TAB nocompress;

alter table AZIwU.Z_TEST nocompress;

quit;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

alter table test.PO_RECEIVING_FACTS move PARTITION SYS_P237220 ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4;

_+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

:1,$ s/compress/ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4/g

index

select 'alter index '||index_owner||'.'||index_name ||' REBUILD PARTITION ' || PARTITION_NAME || ' online nologging parallel 4 ;' from DBA_IND_PARTITIONS where STATUS='UNUSABLE';

move_nocompress

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||'  PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('SALES_F','ZIU_DAILY_PLANNED_ORDER_FACTS','Z_INTPART_TAB','Z_INTPART_TAB','Z_TEST');

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4; '  from all_tab_partitions WHERE table_name in ('SALES_FACTS_NEW');

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move SUBPARTITION '||SUBPARTITION_NAME||' compress; '  from all_tab_subpartitions WHERE table_name in ('KVI_SU','KVI_S','OMSG.MED','OMSG.KV');

 select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COM

PRESS ADVANCED NOLOGGING PARALLEL 4; '  from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F');

select * from OMSTG.KVI_SUMMARY_WBREAKS_STG where rownum<=1;

select * from OMSTG.KVI_SUMMARY_WBREAKS_FINAL_STG where rownum<=1;

select * from OMSTG.MED_KVI_SUMMARY where rownum<=1;

alter table OMSTG.MED_KVI_SUMMARY move PARTITION SYS_P254450 compress;

select * from OMSTG.KVI_SUMMARY_WBREAKS_STG where rownum<=1;

alter table DBMON.LOGIN_TRAIL move compress;

alter table DBMON.SALES_FACTS_TEST move compress;

alter table OBDW.W_GLLANCE_FS_BKP10062015 move compress;

alter table OBDW.W_GLALANCE_F_BKP10062015 move compress;

alter table test.AR_BANCE_FACTS_20180517 move compress;

if any table in advance compression.

header

SET HEADING ON SET PAGESIZE 50001. SET LINESIZE 80. SET TERMOUT OFF SET VERIFY OFF SET FEEDBACK OFF SET TRIMS OFF

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select name from v$database;

set heading off

set pages 400 lines 400

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select name from v$database

++++++++++++++++++++++++++++++++++++++++++++++++++++

crsctl check cluster -all

Upgrade:

________

http://oracle-help.com/oracle-18c/upgrading-oracle-grid-infrastructure-11gr2-to-18c/

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/db_1

[oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.

upgrade

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed

SQL> @emremove.sql

old 70: IF (upper('&LOGGING') = 'VERBOSE')

new 70: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

[oracle@test admin]$ cd $ORACLE_HOME/olap/admin/

SQL> @catnoamd.sql

/oraclebase/app/oracle/product/18.0.0/db11g_home/jdk/bin/java -jar /oraclebase/app/ora18c/product/18.0.0/software/rdbms/admin/preupgrade.jar FILE DIR /oraclebase/app/preupgrade

==================

PREUPGRADE SUMMARY

==================

/u01/preupgrade/preupgrade.log

/u01/preupgrade/preupgrade_fixups.sql

/u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups

@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-02-08T19:42:18

After run the above tool we will get below sql files.

[oracle@test ~]$ cd /u01/preupgrade/

[oracle@test preupgrade]$ ls -lrt

total 648

drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:38 

drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 oracle

-rw-r--r-- 1 oracle oinstall 7963 Feb 8 19:42 preupgrade_driver.sql

-rw-r--r-- 1 oracle oinstall 14846 Feb 8 19:42 dbms_registry_extended.sql

-rw-r--r-- 1 oracle oinstall 422048 Feb 8 19:42 preupgrade_package.sql

-rw-r--r-- 1 oracle oinstall 83854 Feb 8 19:42 preupgrade_messages.properties

-rw-r--r-- 1 oracle oinstall 14383 Feb 8 19:42 parameters.properties

drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 upgrade

-rw-r--r-- 1 oracle oinstall 50172 Feb 8 19:42 components.properties

-rw-r--r-- 1 oracle oinstall 1 Feb 8 19:42 checksBuffer.tmp

-rw-r--r-- 1 oracle oinstall 14521 Feb 8 19:42 preupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 9914 Feb 8 19:42 postupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 11816 Feb 8 19:42 preupgrade.log

 Run the preupgrade_fixup.sql

SQL> @/oraclebase/app/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 18.0.0.0.0 Build: 1

Generated on:            2019-07-15 13:08:59

For Source Database:     test

Source Database Version: 11.2.0.3.0

For Upgrade to Version:  18.0.0.0.0

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

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

    1.  invalid_objects_exist     NO          Manual fixup recommended.

    2.  network_acl_priv          NO          Manual fixup recommended.

    3.  exclusive_mode_auth       NO          Manual fixup recommended.

    4.  mv_refresh                NO          Informational only.

                                              Further action is optional.

    5.  pre_fixed_objects         YES         None.

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade.  To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

__________________________________________________________________

[oracle@test preupgrade]$ export ORACLE_SID=test1

[oracle@test preupgrade]$ export ORACLE_HOME=/oraclebase/app/oracle/product/18.0.0/db11g_home

[oracle@test preupgrade]$ export PATH=/oraclebase/app/oracle/product/18.0.0/db11g_home/bin:$PATH

[oracle@test ]$

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 8 19:48:36 2019

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2019-02-08 19:42:16

For Source Database: DBWR18C

Source Database Version: 11.2.0.3.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

1. parameter_min_val NO Manual fixup recommended.

2. em_present NO Manual fixup recommended.

3. amd_exists NO Manual fixup recommended.

4. apex_manual_upgrade NO Manual fixup recommended.

5. dictionary_stats YES None.

6. trgowner_no_admndbtrg NO Informational only.

Further action is optional.

7. mv_refresh NO Informational only.

Further action is optional.

8. pre_fixed_objects YES None.

9. tablespaces_info NO Informational only.

Further action is optional.

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade. To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

Check INVALID objects count before upgrade :

ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

--------

0

http://www.acehints.com/2011/12/how-to-change-oracle-11g-10g-rac.html -- archivelog to noarchivelog

____________________________________________________________________________________________________________

Upgrade Database from 11.2.0.3 to 18c  using DBCA :-

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

_____________________________________________________________________________________________

[oracle@test ~]$ export ORACLE_SID=test1

[oracle@test~]$ export ORACLE_HOME=/oraclebase/app/ora18c/product/18.0.0/software

[oracle@test ~]$ export PATH=/oraclebase/app/ora18c/product/18.0.0/software/bin/:$PATH

[oracle@test ~]$ dbua

Select database name to upgrade to 18c version.

________________________________________________________________________________

Preupgrade checks :

If you want to upgrade APEX version to 18.2,download APEX software 18.2 and run apexins.sql and upgrade it.

@apexins.sql APEX APEX TEMP /i/

After preupgrade warnings are resolved,select upgrade options to upgrade the database.

Select Recovery options to recover the database in case of issues.

Create listener or use existing listener which is running.

Select option if you want to configure EM express and port number.

Post Upgrade checks :

SQL> @postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.

WARNING - This script was generated for database DBWR18C.

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2019-02-08 19:42:17

For Source Database: DBWR18C

Source Database Version: 11.2.0.3.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name   Remedied Further DBA Action

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

10. depend_usr_tables          YES       None.

11. old_time_zones_exist       YES       None.

12. post_dictionary            YES       None.

13. post_fixed_objects         NO        Informational only.Further action is optional.

14. upg_by_std_upgrd           YES       None.

The fixup scripts have been run and resovled what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database upgrade is not

fully complete. To resolve the outstanding issues, start by reviewing

the postupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

Session altered.

Now the database upgrade is complete and the database is ready for normal use.

SQL> select VERSION,name,open_mode from v$instance,v$database;

VERSION          NAME           OPEN_MODE

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

18.0.0.0.0       DBWR18C        READ WRITE

SQL> SELECT * FROM v$timezone_file;

FILENAME              VERSION     CON_ID

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

timezlrg_31.dat         31          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

    31

Next Post 

Oracle 18c Database Upgrade From…

datafile

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

set lines 100

set pages 500

set heading off

set feedback off

set trimspool on

select 'set newname for datafile '||file_id||' to '''||file_name||''';'

from dba_data_files order by file_id;

set newname for datafile 1 to '+DATA_XDW/test/datafile/system.262.816780069';

set newname for datafile 2 to '+DATA_XDW/test/datafile/sysaux.263.816780077';

set newname for datafile 3 to '+DATA_XDW/test/datafile/pbi.362.943098607';

set newname for datafile 4 to '+DATA_XDW/test/datafile/undotbs2.266.816780099';

set newname for datafile 5 to '+DATA_XDW/test/datafile/users.391.897502637';

set newname for datafile 6 to '+DATA_XDW/test/datafile/qltxplain_data01.333.897478069';

set newname for datafile 7 to '+RECO_XDW/test/datafile/stg_temp.679.1001862685';

set newname for datafile 9 to '+DATA_XDW/test/datafile/test.579.898105713';

set newname for datafile 10 to '+DATA_XDW/test/datafile/undotbs01.514.925655041';

set newname for datafile 11 to '+DATA_XDW/test/datafile/fods.580.898105755';

set newname for datafile 14 to '+DATA_XDW/test/datafile/other.290.816861941';

set newname for datafile 16 to '+DATA_XDW/test/datafile/omstg_xxeom_artctl_mv_stg.411.898689079';

set newname for datafile 18 to '+DATA_XDW/test/datafile/stg_hist.522.915645149';

set newname for datafile 19 to '+DATA_XDW/test/datafile/edw.441.920392165';

set newname for datafile 20 to '+DATA_XDW/test/datafile/undotbs01.548.925655127';

set newname for datafile 21 to '+DATA_XDW/test/datafile/undotbs01.528.925655137';

set newname for datafile 22 to '+DATA_XDW/test/datafile/mstg.287.892722509';

set newname for datafile 23 to '+DATA_XDW/test/datafile/undotbs01.527.925655147';

set newname for datafile 24 to '+DATA_XDW/test/datafile/bdw.403.892722977';

set newname for datafile 25 to '+DATA_XDW/test/datafile/undotbs01.525.925655155';

set newname for datafile 26 to '+DATA_XDW/test/datafile/undotbs01.521.925655165';

set newname for datafile 29 to '+DATA_XDW/test/datafile/sysaux.402.978467891';

set newname for datafile 168 to '+DATA_XDW/test/datafile/sysaux.462.869854087';

set newname for datafile 200 to '+DATA_XDW/test/datafile/system.602.893182993';

logfile

select 'alter database rename file '''||member||''' to '''||replace(member,'&sourcedb','&targetdb')||''';' from v$logfile;

ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_4.269.904561065'

multiplexing controlfile

sqlplus '/ as sysdba'

SQL> create pfile='/tmp/spfileBACKUP.ora' from spfile;

sqlplus '/ as sysdba'

SQL> select name from v$controlfile;

NAME

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

+DATA01/MYDB/controlfile/current.200.899418013

SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01' scope=spfile sid='*';

sqlplus '/ as sysdba'

SQL> shutdown immediate;

SQL> startup nomount;

rman

RMAN> connect target /

RMAN> restore controlfile from '+DATA1/MYDB/controlfile/current.200.899418013';

sqlplus '/ as sysdba'

SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01/MYDB/controlfile/current.200.800418013' scope=spfile sid='*';

alter database rename file '+DATA_XDW/test/onlinelog/group_1.259.932591795' to '+RECO_DG01/test/onlinelog/group_1.259.932591795';

alter database rename file '+DATA_XDW/test/onlinelog/group_1.258.932591799' to '+RECO_DG01/test/onlinelog/group_1.258.932591799';

alter database rename file '+DATA_XDW/test/onlinelog/group_2.261.932591803' to '+RECO_DG01/test/onlinelog/group_2.261.932591803';

alter database rename file '+DATA_XDW/test/onlinelog/group_2.260.932591807' to '+RECO_DG01/test/onlinelog/group_2.260.932591807';

alter database rename file '+DATA_XDW/test/onlinelog/group_3.269.932591811' to '+RECO_DG01/test/onlinelog/group_3.269.932591811';

alter database rename file '+DATA_XDW/test/onlinelog/group_3.268.932591815' to '+RECO_DG01/test/onlinelog/group_3.268.932591815';

alter database rename file '+DATA_XDW/test/onlinelog/group_4.271.932591821' to '+RECO_DG01/test/onlinelog/group_4.271.932591821';

alter database rename file '+DATA_XDW/test/onlinelog/group_4.270.932591823' to '+RECO_DG01/test/onlinelog/group_4.270.932591823';

alter database rename file '+DATA_XDW/test/onlinelog/group_5.274.932591829' to '+RECO_DG01/test/onlinelog/group_5.274.932591829';

alter database rename file '+DATA_XDW/test/onlinelog/group_5.273.932591833' to '+RECO_DG01/test/onlinelog/group_5.273.932591833';

alter database rename file '+DATA_XDW/test/onlinelog/group_6.276.932591837' to '+RECO_DG01/test/onlinelog/group_6.276.932591837';

alter database rename file '+DATA_XDW/test/onlinelog/group_6.275.932591841' to '+RECO_DG01/test/onlinelog/group_6.275.932591841';

alter database rename file '+DATA_XDW/test/onlinelog/group_7.278.932591845' to '+RECO_DG01/test/onlinelog/group_7.278.932591845';

alter database rename file '+DATA_XDW/test/onlinelog/group_7.277.932591849' to '+RECO_DG01/test/onlinelog/group_7.277.932591849';

alter database rename file '+DATA_XDW/test/onlinelog/group_8.280.932591853' to '+RECO_DG01/test/onlinelog/group_8.280.932591853';

alter database rename file '+DATA_XDW/test/onlinelog/group_8.279.932591857' to '+RECO_DG01/test/onlinelog/group_8.279.932591857';

alter database rename file '+DATA_XDW/test/onlinelog/group_9.336.819990135' to '+RECO_DG01/test/onlinelog/group_9.336.819990135';

alter database rename file '+DATA_XDW/test/onlinelog/group_9.337.819990137' to '+RECO_DG01/test/onlinelog/group_9.337.819990137';

alter database rename file '+DATA_XDW/test/onlinelog/group_10.338.819990141' to '+RECO_DG01/test/onlinelog/group_10.338.819990141';

alter database rename file '+DATA_XDW/test/onlinelog/group_10.339.819990145' to '+RECO_DG01/test/onlinelog/group_10.339.819990145';

alter database rename file '+DATA_XDW/test/onlinelog/group_11.340.819990165' to '+RECO_DG01/test/onlinelog/group_11.340.819990165';

alter database rename file '+DATA_XDW/test/onlinelog/group_11.341.819990169' to '+RECO_DG01/test/onlinelog/group_11.341.819990169';

alter database rename file '+DATA_XDW/test/onlinelog/group_12.342.819990173' to '+RECO_DG01/test/onlinelog/group_12.342.819990173';

alter database rename file '+DATA_XDW/test/onlinelog/group_12.343.819990177' to '+RECO_DG01/test/onlinelog/group_12.343.819990177';

alter database rename file '+DATA_XDW/test/onlinelog/group_13.344.819990201' to '+RECO_DG01/test/onlinelog/group_13.344.819990201';

alter database rename file '+DATA_XDW/test/onlinelog/group_13.345.819990205' to '+RECO_DG01/test/onlinelog/group_13.345.819990205';

alter database rename file '+DATA_XDW/test/onlinelog/group_14.346.819990209' to '+RECO_DG01/test/onlinelog/group_14.346.819990209';

alter database rename file '+DATA_XDW/test/onlinelog/group_14.347.819990213' to '+RECO_DG01/test/onlinelog/group_14.347.819990213';

alter database rename file '+DATA_XDW/test/onlinelog/group_15.348.819990229' to '+RECO_DG01/test/onlinelog/group_15.348.819990229';

alter database rename file '+DATA_XDW/test/onlinelog/group_15.349.819990233' to '+RECO_DG01/test/onlinelog/group_15.349.819990233';

alter database rename file '+DATA_XDW/test/onlinelog/group_16.350.819990237' to '+RECO_DG01/test/onlinelog/group_16.350.819990237';

alter database rename file '+DATA_XDW/test/onlinelog/group_16.351.819990239' to '+RECO_DG01/test/onlinelog/group_16.351.819990239';

alter database rename file '+DATA_XDW/test/onlinelog/group_17.352.819990269' to '+RECO_DG01/test/onlinelog/group_17.352.819990269';

alter database rename file '+DATA_XDW/test/onlinelog/group_17.353.819990273' to '+RECO_DG01/test/onlinelog/group_17.353.819990273';

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME

FROM   V$RECOVER_FILE;

RUN

{

  # Set a new location for logs 1 through 100.

  SET ARCHIVELOG DESTINATION TO '/fs1/tmp';

  RESTORE ARCHIVELOG FROM SEQUENCE 1 UNTIL SEQUENCE 100;

  # Set a new location for logs 101 through 200.

  SET ARCHIVELOG DESTINATION TO '/fs2/tmp';

  RESTORE ARCHIVELOG FROM SEQUENCE 101 UNTIL SEQUENCE 200;

  # Set a new location for logs 201 through 300.

  SET ARCHIVELOG DESTINATION TO '/fs3/tmp';

  RESTORE ARCHIVELOG FROM SEQUENCE 201 UNTIL SEQUENCE 300;

  # restore and recover datafiles as needed

  .

  .

  .

}

________________________________________________

create.sql_prd

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.