Why and How to migrate and upgrade from 12c to 19c using RMAN incremental backup?

 Why ?

Migrate is move your database from machine to different machine and upgrade is moving your data from old to new version , it is may you need to move and upgrade in same time , below I am listing simple steps to achieve this goal by using RMAN incremental backup to minimize the downtime needed in case the RMAN backup is the only suitable solution in your case.

How ?

Below are steps to be follow in order to achive the goal , I am assuming the below:

Source DB 12c : orcl , running in archive log mode , OS is Linux

Destination DB 19c : orcl , software only is installed , OS is Linux

1- check the archive log mode in source DB, if it is disable then backup will be only offline backup ( cold backup) :

Sql> archive log list ;

2- Take backup level 0 which is equivalent to full backup for both database and archive logs:

rman> backup incremental level 0 database format '/u01/backup/db_%U' plus archivelog format '/u01/backup/arch_%U';

3- On target database start create a password file as below:

ORAPWD file=PWD<ORACLE_SID>.ora password=<password>

4- take a pfile from spfile of source and edited as needed and startup the target DB using this pfile:

Sql> create pfile=’location/orclinit.ora’ from spfile;

 

-- copy the file to the target and edit it with the needed, make sure the create any missed directory specially the adum path.

 

-- startup the target DB to nomount:

Sql> startup nmount pfile=location\orclinit.ora;

 

5- On source database take backup of control file:

rman> backup current controlfile format '/u01/backup/cf_%U';

6- On target database restore backup of control file and start database on mount stage:

rman> restore controlfile from '/u01/backup/<filename> ;

rman> alter database mount ;

 

7- On target database correct any path need to be correct if it is differ from source , you may check this using the below:

sql> report schema ;

sql> select * from v$logfile ;

sql> alter database rename file '<sourcepath>/redo01.log' to '<distpath>/redo01.log';

sql> alter database rename file '<sourcepath>/redo02.log' to '<distpath>/redo02.log';

sql> alter database rename file '<sourcepath>/redo03.log' to '<distpath>/redo03.log';

8- On target database restore backup incremental backup level 0:

rman>

run

{

allocate channel c1 device type disk;

set newname for database to '/u02/oradata/%U';

set newname for tempfile '/u01/oradata/orcl/temp01.dbf' to '/u02/oradata/temp01.dbf' ;

restore database ;

switch datafile all;

switch tempfile all;

release channel c1;

}

 

8- On source database take backup incremental level 1:

rman> backup incremental level 1 database format '/u01/backup/db_%U' plus archivelog format '/u01/backup/arch_%U';

8- On target database recover backup incremental level 1 and level 0 after catalog the new level1 in the control file, check the last sequence and add 1 then:

rman> catalog start with '/u01/backup/';

rman> list backup ;

run

{

allocate channel c1 device type disk;

set until sequence (maxnumber+1);

recover database ;

release channel c1;

}

9- Now you can start database in upgrade stage and reset the logs , then perform the upgrade(you may use autoupgrade.jar) :

rman> alter databas open resetlogs upgrade ;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

Why and How to use PowerShell script in CMD Windows?

Why ?

If you intended to use a PowerShell functions and code to retrieve certain output or do some work using PowerShell scripting instead of CMD and accordingly you will complete your work in CMD batch scripting , in this case I am sharing a simple code to achieve this.

How ?

Below is a sample code script running as .bat , CMD batch scripting that will call PowerShell script to retrieve yesterday date and store it in CMD batch variable and continue work with it in CMD , retrieving yesterday date is very difficult in CMD however it is so easy on PowerShell thus I use it as below:

1- Code below, please save it in .bat and run it using CMD:

for /f "delims=" %%a in ('powershell get-date((get-date^).addDays(-1^)^) -uformat "%%Y%%m%%d"') do set date=%%a

for /f "delims=" %%a in ('powershell get-date((get-date^).addDays(-1^)^) -uformat "%%Y%%b%%d"') do set date2=%%a

  

echo %date%

echo %date2%

 

::echo %datetime%

set year=%date:~0,4%

set mm=%date:~4,2%

set day=%date:~6,2%

set mmm=%date2:~4,3%

 

echo checking files for date %year%/%mm%/%day% %time%

echo checking files for date %year%/%mmm%/%day% %time%

pause  

Why and How to delete and compact files Windows?

Why ?

To use a CMD code (.bat) that delete files older than x days and/or use a compact windows feature ( compact) use the below code as a sample to achieve this goal.

How ?

Below is a Windows CMD code that will cleanup/delete files and compact other files based on age, in my below code example I will delete files older than 180 days and compact(compress) files older than 2 days in specific path with certain file pattern naming:

1- Code below:

:: Delete All Archives older than 6 months (1 weeks for client logs)

 

echo %DATE% %TIME%

 

forfiles /p "E:\Avanza\Logs\Svc" /s /m PayHub_*.log /c "cmd /c Del @path" /d -180

forfiles /p "E:\Avanza\Logs\Svc" /s /m PayHub_*.log /c "cmd /c if @isdir==FALSE Compact /c @path" /d -2

Why and How to use winrar to zip and delete (Archive) files Windows?

 

Why ?

If you got a case where you have to compress and cleanup files on windows using winrar to get the most compression , also you may delete the compressed file , this can be consider of archiving of logs , the files in my case containing the dates in the file name and I will use this as a condition while doing this archiving.

How ?

Below is a Windows CMD code that will get the system date and archive files before last week ,:

1- Code below:

::@ECHO OFF

 

:: Keep variables local

SETLOCAL

  

set yyyy=

 

set $tok=1-3

for /f "tokens=1 delims=.:/-, " %%u in ('date /t') do set $d1=%%u

if "%$d1:~0,1%" GTR "9" set $tok=2-4

for /f "tokens=%$tok% delims=.:/-, " %%u in ('date /t') do (

for /f "skip=1 tokens=2-4 delims=/-,()." %%x in ('echo.^|date') do (

set %%x=%%u

set %%y=%%v

set %%z=%%w

set $d1=

set $tok=))

 

if "%yyyy%"=="" set yyyy=%yy%

if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% - 100

 

set CurDate=%mm%/%dd%/%yyyy%

set dayCnt=%7

 

if "%dayCnt%"=="" set dayCnt=7

 

REM Substract your days here

set /A dd=1%dd% - 100 - %dayCnt%

set /A mm=1%mm% - 100

 

:CHKDAY

if /I %dd% GTR 0 goto DONE

set /A mm=%mm% - 1

if /I %mm% GTR 0 goto ADJUSTDAY

set /A mm=12

set /A yyyy=%yyyy% - 1

 

:ADJUSTDAY

if %mm%==1 goto SET31

if %mm%==2 goto LEAPCHK

if %mm%==3 goto SET31

if %mm%==4 goto SET30

if %mm%==5 goto SET31

if %mm%==6 goto SET30

if %mm%==7 goto SET31

if %mm%==8 goto SET31

if %mm%==9 goto SET30

if %mm%==10 goto SET31

if %mm%==11 goto SET30

REM ** Month 12 falls through

 

:SET31

set /A dd=31 + %dd%

goto CHKDAY

 

:SET30

set /A dd=30 + %dd%

goto CHKDAY

 

:LEAPCHK

set /A tt=%yyyy% %% 4

if not %tt%==0 goto SET28

set /A tt=%yyyy% %% 100

if not %tt%==0 goto SET29

set /A tt=%yyyy% %% 400

if %tt%==0 goto SET29

 

:SET28

set /A dd=28 + %dd%

goto CHKDAY

 

:SET29

set /A dd=29 + %dd%

goto CHKDAY

 

:DONE

if /I %mm% LSS 10 set mm=0%mm%

if /I %dd% LSS 10 set dd=0%dd%

 

REM Set yesterday variable variables

::set IISDT=%yyyy:~2,2%%mm%%dd%

::set AWSDT=%yyyy%-%mm%-%dd%

 

set YYYYMMDD=%yyyy%%mm%%dd%

set MMDD=%mm%%dd%

echo %MMDD%

echo %YYYYMMDD%

echo %DATE% %TIME%

 

 

C:

CD "C:\Program Files\WinRAR"

 

taskkill /F /IM WinRAR.exe /T

 

WinRAR.exe a -r -m5 -dh -df -ep E:{path_to_file}\rarfile1_%YYYYMMDD% "E:{path_to_files}\*%YYYYMMDD%*.log"

taskkill /F /IM WinRAR.exe /T

echo %DATE% %TIME%

 

echo completed

echo %DATE% %TIME%