Blog for oracle basic 9i,10g,11g,12c

12c upgrade steps manually , SOURCE  /u01/app/oracle/product/11.2.0.4/dbhome_2 TARGET /u01/app/oracle/product/12.1.0.2/dbhome_2  SOURCE  /u01/app/oracle/product/11.2.0.4/dbhome_2 at source home . -- CREATE RESTORE POINT before_Uprgade  GUARANTEE FLASHBACK DATABASE; SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,         GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE         FROM V$RESTORE_POINT;  /u01/app/oracle/product/12.1.0.2/dbhome_2/rdbms/admin SQL> @preupgrd.sql Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in INFXXXXX... ***************************************************************************       ************************************************************                  ====>> ERRORS FOUND for INFXXXXX <<==== ...

set echo off; Set pages 999; set long 90000; select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb; Create datapump directory CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/'; GRANT READ, WRITE ON DIRECTORY test_dir TO scott; nohup expdp \"/ as sysdba\"  schemas=SCN,FUND EXCLUDE=STATISTICS ACCESS_METHOD=EXTERNAL_TABLE directory=DATAPUMP  dumpfile=DATE_DBNAME_BACKUP.dmp logfile=SATINUAT_BACKUP.log   JOB_NAME=FULL_export_A3  & Import   command impdp   \"/ as sysdba\"   dumpfile=DATE_DBNAME_BACKUP.dmp  DIRECTORY=DATAPUMP logfile=SCNL1_schemas_backup_201805260200.log  remap_tablespace=SCN_IMAGE:Scn_TA CLUSTER=N

backup as copy datafile 7  format '+ORADATA2'; switch datafile 7 to copy; DELETE DATAFILECOPY '+ORADATA1U*******_aml/datafile/*xxx_ts.272.949091579';

 Online file movement  not possible for  standard edition. ALTER DATABASE MOVE DATAFILE '+REDO1/SCNLR/DATAFILE/scnl_data.262.965626223'   TO '+ORADATA/SCNLR/DATAFILE/scnl_data02.dbf';

set head off pagesize 0 linesize 200 select 'alter database datafile ' || '''' || file_name || '''' || ' autoextend on maxsize unlimited;' from dba_data_files where tablespace_name not like 'UNDO%';

Execute below  statement & re-triggered  stats for fixed objects . $ sqlplus / as sysdba  EXEC dbms_stats.init_package(); Backup  status col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time, elapsed_seconds/3600                   hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key; --This script will report all on full and incremental backups, not archivelog backups – col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time, elapsed_seconds/3600                   hrs from V$RMAN_BACKUP_JOB_DETAILS where input_type='DB INCR' order by session_key; =====...

Reference document for  Doc ID 2127675.1 $ sqlplus / as sysdba  EXEC dbms_stats.init_package(); Execute below  statement & re-triggered  stats for fixed objects . $ sqlplus / as sysdba  EXEC dbms_stats.init_package();

===================DATA PUMP ======================== select * from nls_database_parameters where parameter like '%SET%'; set echo off; Set pages 999; set long 90000;  select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;  select VALUE from v$parameter   where name = 'db_block_size'; nohup expdp \"/ as sysdba\" DIRECTORY=DATAPUMP dumpfile=SCNL1_MV_DEMAND_DETAIL_01132018.dmp log=SCNL1_MV_DEMAND_DETAIL_01132018.log tables=SCNL.MV_DEMAND_DETAIL  &  nohup  impdp  \"/ as sysdba\"  full=Y   directory=exp  dumpfile=ESCDB_A%U.dmp  logfile=ESCDB_A.log   JOB_NAME=FULL_import1 & http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_4.shtml

Use full  script    for DR status .!! Start & Stop  mrp  !!   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; select process,client_process,status,sequence# from v$managed_standby; SELECT * FROM ( SELECT NAME,sequence#,thread#, archived, applied,TO_CHAR(first_time, 'RRRR/MM/DD HH24:MI') A...