High Availbility

OS & Virtualization

Friday, August 16, 2019

Top 25 initialization parameters

TOP 25 INITIALIZATION PARAMETERS

The following list is my list of the top 25 most important initialization parameters, in order of importance.

1.   MEMORY_TARGET This is the initialization parameter setting for all of the memory allocated to both the PGA and SGA combined (new in 11g). Setting MEMORY_TARGET enables Automatic Memory Management, so Oracle allocates memory for you based on system needs, but you can also set minimum values for key parameters. MEMORY_TARGET is used for everything that SGA_TARGET was used for but now additionally includes the PGA (especially important as MEMORY_TARGET now includes the important area PGA_AGGREGATE_TARGET). Important parameters such as DB_CACHE_SIZE, SHARED_POOL_SIZE, PGA_AGGREGATE_TARGET, LARGE_POOL_SIZE, and JAVA_POOL_SIZE are all set automatically when you set MEMORY_TARGET. Setting minimum values for important initialization parameters in your system is also a very good idea.
2.   MEMORY_MAX_TARGET This is the maximum memory allocated for Oracle and the maximum value to which MEMORY_TARGET can be set.
3.   DB_CACHE_SIZE Initial memory allocated to data cache or memory used for data itself. This parameter doesn’t need to be set if you set MEMORY_TARGET or SGA_TARGET, but setting a value for this as a minimum setting is a good idea. Your goal should always be toward a memory-resident database or at least toward getting all data that will be queried in memory.
4.   SHARED_POOL_SIZE Memory allocated for the data dictionary and for SQL and PL/SQL statements. The query itself is put in memory here. This parameter doesn’t need to be set if you set MEMORY_TARGET, but setting a value for this as a minimum is a good idea. Note that SAP recommends setting this to 400M. Also note that the Result Cache gets its memory from the shared pool and is set with the RESULT_CACHE_SIZE and RESULT_CACHE_MODE (FORCE/AUTO/MANUAL) initialization parameters. Lastly, an important note since 11g is that this parameter now includes some SGA overhead (12M worth) that it previously did not in version 10g. Set this 12M higher than you did in 10g!
5.   INMEMORY_SIZE The In-Memory column store resides in this area, which is separate from the buffer cache used to store data in memory. Tables, tablespaces, partitions, and other objects can have single columns stored in this memory area in a compressed fashion. This allows for much faster analytics (like summing an individual column). Oracle builds indexes to make this even faster based on ranges of values. This is new in 12c.
6.   SGA_TARGET If you use Oracle’s Automatic Shared Memory Management, this parameter is used to determine the size of your data cache, shared pool, large pool, and Java pool automatically (see Chapter 1 for more information). Setting this to 0 disables it. This parameter doesn’t need to be set if you set MEMORY_TARGET, but you may want to set a value for this as a minimum setting for the SGA if you’ve calibrated it in previous versions. The SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and DB_CACHE_SIZE are all set automatically based on this parameter (or MEMORY_TARGET if used). INMEMORY_SIZE is also included in this number.
7.   PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT The _TARGET is a soft memory cap for the total of all users’ PGAs. This parameter doesn’t need to be set if you set MEMORY_TARGET, but setting a value as a minimum setting is a good idea. Note that SAP specifies to set this to 20 percent of available memory for OLTP and 40 percent for OLAP. The _LIMIT sets the upper limit that is allowed (the hard memory cap).
8.   SGA_MAX_SIZE Maximum memory that SGA_TARGET can be set to. This parameter doesn’t need to be set if you set MEMORY_TARGET, but you may want to set a value if you use SGA_TARGET.
9.   OPTIMIZER_MODE FIRST_ROWS, FIRST_ROWS_n, or ALL_ROWS. Although RULE/CHOOSE are definitely desupported and obsolete and people are often scolded for even talking about using rule-based optimization, I was able to set the mode to RULE. Consider the following error I received when I set OPTIMIZER_MODE to a mode that doesn’t exist (SUPER_FAST):
Images
10.   SEC_MAX_FAILED_LOGIN_ATTEMPTS If the user fails to enter the correct password after this many tries (new as of 11g) the server process drops the connection and the server process is terminated. The default is 3 (consider increasing this value for less secure systems). A similar parameter in my top 25 list in the prior edition included SEC_CASE_SENSITIVE_LOGON, which was new as of 11g, but is deprecated as of 12.1. Be careful if you’re still using this parameter in 11g (fix case issues with passwords by ensuring passwords can be lower, upper, or mixed case before you upgrade to 12c!).
11.   CURSOR_SHARING Converts literal SQL to SQL with bind variables, reducing parse overhead. The default is EXACT. Consider setting it to FORCE after research.
12.   OPTIMIZER_USE_INVISIBLE_INDEXES The default is FALSE to ensure invisible indexes are not used by default (new in 11g). Set this parameter to TRUE to use all of the indexes and to check which ones might have been set incorrectly to be invisible; this could be a helpful tuning exercise, or it could also bring the system to halt, so only use it in development.
13.   OPTIMIZER_USE_PENDING_STATISTICS The default is FALSE to ensure pending statistics are not used, whereas setting this to TRUE enables all pending statistics to be used (new in 11g).
14.   OPTIMIZER_INDEX_COST_ADJ Coarse adjustment between the cost of an index scan and the cost of a full table scan. Set between 1 and 10 to force index use more frequently. Setting this parameter to a value between 1 and 10 pretty much guarantees index use, however, even when not appropriate, so be careful because it is highly dependent on the index design and implementation being correct. Please note that if you are using Applications 11i, setting OPTIMIZER_INDEX_COST_ADJ to any value other than the default (100) is not supported (see My Oracle Support Note 169935.1). I’ve seen a benchmark where this was set to 200. Also, see bug 4483286. SAP suggests that you not set it for OLAP, but set it to 20 for OLTP.
15.   DB_FILE_MULTIBLOCK_READ_COUNT For full table scans to perform I/O more efficiently, this parameter reads the given number of blocks in a single I/O. The default value is 12812cR2, but it is usually noted not to change this from the default.
16.   LOG_BUFFER Server processes making changes to data blocks in the buffer cache generate redo data into the log buffer. SAP says to use the default, whereas Oracle Applications sets it to 10M. I’ve seen benchmarks with it set over 100M.
17.   DB_KEEP_CACHE_SIZE Memory allocated to the keep pool or an additional data cache that you can set up outside the buffer cache for very important data that you don’t want pushed out of the cache.
18.   DB_RECYCLE_CACHE_SIZE Memory allocated to a recycle pool or an additional data cache that you can set up outside the buffer cache and in addition to the keep pool described in item 17. Usually, DBAs set this up for ad hoc user query data with poorly written queries.
19.   OPTIMIZER_USE_SQL_PLAN_BASELINES The default is TRUE, which means Oracle uses these baselines if they exist (new in 11g). Note that Stored Outlines are deprecated (discouraged but they still work) in 11g, as they are replaced with SQL Plan Baselines.
20.   OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES The default is FALSE, which means that Oracle does not capture them by default, but if you create some, it will use them as stated in the previous parameter (new in 11g).
21.   LARGE_POOL_SIZE Total bytes in the large pool allocation for large PL/SQL and a few other Oracle options less frequently used.
22.   STATISTICS_LEVEL Used to enable advisory information and optionally keep additional OS statistics to refine optimizer decisions. TYPICAL is the default.
23.   JAVA_POOL_SIZE Memory allocated to the JVM for Java stored procedures.
24.   JAVA_MAX_SESSIONSPACE_SIZE Upper limit on memory that is used to keep track of the user session state of Java classes.
25.   OPEN_CURSORS Specifies the size of the private area used to hold (open) user statements. If you get an “ORA-01000: maximum open cursors exceeded,” you may need to increase this parameter, but make sure you are closing cursors that you no longer need. Prior to 9.2.0.5, these open cursors were also cached and, at times, caused issues (ORA-4031) if OPEN_CURSORS was set too high. As of 9.2.0.5, SESSION_CACHED_CURSORS now controls the setting of the PL/SQL cursor cache. Do notset the parameter SESSION_CACHED_CURSORS as high as you set OPEN_CURSORS, or you may experience ORA-4031 or ORA-7445 errors. SAP recommends setting this to 2000; Oracle Applications has OPEN_CURSORS at 600 and SESSION_CACHED_CURSORS at 500.

Undestanding AWR

Instance Efficiency

The Instance Efficiency section continues to grow and shows information for many of the common hit ratios


Things to look for include the following:
Images   A Buffer Nowait % of less than 99 percent. This value is the ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then there are (hot) blocks being contended for that should be found in the Buffer Wait section.
Images   A Buffer Hit % of less than 95 percent. This value is the ratio of hits on a request for a specific buffer when the buffer was in memory and no physical I/O was needed. While originally one of the few methods of measuring memory efficiency, it still is an excellent method for showing how often you need to do a physical I/O, which merits further investigation as to the cause. Unfortunately, if you have unselective indexes that are frequently accessed, that will drive your hit ratio higher, which can be a misleading indication of good performance to some DBAs. When you effectively tune your SQL and have effective indexes on your entire system, this issue is not encountered as frequently and the hit ratio is a better performance indicator. A high hit ratio is not a measure of good performance, but a low hit ratio is often a sign of performance that can be improved or should at least be looked into.
Images   A hit ratio that is steadily at 95 percent and then one day goes to 99 percent should be checked for bad SQL or a bad index that is causing a surge of logical reads (check the load profile and top buffer gets SQL).
Images   A hit ratio that is steadily at 95 percent and then drops to 45 percent should be checked for bad SQL or a dropped index (check the top physical reads SQL) causing a surge in physical reads that are not using an index or an index that has been dropped (I’ve seen this more often than you can imagine).
Images   A Library Hit % of less than 95 percent. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small). A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case, a smaller shared pool may only be a bandage that potentially fixes a resulting library latch problem). Despite the rants about lowering your shared pool all the time to fix library cache and shared pool latching issues, most multiterabyte systems I’ve seen with heavy usage have shared pools in the gigabytes without any issues because they’ve fixed the SQL issues. You must fix the problem (use bind variables or CURSOR_SHARING) and then appropriately size the shared pool. I’ll discuss this further when I get to latch issues.
Images   An In-memory Sort % of less than 95 percent in OLTP. In an OLTP system, you really don’t want to do disk sorts. Setting the MEMORY_TARGET or PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE in previous versions) initialization parameter effectively eliminates this problem. Note that In-memory Sort % appears only in the AWR Report (as in 11g), not in the Statspack Report.
Images   A Soft Parse % of less than 95 percent. As covered in the Load Profile section (last section), a soft parse ratio that is less than 80 percent indicates that SQL is not being reused and needs to be investigated.
Images   A Latch Hit % of less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue. I cover this in detail in the “Latch Free” section later in the chapter.
Images   A Flash Cache Hit % (only appears in the AWR Report on 12c) of less than 90 percent could be a problem if you have a large amount of Flash Cache (system dependent). If you are using Exadata, Chapter 11 provides some queries to help tune this problem if you’re not appropriately using your Flash Cache.

Shared Pool Statistics

The Shared Pool Statistics section that follows the Instance Efficiency section (both in the AWR Report and Statspack) shows the percentage of the shared pool in use and the percentage of SQL statements that have been executed multiple times (as desired). 


Top Wait Events

The Top Wait Events section of Statspack is probably the most revealing section in the entire report when you are trying to eliminate bottlenecks quickly on your system.


Wednesday, August 14, 2019

Basic Automatic Workload Repository (AWR) Information

What’s the oldest AWR information on the system?



What’s the retention period for AWR information?

To change the retention period for AWR information to 15 days:



Tuesday, August 13, 2019

Anatomy of Query Optimizer



System statistics:
The query optimizer must know the power of the system it’s running on to provide accurate estimates


Object statistics:
Table, index, and column statistics, which are stored in the data dictionary, are essential because they describe the data stored in the database.


Constraints:
The query optimizer takes advantage of NOT NULL constraints, unique key constraints, primary key constraints, foreign key constraints, and some check constraints


Physical Design
There are three main physical design areas that have an effect on the query optimizer. First, Oracle Database offers five strategies to store data: heap-organized tables (this is the default), index-organized tables, external tables, index clusters, and hash clusters. In addition, heap-organized tables and index-organized tables can be partitioned. One or several access paths are associated to each strategy


SQL control
features like stored outlines, SQL profiles, and SQL plan baselines allow you to store in the data dictionary information that influences the decisions taken by the query optimizer while generating execution plans


Execution environment:
A set of initialization parameters controls the behavior of the query optimizer.
Bind variables: Bind variables has a strong influence on the execution plans generated by the query optimizer


Cardinality feedback(also called statistics feedback):
Either because of complex predicates or missing input information, the query optimizer can’t always compute accurate estimations.

Monday, August 12, 2019

Typical Steps in Machine Learning



Frame the problem

The first thing you have to do before you solve a problem is to define exactly what it is

Data Collection: The very first and the most important step is to collect relevant data corresponding to our problem statement

Data Pre-Processing: The data gathered from the previous step most probably is not fit to be used by our machine learning algorithm yet, as this data might be incomplete, inconsistent and is likely to contain many errors and missing values
  • Missing values, perhaps customers without an initial contact date
  • Corrupted values, such as invalid entries

Creating Transformers


Before we can use the dataset to estimate a model, we need to do some transformation.

Spark machine learning algorithms work with two columns that must be named 
features and label, by default. The features column must be a vectorrepresentation of the features we intend to use to estimate a model while the label column represents the column with the different outcomes

Creating an Estimator

We shall now create our estimator

Creating a Pipeline

Now, create a Pipeline to pull the different transformations together:


Model Evaluation: After the model is trained, it is then evaluated, using some evaluation metric, on the test dataset

Performance Improvement: The performance of the model can further be improved on both the training and testing datasets

Tuning dataguard and RMAN

Session Data Unit

As we deal with wide area network (WAN) connectivity between the primary database and the standby database, we often have the need to adjust the session data unit (SDU) to gain additional performance and throughput. We will want to adjust the SDU when the transferred data is fragmented into separate packets or large amounts of data are being transferred.

/u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/sqlnet.ora

# -- This will set SDU to 32k for all the databases
# --
DEFAULT_SDU_SIZE=32767
TCP.NODELAY=YES

Block Change Tracking

If you are performing any level of incremental backups, you need to enable block change tracking (BCT) to track changed blocks on the database since the last backup. Enabling BCT will significantly reduce your backup window for incremental backups as RMAN leverages the BCT file to identify specific blocks that must be backed up

alter database enable block change tracking using file '+DATA_EXAP';
SQL> select filename, status, bytes from v$block_change_tracking;

image copy of the database again. Updated incremental backup image copies are essential when it comes to reducing restore times, deploying snapshot technology, and creating clones of production snapshot data.

Settings for RMAN Backups on the Exadata

With RMAN, we can perform image copy backups or backup set backups. More companies choose RMAN backup set implementations simply because this technology is what they are comfortable with. Instead, consider image copy backups with incremental updates on the Exadata and imagine a world where you do not have to perform a full backup again. Depending on which method we choose, we can tune the RMAN backup processes with the following hidden underscore (_) parameters:
Image _backup_disk_bufcnt—number of buffers used to process backup sets
Image _backup_disk_bufsz—size of the buffers used to process backup sets
Image _backup_file_bufcnt—number of buffers used to process image copies
Image _backup_file_bufsz—size of the buffers used to process image copies
Table 8.1 depicts the best-practice recommendations for the number of buffers and buffer size to perform backup sets and image copies for RMAN backups over IB or 10GigE connections to the Exadata over dNFS.
Image
Table 8.1 RMAN Backup Settings
The following are additional recommended guidelines:
Image Use two to four RMAN channels per tray of disks.
Image Load-balance RMAN channels across shares and controllers.
Image Load-balance RMAN channels across Exadata Compute Nodes.


Suppressing indexes unintentionally

Unintentionally suppressing indexes is one of the most common mistakes made by an inexperienced developer. SQL contains many traps that cause indexes not to be used.

  • Using the NOT EQUAL Operators: <>, !=
    Indexes can only be used to find data that exists within a table. Whenever the NOT EQUAL operators are used in the WHERE clause, indexes on the columns being referenced cannot be used.
  • Using IS NULL or IS NOT NULL

    When you use IS NULL or IS NOT NULL in your WHERE clauses, index usage is suppressed because the value of NULL is undefined

  • Using LIKE

    LIKE, in some cases, uses an index, while in others, it does not. The most common uses of LIKE are LIKE ‘%somevalue%’ or LIKE ‘somevalue%’ (where the % is only at the end of the search string). Only one of these cases uses the index—the case where the value is first, LIKE ‘somevalue%’.
     
  •  Using Function

    Unless you are using function-based indexes, applying functions on indexed columns in the WHERE clause of a SQL statement causes the optimizer to bypass indexes. Some of the most common functions are TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR

Finding RAC Wait Events—Sessions Waiting

To identify the sessions experiencing waits on the system, perform the following tasks:
1.   Query GV$SESSION_WAIT to determine whether any sessions are experiencing RAC-related waits (at the current time).
2.   Identify the objects that are causing contention for these sessions.
3.   Try to modify the object or query to reduce contention.
For example, query GV$SESSION_WAIT (shown next) to determine whether any sessions are experiencing RAC cache–related waits. Note that the GV$ views are used much more to show statistics for the entire cluster, whereas the V$ views still show statistics from a single node. If you plan to use RAC, you must extend the V$ views and queries to the GV$ views for multiple nodes. This section is only an initial guide to help you see all of the components. The scope of this book does not cover RAC specifically, but some things that will help you tune RAC.
Images
Images
The output from this query should look something like this:
Images
Images
Run this query to identify objects that are causing contention for these sessions and the object that corresponds to the file and block for each FILE_NUMBER/BLOCK_NUMBER combination returned (this query is a bit slower):
Images
Images
The output is similar to
Images
Images
Modify the object to reduce the chances for application contention by doing the following:
Images   Reduce the number of rows per block.
Images   Adjust the block size to a smaller block size.
Images   Modify INITRANS and FREELISTS.

Wednesday, June 12, 2019

Oracle Interview Questions - Part 1 (database)


What is the difference between SYSDBA, SYSOPER and SYSASM?
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK

SYSASM can do anything SYSDBA can do.



What is the difference between SYS and SYSTEM?
SYSTEM can’t shutdown the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

21. What is a datafile?

Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

22. What are the contents of control file?

Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

1. What is direct path read?
A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA. The direct path read is available only when the Oracle optimizer chooses a full table scan.


3. What is cache buffer chains wait event ?
A user process acquires the CACHE BUFFERS CHAINS latch to scan the System Global Area (SGA) for database cache buffers. Blocks in the buffer cache are placed on linked lists (cache buffer chains). Blocks are put on the hash chain according to their DBA (data block address) and CLASS of the block. Each hash chain is protected by a single child latch. The latch allows a process to scan a hash chain without having the linked list change while it scans.
Hot blocks are common cause of cache buffers chains latch contention.

4. What are Scattered read and sequential read ?
A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database

A db file scattered read will read multiple data blocks. Multi-block reads are typically used on full table scans.

5. What is the difference between Hash join and nested loop join?
hash join uses a hash-table based lookup mechanism while nested loop doesn't or that the hash join can use cursor work-area memory (allocated in UGA) for buffering rows, while nested loops join can not.

6. What will happens when we open database in resetlogs ?
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

7. What is cursor sharing ?
A cursor is a name to a specific private SQL area. The cursor contains session-specific state information such as bind variable values and result sets. you can think of a cursor as a pointer on the client side and as a state on the server side.



9. What is bind peeking?
One of the first drawbacks of bind variables are the explain plan.  Oracle optimizer is able to peek the value of the bind variable and generate a plan like if the query where with a fixed value and so using histograms and statistics.
Bind variable peeking is used with SQL statements in the library cache to see if the value of a host variable is going to change the existing execution plan.

10. What is adaptive cursor sharing ?
Oracle 11g uses Adaptive Cursor Sharing to solve bind peeking problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.


14. What are histograms?
Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL and the data distribution is skewed.
Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with non-uniform data distributions.



75. What is the difference between instance recovery and crash recovery ?
Crash recovery is done automatically by SMON, in case of abonormal shutdown such as shutdown abort or any such reason.

Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters (RAC) database.


Friday, April 12, 2019

Oracle Interview questions - Part 2 (RAC)

What is a VIP in RAC use for?
The VIP is an alternate Virtual IP address assigned to each node in a cluster. During a node failure the VIP of the failed node moves to the surviving node and relays to the application that the node has gone down. Without VIP, the application will wait for TCP timeout and then find out that the session is no longer live due to the failure.


How do we know which database instances are part of a RAC cluster?
You can query the V$ACTIVE_INSTANCES view to determine the member instances of the RAC cluster


What is a rolling upgrade?
A patch is considered a rolling if it is can be applied to the cluster binaries without having to shutting down the database in a RAC environment


What is cache fusion?
Ans:
Transferring of data between RAC instances by using private network. Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster


What is the purpose of Private Interconnect?
Ans:
Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the clustered node


What is OCR file?
Ans:
RAC configuration information repository that manages information about the cluster node list and instance-to-node mapping information.


How do you identify the OCR file location?
Ans:
check /var/opt/oracle/ocr.loc or /etc/ocr.loc
# ocrcheck


What is Voting file/disk and how many files should be there?
Voting disk is akin to the quorum disk, which helps to avoid the split-brain syndrome. Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures
Number of voting files must be odd i.e. 1, 3, 5, 7, 9


How do I identify the voting disk location?
Ans:
# crsctl query css votedisk


What is GNS?
Grid Naming service is alternative service to DNS , which will act as a sub domain in your DNS but managed by Oracle, with GNS the connection is routed to the cluster IP and manages internally

What is FAN?
Ans:
Applications can use Fast Application Notification (FAN) to enable rapid failure detection,


What is TAF and TAF policies?
Ans:
Transparent Application Failover (TAF) - A runtime failover for high availability environments, such as Real Application Clusters and Oracle Real Application Clusters Guard, TAF refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails


How to know the public IPs, private IPs, VIPs in RAC?
Ans:
# olsnodes -n -p -i
Client Side Connect-Time Load Balance:
 The client load balancing feature enables clients to randomize connection requests among the listeners.
 This is done by client Tnsnames Parameter: LOAD_BALANCE.
 The (load_balance=yes) instructs SQLNet
Server Side Listener Connection Load Balancing.

With server-side load balancing, the listener directs a connection request to the best instance currently providing the service.
 Init parameter remote_listener should be set.
===================================================

Tuesday, April 09, 2019

Basic admin for Oracle 12c MultiTenant

Connecting to PDBs


SELECT name, pdb FROM   v$services ORDER BY name;

Display current container
SQL> SHOW CON_NAME

Switching Between Containers

SQL> ALTER SESSION SET CONTAINER=pdb1;
SQL> ALTER SESSION SET CONTAINER=cdb$root;

Startup/shutdown

SELECT name, open_mode FROM v$pdbs;

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

Preserve PDB Startup State
ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;


SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

Managing Users

Multitenant environments there are two types of user.
  • Common User : The user is present in all containers (root and all PDBs).
  • Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Create common users
CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;



Create local users
CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;



Tracking PDBs to Datafiles




Map tables to PDBs:




Checking PDB History