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