Restore RMAN to new server and refresh with Archivelog apply

Source Database
—————

::: Directory Structure :::
ORACLE_HOME : E:\oracle\product\10.2.0\db_1
RMAN Backup : E:\rman_backup_full
Control Files : E:\oradata\ecc
DataFiles: E:\oradata\ECC
Archivelog Destination: E:\archivelogs
adump : e:\oracle\product\10.2.0\admin\ecc\adump
bdump : e:\oracle\product\10.2.0\admin\ecc\bdump
cdump : e:\oracle\product\10.2.0\admin\ecc\cdump
udump : e:\oracle\product\10.2.0\admin\ecc\udump

1. create pfile=’E:\eccprod.ora’ from spfile;

 

2. rman target /

backup incremental level 0 TAG ‘ECC_DAILY_FULL’ database filesperset 4;
backup archivelog all not backed up 1 times;
exit;

 

3. Copy RMAN backup files and pfile to Target Database directory in same structure

 

Target Database
—————

::: Directory Structure :::
ORACLE_HOME : E:\oracle\product\10.2.0\db_1
Control Files : E:\oradata\ecc
RMAN Backup : E:\rman_backup_full
DataFiles: E:\oradata\ECC
Archivelog Destination: E:\archivelogs
adump : e:\oracle\product\10.2.0\admin\ecc\adump
bdump : e:\oracle\product\10.2.0\admin\ecc\bdump
cdump : e:\oracle\product\10.2.0\admin\ecc\cdump
udump : e:\oracle\product\10.2.0\admin\ecc\udump

Make sure RMAN directory structure and Datafiles and Control Files Directory structure is identical as Source DB server else you have to use DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters

1. Create required folders in the Target database server

adump
bdump
cdump
udump

Control File Location
Datafile Location
Archivelog Location
RMAN backup location

 

——
|PFILE |
——

ecc.__db_cache_size=1342177280
ecc.__java_pool_size=8388608
ecc.__large_pool_size=8388608
ecc.__shared_pool_size=226492416
ecc.__streams_pool_size=16777216
*.audit_file_dest=’e:\oracle\product\10.2.0\admin\ecc\adump’
*.background_dump_dest=’e:\oracle\product\10.2.0\admin\ecc\bdump’
*.compatible=’10.2.0.5.0′
*.control_files=’e:\oradata\ecc\control01.ctl’,’e:\oradata\ecc\control02.ctl’,’e:\oradata\ecc\control03.ctl’
*.core_dump_dest=’e:\oracle\product\10.2.0\admin\ecc\cdump’
*.db_16k_cache_size=67108864
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT=’eccstandby’,’ecc’
*.db_name=’ecc’
*.DB_UNIQUE_NAME=’ecc’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=eccXDB)’
*.FAL_CLIENT=’ecc’
*.FAL_SERVER=’eccstandby’
*.global_names=TRUE
*.INSTANCE_NAME=’ecc’
*.job_queue_processes=20
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ecc,eccstandby)’
*.LOG_ARCHIVE_DEST_1=’LOCATION=E:\archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ecc’
*.log_archive_dest_2=’SERVICE=eccstandby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eccstandby’
*.log_archive_dest_state_1=’enable’
*.log_archive_dest_state_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.LOG_FILE_NAME_CONVERT=’ecc’,’eccstandby’
*.open_cursors=700
*.OPTIMIZER_INDEX_COST_ADJ=30
*.pga_aggregate_target=364904448
*.processes=250
*.REMOTE_LOGIN_PASSWORDFILE=’EXCLUSIVE’
*.SERVICE_NAMES=’ecc’
*.session_cached_cursors=100
*.sessions=280
*.sga_max_size=1677721600
*.sga_target=1677721600
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’e:\oracle\product\10.2.0\admin\ecc\udump’

 

 

2. Create a service using oradim (if on windows) or with the pfile startup database in nomount mode

 

oradim -NEW -SID ecc -SYSPWD sys123 -STARTMODE auto -PFILE e:\eccprod.ora

 

3. Restore Database

rman target /

restore database ;

 

4. Now copy the new archivelogs to the archivelog directory and do recovery

 

rman target /

RMAN> catalog start with ‘E:\archivelogs’;

searching for all files that match the pattern E:\archivelogs

List of Files Unknown to the Database
=====================================
File Name: E:\archivelogs\LOG1_53601_781008485.ARC
File Name: E:\archivelogs\LOG1_53602_781008485.ARC
File Name: E:\archivelogs\LOG1_53603_781008485.ARC
File Name: E:\archivelogs\LOG1_53604_781008485.ARC
File Name: E:\archivelogs\LOG1_53605_781008485.ARC
File Name: E:\archivelogs\LOG1_53606_781008485.ARC
File Name: E:\archivelogs\LOG1_53607_781008485.ARC
File Name: E:\archivelogs\LOG1_53608_781008485.ARC
File Name: E:\archivelogs\LOG1_53609_781008485.ARC
File Name: E:\archivelogs\LOG1_53610_781008485.ARC
File Name: E:\archivelogs\LOG1_53611_781008485.ARC
File Name: E:\archivelogs\LOG1_53612_781008485.ARC
File Name: E:\archivelogs\LOG1_53613_781008485.ARC
File Name: E:\archivelogs\LOG1_53614_781008485.ARC
File Name: E:\archivelogs\LOG1_53615_781008485.ARC
File Name: E:\archivelogs\LOG1_53616_781008485.ARC
File Name: E:\archivelogs\LOG1_53617_781008485.ARC
File Name: E:\archivelogs\LOG1_53618_781008485.ARC
File Name: E:\archivelogs\LOG1_53619_781008485.ARC
File Name: E:\archivelogs\LOG1_53620_781008485.ARC
File Name: E:\archivelogs\LOG1_53621_781008485.ARC
File Name: E:\archivelogs\LOG1_53622_781008485.ARC
File Name: E:\archivelogs\LOG1_53623_781008485.ARC
File Name: E:\archivelogs\LOG1_53624_781008485.ARC
File Name: E:\archivelogs\LOG1_53625_781008485.ARC
File Name: E:\archivelogs\LOG1_53626_781008485.ARC
File Name: E:\archivelogs\LOG1_53627_781008485.ARC
File Name: E:\archivelogs\LOG1_53628_781008485.ARC
File Name: E:\archivelogs\LOG1_53629_781008485.ARC
File Name: E:\archivelogs\LOG1_53630_781008485.ARC
File Name: E:\archivelogs\LOG1_53631_781008485.ARC
File Name: E:\archivelogs\LOG1_53632_781008485.ARC
File Name: E:\archivelogs\LOG1_53633_781008485.ARC
File Name: E:\archivelogs\LOG1_53634_781008485.ARC
File Name: E:\archivelogs\LOG1_53635_781008485.ARC
File Name: E:\archivelogs\LOG1_53636_781008485.ARC
File Name: E:\archivelogs\LOG1_53637_781008485.ARC

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

List of Cataloged Files
=======================
File Name: E:\archivelogs\LOG1_53601_781008485.ARC
File Name: E:\archivelogs\LOG1_53602_781008485.ARC
File Name: E:\archivelogs\LOG1_53603_781008485.ARC
File Name: E:\archivelogs\LOG1_53604_781008485.ARC
File Name: E:\archivelogs\LOG1_53605_781008485.ARC
File Name: E:\archivelogs\LOG1_53606_781008485.ARC
File Name: E:\archivelogs\LOG1_53607_781008485.ARC
File Name: E:\archivelogs\LOG1_53608_781008485.ARC
File Name: E:\archivelogs\LOG1_53609_781008485.ARC
File Name: E:\archivelogs\LOG1_53610_781008485.ARC
File Name: E:\archivelogs\LOG1_53611_781008485.ARC
File Name: E:\archivelogs\LOG1_53612_781008485.ARC
File Name: E:\archivelogs\LOG1_53613_781008485.ARC
File Name: E:\archivelogs\LOG1_53614_781008485.ARC
File Name: E:\archivelogs\LOG1_53615_781008485.ARC
File Name: E:\archivelogs\LOG1_53616_781008485.ARC
File Name: E:\archivelogs\LOG1_53617_781008485.ARC
File Name: E:\archivelogs\LOG1_53618_781008485.ARC
File Name: E:\archivelogs\LOG1_53619_781008485.ARC
File Name: E:\archivelogs\LOG1_53620_781008485.ARC
File Name: E:\archivelogs\LOG1_53621_781008485.ARC
File Name: E:\archivelogs\LOG1_53622_781008485.ARC
File Name: E:\archivelogs\LOG1_53623_781008485.ARC
File Name: E:\archivelogs\LOG1_53624_781008485.ARC
File Name: E:\archivelogs\LOG1_53625_781008485.ARC
File Name: E:\archivelogs\LOG1_53626_781008485.ARC
File Name: E:\archivelogs\LOG1_53627_781008485.ARC
File Name: E:\archivelogs\LOG1_53628_781008485.ARC
File Name: E:\archivelogs\LOG1_53629_781008485.ARC
File Name: E:\archivelogs\LOG1_53630_781008485.ARC
File Name: E:\archivelogs\LOG1_53631_781008485.ARC
File Name: E:\archivelogs\LOG1_53632_781008485.ARC
File Name: E:\archivelogs\LOG1_53633_781008485.ARC
File Name: E:\archivelogs\LOG1_53634_781008485.ARC
File Name: E:\archivelogs\LOG1_53635_781008485.ARC
File Name: E:\archivelogs\LOG1_53636_781008485.ARC
File Name: E:\archivelogs\LOG1_53637_781008485.ARC

RMAN> recover database;

Starting recover at 18-FEB-18
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 53636 is already on disk as file E:\ARCHIVELOGS\LO
G1_53636_781008485.ARC
archive log thread 1 sequence 53637 is already on disk as file E:\ARCHIVELOGS\LO
G1_53637_781008485.ARC
archive log filename=E:\ARCHIVELOGS\LOG1_53636_781008485.ARC thread=1 sequence=5
3636
archive log filename=E:\ARCHIVELOGS\LOG1_53637_781008485.ARC thread=1 sequence=5
3637
unable to find archive log
archive log thread=1 sequence=53638
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2018 16:04:01
RMAN-06054: media recovery requesting unknown log: thread 1 seq 53638 lowscn 857
961236

RMAN>

 

Database is now in incomplete recovery, after new archivelogs are generated do recovery again. You can keep doing this till you are ready to open database with resetlogs and startup in mount mode

 

rman target /

RMAN> catalog start with ‘E:\archivelogs’;

 

RMAN> recover database;

Starting recover at 18-FEB-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=269 devtype=DISK

starting media recovery

archive log thread 1 sequence 53638 is already on disk as file E:\ARCHIVELOGS\LO
G1_53638_781008485.ARC
archive log thread 1 sequence 53639 is already on disk as file E:\ARCHIVELOGS\LO
G1_53639_781008485.ARC
archive log filename=E:\ARCHIVELOGS\LOG1_53638_781008485.ARC thread=1 sequence=5
3638
archive log filename=E:\ARCHIVELOGS\LOG1_53639_781008485.ARC thread=1 sequence=5
3639
unable to find archive log
archive log thread=1 sequence=53640
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2018 17:03:52
RMAN-06054: media recovery requesting unknown log: thread 1 seq 53640 lowscn 857
981603

RMAN> alter database open resetlogs;

database opened

RMAN> list incarnation;

ist of Database Incarnations
B Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
—— ——- ——– —————- — ———- ———-
1 ECC 1040999203 PARENT 1 07-JUL-10
2 ECC 1040999203 PARENT 583052 19-APR-12
3 ECC 1040999203 CURRENT 857981604 18-FEB-18

 

 

 

 

Advertisements

Advanced Queuing Dequeue Freeze

jarneil

The Issue

We have been running an Advanced Queuing solution for a while now, and with a frustrating regularity we were seeing messages building up in our queue table and the dequeue process seeming to think that there was no work for it to do. These were messages that had a time delay set before being ready for dequeuing. Messages queued without a delay were processed at all times.

This was a RAC system running 10.2.0.4

Perhaps the most frustrating thing about this problem was that Oracle Support effectively refused to look into the problem because we had the parameter aq_tm_processes set. Aparently the recomendation in 10g is to have this parameter unset. Of course if this parameter is currently set you can’t actually unset it without a bounce of the database, which is not something you can automatically perform.

The Workaround

While awaiting the chance to bounce the database…

View original post 569 more words

Execute Windows Commands from Linux using winexe. Connect from Linux to Windows without SSH

Winexe – is a software on Linux which uses RPC to run remote commands and might even come with your distribution’s packages. It depends on Samba for running rpc commands

Edit: even if your distro does not have it precompiled and you do not want to compile yourself, you should check out the Build Service – it is likely to have the binary version you would need for your system.

First Install samba-common on your Linux machine before beginning winexe

Use below article to check WinEXE installation

https://www.kickass.se/?p=189

Winexe is a GNU/Linux based application that allows users to execute commands remotely on WindowsNT/2000/XP/2003/Vista/7/8 systems. It installs a service on the remote system, executes the command and uninstalls the service. Winexe allows execution of most of the windows shell commands.

How to install:
You can download the source package from here [Current version is winexe-1.00.tar.gz]

  1. tar -xvf winexe-1.00.tar.gz
  2. cd winexe-1.00/source4/
  3. ./autogen.sh
  4. ./configure
  5. make basics bin/winexe
  6. make “CPP=gcc -E -ffreestanding” basics bin/winexe (For X64 bit)

this will create a [ winexe ] binary file in the bin folder. You can use that binary to execute the windows commands from Linux.

or else there are some compiled version of binary itself available for download. You can download and use it from here.

The above version of winexe works only till version Windows Server 2012. For Windows Server 2012R2 requires below method to make winexe work with latest version of windows. Below method will get the code for samba and winexe from the github repository and we will have to build it for it to work.

Alternate Method (Best Method to Work with Latest Version of Windows 10 and Windows Server 2012R2)

http://rand0m.org/2015/08/05/winexe-1-1-centos-6-and-windows-2012-r2/

  1. Install Samba on Linux

yum install samba-common

2.      fixup a bunch of samba dependencies

yum install gcc perl mingw-binutils-generic mingw-filesystem-base mingw32-binutils mingw32-cpp mingw32-crt mingw32-filesystem mingw32-gcc mingw32-headers mingw64-binutils mingw64-cpp mingw64-crt mingw64-filesystem mingw64-gcc mingw64-headers libcom_err-devel popt-devel zlib-devel zlib-static glibc-devel glibc-static python-devel

yum install git gnutls-devel libacl1-dev libacl-devel libldap2-dev openldap-devel

  1. As per the docs, remove libbsd-devel if installed

yum remove libbsd-devel

  1. Clone the git repos. samba is huge, like 280MB

cd /usr/src

git clone git://git.code.sf.net/p/winexe/winexe-waf winexe-winexe-wafgit clone git://git.samba.org/samba.git samba

  1. per winexe bug 64, samba needs to be reverted to a6bda1f2bc85779feb9680bc74821da5ccd401c5

cd /usr/src/samba

git reset –hard a6bda1f2bc85779feb9680bc74821da5ccd401c5

  1. Fixup the build deps

cd /usr/src/winexe-winexe-waf/source

vi wscript_build

# modify ‘wscript_build’, and at the very end …stlib=’smb_static bsd z resolv rt’lib=’dl gnutls’

7.     Build it! his does a huge configure, then also compiles samba, which takes a while. 

./waf –samba-dir=../../samba configure build

8.     Executable should be  /usr/src/winexe-winexe-waf/source/build/winexe-static

cd /usr/src/winexe-winexe-waf/source/build/

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 “ipconfig -all”

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 “ipconfig -all” -d99

Sample on How to use it to Check Service on Linux :

export CHECK_COMMAND=’sc query “Notification Service”‘

export STOP_COMMAND=’sc stop “Notification Service”‘

export START_COMMAND=’sc start “Notification Service”‘

## Check Service Status ##

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$CHECK_COMMAND”

## Check with Debug ##

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$CHECK_COMMAND” -d99

## Run powershell script or command on windows then like below ##

winexe -U administrator%$PWD //$IP “powershell -Command & {(c:\User\administrator\powershell.ps1)}”

 

## Stop and Start Service ##

export CHECK_COMMAND=’sc query “Browser”‘

export STOP_COMMAND=’sc stop “Browser “‘

export START_COMMAND=’sc start “Browser “‘

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$CHECK_COMMAND”

cat </dev/null | ./winexe-static –U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$STOP_COMMAND”

cat </dev/null | ./winexe-static -U ‘domain\admin%$PWD’ //iihoserver01 –interactive=0 “$START_COMMAND”

 

 

Compare 2 Servers RPM and Install RPM from Text File List

1. Generate RPM list on Old Server

rpm -qa –queryformat=’%{NAME}\n’ | sort > serverold.txt

2. Generate RPM list on New Server

rpm -qa –queryformat=’%{NAME}\n’ | sort > servernew.txt

3. Copy File to New Server and use diff comand to get differences

diff serverold.txt servernew.txt > diff.txt

4. Get list of packages missing in new Server

cat diff.txt | grep “<” > difference.txt

vi difference.txt

%s,< ,

## save and exit

5. Install missing packages on new server

yum -y install $(cat difference.txt)

Conditional Column Formatting in APEX

Tyler Muth's Blog

I wanted to share a little trick I’ve used in APEX for a while now to conditionally format report columns based on their values. I’m sure there are plenty of alternatives to this trick, including the 4 built-in conditional alternatives for a row when using a named column template.

At a high level, this technique uses the following components:

  1. A hidden column in the query that returns the formatting attributes for a column. I’m going to return the color or padding-left in my examples later in this post.
  2. Edit the Report Attributes > Column Attributes > Column Formatting > HTML Expression of a visible column and use the hidden column to change it’s formatting. This is the same section you apply a date or number format.

Example 1 – Color Code Salary

Query


Report Attributes > Column Attributes for “SAL” > Column Formatting > HTML Expression

Result
salary_color

Example 2 –…

View original post 17 more words

Sending Mail with Multiple Attachment In PL/Sql

Aykut Akın's Blog

Oracle provides you two different packages for sending mail. Utl_Smtp package firstly introduced in Oracle 8i and Utl_Mail package firstly introduced in Oracle 10g. In my post, I will give example with Utl_Smtp package.

First of all you need to understand ‘MIME Type’ concept for my example. I will explain it briefly, but you can find more detail in here. We will use MIME standart for creating multiple part e-mail.

We will use common header MIME informations like To, Date, From etc. to standartize our email content. Boundry feature will be used for creating multi attachment mail. Every attachment will be divided by a boundry and every attachment will have its own content type and name. With content type parameter, you can specify text/plain, text/html etc. types. If your content type is set to text/html, you can use html tags like table, tr, br etc. and with this way…

View original post 547 more words

/opt/freeware/lib/libintl.a(libintl.so.8) Error while installing RPM on AIX 7.1

While installing some RPM’s on AIX we got below dependency error.  Now without resolving this error you cannot even proceed further.

[octst3:root:/aixbackup:]rpm -ivh rsync-3.1.2-2.aix6.1.ppc.rpm
exec(): 0509-036 Cannot load program /usr/opt/freeware/bin/rpm because of the following errors:
0509-150 Dependent module /opt/freeware/lib/libintl.a(libintl.so.8) could not be loaded.
0509-152 Member libintl.so.8 is not found in archive

After scourging through the internet and spending half the day, our system admin almost gave up on it. And in come’s the DBA, as they say smart work is better than hard work. Instead of re-inventing the wheel i asked him for a server where the RPM installation works. I logged into the server, copied the file to the same path in the server where it isn’t installing and it worked like a charm. Now i cannot guarantee this method will work 100% with all enviroments but just a refresh of the library from another source worked for us.

[ocptst3:root:/aixbackup:]rpm -vh rsync-3.1.2-2.aix6.1.ppc.rpm
rpm: –hash (-h) may only be specified during package installation and erasure
[ocprd3:root:/aixbackup:]rpm -ivh rsync-3.1.2-2.aix6.1.ppc.rpm
Preparing… ################################# [100%]
Updating / installing…
1:rsync-3.1.2-2 ################################# [100%]

I’m attaching the library for the purpose of any one who wants to do this on AIX 7.1 on Power7 CPU architecture.

https://drive.google.com/open?id=1rL5YbinDHgBOlVNybSHGJEZnh19aLwtm