datawarehouse implementation

[Roshan]

Oracle Database 12c

Red Hat Linux 7

Hi,

We are implementing a datawarehouse. The architecture is as follows.
BIDW.PNG

Production, Test and Other Environments
PRODTEST.png

In order to test new SW patches, SW upgrades, HW upgrades and problem investigation a test system environment is required. It should be possible to test the SW level as it is running in the live environment but new SW versions and/or patches will be tested as well.

This environment shall be equivalent to the production system in terms of functionalities and capabilities. The only difference between the test system and production system would be capacity. The test environment shall be built with a minimalist view

Grateful if you can guide me for the preparation of the new systems for PROD and test environments of the Data warehouse. Please find attached the sizing for PROD and test.

Sizing.docx



Thanks,

Roshan

[ErmanArslansOracleBlog]

I can't blindly recommend you a configuration..
Sizing depends on lots of factors..

As for the datawarehouse, it depends mainly on the following factors:

-The amount of data accessed by queries during peak time and the acceptable response time.
-The amount of data that is loaded within a window of time.

[Roshan]

I am planning to create a Development server for datawarehouse. As you have stated above, there will be some metrics I will need to collect first.
 Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28313/hardware.htm

 I have not yet got the usage requirements: SLA/number of concurrent users. We will only setup a DEV environment and monitor the workload and performance for this environment.

I have reviewed all the metrics from the doc above.

CPU cores.

I will start with 4 cores for CPU.

 
Number of disks

It will be at least twice for each CPU cores: total 8 cores

RAID redundancy

A  balance must be made between redundancy and performance. Which RAID storage would you advise to best meet redundancy and performance?

storage management:

ASM

 

Please advise.

 

[ErmanArslansOracleBlog]

First, understand the RAID types and then make your decision accordingly.

For ex: Raid mirror is good for write performance.. You may place your redolog files on Raid Mirror based disks, because LGWR is write intensive and it has to be very quick when writing..

As for the Storage tier, ASM is not enough.. The underlying disks and the storage is also important..
For ex: you may put your redologs to ssd disks.. You may consider using Exadata, or a new generation ODA ...

So, for each decision, you should think all the details..

[Roshan]

Hi Erman,

based on the screenshot below,
datareplication1.PNG

1. The 'Archive Database server' (as shown above), will house data which are less accessed (data > 18 months...). I am planning to implement the ILM. Do you think it will be a good idea to implement ILM?

https://blogs.oracle.com/dbstorage/heat-map-vital-to-automating-information-lifecycle-management-ilm

2. What is the role of Master/Slave?

3. Normally in a storage, there will be differrent level of disks(nvme,sda and sata). Should I use different different storage for the Main Database and Archive database server?

Regards,

Roshan

[ErmanArslansOracleBlog]

1)Yes why not.. But what is your exact purpose?

2) You are talking aboout the master and slaves given your diagram, right?
You shouldn't ask this question to me.. You should ask it to the guy who created that diagram :)
I think there may be a master-slave relationship between these servers given in the diagram.
As I don't know the technology used there, I can't make more comments.
However; you know the master-slave in comp. science.
Master/slave is a model of communication where one device or process has unidirectional control over one or more other devices.
So those masters probably are controlling some part of the slaves. (maybe in software layer or hardware layer)
Again, you need to ask it to the guy, who created this diagram..


3)Yes .. You should have.. Don't consume your resources unnecessarly.
For instance, if you don't need sped, don't use Nvme storage for that database.

[Roshan]

1) As shown in the picture, the aim is to move data which has not been accessed for more than 18 months to another storage .

I am checking the doc below
http://www.oracle.com/us/products/database/database-11g-managing-storage-wp-354099.pdf

I see data from a table can be compressed and moved to different tiers.

The datawarehouse requirement is as follows:

1. Operational report based on small data-sets (Transaction monitoring mechanism)

2. BI reports for Metrics analysis (Differed data streams transfer for analytics. Bulk data transfer @ a specific time of the day/week/month/… the data streams are then processed in a way that is easier to understand and points more obviously towards actions needed in a particular context.

3. Reports based on Big Data for generating, capturing and processing enormous (petabytes or terabytes) amounts of data on a continuous basis. Example: reading bulky transactional files in near-real-time to identify any issue.

The amount of data to be processed will be around 500,000T. What hardware requirements(CPU,memory and storage..) do you advise? For the database and ETL?

Is it strictly necessary to buy Exadata/ODA(X 7 series) for Datawarehousing?


How should I monitor database growth?

Is there any good book for datawarehouse or should I consider Oracle docs?

How fast should the disks be(in terms of rpms)?

Many Thanks for your usual support.

[ErmanArslansOracleBlog]

Yes it seems ok. Before implementing those examples, consider and check your licenses as well.  This is important..
You can also do these moving of data things by using manual operations (or by using scripts..)
What is shown in that whtepaper you sent to me, is moving data between tablespaces by using "alter table move" operations based on an aging out mechanism..