Install Oracle forms & reports 12.2.1.4 with Weblogic 12c 12.2.1.4

Hi guys

This time we are going to see how we could install and configure Oracle forms & reports 12.2.1.4, the latest version using WebLogic Server 12c 12.2.1.4

Side Note: Kindly refer this document from Oracle https://docs.oracle.com/en/middleware/fusion-middleware/12.2.1.3/sysrs/system-requirements-and-specifications.pdf

The Visual C++ requirements mentioned for 12.2.1.3 should suffice 12.2.1.4 also, without which you will NOT able to startup OHS instance.

As a rule of thumb, regardless whether you are going to install and configure Oracle software on LINUX or Windows operating systems, always update the OS to latest patch levels, prior you start with the Oracle software installation(s). I strongly recommend using Oracle VirtualBox based Virtual Machines for the lab & later you may move the tried out solution to your preferred environments (TEST, Development). I don’t recommend any of the suggestions I make through my blog posts to be applied to PRODUCTION environments without consulting a Professional support entity.

Download Software from Oracle

Fusion Middleware Infrastructure Installer (Weblogic)

https://www.oracle.com/middleware/technologies/weblogic-server-installers-downloads.html

Oracle Forms & Reports 12.2.1.4

https://www.oracle.com/middleware/technologies/forms/downloads.html

JAVA SE Development Kit 1.8

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Install JDK

I prefer installing JDK on C: drive & Usually my JDK is installed on C:\Java\JDK folder. The shortest name, the best to handle it later. Once the 64Bit JDK is installed, you need to setup either JAVA_HOME environment variable or add a new entry in to the system path. I prefer a system path modification than user path modification.

Okay, there are hell loads of Oracle software installed on my system. Don’t worry, I manage them. Just make sure the JDK installation path is on top of the list and you are good to go

Install Weblogic Server 12.2.1.4

It may take a while for the jar file to extract and start the installer. Please wait patiently. I was setting up the entire infrastructure using Windows 10 VM & due to the limited resources, hence the entire ordeal was kind of time taking this time for me.

You can safely skip the Auto Update

Naming Oracle installation folders is an art. The complex the folder structures, the complex later configurations would be. I usually prefer to install all my Oracle software on D: drive. Please feel comfortable to format the Oracle home as per your requirements. Maintaining the same format helps me to locate and sometimes replace files from previous installations.

Select “Fusion Middleware Infrastructure”

WebLogic or Middleware installation is NOT complex anymore on Windows 10 OS. There are no special hacks needs to complete the installation.

Install forms & reports 12.2.1.4

Extract the downloaded zip file & you MUST start the installer as “Administrator”.

We’ve to install the Forms and Reports stack on an existing Oracle Home. So please select the Oracle Home where you have installed “WebLogic” Server. Alternatively you can always install the stack on a different Oracle Home, which I never tried earlier.

Select “Forms and Reports Deployment”, as the last two versions of Forms & Reports allow you to install a Standalone Forms Builder.

Select the correct JDK Home

Please make sure that you have Visual C++ libraries pre-installed prior proceeding. If you don’t have the VC++ libraries installed, the OHS instance will not start & you might experience additional problems during installation also.

That completes the Forms & Reports 12.2.1.4 installation.

Create a REPO for the Classic Domain

To create a classic domain, you must have a repository available with the database. Weblogic 12c supports Oracle 11G 11.2.0.4 or later database for the repositories & my pick will be always 12c Database as the 11.2.0.4 is only accessible for customers with valid Oracle support. You may refer this document for a detailed “how to” towards installing Oracle 12c database.

From an elevated command prompt, switch to Oracle Home\oracle_common\bin folder & execute “rcu.bat”

Check the below image for details

Choose the defaults

Provide the connection parameters. If the database is on another server, you don’t have to install additional clients, connection parameters will take care of the connection. Refer the below image for details (Adjust according to your database setup)

My 12c Database already has a repository for Forms & Reports 12c 12.2.1.3, hence I created a new prefix “DEV1”. If you are creating a repo for the first time, you can use “DEV” or any other 3 letter combination (make sure you will able to remember it laters).

Select all the components as seen with the below image. The selected are mandatory, especially the Metadata Services.

Use the same password everywhere! Unless you are setting up a production instance, use the same password everywhere. Example, I use “Abc1234” for the schemas, Weblogic Admin account, node manager etc, which makes the administration much easier. For production, follow the Oracle standards.

That completes the repository creation for a Classic domain.

Create a Classic Domain for deploying forms & reports application

From an elevated command prompt, switch to Oracle Home\oracle_common\common\bin (eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin) & execute config.cmd, that will kick start the classic domain configuration wizard.

Select the defaults, unless you have specifics. We will be creating a new domain with the following example.

Refer the below images and select the same components for a successful domain creation for forms and reports.

As I mentioned earlier, use the same password here, so that you don’t have to worry about forgetting & recovering it at later stages.

We will setup the domain for production (never tried Development, so don’t know how exactly both differ from each other). Leave the JDK selection as it is.

Now, your earlier created repository will come in act. Refer the below image(s) and adjust as per your environment

Make sure all the Schema validations are successful. Next step select components as per the below image

Make sure the Server Group is filled the value as seen with the below image. Don’t miss it!

We’ll add an instance OHS now. Click on the add button and refer the below images for more details

Fill in the Listen Address with “localhost” and leave rest to defaults

That finishes the creation of your classic domain.

You should able to access the newly created WebLogic admin server through the URL http://localhost:7001/console

I’m hurrying to publish the POST without further delays, as I am once again caught up with multiple assignments & keeping up with promises are NOT something I am look up to at this time. However, I will add up the missing configurations for OHS and setting up the Forms and reports very soon an an annexure as early as possible!

regards,

rajesh

Oracle Reports Developer 10g (10.1.2.3)| Layout designer hangs

Hello guys

Update: 25th April 2019

It looks like, setting up the compatibility for Windows 7 solves the Reports Builder shaking, freezing behavior to a greater extend. Going on with the “Fix” as NOTHING else could be done with a product that is NOT anymore supported by Oracle.

====

I “trust” Microsoft and I do update my Work/Home/VMs regularly. Ahum! 99.9% of the times I never had to go back and fix anything, that .1% times, things went awfully bad.

I cannot confirm which update, certainly one update within last 2 months have made Oracle Reports Developer 10g/6i loads messier than earlier. Now both version of reports developer will NOT load the layout designer properly.

The interesting factor about the layout designer is:

It hangs more often when you have multiple reports opened from a single instance of the reports developer & you are switching between the layouts.

For example, if you opened Report 1’s layout & closed it, then opening the Report 2’s layout arrests the designer & all the toolbar icons and rest go astray (as you could see with above image) which will force you to end task the developer using Task Manager/or other known measures.

It is irky, shaky, yet you can do your work with multiple sessions of reports developer opened with only one report loaded. There are no guarantees that this IS the ultimate solution. I often have to terminate the report builder and restart it to do my development(s) :(

I’ve more news for you. I did a reinstall routine of Patch 3 for 10g developer & remembering Windows 7 was the last OS certified with the developer suite, changed the compatibility for the Report Developer and set it as Windows 7. I am not sure whether it acts as a mediator, my report builder is much stable as I am jolting these few lines. Keeping fingers crossed.

regards,

Install Oracle 18c on Windows 10

Hello guys

I am kind of disappointed, to be frank. After long planning, I decided to post about installing Oracle 18c on Windows 10 & started hearing about 19c. Well, shit happens and we will continue with what we were planning to do.

Before getting in to the real business, let me remind you. Oracle stack depends upon hell loads of VC++ components, and missing those packages could make things pretty difficult on Windows. So, as a thumb rule, please make sure that you have all available VC++ packages installed with your Windows box. Never forget that .Net 3.5 is also required by multiple Oracle software on Windows. Finally, do update your Windows.

You may refer this image for the VC++ & .Net components those are installed in my Windows 10 computer.

You can download Oracle 18c installation media for Windows 64Bit from the following link

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-windows-180000-5066774.html

Please spend a moment to read what Oracle says just below the media link, “Do not install Oracle Database from a directory that contains a space character in it”

That means, while extracting the downloaded ZIP file, make sure you are extracting the archive to a folder like “D:\Oracle18c” NOT “D:\Oracle 18c” or “D:\Oracle Database 18c”

Oracle never liked “space” character in the directory name & if you are going to ignore this, you are going to have true troubles waiting for you mate.

What I have came to conclusion about Oracle 18c installation is:

Oracle is NOT anymore copying all the source files to a new destination. For example, usually when you install a software on Windows, there is installation source, from which necessary files are copied to a new destination (installed folder). This time Oracle is approaching the installation in a different fashion.

It ONLY configures the database.

That means, Once you extract the media, say to a folder called “D:\Oracle18c”, this is the same folder where the database will be installed! You will only be asked for Oracle base location, where the diagnostics and others will be configured.

The above is MY PERSONAL observation & I am yet to refer the installation documents from Oracle for clarify against what I found. Feel free to prove me wrong.

With the below installation example, I have extracted the 18c source media to a folder called “D:\Oracle_Installers\18c”, which will be the installation folder also

Run “setup.exe” as “Administrator”, that brings up a console sort of Windows that lists the installation progress (minimalistic)

We’ll create a database along with the installation, which is quick and effective (for this TEST)

Although we are installing the software on Windows 10, We will select “Server class” for System Class to “experience” the load stress.

Usually I choose “Advanced Install”, so that I can minutely access the different options available for the database like character set, memory etc

Database Edition should be “Enterprise” to experience the same “Cloud” version capabilities.

I’ve one Windows user account “Oracle” that I use for Oracle installations and setup. I suggest you also to create a non-administrator windows user “oracle” or something equivalent specifically for Oracle software.

As I have mentioned in the beginning, “The Software directory is the Oracle Database home directory”, ie, the folder from which you start the setup.exe will be the Database home directory. The path that specified by you will be the Oracle base & usually the diagnostics elements will be placed within.

As a rule, I always select the same password for all vital accounts & the pattern is something like ‘Abcxyz123’. For a TEST lab, this saves me unwanted complexities & less documentation. For a production instance, please follow Oracle’s recommendations.

My laptop boasts one i7 8th generation processor, 16GB DDR-4 memory and 512GB Samsung 860PRO SSD. Yet, the installation takes it’s own time. So if you have less hardware configurations please wait patiently.

You know what to do with JAVA security prompts!

Once the installation over, you will be provided the link to Oracle Enteprise Manager Database Express, which is a minimalistic EM for the database. You can log in and view the database details and interact the database parameters through this interface.

I know you are smart enough to find out that I altered the below image a little bit…Well it looks something like this…

My laptop doesn’t have FLASH installed, hence I am using Internet Explorer for accessing the EM. I have to check whether I can access the EM using Firefox after installing Flash.

That’s all folks. The installation is pretty straight forward other than for the installation folder/directory design part. For a production environment, you may need to truly plan how the folder/directory name is going to be.

Ah, finally! My laptop is lagging beyond expected levels with the database services running.

Have comments & suggestions, please let them come in.

regards,

rajesh

Classic ASP with Oracle database 12c 64Bit

Hi guys

Yesterday I was contacted by one of the visitors after referring my posts about Classic ASP connection to Oracle databases in general & I revisited this “area” after a long time. It took a while for me to setup everything, however the results were pretty awesome. So here comes one more post about Classic ASP with Oracle database, this time Oracle 12c 64Bit.

OS: Windows 10/64Bit Windows Server OS

Prerequisites

  • Setup IIS for publish classic ASP with Oracle 12c database

Software requirement(s):

  • Oracle client 32Bit, 12c (for best connectivity)

Launch IIS and create a new application pool, as shown with the image.

clip_image002

Now go to the advanced settings for the application pool that you have created & switch “Enable 32-Bit Applications” from “false” to “True”

clip_image004

Connection possibilities

There are two ways to connect to Oracle database from Classic ASP

  1. Using DSN (Data Source Name)
  2. DSN less connections. Here we will use the Oracle tnsnames.ora file to identify and connect to the database. We have to insure that the TNS_HOME directory is set at the environment level, or the client that is used is 1st entry in the environment variable “PATH” for Oracle products in case of multi Oracle homed hosts.

Create a DSN

Classic ASP could interact with only 32Bit environment, hence the IIS server hosting machine must have the latest Oracle 32-Bit client installed (older clients may not connect to later databases properly). As I am writing this post, Oracle 12c 12.2.0 is the latest client available.

Logged with an account having “Administrator” privileges, Open ODBC Data Source (32-bit)

clip_image005

Please note, my box has multiple Oracle products, hence don’t get confused by the names in next few screens.

clip_image007

If your installation for the Oracle client was correct, you should able to see an entry for the Oracle driver like the one you could see the image above.

Click “Finish”. This will pop up another window. Under the “System DSN” tab we have to create our new DSN

clip_image008

Provide a name of your choice for the “Data Source Name” and “Description”

Make sure you have the “TNS Service Name” already available with the listener.ora file. Oracle 12c passwords are case sensitive. Hence make sure you are going to provide the case sensitive password while testing the connection.

If you don’t have previous experiences with creating net service names, please follow the instructions below.

clip_image009

Start “Net Configuration Assistant” from the Oracle Home and follow the images below.

clip_image010

Read the help texts available, especially if you are new to Oracle

clip_image011

Provide the service name, which is your database name usually.

clip_image012

 

clip_image013

Provide the FQDN (fully qualified domain name), ie, your computer name like “rajesh-pc.abc.com” in case if you are connected to a domain, else just the name of your computer, like “rajesh-pc”

clip_image014

I would suggest doing a test, if you are sure that all the details provided are correct & no need to test, you can skip this step

clip_image015

clip_image016

By default the configuration tool would suggest you the Oracle service name as new net service name, which you can change to any name. Just make sure that you will remember it.

clip_image017

clip_image018

Now let us test the new service name we have just created.

clip_image019

Once the Net Service Name is created, we will see both the scenarios using 2 different asp files, both using different connection approaches

(ASP sample was copied from here)

1. Connecting to 12c using DSN


<html>
<head>
<title>Connecting to an Oracle database using ODBC and DSN connection</title>
</head>
<body>
<% 
SET myConn=SERVER.createobject("adodb.connection")
myConn.Open "DSN=BAC;" & _ 
"Uid=APPS;" & "Pwd=APPS" 
SQLStr="SELECT BANK_ID, BANK_NAME, BANK_TYPE FROM BAC_BANKS" 
SET result=adoCon.execute(SQLStr) 
IF NOT result.EOF thEN 
response.write("<h2>Oracle ASP Example</h2>") 
response.write("<p>Connecting to the Oracle11g database using ODBC & without a DSN</p>")
response.write("<table BORDER=3 BGCOLOR=#0099CC><tr><th>BANK ID</th>" & _
"<th>Name</th><th>TYPE</th>") 
WHILE NOT result.EOF 
response.write("<tr><td>" & result("BANK_ID") & "</td>") 
response.write("<td>" & result("BANK_NAME") & "</td>") 
response.write("<td>" & result("BANK_TYPE") & "</td></tr>") 
result.movenext() 
WEND 
response.write("</table>") 
ELSE 
response.write("<p>Error retrieving bank data!!</p>") 
END IF 
adoCon.Close()
%>
</body>
</html>

2. Connecting to 12c without DSN


<%
Dim adoCon 'Holds Connection
Dim rsViewRecords 'Holds Record Set

' Initiate connection

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "provider=oraoledb.oracle;data source=SCT;user id=APPS;password=APPS"

if Err.Number <> 0 then
Response.Clear()
response.Write "<hr>ORASESSION Error<br>" & err.number & " -- " & err.Description & "<hr>"
response.End
end if

SQLStr="SELECT BANK_ID, BANK_NAME, BANK_TYPE FROM BAC_BANKS"
SET result=adoCon.execute(SQLStr)
IF NOT result.EOF thEN
response.write("<h2>Oracle ASP Example</h2>")
response.write("<p>Connecting to the Oracle11g database using ODBC & without a DSN</p>")
response.write("<table BORDER=3 BGCOLOR=#0099CC><tr><th>BANK ID</th>" & _
"<th>Name</th><th>TYPE</th>")
WHILE NOT result.EOF
response.write("<tr><td>" & result("BANK_ID") & "</td>")
response.write("<td>" & result("BANK_NAME") & "</td>")
response.write("<td>" & result("BANK_TYPE") & "</td></tr>")
result.movenext()
WEND
response.write("</table>")
ELSE
response.write("<p>Error retrieving bank data!!</p>")
END IF
adoCon.Close()
%>

Hope this helps few “Classic ASP” guys out there ;)

regards,

rajesh

Oracle E-Business Suite R12 | SGA vs PGA vs NOT being a DBA!

Hi guys

I know the subject title is not very professional this time. Yet, I want to make a claim that I figured out something, for which I spent more than couple of years time and have followed up few Oracle community threads (without much interesting results)

We had to retired a hardware that was recommended by the Oracle EBS implementation partner, within 2 years once after we went online with the R12 instance. We had 10g 10.2.0.3 with the instance, things were getting messy and slow & the new support partner recommended for a better hardware.

I always had eyes on this retired server. It had Linux, hence we couldn’t come up with a practical requirement to integrate the Linux server with our Windows domain environment and it was kept switched off until the virtualization project came online.

We needed “something” to hold a copy of the EBS instance while it was being virtualized.

So, I cloned this machine & before continuing let me describe what this is hardware is like:

Processor:


Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 1
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 23
Model name: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
Stepping: 6
CPU MHz: 1992.000
BogoMIPS: 4987.29
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 6144K
NUMA node0 CPU(s): 0-7

Storage space


[root@erp-bak ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sdj2 59G 12G 44G 22% /
tmpfs 9.7G 3.9M 9.7G 1% /dev/shm
/dev/sdj1 2.0G 325M 1.5G 18% /boot
/dev/sdj5 738G 531G 170G 76% /u01
/dev/mapper/mpathbp1 591G 320G 242G 57% /u02
/dev/mapper/mpathcp1 197G 38G 150G 20% /u03
/dev/sdf1 591G 70M 561G 1% /u04
/dev/sdi1 269G 59M 256G 1% /u05

in addition to the local disks this server have partitions mounted from a IBM SAN.

Once the clone was done, I realized that the instance was extremely slow & our part time DBA started making excuses like “See that’s why we are changing the hardware” (He had 2G SGA and 1G PGA with 20 job_queue_processes against nearly 1TB database)

I opened few discussions with Oracle communities and was pointed towards a tone of documents suggesting me how to fine tune the hardware and database for better performances. Actually nothing were applicable as I didn’t have much hands on experiences with a database & I couldn’t find a person who could really HELP me.

Then I started taking interest about database technology, which I should have years back & came across SGA/PGA and JVM etc & as I had an idle instance, started trying out whatever I have “learned” against it.

While doing the 11g R2 the hard way I realized that I can use AMM and forget about tuning different parameters for memory optimization. Well, still the goddamn instance lagged like hell & I was almost done with it!.

Few of the persistent issues were:

After a cold boot

  • The login form would load at client end after waiting almost 3-4 minutes, which gets faster during consecutive attempts.
  • It takes ages for to open the concurrent programs window
  • Our custom forms & LOVs lag to extremes and so on..

Even shutting down the instance for anything was turning into a nightmare as the database always took more than 15-20 minutes and I had to kill multiple processes manually in order to bring it offline!

Then on a different note, while trying to learn SQL learning I landed against an ask Tom thread, where the asker says “I have setup both SGA and PGA 3GB”, still the SQL runs slow…

I did a fresh clone. Our database was upgraded to 11g almost year back. The default clone had 1G for both SGA and PGA. I altered them with 3G and 3G & bullseye

I went back and altered the SGA and PGA with 4G which was 40% of the total physical memory available for the hardware. I did three shutdowns and restarts of the physical server, did a dozen application and database startup to confirm that what I am experiencing is NOT a once in bluemoon phenomena. Each of my attempt to shutdown the database gracefully were completed within few seconds, not a single time I had to kill the Linux processes to bring it down!

I modified one of the main forms for a custom application and changed few VIEW calls with better logic & I can’t be happier!

Now, said that, don’t rush to me saying “I also did 4G for SGA and PGA and moron I still have a slow instance”. There are many factors affecting the performance of your database and application & most important few are:

Age of your hardware, especially the spinning disks. The aged they are, the worse the performance is going to be as there is hell loads of I/O happens when you are accessing/processing the data from a database.

Recently I was going through a MS SQL discussion about Multi-Tenant architecture and one of the contributors were discussing about a hosting firm that keeps on changing their hardware once in 6 months. I think he was just BLUFFING! ;)

I hope someone gets benefitted by the minor finding I have made YESTERDAY (6th May 2018)!

regards,

rajesh

 

 

RMAN | Restore Linux Backup to Windows

 

Update(06-March-2018)

Once after I built the Windows Instance from Linux Backups, I have started a thread with community.oracle.com, expecting answers for few concerns. Below, please have a look at the thread

https://community.oracle.com/message/14730577#14730577

jgarry states, as the redo logs are not applied, I am risking data loss. Now, I don’t really think someone would move from Linux to Windows for Oracle database, when the opposite happens most of the times.

We will consider the entire exercise as limited LAB & for some reasons, if this has to be performed for a production instance, make sure AN IMAGE BACKUP/Cold Backup is available to avoid possible data loss. Cold backup routine as below:

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN>backup database; #replace with your backup routine

RMAN>

 

Hi guys

Greetings. I have been hell busy during last few weeks. Traveling, fixing stuffs & as usual learning new “things”. This time I am working with RMAN, the recovery manager for Oracle database & trying to establish something that is NOT that orthodox or this is how I feel once after going through many documents.  Well, remember we did hack installations, we got “stuffs” work ;). So why not give it a try?

Attempted: Restoring Linux RMAN backup(s) to Windows. Doable? Well, YES.

Cons: No idea yet (6-March-2018, possible data loss as the redo log files will not be readable)

Could be used at Production: At your own risk

 

I am not going to break the flow anywhere, it is a lengthy one shot document. Prior giving it a try, make sure you have copied the backups from Linux machine to your Windows Machine.


Microsoft Windows [Version 10.0.16299.248]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>d:

D:\>cd Oracle11g64\product\11.2.0\dbhome_1\BIN

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>oradim -new -sid LINUXDB
Instance created.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=LINUXDB

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 1 13:34:58 2018

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

connected to target database (not started)

RMAN> set DBID=439294518  shutdown immediate

using target database control file instead of recovery catalog
Oracle instance shut down
--We will use a copy of Windows environment specific INIT file to
--Manually create a INIT file for our new database
--sample
--make sure you have created all paths mentioned in the INIT file prior restarting the database
LINUXDB.__db_cache_size=486539264
LINUXDB.__java_pool_size=8388608
LINUXDB.__large_pool_size=8388608
LINUXDB.__shared_pool_size=746586112
LINUXDB.__streams_pool_size=0
*.audit_file_dest='D:\Oracle11g64\admin\LINUXDB\adump'
*.compatible='11.2.0.4.0'
*.control_files='D:\Oracle11g64\oradata\LINUXDB\control01.ctl','D:\Oracle11g64\oradata\LINUXDB\control02.ctl','D:\Oracle11g64\oradata\LINUXDB\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DB11G' #source database name
*.db_recovery_file_dest='D:\Oracle11g64\oradata\LINUXDB\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=H:\db11g\archivelog'
*.log_archive_format='DB11G_ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=418381824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1256194048
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
--Sample INITFILE end

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1252663296 bytes

Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes

RMAN> restore controlfile from 'H:\db11g\backup\bkpcontrol_file.ctl_DB11G_20180301';

Starting restore at 01-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL
Finished restore at 01-MAR-18

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with 'H:\db11g\backup';

Starting implicit crosscheck backup at 01-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 01-MAR-18

Starting implicit crosscheck copy at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 01-MAR-18

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern H:\db11g\backup

List of Files Unknown to the Database
=====================================
File Name: H:\DB11G\backup\archive_DB11G_lvl0_07ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_08ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0kssmnh4_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0lssmnh4_1_1
File Name: H:\DB11G\backup\bkpcontrol_file.ctl_DB11G_20180301
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-00
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-01
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-02
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-03
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-04
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_01ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_02ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_03ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_04ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_05ssmmoe_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0essmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0fssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0gssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0hssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0issmnga_1_1

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

List of Cataloged Files
=======================
File Name: H:\DB11G\backup\archive_DB11G_lvl0_07ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_08ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0kssmnh4_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0lssmnh4_1_1
File Name: H:\DB11G\backup\bkpcontrol_file.ctl_DB11G_20180301
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-00
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-01
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-02
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-03
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-04
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_01ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_02ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_03ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_04ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_05ssmmoe_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0essmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0fssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0gssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0hssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0issmnga_1_1

--Once the catalog built, query the schema for datafile details
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/DB11G/system01.dbf
2 0 SYSAUX *** /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3 0 UNDOTBS1 *** /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4 0 USERS *** /u01/app/oracle/oradata/DB11G/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/DB11G/temp01.dbf

--We can get the last sequence number of the archivelog by running the below query
--Which we will use for media recovery

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DB11G
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000005_0969624950_0001.arc

2 1 6 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000006_0969624950_0001.arc

3 1 7 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000007_0969624950_0001.arc

4 1 8 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000008_0969624950_0001.arc

--As 8 being the last sequence, we will add 8+1=9 AS the sequence number for the media recovery
RMAN> run
2> {
3> SET UNTIL SEQUENCE 9 THREAD 1;
4> set newname for datafile 1 to 'D:\Oracle11g64\oradata\LINUXDB\system01.dbf';
5> set newname for datafile 2 to 'D:\Oracle11g64\oradata\LINUXDB\sysaux01.dbf';
6> set newname for datafile 3 to 'D:\Oracle11g64\oradata\LINUXDB\undotbs01.dbf';
7> set newname for datafile 4 to 'D:\Oracle11g64\oradata\LINUXDB\users01.dbf';
8> restore database;
9> switch datafile all;
10> recover database;
11> }

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

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 D:\Oracle11g64\oradata\LINUXDB\users01.dbf
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ISSMNGA_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to D:\Oracle11g64\oradata\LINUXDB\undotbs01.dbf
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0GSSMNG7_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ISSMNGA_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
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 00002 to D:\Oracle11g64\oradata\LINUXDB\sysaux01.dbf
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0FSSMNG7_1_1
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0GSSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to D:\Oracle11g64\oradata\LINUXDB\system01.dbf
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ESSMNG7_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0FSSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:22
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ESSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:35
Finished restore at 01-MAR-18

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=969632162 file name=D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF

Starting recover at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

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=7
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0KSSMNH4_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0LSSMNH4_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0KSSMNH4_1_1 tag=TAG20180301T132700
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=H:\DB11G\ARCHIVELOG\DB11G_ARC0000000007_0969624950.0001 thread=1 sequence=7
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0LSSMNH4_1_1 tag=TAG20180301T132700
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=H:\DB11G\ARCHIVELOG\DB11G_ARC0000000008_0969624950.0001 thread=1 sequence=8
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-MAR-18

RMAN> exit

Recovery Manager complete.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>

--SQL Activities
Microsoft Windows [Version 10.0.16299.248]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>d:

D:\>cd D:\Oracle11g64\product\11.2.0\dbhome_1\BIN

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=LINUXDB

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 1 14:18:44 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT GROUP#, STATUS FROM V$LOG
2 /

GROUP# STATUS
---------- ----------------
1 INACTIVE
3 CURRENT
2 INACTIVE

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

--As the datafiles for the redo logs are pointing towards the LINUX file system
--We need to recreate redo log files for the Windows environment
--Prior that, we will try to drop those redo log groups which are inactive
--In our case redo log group 3 is the one active, hence 1,2 should be available to be dropped

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance
linuxdb (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/DB11G/redo02.log'

--Minimum 2 redo log files are required for the instance, so dropping redo log group 2 fails in our case
--So we will add more redo groups to the database

SQL> alter database add logfile group 4
2 ('D:\Oracle11g64\oradata\LINUXDB\redo04.rdo','D:\Oracle11g64\oradata\LINUXDB\redo04a.rdo') size 50M;

Database altered.

SQL> alter database add logfile group 5
2 ('D:\Oracle11g64\oradata\LINUXDB\redo05.rdo','D:\Oracle11g64\oradata\LINUXDB\redo05a.rdo') size 50M;

Database altered.

SQL> alter database add logfile group 6
2 ('D:\Oracle11g64\oradata\LINUXDB\redo06.rdo','D:\Oracle11g64\oradata\LINUXDB\redo06a.rdo') size 50M;

Database altered.

--Now try to drop the rego log file group 2
SQL> alter database drop logfile group 2;

Database altered.

--Can we drop the redo group 3 finally?
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance linuxdb (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/DB11G/redo03.log'

--We cannot drop the redo group 3 because it is being the current redo group for the database
--We cannot switch the log file group because the database is not open

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

--We cannot rename the redo log group members because the filenames are not recognized by the Windows environment

SQL> alter database rename file '/u01/app/oracle/oradata/DB11G/redo03.log' to 'D:\Oracle11g64\oradata\LINUXDB\redo03.log';
alter database rename file '/u01/app/oracle/oradata/DB11G/redo03.log' to 'D:\Oracle11g64\oradata\LINUXDB\redo03.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oradata/DB11G/redo03.log"

--Though we know the database cannot be opened, let us give it an attempt
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/DB11G/redo03.log'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
--Expected, we will proceed to next step

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc';
alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc'
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount
2 /

Database altered.
--We will trace the current control file to a readable format now

SQL> alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

--Start the database in nomount state, so that we can try to create a fresh control file for the database
--Using NORESETLOGS
--COPY AND PASTE THE CREATE CONTROL FILE SEGEMENT FROM THE controlfile trace
--Do not forget to remove the line(s) pointing towards redo log files from LINUX enviornment
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 (
9 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04.RDO',
10 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04A.RDO'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 5 (
13 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05.RDO',
14 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05A.RDO'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 6 (
17 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06.RDO',
18 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06A.RDO'
19 ) SIZE 50M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF',
23 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF',
24 'D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF',
25 'D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF'
26 CHARACTER SET AL32UTF8
27 ;
CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread

--So, next attempt we will try to create the control file using RESETLOGS

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 (
9 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04.RDO',
10 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04A.RDO'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 5 (
13 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05.RDO',
14 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05A.RDO'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 6 (
17 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06.RDO',
18 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06A.RDO'
19 ) SIZE 50M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF',
23 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF',
24 'D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF',
25 'D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF'
26 CHARACTER SET AL32UTF8
27 ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
Database opened.
SQL> create temporary tablespace TEMP01 TEMPFILE 'D:\Oracle11g64\oradata\LINUXDB\TEMP01.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMP TEMPFILE 'D:\Oracle11g64\oradata\LINUXDB\TEMP02.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

--You may drop the TEMP01 tablespace and content once after a shutdown, startup procedure

--Now we will attempt to change the DBID & DATABASE name for the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>nid target=/ DBNAME=LINUXDB

DBNEWID: Release 11.2.0.4.0 - Production on Thu Mar 1 14:52:32 2018

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

Connected to database DB11G (DBID=439294518)

Connected to server version 11.2.0

Control Files in database:
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL

Change database ID and database name DB11G to LINUXDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 439294518 to 3227660209
Changing database name from DB11G to LINUXDB
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL - modified
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL - modified
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL - modified
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\TEMP02.DB - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL - dbid changed, wrote new name
Instance shut down

Database name changed to LINUXDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database LINUXDB changed to 3227660209.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 1 14:52:57 2018

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> alter system set DB_NAME=LINUXDB scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Give it a try, recompile the invalid objects (my case there were none).

That’s all folks

Oracle| Altering table structures through PL/SQL block

Hi guys

Prior moving to Oracle EBS (R12) we were using a mini ERP system, that was as robust as possible, however lacked many features those were required for modern business environments. While the company that distributed the software in the market over a decade finally dissolved, we were also provided a full copy of the source codes & the same was in my custody for whole these years.

Recently I wanted to revive it, after WebLogic 12c & seeing the possibility of “JAVA WEBSTART” in small & middle scale industries.

One of the major hurdles I came across were with the columns those saved document numbers. This software which was designed during late 90s only had maximum 6 digits document numbers across modules, totaling 496 tables!

It was not a very feasible idea to go through each table & to modify them using GUI tools like Oracle SQL developer. Instead I planned for a PL/SQL procedure.

& here comes the PL/SQL block


SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

V_EXECUTE_MODIFY BOOLEAN := FALSE; --'TRUE' FOR TABLE MODIFY

BEGIN
FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
--T_YR_MTH
IF (V_EXECUTE_MODIFY) THEN
IF (C.COLUMN_NAME 'T_YR_MTH') THEN
/*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
END IF;

END IF;
END IF;

END LOOP;

END LOOP;
END;

So what does this block do?


CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

This block fetches all table names from the DBA_TABLES for the owner that is passed in as a parameter, in this specific case ‘OPMAN’


CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

The above cursor takes in two parameters, OWNER name and the table name, that comes from the 1st cursor


FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
    FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
    IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

        DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
        --T_YR_MTH
        IF (V_EXECUTE_MODIFY) THEN
        IF (C.COLUMN_NAME 'T_YR_MTH') THEN
        /*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
        END IF;

        END IF;
        END IF;

        END LOOP;

END LOOP;

Let us analyze this part of the block now.

The first cursor passes the table name to 2nd cursor and the loop goes through all columns matching data type, precision and scale. I’ve checked for “Number”, precision 6 & scale 0 which are common for all columns those were defined for document numbers. However there were few instances where YearMonth (YYYYMM) values captured for month end processes, which shouldn’t be checked. As our mini ERP is a standard business application, it was designed following same nomenclature across tables while columns defined. Thus it become easier for me to isolate the columns whose were matching same data type, precision and scale & to limit the alter commands modifying only those columns which were defined for document numbers.

The above example could be further developed to easily alter tables while the entire application is modernized and to easily modify columns with bigger sizes.

Interesting? Why don’t you give it a try and post your comments?

regards,

rajesh