Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1] Information in this document applies to any platform.
PURPOSE
The present document provides the required guidelines to set the Shared Pool in the ASM instance and Database instance (when ASM is used as storage option).
This is a very important setting, since the SHARED_POOL is used for standard memory usage (control structures and so on) to manage the instance. The value is also used to store open file extent maps.
SCOPE
DETAILS
ASM Instance Shared Pool Settings:
The setting for the SHARED_POOL_SIZE parameter determines the amount of memory required to manage the instance. The setting for this parameter is also used to determine the amount of space that is allocated for extent storage. The default value for this parameter is suitable for most ASM environments.
Shared Pool in ASM is used for metadata information.
You do not have to set a value for the SHARED_POOL_SIZE initialization parameter if you use Automatic Memory Management (AMM).
Oracle strongly recommends that you use Automatic Memory Management (AMM) for ASM. Automatic Memory Management, automatically manages the memory-related parameters for ASM instances with the MEMORY_TARGET parameter. AMM is enabled by default on ASM instances, even when the MEMORY_TARGET parameter is not explicitly set. The default value used for MEMORY_TARGET (272 MB) is acceptable for most environments. This is the only parameter that you need to set for complete ASM memory management. You can also increase MEMORY_TARGET dynamically, up to the value of the MEMORY_MAX_TARGET parameter, just as you can for a database instance.
Note: For Linux environments, automatic memory management will not work if /dev/shm is not available or is sized smaller than MEMORY_TARGET. For Enterprise Linux Release 5, /dev/shm is configured to be half the size of the system memory by default. You can adjust this by adding a size option to the entry for /dev/shm in /etc/fstab. For more details, see the man page for the mount command.
Note: The minimum MEMORY_TARGET for ASM is 256 MB in the SPFILE. If you set MEMORY_TARGET to a lower value, Oracle Database increases the value to 256 MB automatically.
If you are not using Automatic Memory Management, then the default value for this parameter is suitable for most environments.
=)> For 32-bit environments 32 MB is the default and minimum requirement for an ASM instance, but 128 MB is recommended.
=)> On 64-bit platforms 88 MB are required for an ASM instance, recommended values is 150 MB.
For ASM release 11.2.0.3/11.2.0.4/12.1 or before upgrade to ASM release 11.2.0.3/11.2.0.4/12.1, please follow the next recommendation:
Log in to ASM:
SQL> show parameter memory_target
If the value is smaller than 1536m, issue the following:
SQL> alter system set memory_max_target=4096m scope=spfile;
SQL> alter system set memory_target=1536m scope=spfile;
The number 1536m has proven to be sufficient for most environment, the change will not be effective until next restart.
Database Instance Shared Pool Settings for Use with ASM:
When you do not use Automatic Memory Management in a database instance, the SGA parameter settings for a database instance may require minor modifications to support ASM. When you use Automatic Memory Management, the sizing data discussed below can be treated as informational only or as supplemental information to help determine the appropriate values that you should use for the SGA. Oracle highly recommends using automatic memory management.
The following are configuration guidelines for Shared Pool sizing on the database instance (when Automatic Memory Management is not used):
SHARED_POOL_SIZE initialization parameter. Aggregate the values from the following queries to obtain the current database storage size that is either on Oracle ASM or stored in Oracle ASM. Next, determine the redundancy type and calculate the SHARED_POOL_SIZE using the aggregated value as input.
SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;
SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b
WHERE a.group#=b.group#;
SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE
WHERE status='ONLINE';
o For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool plus 2 MB.
o For disk groups using normal redundancy, every 50 GB of space needs 1 MB of extra shared pool plus 4 MB.
o For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool plus 6 MB.
ASM Shared Pool RAC Considerations: When Migrating from single instance to RAC add an additional 15% more shared pool to the Database & ASM instances, since RAC-specific memory is mostly allocated in the shared pool at SGA creation time (that value is heuristic, based on RAC sizing experience).
ORA-04031 Error reported by ASM.
1) Error description:
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
2) In a generic description, an ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a contiguous memory chunk large enough to satisfy an allocation request. In ASM, an ORA-4031 error indicates that the ASM instance is running out of shared or large pool memory.
3) If the ASM instance (Standalone or RAC) is reporting shared pool and/or large pool ORA-04031 errors as follow:
Fri Dec 16 02:54:29 2011
Errors in file /u01/app/11.2.0/grid/log/diag/asm/+asm/+ASM1/trace/+ASM1_ora_1510.trc (incident=94313):
ORA-04031: unable to allocate 3656 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ASM file")
.
.
.
"SQL> alter diskgroup NUCLEUS_DG mount;
alter diskgroup NUCLEUS_DG mount
*
ERROR at line 1:
ORA-04031: unable to allocate 1061464 bytes of shared memory ("large pool","unknown object","large pool","kfr redo buffer")
then please collect the next information for Oracle Support.
3.1) Please connect to each ASM instance and provide the output of the next script from all the ASM instances:
spool asm<#>_4031_shared_pool.html
SET MARKUP HTML ON
set echo on
set pagesize 200
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select bytes/1024/1024 MB
from v$sgastat
where pool = 'shared pool' and name = 'free memory';
select bytes/1024/1024 MB
from v$sgastat
where pool = 'large pool' and name = 'free memory';
select * from V$SHARED_POOL_ADVICE;
select * from v$version;
show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show sga
show parameter
spool off
exit
3.2) Please upload the alert log from the ORACLE_HOME corresponding to the instance in which you got the ORA-4031 error.
3.3) For every ORA-4031 error, the database generates a trace file which contains details about the error. This file exists in the ORACLE_HOME/trace directory (please provide them).
3.4) Please provide an IPS package that contains a text-based (non-XML) Alert log and default (non-incident) trace file.
Example:
In the ASM alert.log you will see a message like this:
Fri Dec 16 02:19:42 2011
Errors in file /u01/app/11.2.0/grid/log/diag/asm/+asm/+ASM1/trace/+ASM1_ora_16890.trc (incident=94321):
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Incident details in: /u01/app/11.2.0/grid/log/diag/asm/+asm/+ASM1/incident/incdir_94321/+ASM1_ora_16890_i94321.trc
Fri Dec 16 02:19:44 2011
Dumping diagnostic data in directory=[cdmp_20111216021944], requested by (instance=1, osid=16890), summary=[incident=94321].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Dec 16 02:19:45 2011
Where: "summary=[incident=94321]" is the incident number.
Therefore please package and provide that incident number following the steps described in the next note:
=)> ADR Different Methods to Create IPS Package Document 411.1
Note 1: Please compress those files in just one file (*.zip or *.tar) and upload it.
Note 2: Trace information from point 3.2, 3.3 & 3.4 are required as input for the ORA-4031 Tool : Document 559339.1
Important Notes:
Note: In 11.2.0.3/11.2.0.4, we increase the default PROCESSES based on the number of CPU cores, but the default MEMORY_TARGET value is not increased. If in 11.2.0.2, customers explicitly set MEMORY_TARGET to some value that may not be big enough for 11.2.0.3/11.2.0.4, when they upgrade to 11.2.0.3/11.2.0.4, ASM will fail to start with error "memory_target is too small". We should add additional check for MEMORY_TARGET during the upgrade prerequisite check.
You can unset MEMORY_TARGET so that ASM can use the default value, but if MEMORY_TARGET is explicitly set, please make sure it's large enough, following the next rules:
NOTE:1625886.1 - ORA-4031 Errors On ASM Instance When Huge Pages Are Enabled On The System NOTE:1373255.1 - 11.2.0.1/11.2.0.2 to 11.2.0.3 Grid Infrastructure and Database Upgrade on Exadata Database Machine NOTE:1416083.1 - Unable To Start ASM (ORA-00838 ORA-04031) On 11.2.0.3/11.2.0.4 If OS CPUs # > 64. NOTE:1982132.1 - Default and Minimum MEMORY_TARGET & MEMORY_MAX_TARGET Value for ASM 11.2.0.4 and Onwards
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1] Information in this document applies to any platform. ***Checked for relevance on 29-Jul-2014***
GOAL
Step by step guide on how to recreate standby control file when datafiles are on ASM and using Oracle Managed Files(OMF)
Please Note: If your Standby database role at the time of creating the new controlfile is "SNAPSHOT STANDBY" do NOT follow this process as you will destroy your standby databases ability to revert from a snapshot standby back to a physical standby.
SOLUTION
Below are the steps to accomplish the task :
Step 1: Disable data guard broker if it exist Step 2: Create the Standby control file on primary database Step 3: Copy the controlfile backup to the standby system Step 4: Shutdown, restore, rename Step 5: Enable configuration if disabled in step 1.
Example
Step 1 : DGMGRL> disable configuration;
Step 2 : Create the Standby control file on primary database.
$ export ORACLE_SID=DEL $rman target / RMAN> backup current controlfile for standby format 'stdbyctl.bkp'; RMAN> EXIT;
stdbyctl.bkp file will be created in "$ORACLE_HOME/dbs" (Unix) or "$ORACLE_HOME/database" (Windows).
Step 3 : Copy the controlfile backup to the standby system Using ftp/scp move stdbyctl.bkp to standby system
Step 4 : Shutdown, restore, rename.
A. Shutdown all instances of the standby.
On linux
$ export ORACLE_SID=MUM
On windows set ORACLE_SID=MUM
$sqlplus / as sysdba SQL> shutdown immediate ORA-01109: database not open
Database dismounted. ORACLE instance shut down.
B. Depending on the location of the logfiles on the standby server remove all online and standby redo logs from the standby directories Using an Operating System utility or ASMCMD and make sure that you have the LOG_FILE_NAME_CONVERT parameter defined to translate any directory paths.
C. Startup one instance of Standby database in nomount stage:
$sqlplus / as sysdba SQL> startup nomount ORACLE instance started.
Total System Global Area 209715200 bytes Fixed Size 1248116 bytes Variable Size 75498636 bytes Database Buffers 125829120 bytes Redo Buffers 7139328 bytes
D. Connect to RMAN with nocatalog option and Restore the standby control file:
$rman nocatalog target / RMAN> restore standby controlfile from '\tmp\stdbyctl.bkp';
Starting restore at 29-AUG-08 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:17 output filename=+DATA1/del/controlfile/current.257.661096899 Finished restore at 29-AUG-08
E. Mount standby database
RMAN> alter database mount;
database mounted
F. Catalog the datafiles of standby database
Below command will give you a list of files and ask if they should all be catalog. Review the list and say YES if all the datafiles are properly listed In below command while cataloging the files, the string specified should refer to the diskgroup/filesystem destination of the standby data files.
RMAN> catalog start with '+DATA1/MUM/DATAFILE/';
Starting implicit crosscheck backup at 29-AUG-08 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK Crosschecked 10 objects Finished implicit crosscheck backup at 29-AUG-08
Starting implicit crosscheck copy at 29-AUG-08 using channel ORA_DISK_1 Finished implicit crosscheck copy at 29-AUG-08
searching for all files in the recovery area cataloging files... cataloging done
List of Cataloged Files ======================= File Name: +fra/MUM/BACKUPSET/2008_07_28/nnndf0_TAG20080728T113319_0.296.661260801 File Name: +fra/MUM/BACKUPSET/2008_07_28/ncsnf0_TAG20080728T113319_0.297.661260847 File Name: +fra/MUM/CONTROLFILE/backup.272.661096103
searching for all files that match the pattern +DATA1/MUM/DATAFILE/
List of Files Unknown to the Database ===================================== File Name: +data1/MUM/DATAFILE/SYSTEM.258.661097855 File Name: +data1/MUM/DATAFILE/SYSAUX.259.661097855 File Name: +data1/MUM/DATAFILE/UNDOTBS1.260.661097855 File Name: +data1/MUM/DATAFILE/USERS.261.661097855
Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done
List of Cataloged Files ======================= File Name: +data1/MUM/DATAFILE/SYSTEM.258.661097855 File Name: +data1/MUM/DATAFILE/SYSAUX.259.661097855 File Name: +data1/MUM/DATAFILE/UNDOTBS1.260.661097855 File Name: +data1/MUM/DATAFILE/USERS.261.661097855
NOTE: a) This will only work if you are using OMF. If you are using ASM without OMF you have to catalog all non-OMF Datafiles as Datafile Copies manually using
Create and apply computations over a sliding window of data
Step 1. Open Jupyter Python Notebook for Spark Streaming. Open a web browser by clicking on the web browser icon at the top of the toolbar: Navigate to localhost:8889/tree/Downloads/big-data-3/spark-streaming: Open the Spark Streaming Notebook by clicking on Spark-Streaming.ipynb: Step 2. Look at sensor format and measurement types. The first cell in the notebook gives an example of the streaming measurements coming from the weather station: Each
line contains a timestamp and a set of measurements. Each measurement
has an abbreviation, and for this exercise, we are interested in the
average wind direction, which is Dm. The next cell lists the abbreviations used for each type of measurement: The third cell defines a function that parses each line and returns the average wind direction (Dm). Run this cell:
Step 3. Import and create streaming context. Next, we will import and create a new instance of Spark's StreamingContext: Similar to the SparkContext, the StreamingContext provides an interface to Spark's streaming capabilities. The argument sc is the SparkContext, and 1 specifies a batch interval of one second.
Step 4. Create DStream of weather data. Let's open a connection to the streaming weather data: Instead of 12028, you may find that port 12020 works instead. This create a new variable lines to be a Spark DStream that streams the lines of output from the weather station.
Step 5. Read measurement. Next, let's read the average wind speed from each line and store it in a new DStream vals: This line uses flatMap() to iterate over the lines DStream, and calls the parse() function we defined above to get the average wind speed.
Step 6. Create sliding window of data. We can create a sliding window over the measurements by calling the window() method:
This create a new DStream called window that combines the ten seconds worth of data and moves by five seconds.
Step 7. Define and call analysis function.
We would like to find the minimum and maximum values in our window.
Let's define a function that prints these values for an RDD: This function first prints the entire contents of the RDD by calling the collect() method.
This is done to demonstrate the sliding window and would not be
practical if the RDD was containing a large amount of data. Next, we
check if the size of the RDD is greater than zero before printing the
maximum and minimum values.
Next, we call the stats() function for each RDD in our sliding window:
This line calls the stats() function defined above for each RDD in the DStream window.
Step 8. Start the stream processing. We call start() on the StreamingContext to begin the processing: The
sliding window contains ten seconds worth of data and slides every five
seconds. In the beginning, the number of values in the windows are
increasing as the data accumulates, and after Window 3, the size stays
(approximately) the same. Since the window slides half as often as the
size of the window, the second half of a window becomes the first half
of the next window. For example, the second half of Window 5 is 310,
321, 323, 325, 326, which becomes the first half of Window 6.
When we are done, call stop() on the StreamingContext:
Spark Streaming requires more than one executor. This Reading
describes how to configure VirtualBox so that the Cloudera VM has more
than one virtual processor.
Step 1. Stop the Cloudera VM. Before we can change the settings for the Cloudera VM, the VM needs to be powered off. If the VM is running, click on System in the top toolbar, and then click on Shutdown: Next, click on Shut down:
Step 2. Change number of processors. Once the Cloudera VM is powered off, select the Cloudera VM in the list of virtual machines in the VirtualBox Manager: Next, click on Settings: Next, click on System: Next, click on Processor: The default number of processors is one. Change this to two or more:
Finally, click on OK and start the Cloudera VM.
Group and perform aggregate functions on columns in a Spark DataFrame
Join two SparkDataframes on a single column
Step 1. Open Jupyter Python Notebook for SparkSQL.
First Open the Terminal and enter the command "pyspark" to setup the
server. Next, open a web browser by clicking on the web browser icon at
the top of the toolbar: Navigate to localhost:8889/tree/Downloads/big-data-3/spark-sql: Open the SparkSQL Notebook by clicking on SparkSQL.ipynb:
Step 2. Connect to Postgres Table.
This notebook already contains three lines of code so you do not have
to enter them. Run these three lines. The first line imports the
SQLContext module, which is needed access SQL databases in Spark: The second line creates a new SQLContext from the SparkContext sc: The third line creates a new Spark DataFrame in the variable df for the Postgres table gameclicks: The format("jdbc") says that the source of the DataFrame will be using a Java database connection, the url option is the URL connection string to access the Postgres database, and the dbtable option specifies the gameclicks table.
Step 3. View Spark DataFrame schema and count rows. We can call the printSchema() method to view the schema of the DataFrame:
The description lists the name and data type of each column.
We can also call the count() method to count the number of rows in the DataFrame:
Step 4. View contents of DataFrame. We can call the show() method to view the contents of the DataFrame. The argument specifies how many rows to display:
Step 5. Filter columns in DataFrame. We can filter for one or more columns by calling the select() method:
Step 6. Filter rows based on criteria. We can also filter for rows that match a specific criteria using filter():
The arguments to filter() are a Column, in this case specified as df["teamlevel"], and the condition, which is greater than 1. The remainder of the commander selects only the userid and teamlevel columns and shows the first five rows.
Step 7. Group by a column and count. The groupBy() method groups the values of column(s). The ishit column only has values 0 and 1. We can calculate how many times each occurs by grouping the ishit column and counting the result:
Step 8. Calculate average and sum. Aggregate
operations can be performed on columns of DataFrames. First, let's
import the Python libraries for the aggregate operations. Next, we can
calculate the average and total values by calling the mean() and sum() methods, respectively:
Step 9. Join two DataFrames. We can merge or join two Dataframes on a single column. First, let's create a DataFrame for the adclicks table in the Postgres database by copying the third cell in this notebook and changing gameclicks to adclicks and storing the result in a new variable df2: Let's view the columns in df2 by calling printSchema(): We can see that the adclicks df2 DataFrame also has a column called userid. Next, we will combine the gameclicks and adclicks DataFrames by calling the join() method and saving the resulting DataFrame in a variable called merge: We are calling the join() method on the gameclicks DataFrame; the first argument is the DrataFrame to join with, i.e., the adclicks DataFrame, and the second argument is the column name in both DataFrames to join on.
Let's view the schema of merge: We can see that the merged DataFrame has all the columns of both gameclicks and adclicks.
Finally, let's look at the contents of merge:
