Creating an Oracle 12c R2 Physical Standby RAC Database

 https://ahmedbaraka.com/download/video-tutorials/Create%20an%20Oracle%20Physical%20Standby%20Database.pdf

Migration using Datapump

 

#### Migration using Datapump #####

https://oracledbwr.com/migrating-and-upgrade-oracle-database-windows-from-11-2-0-1-to-linux-database-12-2-0-1-using-datapump/

https://smarttechways.com/2022/01/23/steps-for-migration-with-expdp-and-impdp-datapump/

https://rajeevjhaoracle.wordpress.com/2015/12/25/step-by-step-database-refresh/

Migration Datapump/Schema refresh/Dry run

 

######Migration Datapump/Schema refresh/Dry run#####

http://oracledatabasetutorial.blogspot.com/2014/09/schema-refresh-in-oracle-using-datapump.html

http://www.br8dba.com/schema-refresh-from-11g-to-12c-pdb/

Migration using Datapump 11g to 19c

 select instance_name, status, version, host_name from v$instance;

 

SELECT value AS db_ncharset FROM nls_database_parameters WHERE parameter = 'NLS_NCHAR_CHARACTERSET';

 

--Examine datafile storage structure

select name from v$datafile;

 

--- get info for tablespaces

select tablespace_name from dba_tablespaces;

 

--- compared list of tablespaces with the same output from newly created database and create missing tablespace (s).

--- DDL for creating tablesapces ... use

SELECT dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;

 

--- create datapump directory objects 

--- make datapump directory on on file system

--- create directory objects

select * from dba_directories;

create or replace directory expdir as '';

 

--- Validation after migration---

select * from dba_users;---where account_status='OPEN';

select username, account_status, default_tablespace, password_versions from dba_users;

select distinct schema, other_schemas from dba_registry;-- (oracle maintained users in 11g) 

--- Export all schemas except those maintained by oracle

select schema, other_schemas from dba_registry;

--During the import if you had users with 10G password versions, these accounts will be locked and expired, unless the SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 10 or 11

--- Object Counts

--- Number of oracle schema objects and their sizes : [preferred]

select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj , (select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg where obj.owner = seg.owner(+) order by 3 desc ,2 desc, 1;

 

object count should match, size does not matter.

-- Memory type

-- AMM vs ASMM

show parameter memory;

if memory_target is used, it means AMM is used.

vi fullorcl.par

directory=EXPDIR

full=Y

dumpfile=fullorclexp.dmp

logfile=fullorclexp.log

exclude=statistics

exclude=schema:"IN (''SYS','SYSTEM','APEX_030200','CTXSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','OWBSYS','SYSMAN','WMSYS','XDB

 

expdp \'/ as sysdba\' parfile=fullorcl.par 

vi imporcl.par

directory=EXPDIR

full=Y

dumpfile=fullorclexp.dmp

logfile=fullorclimp.log

 

impdp \'/ as sysdba\' parfile=fullorcl.par 

 

Ref : https://www.youtube.com/watch?v=JYMRpgEJ5lc

 

 

Migration using Datapump 12c to 19c

 Major Steps

> Export user,role,profile from 12c databases.

> Import user,role,profile to 19c database.

>Export schemas from 12c/11g database.

>Import schemas to 19c database.

Source Server

$sqlplus / as sysdba

sql>! mkdir /u01/export

sql>create directory export as '/u01/export';

sql>exit

$expdp directory=export dumpfile=user_role_profile.dmp logfile=user_role_profile_expdp.log full=y include=user,role,role_grant,profile 

$expdp directory=export dumpfile=test_schema.dmp logfile=test_schema_expdp.log schemas=test

 

Dest server

mkdir /u01/import

 

Source server

cd /u01/export

scp * oracle@111.111.11.11:/u01/import

 

Dest Server

sqlplus / as sysdba

create directory import as '/u01/import';

exit

create tablespace same as source for test schema

select tablespace_name from dba_segments where owner='TEST';

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

 

$impdp directory=import dumpfile=user_role_profile.dmp logfile=user_role_profile_impdp.log full=y include=user,role,role_grant,profile 

$impdp directory=import dumpfile=test_schema.dmp logfile=test_schema_impdp.log schemas=test

 

Ref https://www.youtube.com/watch?v=mYQ5OvSaSFI

 

 

12c to 19c upgrade by autoupgrade

Prerequisite

1) Download autoupgrade.jar file from MOS 2485457.1

mv  /u01/app/oracle/product/19c/db_1/rdbms/admin/autoupgrade.jar autoupgrade.jar_old

cp -r autoupgrade.jar /u01/app/oracle/product/19c/db_1/rdbms/admin

$ /u01/app/oracle/product/19c/db_1/jdk/bin/java -jar /u01/app/oracle/product/19c/db_1/rdbms/admin/autoupgrade.jar -version

$ /u01/app/oracle/product/19c/db_1/jdk/bin/java -version

 

 For an upgrade, the java version should be 1.8 which is available in “ORACLE_HOME/jdk/bin” so make sure match to the minimum required version of java.

 cd /u01/autoupgrade_19c

$ /u01/app/oracle/product/19c/db_1/jdk/bin/java -jar /u01/app/oracle/product/19c/db_1/rdbms/admin/autoupgrade.jar -create_sample_file config

$ cp sample_config.cfg prod_db_config.cfg

$ > prod_db_config.cfg

$ vi prod_db_config.cfg

upg1.dbname=prod

upg1.start_time=NOW

upg1.source_home=/u01/app/oracle/product/12/db_1

upg1.target_home=/u01/app/oracle/product/19c/db_1

upg1.sid=prod

upg1.log_dir=/u01/autoupgrade_19c/upg_logs/prod

upg1.upgrade_node=hostname (select host_name from v$instance;)

upg1.target_version=19.11

upg1.run_utlrp=yes

upg1.timezone_upg=yes

 

 

 

 

export ORACLE_HOME=/u01/app/oracle/product/19c/db_1/

export PATH=$PATH:$ORACLE_HOME/jdk/bin

cd u01/autoupgrade_19c

$ /u01/app/oracle/product/19c/db_1/jdk/bin/java -jar /u01/app/oracle/product/19c/db_1/rdbms/admin/autoupgrade.jar -config prod_db_config.cfg -mode ANALYZE

 

lsj - to list the jobs

status -job 101 - to show the job status

$ /u01/app/oracle/product/19c/db_1/jdk/bin/java -jar /u01/app/oracle/product/19c/db_1/rdbms/admin/autoupgrade.jar -config prod_db_config.cfg -mode DEPLOY

Autoupgrade logs are available at below location

/u01/autoupgrade_19c/upg_logs

tail below log and as well as database alert log for monitoring. Also user lsj and status command

 

select name from v$restore_point;

drop restore point restorepoint_name;

show parameter compatible

alter system set conpatible='19.0.0' scope=spfile;

shutdown immediate;

startup;

show parameter compatible;

 Ref https://www.youtube.com/watch?v=UPYGd78USSw&t=2478s

 

 

12c to 19c upgrade manual

 Executing the pre-upgrade jar tool 

The pre-upgrade information tool is available in the new release oracle home, in path

$ORACLE_HOME/rdbms/admin/preupgrade.jar 

mkdir -p /u02/preupgrade 

Run the preupgrade tool 

/u02/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/19c/db_1/rdbms/admin/preupgrade.jar DIR /u02/preupgrade

select name,open_mode,log_mode from v$database;

@preupgrade_fixups.sql 

Performing the pre-upgrade actions 

Run the preupgrade_fixups.sql

 

select file_name from dba_data_files;

alter database datafile '/u01/app/oradata/PROD/datafile/system.dbf' resize 1g;

alter database datafile '/u01/app/oradata/PROD/datafile/syaux.dbf' resize 700m;

alter database datafile '/u01/app/oradata/PROD/datafile/undotbs.dbf' resize 600m;

select file_name from dba_temp_files;

alter database tempfile '/u01/app/oradata/PROD/datafile/temp.dbf' resize 300m;

 

 Backing up the database / create a gaurantee restore point

>create restore point before_upgrade_19c guarantee flashback database;

>select name,guarnatee_flashback_database, time from v$restore_point;

Upgrading the database

• sqlplus / as sysdba
• shutdown immediate;
• exit
• copy the spfile, password file and listener from old ORACLE_HOME to new 19c ORACLE_HOME.

 cd $ORACLE_HOME/dbs

cp spfileorcl.ora orapwdprod.ora /u01/app/oracle/product/19c/db_1/dbs

cd $ORACLE_HOME/network/admin

cp listener.ora tnsnames.ora /u01/app/oracle/product/19c/db_1/network/admin

Stop listner running on 12c home and start it from 19c home.

start the database from 19c ORACLE_HOME and start the updgrade.

sqlplus / as sysdba

startup upgrade

select name,open_mode, status from v$database, v$instance;

Run the DB Upgrade utility

cd $ORACLE_HOME

pwd

cd bin

./dbupgrade -n 8

check out and wait for 30 minute 

check the upgrade summary log

startup database manually and

select name,open_mode, status from v$database, v$instance;

Execute Post-Upgrade Status Tools, utlusts.sql

• @$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
• Recompile the INVALID objects using utlrp.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

 

Performing the post-upgrade actions.

> Connect to sqlplus and run the postupgrade_fixup.sql script

sqlplus / as sysdba

@/u02/preupgrade postupgrade_fixup.sql

 

>Upgrade the timezone file version

sqlplus / as sysdba

select version from v$timezone_file;

@ORACLE_HOME/rdbms/admin/utltz_countstats.sql

@ORACLE_HOME/rdbms/admin/utltz_countstar.sql

@ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

@ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

select version from v$timezone_file; 

Performing the post-upgrade actions.

>Gather statistics on fixed objects

 

>connect to sqlplus  as sys user  and execute 

sql>execute dbms_stats.gather_fixed_objects_stats;

select name from v$restore_point;

drop restore point before_upgrade_19c;

alter sytem set compatible='19.0.0' scope=spfile;

shutdown immediate;

startup;

show parameter compatible;

 

select name,open_mode, status from v$database, v$instance;

set line 1234 pages 1234

col comments for a30

col action for a15

select * from dba_registry_history;