Monday, March 6, 2023

Migrate oracle database version 12c (12.2.0) to 19c (19.3.0)

 Migrate Oracle Database 



Source Database

DB_NAME : ibmggn

DB-Unique_name = ibmggn

Database version : 12.2.0

OS : RHE 8.7




SQL> select name ,open_mode, log_mode from v$database;


NAME      OPEN_MODE            LOG_MODE

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

IBMGGN    READ WRITE           ARCHIVELOG


SQL>



SQL> select log_mode from v$database;


LOG_MODE

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

ARCHIVELOG


SQL>

SQL>

SQL>

SQL>

SQL> select force_logging from v$database;


FORCE_LOGGING

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

NO


SQL>

SQL>

SQL>

SQL> ALTER DATABASE FORCE LOGGING;


Database altered.


SQL>

SQL>

SQL> select force_logging from v$database;


FORCE_LOGGING

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

YES


SQL>




Add standby redolog : n+1 (where n is current group of redolog )


SQL> select member from v$logfile;


MEMBER

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

/u01/app/oracle/oradata/ibmggn/redo03.log

/u01/app/oracle/oradata/ibmggn/redo02.log

/u01/app/oracle/oradata/ibmggn/redo01.log


SQL>





SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log;  


    GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024    MEMBERS STATUS

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

         1          1          1             200          1 CURRENT

         2          1          0             200          1 UNUSED

         3          1          0             200          1 UNUSED


SQL>



SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL>


SQL> alter system switch logfile;


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL>



SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

SQL>




alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo1.log') size 200m; 

alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo2.log') size 200m;  

alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo3.log') size 200m;  

alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo4.log') size 200m;




SQL>

SQL>

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo1.log') size 200m;


Database altered.


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo2.log') size 200m;


Database altered.


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo3.log') size 200m;


Database altered.


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo4.log') size 200m;


Database altered.


SQL>



column REDOLOG_FILE_NAME format a50


SELECT

 a.GROUP#,

 a.THREAD#,

 a.SEQUENCE#,

 a.ARCHIVED,

 a.STATUS,

 b.MEMBER AS REDOLOG_FILE_NAME,

 (a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP#;




SQL> column REDOLOG_FILE_NAME format a50


SELECT

 a.GROUP#,

 a.THREAD#,

 a.SEQUENCE#,

 a.ARCHIVED,

 a.STATUS,

 b.MEMBER AS REDOLOG_FILE_NAME,

 (a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP#;SQL> SQL>   2    3    4    5    6    7    8    9   10   11


    GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NAME                                     SIZE_MB

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

         1          1          4 NO  CURRENT          /u01/app/oracle/oradata/ibmggn/redo01.log                 200

         2          1          2 YES INACTIVE         /u01/app/oracle/oradata/ibmggn/redo02.log                 200

         3          1          3 YES INACTIVE         /u01/app/oracle/oradata/ibmggn/redo03.log                 200


SQL>





select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

union all

select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

and rownum <=2

/



SQL> select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

  2    3    4    5    6    7  union all

select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||

  8    9   10  ''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

 11   12   13  and rownum <=2

/

 14

Create Standby redo

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

alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo03.log' size 209715200;

alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo02.log' size 209715200;

alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo01.log' size 209715200;

alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo04.rdo' size 209715200;

alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo04.rdo' size 209715200;


SQL>



select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;



SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;


    GROUP# Size in MB

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

         6        200

         4        200

         5        200

         7        200


SQL>





select  bytes/1024/1024"Size in MB" from v$standby_log 



SQL> select * from v$logfile;


    GROUP# STATUS  TYPE    MEMBER                                                                           IS_     CON_ID

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

         3         ONLINE  /u01/app/oracle/oradata/ibmggn/redo03.log                                        NO           0

         2         ONLINE  /u01/app/oracle/oradata/ibmggn/redo02.log                                        NO           0

         1         ONLINE  /u01/app/oracle/oradata/ibmggn/redo01.log                                        NO           0

         4         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo1.log                                     NO           0

         5         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo2.log                                     NO           0

         6         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo3.log                                     NO           0

         7         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo4.log                                     NO           0


7 rows selected.


SQL>







alter system set log_archive_config='dg_config=(ibmggn,ibmnoida)' scope=both;


alter system set log_archive_dest_2='service=ibmnoida  noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibmnoida' scope=both;



ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; 



ALTER SYSTEM SET FAL_SERVER=ibmnoida scope=both; 



ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both; 


col value for a110

col name for a35

select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert', 'standby_file_management','control_files','db_recovery_file_dest');


SQL> col value for a110

col name for a35

SQL> SQL>

select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_arcSQL> hive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert', 'standby_file_management','control_files','db_recovery_file_dest');


NAME                                VALUE

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

control_files                       /u01/app/oracle/oradata/ibmggn/control01.ctl, /u01/app/oracle/fast_recovery_area/ibmggn/control02.ctl

db_file_name_convert

log_file_name_convert

log_archive_dest_1

log_archive_dest_2                  service=ibmnoida  noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibmnoida

log_archive_dest_state_1            enable

log_archive_dest_state_2            ENABLE

fal_client

fal_server                          IBMNOIDA

log_archive_config                  dg_config=(ibmggn,ibmnoida)

log_archive_format                  %t_%s_%r.dbf

log_archive_max_processes           4

db_recovery_file_dest               /u01/app/oracle/fast_recovery_area/ibmggn

standby_file_management             AUTO

remote_login_passwordfile           EXCLUSIVE

db_name                             ibmggn

db_unique_name                      ibmggn


17 rows selected.


SQL>



create test table and insert few records .


do some switch log 




SQL>

SQL>

SQL> ARCHIVE LOG LIST;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     10

Next log sequence to archive   12

Current log sequence           12

SQL>


[oracle@ip-172-31-21-233 backup]$ pwd

/u01/app/oracle/backup

[oracle@ip-172-31-21-233 backup]$


run{

 allocate channel d1 type disk MAXPIECESIZE 2G;

 sql 'alter system archive log current';

 sql 'alter system archive log current';

 backup format '/u01/app/oracle/backup/db_t%t_s%s_p%p' database;

 sql 'alter system archive log current';

 sql 'alter system archive log current';

 backup format '/u01/app/oracle/backup/ar_t%t_s%s_p%p' archivelog all;

 release channel d1;

 }

 

 

 

 

 RMAN>


RMAN>


RMAN> run{

 allocate channel d1 type disk MAXPIECESIZE 2G;

 sql 'alter system archive log current';

 sql 'alter system archive log current';

2> 3> 4> 5>  backup format '/u01/app/oracle/backup/db_t%t_s%s_p%p' database;

 sql 'alter system archive log current';

6> 7>  sql 'alter system archive log current';

 backup format '/u01/app/oracle/backup/ar_t%t_s%s_p%p' archivelog all;

 release channel d1;

 }8> 9> 10>


allocated channel: d1

channel d1: SID=33 device type=DISK


sql statement: alter system archive log current


sql statement: alter system archive log current


Starting backup at 14-DEC-22

channel d1: starting full datafile backup set

channel d1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ibmggn/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ibmggn/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ibmggn/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ibmggn/users01.dbf

channel d1: starting piece 1 at 14-DEC-22

channel d1: finished piece 1 at 14-DEC-22

piece handle=/u01/app/oracle/backup/db_t1123401582_s2_p1 tag=TAG20221214T075941 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:15

Finished backup at 14-DEC-22


Starting Control File and SPFILE Autobackup at 14-DEC-22

piece handle=/u01/app/oracle/fast_recovery_area/ibmggn/IBMGGN/autobackup/2022_12_14/o1_mf_s_1123401597_ksm0mx79_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 14-DEC-22


sql statement: alter system archive log current


sql statement: alter system archive log current


Starting backup at 14-DEC-22

current log archived

channel d1: starting archived log backup set

channel d1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=1 STAMP=1123399726

input archived log thread=1 sequence=2 RECID=2 STAMP=1123399727

input archived log thread=1 sequence=3 RECID=3 STAMP=1123399728

input archived log thread=1 sequence=4 RECID=4 STAMP=1123400453

input archived log thread=1 sequence=5 RECID=5 STAMP=1123400486

input archived log thread=1 sequence=6 RECID=6 STAMP=1123401190

input archived log thread=1 sequence=7 RECID=7 STAMP=1123401191

input archived log thread=1 sequence=8 RECID=8 STAMP=1123401193

input archived log thread=1 sequence=9 RECID=9 STAMP=1123401338

input archived log thread=1 sequence=10 RECID=10 STAMP=1123401339

input archived log thread=1 sequence=11 RECID=11 STAMP=1123401341

input archived log thread=1 sequence=12 RECID=12 STAMP=1123401580

input archived log thread=1 sequence=13 RECID=13 STAMP=1123401581

input archived log thread=1 sequence=14 RECID=14 STAMP=1123401598

input archived log thread=1 sequence=15 RECID=15 STAMP=1123401598

input archived log thread=1 sequence=16 RECID=16 STAMP=1123401601

channel d1: starting piece 1 at 14-DEC-22

channel d1: finished piece 1 at 14-DEC-22

piece handle=/u01/app/oracle/backup/ar_t1123401601_s4_p1 tag=TAG20221214T080001 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-DEC-22


Starting Control File and SPFILE Autobackup at 14-DEC-22

piece handle=/u01/app/oracle/fast_recovery_area/ibmggn/IBMGGN/autobackup/2022_12_14/o1_mf_s_1123401602_ksm0n2m8_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 14-DEC-22


released channel: d1


RMAN>


RMAN>


 

 


Backup controlfile for standby 


RMAN>

 run

 {

 allocate channel c1 type disk;

 backup format '/u01/app/oracle/backup/standby_controlfile_t%t_s%s_p%p' current controlfile for standby;

 }

 




RMAN> run

 {

 allocate channel c1 type disk;

 backup format '/u01/app/oracle/backup/standby_controlfile_t%t_s%s_p%p' current controlfile for standby;

 }2> 3> 4> 5>


allocated channel: c1

channel c1: SID=33 device type=DISK


Starting backup at 14-DEC-22

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including standby control file in backup set

channel c1: starting piece 1 at 14-DEC-22

channel c1: finished piece 1 at 14-DEC-22

piece handle=/u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1 tag=TAG20221214T080111 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-DEC-22


Starting Control File and SPFILE Autobackup at 14-DEC-22

piece handle=/u01/app/oracle/fast_recovery_area/ibmggn/IBMGGN/autobackup/2022_12_14/o1_mf_s_1123401673_ksm0p9fr_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 14-DEC-22

released channel: c1


RMAN>




 SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/standby_controlfile.ctl';


Database altered.



CREATE PFILE ='/u01/app/oracle/backup/india.ora' FROM SPFILE;




Transfer backup files from source database and password file 

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




[oracle@ip-172-31-21-233 oracle]$

[oracle@ip-172-31-21-233 oracle]$ scp -r backup/ oracle@172.31.19.40:/u01/app/oracle

oracle@172.31.19.40's password:

db_t1123401582_s2_p1                                                                                             100% 1024MB  74.8MB/s   00:13

ar_t1123401601_s4_p1                                                                                             100%  152MB 120.3MB/s   00:01

standby_controlfile_t1123401671_s6_p1                                                                            100%   10MB  98.6MB/s   00:00

standby_controlfile.ctl                                                                                          100%   10MB  98.5MB/s   00:00

ibmnoida.ora_bkp_primary_file                                                                                    100% 1431     1.2MB/s   00:00

ibmnoida.ora                                                                                                     100% 1012   750.5KB/s   00:00

[oracle@ip-172-31-21-233 oracle]





On Target DB

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


DB_NAME : ibmggn

DB-Unique_name = ibmnoida

Database version : 19.3.0

OS : RHE 8.7





mkdir -p /u01/app/oracle/admin/ibmggn/adump


mkdir -p /u01/app/oracle/oradata/ibmggn/


mkdir -p /u01/app/oracle/fast_recovery_area/ibmggn



[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$ mkdir -p /u01/app/oracle/admin/ibmggn/adump

[oracle@ip-172-31-19-40 ~]$ mkdir -p /u01/app/oracle/oradata/ibmggn/

[oracle@ip-172-31-19-40 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/ibmggn

[oracle@ip-172-31-19-40 ~]$






startup database in nomount mode 



select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_2','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert', 'standby_file_management','control_files','db_recovery_file_dest');



SQL> /


NAME                                VALUE

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

control_files                       /u01/app/oracle/oradata/ibmggn/control01.ctl, /u01/app/oracle/fast_recovery_area/ibmggn/control02.ctl

db_file_name_convert

log_file_name_convert

log_archive_dest_1

log_archive_dest_2                  service=ibmggn  noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibmggn

log_archive_dest_state_1            enable

log_archive_dest_state_2            ENABLE

fal_client

fal_server                          IBMGGN

log_archive_config                  dg_config=(ibmggn,ibmnoida)

log_archive_format                  %t_%s_%r.dbf

log_archive_max_processes           4

db_recovery_file_dest               /u01/app/oracle/fast_recovery_area/ibmggn

standby_file_management             AUTO

remote_login_passwordfile           EXCLUSIVE

db_name                             ibmggn

db_unique_name                      ibmnoida


17 rows selected.


SQL>




Source DB tnsnames.ora



IBMGGN =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ibmggn)

    )

  )





IBMNOIDA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ibmnoida)

    )

  )





chnage listeener.ora on target server 

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


LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

   (SID_DESC =

       (GLOBAL_DBNAME = ibmnoida)

       (ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1 )

       (SID_NAME = ibmnoida)

     )

    )

  )



from source server 

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


[oracle@ip-172-31-21-233 admin]$ tnsping ibmnoida


TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-DEC-2022 09:53:11


Copyright (c) 1997, 2016, Oracle.  All rights reserved.


Used parameter files:

/u01/app/oracle/product/12.2.0/dbhome1/network/admin/sqlnet.ora



Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ibmnoida)))

OK (0 msec)

[oracle@ip-172-31-21-233 admin]$





Restore target database 

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


Backup location :


/u01/app/oracle/backup



[oracle@ip-172-31-19-40 backup]$ ls -lrt

total 1225948

-rw-r----- 1 oracle oinstall 1074257920 Dec 14 08:12 db_t1123401582_s2_p1

-rw-r----- 1 oracle oinstall  159834624 Dec 14 08:12 ar_t1123401601_s4_p1

-rw-r----- 1 oracle oinstall   10665984 Dec 14 08:12 standby_controlfile_t1123401671_s6_p1

-rw-r----- 1 oracle oinstall   10600448 Dec 14 08:12 standby_controlfile.ctl

-rw-r--r-- 1 oracle oinstall       1431 Dec 14 08:12 ibmnoida.ora_bkp_primary_file

-rw-r--r-- 1 oracle oinstall       1012 Dec 14 08:12 ibmnoida.ora

[oracle@ip-172-31-19-40 backup]$

[oracle@ip-172-31-19-40 backup]$ pwd





restore controlfile from '/u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1';




[oracle@ip-172-31-19-40 backup]$ rman target /


Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 14 10:05:05 2022

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


connected to target database: IBMGGN (not mounted)


RMAN> restore controlfile from '/u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1';


Starting restore at 14-DEC-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=251 device type=DISK


channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ibmggn/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ibmggn/control02.ctl

Finished restore at 14-DEC-22


RMAN>



RMAN>


RMAN> sql 'alter database mount';


sql statement: alter database mount

released channel: ORA_DISK_1


RMAN>



RMAN> report schema;


Starting implicit crosscheck backup at 14-DEC-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=251 device type=DISK

Crosschecked 5 objects

Finished implicit crosscheck backup at 14-DEC-22


Starting implicit crosscheck copy at 14-DEC-22

using channel ORA_DISK_1

Finished implicit crosscheck copy at 14-DEC-22


searching for all files in the recovery area

cataloging files...

no files cataloged


RMAN-06139: warning: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name IBMNOIDA


List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    0        SYSTEM               ***     /u01/app/oracle/oradata/ibmggn/system01.dbf

3    0        SYSAUX               ***     /u01/app/oracle/oradata/ibmggn/sysaux01.dbf

4    0        UNDOTBS1             ***     /u01/app/oracle/oradata/ibmggn/undotbs01.dbf

7    0        USERS                ***     /u01/app/oracle/oradata/ibmggn/users01.dbf


List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    20       TEMP                 32767       /u01/app/oracle/oradata/ibmggn/temp01.dbf


RMAN>


RMAN>




RMAN> catalog start with '/u01/app/oracle/backup';


searching for all files that match the pattern /u01/app/oracle/backup


List of Files Unknown to the Database

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

File Name: /u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1

File Name: /u01/app/oracle/backup/standby_controlfile.ctl

File Name: /u01/app/oracle/backup/ibmnoida.ora_bkp_primary_file

File Name: /u01/app/oracle/backup/ibmnoida.ora


Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done


List of Cataloged Files

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

File Name: /u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1

File Name: /u01/app/oracle/backup/standby_controlfile.ctl


List of Files Which Were Not Cataloged

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

File Name: /u01/app/oracle/backup/ibmnoida.ora_bkp_primary_file

  RMAN-07517: Reason: The file header is corrupted

File Name: /u01/app/oracle/backup/ibmnoida.ora

  RMAN-07517: Reason: The file header is corrupted


RMAN>




RMAN> list backup of archivelog all;



List of Backup Sets

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



BS Key  Size       Device Type Elapsed Time Completion Time

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

4       152.43M    DISK        00:00:00     14-DEC-22

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20221214T080001

        Piece Name: /u01/app/oracle/backup/ar_t1123401601_s4_p1


  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    1       1408558    14-DEC-22 1421197    14-DEC-22

  1    2       1421197    14-DEC-22 1421200    14-DEC-22

  1    3       1421200    14-DEC-22 1421203    14-DEC-22

  1    4       1421203    14-DEC-22 1421591    14-DEC-22

  1    5       1421591    14-DEC-22 1421629    14-DEC-22

  1    6       1421629    14-DEC-22 1423114    14-DEC-22

  1    7       1423114    14-DEC-22 1423119    14-DEC-22

  1    8       1423119    14-DEC-22 1423127    14-DEC-22

  1    9       1423127    14-DEC-22 1424503    14-DEC-22

  1    10      1424503    14-DEC-22 1424514    14-DEC-22

  1    11      1424514    14-DEC-22 1424524    14-DEC-22

  1    12      1424524    14-DEC-22 1425054    14-DEC-22

  1    13      1425054    14-DEC-22 1425065    14-DEC-22

  1    14      1425065    14-DEC-22 1425129    14-DEC-22

  1    15      1425129    14-DEC-22 1425140    14-DEC-22

  1    16      1425140    14-DEC-22 1425152    14-DEC-22


RMAN>



RMAN> restore database;


Starting restore at 14-DEC-22

using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ibmggn/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ibmggn/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ibmggn/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ibmggn/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/db_t1123401582_s2_p1

channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/db_t1123401582_s2_p1 tag=TAG20221214T075941

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 14-DEC-22


RMAN>




RMAN> recover database;


Starting recover at 14-DEC-22

using channel ORA_DISK_1


starting media recovery


channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=14

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=15

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=16

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/ar_t1123401601_s4_p1

channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/ar_t1123401601_s4_p1 tag=TAG20221214T080001

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_14_ksm93891_.arc thread=1 sequence=14

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_14_ksm93891_.arc RECID=1 STAMP=1123410280

archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_15_ksm938b9_.arc thread=1 sequence=15

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_15_ksm938b9_.arc RECID=3 STAMP=1123410280

archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_16_ksm9389n_.arc thread=1 sequence=16

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_16_ksm9389n_.arc RECID=2 STAMP=1123410280

media recovery complete, elapsed time: 00:00:00

Finished recover at 14-DEC-22


RMAN>




  GROUP# STATUS  TYPE    MEMBER                                                                                     IS_     CON_ID

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

         3         ONLINE  /u01/app/oracle/oradata/ibmggn/redo03.log                                                  NO           0

         2         ONLINE  /u01/app/oracle/oradata/ibmggn/redo02.log                                                  NO           0

         1         ONLINE  /u01/app/oracle/oradata/ibmggn/redo01.log                                                  NO           0

         4         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo1.log                                               NO           0

         5         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo2.log                                               NO           0

         6         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo3.log                                               NO           0

         7         STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo4.log                                               NO           0


7 rows selected.


SQL>






SQL>

SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     15

Next log sequence to archive   17

Current log sequence           17

SQL>

SQL>







alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=10.1.0.2)(PORT=1521))' scope=both;


check passwordfile on target 


SQL>

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    MOUNTED              PHYSICAL STANDBY ibmnoida                       MAXIMUM PERFORMANCE


SQL>





on source 


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


SQL>

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;


PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

DGRD      ALLOCATED             0          0          0          0

ARCH      CLOSING               1          8          1          4

DGRD      ALLOCATED             0          0          0          0

ARCH      OPENING               1         16          1          2

ARCH      CLOSING               1         17      43008         67

ARCH      CLOSING               1         11          1         10

DGRD      ALLOCATED             0          0          0          0

LNS       CONNECTED             0          0          0          0

LNS       WRITING               1         18         81          1

DGRD      ALLOCATED             0          0          0          0


10 rows selected.


SQL>





on target 

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


SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;


PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

ARCH      CONNECTED             0          0          0          0

DGRD      ALLOCATED             0          0          0          0

DGRD      ALLOCATED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

RFS       IDLE                  1          0          0          0

RFS       IDLE                  1         18         76          1


8 rows selected.


SQL>


alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;




SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;


PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

ARCH      CONNECTED             0          0          0          0

DGRD      ALLOCATED             0          0          0          0

DGRD      ALLOCATED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

RFS       IDLE                  1          0          0          0

RFS       IDLE                  1         18         76          1


8 rows selected.


SQL> alter database recover managed standby database disconnect from session;


Database altered.


SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;


PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

ARCH      CONNECTED             0          0          0          0

DGRD      ALLOCATED             0          0          0          0

DGRD      ALLOCATED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

ARCH      CONNECTED             0          0          0          0

RFS       IDLE                  1          0          0          0

RFS       IDLE                  1         18        233          6

MRP0      WAIT_FOR_LOG          1         18          0          0


9 rows selected.


SQL>





on source

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



SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED

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

         1 14-DEC-2022 07:12:52 14-DEC-2022 07:28:45 NO

         2 14-DEC-2022 07:28:45 14-DEC-2022 07:28:47 NO

         3 14-DEC-2022 07:28:47 14-DEC-2022 07:28:48 NO

         4 14-DEC-2022 07:28:48 14-DEC-2022 07:40:53 NO

         5 14-DEC-2022 07:40:53 14-DEC-2022 07:41:26 NO

         6 14-DEC-2022 07:41:26 14-DEC-2022 07:53:10 NO

         7 14-DEC-2022 07:53:10 14-DEC-2022 07:53:11 NO

         8 14-DEC-2022 07:53:11 14-DEC-2022 07:53:13 NO

         9 14-DEC-2022 07:53:13 14-DEC-2022 07:55:38 NO

        10 14-DEC-2022 07:55:38 14-DEC-2022 07:55:39 NO

        11 14-DEC-2022 07:55:39 14-DEC-2022 07:55:41 NO

        12 14-DEC-2022 07:55:41 14-DEC-2022 07:59:40 NO

        13 14-DEC-2022 07:59:40 14-DEC-2022 07:59:41 NO

        14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 NO

        15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 NO

        16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 NO

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 NO

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES


18 rows selected.


SQL>




on target 

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


SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

        14 14-DEC-22 14-DEC-22 YES

        15 14-DEC-22 14-DEC-22 YES

        16 14-DEC-22 14-DEC-22 YES

        17 14-DEC-22 14-DEC-22 YES


SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    MOUNTED              PHYSICAL STANDBY ibmnoida                       MAXIMUM PERFORMANCE


SQL>





on source 

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



SQL> select * from v$version;


BANNER                                                                               CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

PL/SQL Release 12.2.0.1.0 - Production                                                    0

CORE    12.2.0.1.0      Production                                                                0

TNS for Linux: Version 12.2.0.1.0 - Production                                            0

NLSRTL Version 12.2.0.1.0 - Production                                                    0


SQL>  select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    READ WRITE           PRIMARY          ibmggn                         MAXIMUM PERFORMANCE


SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED

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

         1 14-DEC-2022 07:12:52 14-DEC-2022 07:28:45 NO

         2 14-DEC-2022 07:28:45 14-DEC-2022 07:28:47 NO

         3 14-DEC-2022 07:28:47 14-DEC-2022 07:28:48 NO

         4 14-DEC-2022 07:28:48 14-DEC-2022 07:40:53 NO

         5 14-DEC-2022 07:40:53 14-DEC-2022 07:41:26 NO

         6 14-DEC-2022 07:41:26 14-DEC-2022 07:53:10 NO

         7 14-DEC-2022 07:53:10 14-DEC-2022 07:53:11 NO

         8 14-DEC-2022 07:53:11 14-DEC-2022 07:53:13 NO

         9 14-DEC-2022 07:53:13 14-DEC-2022 07:55:38 NO

        10 14-DEC-2022 07:55:38 14-DEC-2022 07:55:39 NO

        11 14-DEC-2022 07:55:39 14-DEC-2022 07:55:41 NO

        12 14-DEC-2022 07:55:41 14-DEC-2022 07:59:40 NO

        13 14-DEC-2022 07:59:40 14-DEC-2022 07:59:41 NO

        14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 NO

        15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 NO

        16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 NO

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 NO

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES

        18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 NO

        18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 YES

        19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 NO

        19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 YES


22 rows selected.


SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     18

Next log sequence to archive   20

Current log sequence           20

SQL>





On target 


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



SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    MOUNTED              PHYSICAL STANDBY ibmnoida                       MAXIMUM PERFORMANCE


SQL> select * from v$version;


BANNER

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

BANNER_FULL

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

BANNER_LEGACY                                                                        CON_ID

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                    0



SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

        14 14-DEC-22 14-DEC-22 YES

        15 14-DEC-22 14-DEC-22 YES

        16 14-DEC-22 14-DEC-22 YES

        17 14-DEC-22 14-DEC-22 YES

        18 14-DEC-22 14-DEC-22 YES

        19 14-DEC-22 14-DEC-22 YES


6 rows selected.


SQL>




######################################################################################################3



create a user and sample table on source 


SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     18

Next log sequence to archive   20

Current log sequence           20

SQL>

SQL>

SQL>

SQL> create user test_after_sync identified by test;


User created.


SQL> grant create session,dba to test_after_sync;


Grant succeeded.


SQL> conn test/test

Connected.

SQL>

SQL> select * from tab;


TNAME                                                                                                                            TABTYPE  CLUSTERID

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

TEST                                                                                                                             TABLE


SQL>

SQL>

SQL> create table anurag as select * from test;


Table created.


SQL>

SQL> alter system switch logfile;


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL>

SQL>

SQL> archive log list

ORA-01031: insufficient privileges

SQL> conn /as sysdba

Connected.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     23

Next log sequence to archive   25

Current log sequence           25

SQL>




SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED

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

         1 14-DEC-2022 07:12:52 14-DEC-2022 07:28:45 NO

         2 14-DEC-2022 07:28:45 14-DEC-2022 07:28:47 NO

         3 14-DEC-2022 07:28:47 14-DEC-2022 07:28:48 NO

         4 14-DEC-2022 07:28:48 14-DEC-2022 07:40:53 NO

         5 14-DEC-2022 07:40:53 14-DEC-2022 07:41:26 NO

         6 14-DEC-2022 07:41:26 14-DEC-2022 07:53:10 NO

         7 14-DEC-2022 07:53:10 14-DEC-2022 07:53:11 NO

         8 14-DEC-2022 07:53:11 14-DEC-2022 07:53:13 NO

         9 14-DEC-2022 07:53:13 14-DEC-2022 07:55:38 NO

        10 14-DEC-2022 07:55:38 14-DEC-2022 07:55:39 NO

        11 14-DEC-2022 07:55:39 14-DEC-2022 07:55:41 NO

        12 14-DEC-2022 07:55:41 14-DEC-2022 07:59:40 NO

        13 14-DEC-2022 07:59:40 14-DEC-2022 07:59:41 NO

        14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 NO

        15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 NO

        16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 NO

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 NO

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES

        18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 NO

        18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 YES

        19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 NO

        19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 YES

        20 14-DEC-2022 10:57:34 14-DEC-2022 11:03:55 NO

        20 14-DEC-2022 10:57:34 14-DEC-2022 11:03:55 YES

        21 14-DEC-2022 11:03:55 14-DEC-2022 11:03:56 NO

        21 14-DEC-2022 11:03:55 14-DEC-2022 11:03:56 YES

        22 14-DEC-2022 11:03:56 14-DEC-2022 11:03:59 NO

        22 14-DEC-2022 11:03:56 14-DEC-2022 11:03:59 YES

        23 14-DEC-2022 11:03:59 14-DEC-2022 11:04:02 NO

        23 14-DEC-2022 11:03:59 14-DEC-2022 11:04:02 YES

        24 14-DEC-2022 11:04:02 14-DEC-2022 11:04:03 NO

        24 14-DEC-2022 11:04:02 14-DEC-2022 11:04:03 YES


32 rows selected.


SQL>




on target db 

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


alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';



SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';


Session altered.


SQL>

SQL>

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;


 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED

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

        14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 YES

        15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 YES

        16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 YES

        17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES

        18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 YES

        19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 YES

        20 14-DEC-2022 10:57:34 14-DEC-2022 11:03:55 YES

        21 14-DEC-2022 11:03:55 14-DEC-2022 11:03:56 YES

        22 14-DEC-2022 11:03:56 14-DEC-2022 11:03:59 YES

        23 14-DEC-2022 11:03:59 14-DEC-2022 11:04:02 YES

        24 14-DEC-2022 11:04:02 14-DEC-2022 11:04:03 YES


11 rows selected.


SQL>






Now activate standby 

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


stop the mrp 


alter database recover managed standby database cancel;




SQL>

SQL> alter database recover managed standby database cancel;


Database altered.


SQL>





shut immediate;

startup mount;

alter database activate standby database;



alter database flashback on;


create restore point BEFORE_UPGRADE_DB guarantee flashback database;


select * from v$restore_point;



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





startup upgrade;



[oracle@ip-172-31-19-40 dbs]$ sqlplus "/as sysdba"


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 14 11:14:52 2022

Version 19.3.0.0.0


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


Connected to an idle instance.


SQL> startup mount;

ORACLE instance started.


Total System Global Area 1895822688 bytes

Fixed Size                  8897888 bytes

Variable Size             436207616 bytes

Database Buffers         1442840576 bytes

Redo Buffers                7876608 bytes

Database mounted.

SQL>

SQL>

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

PROTECTION_MODE

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

IBMGGN    MOUNTED              PHYSICAL STANDBY ibmnoida

MAXIMUM PERFORMANCE



SQL>

SQL> set lines 200 pages 500

SQL>

SQL>

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    MOUNTED              PHYSICAL STANDBY ibmnoida                       MAXIMUM PERFORMANCE


SQL>

SQL>

SQL>

SQL> alter database activate standby database;


Database altered.


SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    MOUNTED              PRIMARY          ibmnoida                       MAXIMUM PERFORMANCE


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

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

                     *

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only



SQL>

SQL>

SQL> shut immediate;

ORA-01109: database not open



Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0




[oracle@ip-172-31-19-40 dbs]$

[oracle@ip-172-31-19-40 dbs]$

[oracle@ip-172-31-19-40 dbs]$

[oracle@ip-172-31-19-40 dbs]$ sqlplus "/as sysdba"


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 14 11:25:20 2022

Version 19.3.0.0.0


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


Connected to an idle instance.


SQL> startup upgrade;

ORACLE instance started.


Total System Global Area 1895822688 bytes

Fixed Size                  8897888 bytes

Variable Size             436207616 bytes

Database Buffers         1442840576 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME

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

PROTECTION_MODE

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

IBMGGN    READ WRITE           PRIMARY          ibmnoida

MAXIMUM PERFORMANCE



SQL> set lines 200 pages 500

SQL>

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    READ WRITE           PRIMARY          ibmnoida                       MAXIMUM PERFORMANCE


SQL>




SQL>

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


  COUNT(*)

----------

      2319


SQL>



[oracle@ip-172-31-19-40 dbs]$

[oracle@ip-172-31-19-40 dbs]$

[oracle@ip-172-31-19-40 dbs]$ cd ..

[oracle@ip-172-31-19-40 dbhome1]$ cd bin/

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ pwd

/u01/app/oracle/product/19.0/dbhome1/bin

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ ls -lrt dbu

dbua       dbupgrade

[oracle@ip-172-31-19-40 bin]$ ls -lrt dbupgrade

-rwxr-x--- 1 oracle oinstall 3136 Apr 17  2019 dbupgrade

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$



time : 14/12/2015 16:58 PM 

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


[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ ./dbupgrade


Argument list for [/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catctl.pl]

For Oracle internal use only A = 0

Run in                       c = 0

Do not run in                C = 0

Input Directory              d = 0

Echo OFF                     e = 1

Simulate                     E = 0

Forced cleanup               F = 0

Log Id                       i = 0

Child Process                I = 0

Log Dir                      l = 0

Priority List Name           L = 0

Upgrade Mode active          M = 0

SQL Process Count            n = 0

SQL PDB Process Count        N = 0

Open Mode Normal             o = 0

Start Phase                  p = 0

End Phase                    P = 0

Reverse Order                r = 0

AutoUpgrade Resume           R = 0

Script                       s = 0

Serial Run                   S = 0

RO User Tablespaces          T = 0

Display Phases               y = 0

Debug catcon.pm              z = 0

Debug catctl.pl              Z = 0


catctl.pl VERSION: [19.0.0.0.0]

           STATUS: [Production]

            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]



/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0/dbhome1]

/u01/app/oracle/product/19.0/dbhome1/bin/orabasehome = [/u01/app/oracle/product/19.0/dbhome1]

catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0/dbhome1]


Analyzing file /u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catupgrd.sql


Log file directory = [/tmp/cfgtoollogs/upgrade20221214112732]


catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_catcon_3105.lst]


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_*.lst] files for spool files, if any



Number of Cpus        = 2

Database Name         = ibmnoida

DataBase Version      = 12.2.0.1.0

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_catcon_3105.lst]


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_*.lst] files for spool files, if any



Log file directory = [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742]


Parallel SQL Process Count            = 4

Components in [ibmnoida]

    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]

Not Installed [APEX EM MGW ODM RAC WK]


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

Phases [0-107]         Start Time:[2022_12_14 11:27:54]

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

***********   Executing Change Scripts   ***********

Serial   Phase #:0    [ibmnoida] Files:1




upgrade log 

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



[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ ./dbupgrade


Argument list for [/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catctl.pl]

For Oracle internal use only A = 0

Run in                       c = 0

Do not run in                C = 0

Input Directory              d = 0

Echo OFF                     e = 1

Simulate                     E = 0

Forced cleanup               F = 0

Log Id                       i = 0

Child Process                I = 0

Log Dir                      l = 0

Priority List Name           L = 0

Upgrade Mode active          M = 0

SQL Process Count            n = 0

SQL PDB Process Count        N = 0

Open Mode Normal             o = 0

Start Phase                  p = 0

End Phase                    P = 0

Reverse Order                r = 0

AutoUpgrade Resume           R = 0

Script                       s = 0

Serial Run                   S = 0

RO User Tablespaces          T = 0

Display Phases               y = 0

Debug catcon.pm              z = 0

Debug catctl.pl              Z = 0


catctl.pl VERSION: [19.0.0.0.0]

           STATUS: [Production]

            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]



/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0/dbhome1]

/u01/app/oracle/product/19.0/dbhome1/bin/orabasehome = [/u01/app/oracle/product/19.0/dbhome1]

catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0/dbhome1]


Analyzing file /u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catupgrd.sql


Log file directory = [/tmp/cfgtoollogs/upgrade20221214112732]


catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_catcon_3105.lst]


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_*.lst] files for spool files, if any



Number of Cpus        = 2

Database Name         = ibmnoida

DataBase Version      = 12.2.0.1.0

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_catcon_3105.lst]


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_*.lst] files for spool files, if any



Log file directory = [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742]


Parallel SQL Process Count            = 4

Components in [ibmnoida]

    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]

Not Installed [APEX EM MGW ODM RAC WK]


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

Phases [0-107]         Start Time:[2022_12_14 11:27:54]

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

***********   Executing Change Scripts   ***********

Serial   Phase #:0    [ibmnoida] Files:1



   Time: 20s

***************   Catalog Core SQL   ***************

Serial   Phase #:1    [ibmnoida] Files:5





   Time: 29s

Restart  Phase #:2    [ibmnoida] Files:1    Time: 0s

***********   Catalog Tables and Views   ***********

Parallel Phase #:3    [ibmnoida] Files:19   Time: 14s

Restart  Phase #:4    [ibmnoida] Files:1    Time: 3s

*************   Catalog Final Scripts   ************

Serial   Phase #:5    [ibmnoida] Files:7    Time: 13s

*****************   Catproc Start   ****************

Serial   Phase #:6    [ibmnoida] Files:1    Time: 10s

*****************   Catproc Types   ****************

Serial   Phase #:7    [ibmnoida] Files:2    Time: 10s

Restart  Phase #:8    [ibmnoida] Files:1    Time: 1s

****************   Catproc Tables   ****************

Parallel Phase #:9    [ibmnoida] Files:67   Time: 20s

Restart  Phase #:10   [ibmnoida] Files:1    Time: 4s

*************   Catproc Package Specs   ************

Serial   Phase #:11   [ibmnoida] Files:1    Time: 54s

Restart  Phase #:12   [ibmnoida] Files:1    Time: 2s

**************   Catproc Procedures   **************

Parallel Phase #:13   [ibmnoida] Files:94   Time: 7s

Restart  Phase #:14   [ibmnoida] Files:1    Time: 4s

Parallel Phase #:15   [ibmnoida] Files:120  Time: 13s

Restart  Phase #:16   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:17   [ibmnoida] Files:22





  Time: 4s

Restart  Phase #:18   [ibmnoida] Files:1    Time: 2s

*****************   Catproc Views   ****************

Parallel Phase #:19   [ibmnoida] Files:32   Time: 15s

Restart  Phase #:20   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:21   [ibmnoida] Files:3    Time: 11s

Restart  Phase #:22   [ibmnoida] Files:1    Time: 1s

Parallel Phase #:23   [ibmnoida] Files:25   Time: 108s

Restart  Phase #:24   [ibmnoida] Files:1    Time: 2s

Parallel Phase #:25   [ibmnoida] Files:12

  Time: 77s

Restart  Phase #:26   [ibmnoida] Files:1    Time: 1s

Serial   Phase #:27   [ibmnoida] Files:1    Time: 0s

Serial   Phase #:28   [ibmnoida] Files:3    Time: 4s

Serial   Phase #:29   [ibmnoida] Files:1    Time: 0s

Restart  Phase #:30   [ibmnoida] Files:1    Time: 2s

***************   Catproc CDB Views   **************

Serial   Phase #:31   [ibmnoida] Files:1    Time: 2s

Restart  Phase #:32   [ibmnoida] Files:1    Time: 1s

Serial   Phase #:34   [ibmnoida] Files:1    Time: 0s

*****************   Catproc PLBs   *****************

Serial   Phase #:35   [ibmnoida] Files:293  Time: 17s

Serial   Phase #:36   [ibmnoida] Files:1    Time: 0s

Restart  Phase #:37   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:38   [ibmnoida] Files:6    Time: 6s

Restart  Phase #:39   [ibmnoida] Files:1    Time: 2s

***************   Catproc DataPump   ***************

Serial   Phase #:40   [ibmnoida] Files:3    Time: 39s

Restart  Phase #:41   [ibmnoida] Files:1    Time: 3s

******************   Catproc SQL   *****************

Parallel Phase #:42   [ibmnoida] Files:13   Time: 80s

Restart  Phase #:43   [ibmnoida] Files:1    Time: 2s

Parallel Phase #:44   [ibmnoida] Files:11   Time: 7s

Restart  Phase #:45   [ibmnoida] Files:1    Time: 3s

Parallel Phase #:46   [ibmnoida] Files:3    Time: 3s

Restart  Phase #:47   [ibmnoida] Files:1    Time: 2s

*************   Final Catproc scripts   ************

Serial   Phase #:48   [ibmnoida] Files:1    Time: 6s

Restart  Phase #:49   [ibmnoida] Files:1    Time: 4s

**************   Final RDBMS scripts   *************

Serial   Phase #:50   [ibmnoida] Files:1    Time: 4s

************   Upgrade Component Start   ***********

Serial   Phase #:51   [ibmnoida] Files:1    Time: 2s

Restart  Phase #:52   [ibmnoida] Files:1    Time: 1s

**********   Upgrading Java and non-Java   *********

Serial   Phase #:53   [ibmnoida] Files:2



   Time: 203s

*****************   Upgrading XDB   ****************

Restart  Phase #:54   [ibmnoida] Files:1    Time: 3s

Serial   Phase #:56   [ibmnoida] Files:3    Time: 8s

Serial   Phase #:57   [ibmnoida] Files:3    Time: 5s

Parallel Phase #:58   [ibmnoida] Files:10   Time: 5s

Parallel Phase #:59   [ibmnoida] Files:25   Time: 7s

Serial   Phase #:60   [ibmnoida] Files:4    Time: 8s

Serial   Phase #:61   [ibmnoida] Files:1    Time: 0s

Serial   Phase #:62   [ibmnoida] Files:32   Time: 6s

Serial   Phase #:63   [ibmnoida] Files:1    Time: 0s

Parallel Phase #:64   [ibmnoida] Files:6    Time: 6s

Serial   Phase #:65   [ibmnoida] Files:2    Time: 18s

Serial   Phase #:66   [ibmnoida] Files:3    Time: 26s

****************   Upgrading ORDIM   ***************

Restart  Phase #:67   [ibmnoida] Files:1    Time: 3s

Serial   Phase #:69   [ibmnoida] Files:1    Time: 4s

Parallel Phase #:70   [ibmnoida] Files:2

   Time: 26s

Restart  Phase #:71   [ibmnoida] Files:1    Time: 3s

Parallel Phase #:72   [ibmnoida] Files:2    Time: 4s

Serial   Phase #:73   [ibmnoida] Files:2    Time: 4s

*****************   Upgrading SDO   ****************

Restart  Phase #:74   [ibmnoida] Files:1    Time: 3s

Serial   Phase #:76   [ibmnoida] Files:1    Time: 33s

Serial   Phase #:77   [ibmnoida] Files:2    Time: 5s

Restart  Phase #:78   [ibmnoida] Files:1    Time: 3s

Serial   Phase #:79   [ibmnoida] Files:1    Time: 44s

Restart  Phase #:80   [ibmnoida] Files:1    Time: 3s

Parallel Phase #:81   [ibmnoida] Files:3    Time: 38s

Restart  Phase #:82   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:83   [ibmnoida] Files:1    Time: 7s

Restart  Phase #:84   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:85   [ibmnoida] Files:1    Time: 10s

Restart  Phase #:86   [ibmnoida] Files:1    Time: 3s

Parallel Phase #:87   [ibmnoida] Files:4    Time: 58s

Restart  Phase #:88   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:89   [ibmnoida] Files:1    Time: 4s

Restart  Phase #:90   [ibmnoida] Files:1    Time: 2s

Serial   Phase #:91   [ibmnoida] Files:2    Time: 10s

Restart  Phase #:92   [ibmnoida] Files:1    Time: 3s

Serial   Phase #:93   [ibmnoida] Files:1    Time: 2s

Restart  Phase #:94   [ibmnoida] Files:1    Time: 2s

*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******

Serial   Phase #:95   [ibmnoida] Files:1    Time: 10s

Restart  Phase #:96   [ibmnoida] Files:1    Time: 4s

***********   Final Component scripts    ***********

Serial   Phase #:97   [ibmnoida] Files:1

   Time: 3s

*************   Final Upgrade scripts   ************

Serial   Phase #:98   [ibmnoida] Files:1


   Time: 175s

*******************   Migration   ******************

Serial   Phase #:99   [ibmnoida] Files:1    Time: 2s

***   End PDB Application Upgrade Pre-Shutdown   ***

Serial   Phase #:100  [ibmnoida] Files:1    Time: 2s

Serial   Phase #:101  [ibmnoida] Files:1    Time: 0s

Serial   Phase #:102  [ibmnoida] Files:1    Time: 50s

*****************   Post Upgrade   *****************

Serial   Phase #:103  [ibmnoida] Files:1    Time: 12s

****************   Summary report   ****************

Serial   Phase #:104  [ibmnoida] Files:1    Time: 2s

***   End PDB Application Upgrade Post-Shutdown   **

Serial   Phase #:105  [ibmnoida] Files:1    Time: 2s

Serial   Phase #:106  [ibmnoida] Files:1    Time: 0s

Serial   Phase #:107  [ibmnoida] Files:1     Time: 56s


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

Phases [0-107]         End Time:[2022_12_14 11:53:08]

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


Grand Total Time: 1515s


 LOG FILES: (/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd*.log)


Upgrade Summary Report Located in:

/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/upg_summary.log


Grand Total Upgrade Time:    [0d:0h:25m:15s]

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$



SQL>  select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 PROTECTION_MODE

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

IBMGGN    READ WRITE           PRIMARY          ibmnoida                       MAXIMUM PERFORMANCE


SQL>

SQL>




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


  COUNT(*)

----------

      2547



SQL> @?/rdbms/admin/utlrp


Session altered.



TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN              2022-12-14 11:56:12


DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#



PL/SQL procedure successfully completed.



TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END              2022-12-14 11:58:57


DOC> The following query reports the number of invalid objects.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#


OBJECTS WITH ERRORS

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

                  0


DOC> The following query reports the number of exceptions caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC> Note: Typical compilation errors (due to coding errors) are not

DOC>       logged into this table: they go into DBA_ERRORS instead.

DOC>#


ERRORS DURING RECOMPILATION

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

                          0



Function created.



PL/SQL procedure successfully completed.



Function dropped.



PL/SQL procedure successfully completed.


SQL> SQL>

SQL>

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


  COUNT(*)

----------

         0


SQL>










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

########################################################################################################################3



[oracle@ip-172-31-19-40 admin]$

[oracle@ip-172-31-19-40 admin]$ sqlplus system/system@ibm12c


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 07:24:30 2022

Version 19.3.0.0.0


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



Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@ip-172-31-19-40 admin]$

[oracle@ip-172-31-19-40 admin]$






On Source DB

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


DB_NAME : ibm12c

DB-Unique_name = ibm12c

Database version : 12.2.0

OS : RHE 8.7




On Target DB

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


DB_NAME : ibm12c

DB-Unique_name = ibm12c_dr

Database version : 19.3.0

OS : RHE 8.7





SQL> select name ,open_mode, log_mode from v$database;


NAME      OPEN_MODE            LOG_MODE

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

IBM12C    READ WRITE           ARCHIVELOG


SQL>


SQL> select log_mode from v$database;


LOG_MODE

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

ARCHIVELOG


SQL>

SQL> select force_logging from v$database;


FORCE_LOGGING

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

NO


SQL>

SQL> ALTER DATABASE FORCE LOGGING;


Database altered.


SQL> select force_logging from v$database;


FORCE_LOGGING

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

YES


SQL>




alter system set log_archive_config='dg_config=(ibm12c,ibm12c_dr)' scope=both;


alter system set log_archive_dest_2='service=ibm12c_dr  noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibm12c_dr' scope=both;



ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; 



ALTER SYSTEM SET FAL_SERVER=ibm12c_dr scope=both; 



ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both; 




SQL> show parameter remote


NAME                                 TYPE        VALUE

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

remote_dependencies_mode             string      TIMESTAMP

remote_listener                      string

remote_login_passwordfile            string      EXCLUSIVE

remote_os_authent                    boolean     FALSE

remote_os_roles                      boolean     FALSE

remote_recovery_file_dest            string

result_cache_remote_expiration       integer     0

SQL>

SQL>



alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo1.log') size 50m; 

alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo2.log') size 50m;  

alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo3.log') size 50m;  

alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo4.log') size 50m;



SQL> select member from v$logfile;


MEMBER

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

/u01/app/oracle/oradata/ibm12c/redo03.log

/u01/app/oracle/oradata/ibm12c/redo02.log

/u01/app/oracle/oradata/ibm12c/redo01.log


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo1.log') size 50m;


Database altered.


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo2.log') size 50m;


Database altered.


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo3.log') size 50m;


Database altered.


SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo4.log') size 50m;


Database altered.


SQL> alter system set log_archive_config='dg_config=(ibm12c,ibm12c_dr)' scope=spfile;


System altered.


SQL> alter system set log_archive_config='dg_config=(ibm12c,ibm12c_dr)' scope=both;


System altered.


SQL> alter system set log_archive_dest_2='service=ibm12c_dr  noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibm12c_dr' scope=both;


System altered.


SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;


System altered.


SQL> ALTER SYSTEM SET FAL_SERVER=ibm12c_dr scope=both;


System altered.


SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;


System altered.


SQL>

SQL> select member from v$logfile;


MEMBER

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

/u01/app/oracle/oradata/ibm12c/redo03.log

/u01/app/oracle/oradata/ibm12c/redo02.log

/u01/app/oracle/oradata/ibm12c/redo01.log

/u01/app/oracle/oradata/ibm12c/stbyredo1.log

/u01/app/oracle/oradata/ibm12c/stbyredo2.log

/u01/app/oracle/oradata/ibm12c/stbyredo3.log

/u01/app/oracle/oradata/ibm12c/stbyredo4.log


7 rows selected.


SQL>





SID_LIST_LISTENER =

   (SID_LIST =

     (SID_DESC =

       (GLOBAL_DBNAME = ibm12c)

       (ORACLE_HOME = /u01/app/sysadmin/product/19.2.0/db_home )

       (SID_NAME = ibm12c)

     )

(SID_DESC =

       (GLOBAL_DBNAME = ibm12c_dr)

       (ORACLE_HOME = /u01/app/sysadmin/product/19.2.0/db_home )

       (SID_NAME = ibm12c_dr)

     )

   )

   

 

 

 

 ibm12c =

   (DESCRIPTION =

     (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.1)(PORT = 1521))

     )

     (CONNECT_DATA =

       (SERVICE_NAME = ibm12c)

     )

   )

 ibm12c_dr =

   (DESCRIPTION =

     (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521))

     )

     (CONNECT_DATA =

       (SERVICE_NAME = ibm12c_dr)

     )

   )

   

  






backup Source database



run{

 allocate channel d1 type disk MAXPIECESIZE 1G;

 sql 'alter system archive log current';

 sql 'alter system archive log current';

 backup format '/u01/app/oracle/backup/db_t%t_s%s_p%p' database;

 sql 'alter system archive log current';

 sql 'alter system archive log current';

 backup format '/u01/app/oracle/backup/ar_t%t_s%s_p%p' archivelog all;

 release channel d1;

 }

 

 

 

 


Backup controlfile for standby 


RMAN> run

 {

 allocate channel c1 type disk;

 backup current controlfile for standby;

 }

 

 

 SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/std_testdb.ctl';


Database altered.



CREATE PFILE ='/u01/app/oracle/stdby_backup/stdby.ora' FROM SPFILE;




 

 

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

 

 


  


rman target sys/sys@ibm12c auxiliary sys/sys@ibm12c_dr






[oracle@ip-172-31-21-233 dbs]$

[oracle@ip-172-31-21-233 dbs]$ rman target sys/sys@ibm12c auxiliary sys/sys@ibm12c_dr


Recovery Manager: Release 12.2.0.1.0 - Production on Wed Dec 7 13:44:27 2022


Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


connected to target database: IBM12C (DBID=3136829478)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06429: AUXILIARY database is not compatible with this version of RMAN

RMAN-06618: RMAN client and database release mismatch; indicated database release is 19.3.0.0

[oracle@ip-172-31-21-233 dbs]$

[oracle@ip-172-31-21-233 dbs]$




[oracle@ip-172-31-19-40 ~]$ rman target sys/sys@ibm12c


Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 7 13:35:09 2022

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06429: TARGET database is not compatible with this version of RMAN

RMAN-06618: RMAN client and database version mismatch; indicated database version is 12.2.0.1

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$



BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'FULL BACKUP PLUS ARCHIVELOG';




sql 'alter system archive log current';

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";

RUN

{

configure controlfile autobackup on;

set command id to 'ORCLOnlineBackupFull';

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

ALLOCATE CHANNEL c2 DEVICE TYPE disk;

ALLOCATE CHANNEL c3 DEVICE TYPE disk;

ALLOCATE CHANNEL c4 DEVICE TYPE disk;

backup AS COMPRESSED BACKUPSET full database tag ibm12c_FULL format '/tmp/ibm12c/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag ibm12c_ARCHIVE format '/tmp/ibm12c/%d_%T_%s_%p_ARCHIVE' archivelog all;

backup tag ibm12c_CONTROL current controlfile format '/tmp/ibm12c/%d_%T_%s_%p_CONTROL';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}




RMAN> list backup;


using target database control file instead of recovery catalog


List of Backup Sets

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



BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Full    10.19M     DISK        00:00:00     07-DEC-22

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20221207T131608

        Piece Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/c-3136829478-20221207-00

  SPFILE Included: Modification time: 07-DEC-22

  SPFILE db_unique_name: IBM12C

  Control File Included: Ckp SCN: 1436459      Ckp time: 07-DEC-22


RMAN>


RMAN> list archivelog all;


List of Archived Log Copies for database with db_unique_name IBM12C

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


Key     Thrd Seq     S Low Time

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

1       1    1       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_1_1122793448.dbf


2       1    2       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_2_1122793448.dbf


3       1    3       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_3_1122793448.dbf



RMAN>


RMAN>



[oracle@ip-172-31-19-40 tmp]$ rman target /


Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 7 14:18:46 2022

Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


connected to target database: IBM12C (not mounted)


RMAN> restore spfile from '/tmp/c-3136829478-20221207-03';


Starting restore at 07-DEC-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK


channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/c-3136829478-20221207-03

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 07-DEC-22


RMAN>



RMAN> restore controlfile from '/tmp/ibm12c/IBM12C_20221207_13_1_CONTROL';


Starting restore at 07-DEC-22

using channel ORA_DISK_1


channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/product/19.0/dbhome1/dbs/cntrlibm12c.dbf

Finished restore at 07-DEC-22


RMAN>



RMAN> restore controlfile from '/tmp/ibm12c/IBM12C_20221207_13_1_CONTROL';


Starting restore at 07-DEC-22

using channel ORA_DISK_1


channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ibm12c/control01.ctl

output file name=/u01/app/oracle/oradata/ibm12c/control02.ctl

Finished restore at 07-DEC-22


RMAN> alter database mount;


released channel: ORA_DISK_1

Statement processed


RMAN>




RMAN> restore database;


Starting restore at 07-DEC-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ibm12c/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_6_1_FULL

channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_6_1_FULL tag=IBM12C_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ibm12c/users01.dbf

channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_7_1_FULL

channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_7_1_FULL tag=IBM12C_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ibm12c/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_5_1_FULL

channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_5_1_FULL tag=IBM12C_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ibm12c/system01.dbf

channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_4_1_FULL

channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_4_1_FULL tag=IBM12C_FULL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 07-DEC-22


RMAN>



recover database until sequence 4;



RMAN> recover database until sequence 8;


Starting recover at 07-DEC-22

using channel ORA_DISK_1


starting media recovery


channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=6

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=7

channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_11_1_ARCHIVE

channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_11_1_ARCHIVE tag=IBM12C_ARCHIVE

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_6_1122793448.dbf thread=1 sequence=6

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_7_1122793448.dbf thread=1 sequence=7

media recovery complete, elapsed time: 00:00:01

Finished recover at 07-DEC-22


RMAN>





on source12c

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


[oracle@ip-172-31-21-233 tmp]$ sqlplus "/as sysdba"


SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 7 14:33:31 2022


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



Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch

Oldest online log sequence     6

Next log sequence to archive   8

Current log sequence           8

SQL>

SQL>

SQL> alter system switch logfile;


System altered.


SQL> create user abc20pm identified by abc;'

  2

SQL>

SQL> create user abc20pm identified by abc;


User created.


SQL> grant dba to abc20pm;


Grant succeeded.


SQL> conn abc20pm/abc

Connected.

SQL> create table test (id number);


Table created.


SQL> insert into test values (1);


1 row created.


SQL> commit;


Commit complete.


SQL>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@ip-172-31-21-233 tmp]$

[oracle@ip-172-31-21-233 tmp]$ sqlplus "/as sysdba"


SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 7 14:35:08 2022


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



Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> alter system switch logfile;


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch

Oldest online log sequence     11

Next log sequence to archive   13

Current log sequence           13

SQL>

SQL>




BACKUP  format '/tmp/ibm12c/%d_%T_%s_%p_ARCHIVE_7_12'  ARCHIVELOG FROM SEQUENCE 7 UNTIL SEQUENCE 12; 


RMAN> BACKUP  format '/tmp/ibm12c/%d_%T_%s_%p_ARCHIVE_7_12'  ARCHIVELOG FROM SEQUENCE 7 UNTIL SEQUENCE 12;


Starting backup at 07-DEC-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=267 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=7 RECID=7 STAMP=1122819070

input archived log thread=1 sequence=8 RECID=8 STAMP=1122820430

input archived log thread=1 sequence=9 RECID=9 STAMP=1122820519

input archived log thread=1 sequence=10 RECID=10 STAMP=1122820521

input archived log thread=1 sequence=11 RECID=11 STAMP=1122820524

input archived log thread=1 sequence=12 RECID=12 STAMP=1122820527

channel ORA_DISK_1: starting piece 1 at 07-DEC-22

channel ORA_DISK_1: finished piece 1 at 07-DEC-22

piece handle=/tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12 tag=TAG20221207T143852 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 07-DEC-22


Starting Control File and SPFILE Autobackup at 07-DEC-22

piece handle=/u01/app/oracle/product/12.2.0/dbhome1/dbs/c-3136829478-20221207-04 comment=NONE

Finished Control File and SPFILE Autobackup at 07-DEC-22


RMAN>




ship archive log to target 


[oracle@ip-172-31-21-233 tmp]$ scp -r /u01/app/oracle/product/12.2.0/dbhome1/dbs/c-3136829478-20221207-03 oracle@172.31.19.40:/tmp

oracle@172.31.19.40's password:

c-3136829478-20221207-03                                                               100%   10MB  93.5MB/s   00:00

[oracle@ip-172-31-21-233 tmp]$




[oracle@ip-172-31-21-233 ibm12c]$

[oracle@ip-172-31-21-233 ibm12c]$ scp IBM12C_20221207_15_1_ARCHIVE_7_12 oracle@172.31.19.40:/tmp/ibm12c

oracle@172.31.19.40's password:

IBM12C_20221207_15_1_ARCHIVE_7_12                                                      100% 1145KB 101.1MB/s   00:00

[oracle@ip-172-31-21-233 ibm12c]$



on target 

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


RMAN> catalog start with '/tmp/ibm12c';


searching for all files that match the pattern /tmp/ibm12c


List of Files Unknown to the Database

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

File Name: /tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12


Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done


List of Cataloged Files

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

File Name: /tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12


RMAN>



RMAN> list archivelog all;


List of Archived Log Copies for database with db_unique_name IBM12C

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


Key     Thrd Seq     S Low Time

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

1       1    1       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_1_1122793448.dbf


2       1    2       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_2_1122793448.dbf


3       1    3       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_3_1122793448.dbf


4       1    4       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_4_1122793448.dbf


5       1    5       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_5_1122793448.dbf


9       1    6       A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_6_1122793448.dbf


6       1    6       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_6_1122793448.dbf


8       1    7       A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_7_1122793448.dbf


7       1    7       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_7_1122793448.dbf



RMAN>




RMAN> recover database until sequence 13;


Starting recover at 07-DEC-22

using channel ORA_DISK_1


starting media recovery


channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=8

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=9

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=10

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=11

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=12

channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12

channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12 tag=TAG20221207T143852

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_8_1122793448.dbf thread=1 sequence=8

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_9_1122793448.dbf thread=1 sequence=9

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_10_1122793448.dbf thread=1 sequence=10

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_11_1122793448.dbf thread=1 sequence=11

archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_12_1122793448.dbf thread=1 sequence=12

media recovery complete, elapsed time: 00:00:01

Finished recover at 07-DEC-22


RMAN>




RMAN> list archivelog all;


List of Archived Log Copies for database with db_unique_name IBM12C

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


Key     Thrd Seq     S Low Time

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

1       1    1       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_1_1122793448.dbf


2       1    2       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_2_1122793448.dbf


3       1    3       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_3_1122793448.dbf


4       1    4       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_4_1122793448.dbf


5       1    5       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_5_1122793448.dbf


9       1    6       A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_6_1122793448.dbf


6       1    6       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_6_1122793448.dbf


8       1    7       A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_7_1122793448.dbf


7       1    7       A 07-DEC-22

        Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_7_1122793448.dbf


14      1    8       A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_8_1122793448.dbf


10      1    9       A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_9_1122793448.dbf


12      1    10      A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_10_1122793448.dbf


11      1    11      A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_11_1122793448.dbf


13      1    12      A 07-DEC-22

        Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_12_1122793448.dbf



RMAN>



Alter Database open migrate ;


alter database open resetlogs upgrade;




https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=296460073482260&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=984511.1&_afrWindowMode=0&_adf.ctrl-state=3cn7j36nl_129


RMAN> alter database open resetlogs upgrade;


using target database control file instead of recovery catalog

PSDRPC returns significant error 3113.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 12/07/2022 15:25:41

ORA-04023: Object SYS.STANDARD could not be validated or authorized


RMAN>



[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$ sqlplus "/as sysdba"


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 15:30:00 2022

Version 19.3.0.0.0


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


Connected to an idle instance.


SQL> startup upgrade;

ORACLE instance started.


Total System Global Area 2449472408 bytes

Fixed Size                  8899480 bytes

Variable Size             536870912 bytes

Database Buffers         1895825408 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> select status,active_state from v$instance;


STATUS       ACTIVE_ST

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

OPEN MIGRATE NORMAL


SQL>



/u01/app/oracle/product/19.0/dbhome1/rdbms/admin





upgrade start at 9:00PM 1/12/2022

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



SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0



[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$ sqlplus "/as sysdba"


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 15:30:00 2022

Version 19.3.0.0.0


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


Connected to an idle instance.


SQL> startup upgrade;

ORACLE instance started.


Total System Global Area 2449472408 bytes

Fixed Size                  8899480 bytes

Variable Size             536870912 bytes

Database Buffers         1895825408 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> select status,active_state from v$instance;


STATUS       ACTIVE_ST

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

OPEN MIGRATE NORMAL


SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$

[oracle@ip-172-31-19-40 ~]$ cd $ORACLE_HOME/

[oracle@ip-172-31-19-40 dbhome1]$ pwd

/u01/app/oracle/product/19.0/dbhome1

[oracle@ip-172-31-19-40 dbhome1]$

[oracle@ip-172-31-19-40 dbhome1]$ cd rdbms/admin/

[oracle@ip-172-31-19-40 admin]$

[oracle@ip-172-31-19-40 admin]$ pwd

/u01/app/oracle/product/19.0/dbhome1/rdbms/admin

[oracle@ip-172-31-19-40 admin]$

[oracle@ip-172-31-19-40 admin]$ cd ..

[oracle@ip-172-31-19-40 rdbms]$ cd ..

[oracle@ip-172-31-19-40 dbhome1]$ cd bin/

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ ls -lrt dbu

dbua       dbupgrade

[oracle@ip-172-31-19-40 bin]$ ls -lrt dbu

dbua       dbupgrade

[oracle@ip-172-31-19-40 bin]$ ./dbupgrade


Argument list for [/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catctl.pl]

For Oracle internal use only A = 0

Run in                       c = 0

Do not run in                C = 0

Input Directory              d = 0

Echo OFF                     e = 1

Simulate                     E = 0

Forced cleanup               F = 0

Log Id                       i = 0

Child Process                I = 0

Log Dir                      l = 0

Priority List Name           L = 0

Upgrade Mode active          M = 0

SQL Process Count            n = 0

SQL PDB Process Count        N = 0

Open Mode Normal             o = 0

Start Phase                  p = 0

End Phase                    P = 0

Reverse Order                r = 0

AutoUpgrade Resume           R = 0

Script                       s = 0

Serial Run                   S = 0

RO User Tablespaces          T = 0

Display Phases               y = 0

Debug catcon.pm              z = 0

Debug catctl.pl              Z = 0


catctl.pl VERSION: [19.0.0.0.0]

           STATUS: [Production]

            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]



/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0/dbhome1]

/u01/app/oracle/product/19.0/dbhome1/bin/orabasehome = [/u01/app/oracle/product/19.0/dbhome1]

catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0/dbhome1]


Analyzing file /u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catupgrd.sql


Log file directory = [/tmp/cfgtoollogs/upgrade20221207153254]


catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221207153254/catupgrd_catcon_4973.lst]


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221207153254/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221207153254/catupgrd_*.lst] files for spool files, if any



Number of Cpus        = 2

Database Name         = ibm12c

DataBase Version      = 12.2.0.1.0

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304/catupgrd_catcon_4973.lst]


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304/catupgrd*.log] files for output generated by scripts


catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304/catupgrd_*.lst] files for spool files, if any



Log file directory = [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304]


Parallel SQL Process Count            = 4

Components in [ibm12c]

    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]

Not Installed [APEX EM MGW ODM RAC WK]


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

Phases [0-107]         Start Time:[2022_12_07 15:33:16]

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

***********   Executing Change Scripts   ***********

Serial   Phase #:0    [ibm12c] Files:1    Time: 21s

***************   Catalog Core SQL   ***************

Serial   Phase #:1    [ibm12c] Files:5



   Time: 31s

Restart  Phase #:2    [ibm12c] Files:1    Time: 0s

***********   Catalog Tables and Views   ***********

Parallel Phase #:3    [ibm12c] Files:19   Time: 14s

Restart  Phase #:4    [ibm12c] Files:1    Time: 1s

*************   Catalog Final Scripts   ************

Serial   Phase #:5    [ibm12c] Files:7    Time: 13s

*****************   Catproc Start   ****************

Serial   Phase #:6    [ibm12c] Files:1    Time: 10s

*****************   Catproc Types   ****************

Serial   Phase #:7    [ibm12c] Files:2    Time: 10s

Restart  Phase #:8    [ibm12c] Files:1    Time: 1s

****************   Catproc Tables   ****************

Parallel Phase #:9    [ibm12c] Files:67   Time: 20s

Restart  Phase #:10   [ibm12c] Files:1    Time: 1s

*************   Catproc Package Specs   ************

Serial   Phase #:11   [ibm12c] Files:1    Time: 55s

Restart  Phase #:12   [ibm12c] Files:1    Time: 1s

**************   Catproc Procedures   **************

Parallel Phase #:13   [ibm12c] Files:94   Time: 8s

Restart  Phase #:14   [ibm12c] Files:1    Time: 2s

Parallel Phase #:15   [ibm12c] Files:120  Time: 12s

Restart  Phase #:16   [ibm12c] Files:1    Time: 3s

Serial   Phase #:17   [ibm12c] Files:22   Time: 3s

Restart  Phase #:18   [ibm12c] Files:1    Time: 3s

*****************   Catproc Views   ****************

Parallel Phase #:19   [ibm12c] Files:32   Time: 14s

Restart  Phase #:20   [ibm12c] Files:1    Time: 3s

Serial   Phase #:21   [ibm12c] Files:3    Time: 10s

Restart  Phase #:22   [ibm12c] Files:1    Time: 2s

Parallel Phase #:23   [ibm12c] Files:25







upgrade timezone if required 

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


Upgrade timezone data to version 18




[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ sqlplus "/as sysdba"


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 16:03:27 2022

Version 19.3.0.0.0


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


Connected to an idle instance.


SQL>

SQL> startup

ORACLE instance started.


Total System Global Area 2449472408 bytes

Fixed Size                  8899480 bytes

Variable Size             637534208 bytes

Database Buffers         1795162112 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL>





[oracle@ip-172-31-19-40 bin]$

[oracle@ip-172-31-19-40 bin]$ sqlplus "/as sysdba"


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 16:03:27 2022

Version 19.3.0.0.0


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


Connected to an idle instance.


SQL>

SQL> startup

ORACLE instance started.


Total System Global Area 2449472408 bytes

Fixed Size                  8899480 bytes

Variable Size             637534208 bytes

Database Buffers         1795162112 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL>

SQL> select name ,open_mode from v$database;


NAME      OPEN_MODE

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

IBM12C    READ WRITE


SQL>

SQL> conn abc20pm/abc

Connected.

SQL> select * from test;


        ID

----------

         1


SQL>



2 comments:

  1. Amazing, this is what I was looking. So much effort put 🙌

    ReplyDelete