Feed aggregator

Oracle 12c DataGuard – Insufficient SRLs reported by DGMGRL VALIDATE DATABASE VERBOSE

Yann Neuhaus - Wed, 2016-11-30 07:03

I have setup a DataGuard environment and followed the instructions from Oracle to create the Standby Redo Logs. The Standby Redo Logs have to be the same size as the Online Redo Logs. If not, the RFS process won’t attach Standby Redo Logs, and you should have at least one more of the Standby Redo Log Group as you have for your Online Redo Log Group per Thread.

For my single instance, this should be quite straight forward, and so I issued the following commands on the primary and standby.

alter database add standby logfile group 4 size 1073741824;
alter database add standby logfile group 5 size 1073741824;
alter database add standby logfile group 6 size 1073741824;
alter database add standby logfile group 7 size 1073741824;

After setting all up, I started the new cool Broker command “DGMGRL> VALIDATE DATABASE VERBOSE ‘<DB>';” and surprisingly found, that the validation complains that I do have insufficient Standby Redo Logs.

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (OCM12C_SITE2)          (OCM12C_SITE1)
    1         3                       3                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (OCM12C_SITE1)          (OCM12C_SITE2)
    1         3                       3                       Insufficient SRLs

After looking everything up on Primary and Standby, the number of Log Groups and the sizes looked ok. I do have 3 Online Redo Log Groups with 1G each, and I have 4 Standby Redo Log Groups with 1G each.

-- Standby

SQL> select thread#, group#, sequence#, status, bytes from v$log;

   THREAD#     GROUP#  SEQUENCE# STATUS                BYTES
---------- ---------- ---------- ---------------- ----------
         1          1          0 UNUSED           1073741824
         1          3          0 UNUSED           1073741824
         1          2          0 UNUSED           1073741824

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1          4          0 UNASSIGNED 1073741824
         1          5        552 ACTIVE     1073741824
         1          6          0 UNASSIGNED 1073741824
         0          7          0 UNASSIGNED 1073741824

-- Primary

SQL> select thread#, group#, sequence#, status, bytes from v$log;

   THREAD#     GROUP#  SEQUENCE# STATUS                BYTES
---------- ---------- ---------- ---------------- ----------
         1          1        550 INACTIVE         1073741824
         1          2        551 INACTIVE         1073741824
         1          3        552 CURRENT          1073741824

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1          4          0 UNASSIGNED 1073741824
         1          5          0 UNASSIGNED 1073741824
         1          6          0 UNASSIGNED 1073741824
         0          7          0 UNASSIGNED 1073741824

 

The only strange thing, is that the Standby Redo Log Group 7, shows up with Thread 0, instead of Thread 1.
Did not even know, that a thread 0 exists. It always starts with 1, and in case of RAC, you might see Thread 2, 3 or more. But if you want to, you can perfectly create thread 0 without any issues. For what reasons, I don’t know.

SQL> alter database add standby logfile thread 0 group 8 size 1073741824;

Database altered.

Ok. Lets correct the Thread 0 thing, and then lets see want the “DGMGRL> VALIDATE DATABASE VERBOSE ‘<DB>';” shows.

-- On Standby
		 
DGMGRL> EDIT DATABASE 'OCM12C_SITE1' SET STATE = 'APPLY-OFF';
Succeeded.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 size 1073741824;

Database altered.

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1          4        553 ACTIVE     1073741824
         1          5          0 UNASSIGNED 1073741824
         1          6          0 UNASSIGNED 1073741824
         1          7          0 UNASSIGNED 1073741824
		 
DGMGRL> EDIT DATABASE 'OCM12C_SITE1' SET STATE = 'APPLY-ON';
Succeeded.
		 
-- On Primary

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 size 1073741824;

Database altered.

And here we go. Now I have sufficient Standby Redo Logs.

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (OCM12C_SITE2)          (OCM12C_SITE1)
    1         3                       4                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (OCM12C_SITE1)          (OCM12C_SITE2)
    1         3                       4                       Sufficient SRLs

 

Conclusion

Even on a single instance, use the thread number in your create Standby Redo Log statement.

alter database add standby logfile thread 1 group 4 size 1073741824;
alter database add standby logfile thread 1 group 5 size 1073741824;
alter database add standby logfile thread 1 group 6 size 1073741824;
alter database add standby logfile thread 1 group 7 size 1073741824;

Cheers,
William

 

Cet article Oracle 12c DataGuard – Insufficient SRLs reported by DGMGRL VALIDATE DATABASE VERBOSE est apparu en premier sur Blog dbi services.

Database Resource Manager, v$active_session_history p1text=location p2text=consumer group id

Tom Kyte - Wed, 2016-11-30 02:46
Hi Tom, Wish you all the best. 1] I have a 12c database 2] Not created as a CDB SQL> select cdb from v$database; CDB --- NO 3] No resource manager is configured SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME ...
Categories: DBA Blogs

Combining WITH clauses

Tom Kyte - Wed, 2016-11-30 02:46
I've tried to combine an SQL defined in a WITH clause with an inline PL/SQL in 12c. <code>with function is_number(p_str varchar2) return number is l_number number; begin l_number := to_number(p_str); return l_number; exception when v...
Categories: DBA Blogs

VPD and DBMS_SQLTUNE

Tom Kyte - Wed, 2016-11-30 02:46
I have several different applications using multiple schemas in a single database which used VPD and an after logon trigger to establish initial VPD driving context variables. A second db call, secmgr.my_appl_env spec listed below, is required by al...
Categories: DBA Blogs

Fine-grained Access Control (FGAC) vs. Row Level Security (RLS) vs. Virtual Private Database (VPD) vs. Oracle Label Security (OLS)

Tom Kyte - Wed, 2016-11-30 02:46
Good Evening, Throughout my career, I heard about Fine-grained Access Control (FGAC), Row Level Security (RLS), Virtual Private/Policy Database (VPD) and Oracle Label Security (OLS). These all sound very similar. I've only heard of OLS since it ...
Categories: DBA Blogs

I/O of Database

Tom Kyte - Wed, 2016-11-30 02:46
Hi, I am reading a lot on AWR reports, and also posts about this subject. But everyone say something different. My client asked me for: "I want to know the I/O of the database" Is there a metric, so I can execute a script on a time interva...
Categories: DBA Blogs

Imcomplete CSV File

Tom Kyte - Wed, 2016-11-30 02:46
Hi, Iam exporting a simple select command into a csv file. but the resulting csv file is incomplete ie. amon 44 columns it is displaying only 3 columns. please find my code below, set headsep on set term off set newpage 0 set space 0...
Categories: DBA Blogs

How to check in apex, if there is new data

Tom Kyte - Wed, 2016-11-30 02:46
Hello Tom, I'm a bit new to apex and I wonder if there is a way to check in apex, if new data was inserted in my DATA table. I want to fire a dynamic action that refreshes the region only when new data has arrived. For now, this works for me: ...
Categories: DBA Blogs

Unable to connect to database using sql developer

Tom Kyte - Wed, 2016-11-30 02:46
Hi , I am unable to connect to oracle database using SQL DEVELOPER but I am able to connect through windows cmd command. It gives me error i/o error network adapter could not establish connection. vendor code 17002. Please let me know what are the...
Categories: DBA Blogs

LOB data type comparisons

Tom Kyte - Wed, 2016-11-30 02:46
Hi Team We have migrated data from SQL server to Oracle,As part of data validation we want to compare the data from both databases. We have some tools which compare data but that tool will not compare lob columns. Please note we had migrated dat...
Categories: DBA Blogs

Load-Balancing EBS 12.2 Environments

Steven Chan - Wed, 2016-11-30 02:06
You can use load-balancing routers (LBR) to protect your E-Business Suite 12.2 environment from system failures.  Load-balancers increase your environment's fault-tolerance and scalability by distributing load across a pool of servers. See: HTTP Layer Load-Balancing

HTTP Layer load-balancing is the most common method used in E-Business Suite environments. In this configuration, end-users navigate to a specific Web Entry Point that represents your E-Business Suite's domain name.  An HTTP Layer load-balancer routes all subsequent traffic for a specific user to a specific Web Node.

EBS LBR Architecture

HTTP Layer load-balancers may use heartbeat checks for node death detection and restart, and sophisticated algorithms for load-balancing.

DNS-Based Load-Balancing

When an end-user's browser attempts to access your E-Business Suite environment, your local Domain Name Server (DNS) can direct that user to a specific application server in a pool based on available capacity:


Traffic for that user's session will be handled by the application server 10.10.10.10, while other users' traffic may be directed to other application servers in the pool.  Like HTTP layer load-balancers, many DNS-based load-balancers use heartbeat checks against nodes and sophisticated algorithms for load-balancing.

Minimum Requirement:  Session Persistence

Oracle doesn't certify specific load-balancers or networking hardware with the E-Business Suite.  We've designed the E-Business Suite to be able to use load-balancers in general. Our instructions for configuring load-balancers should work for all standards-compliant networking solutions, whether they are hardware or software-based.

The minimum requirement is that a load-balancer support session persistence.  Persistence describes a mode where a client's initial HTTP connection is directed to a particular application server, and then subsequent HTTP requests from that client are directed to the same server.  As long as a load-balancer is able to handle session persistence (also referred to as "stickiness"), it's likely to work with the E-Business Suite.

Other load-balancing variations

EBS 12.2 can be deployed in a number of variations to handle specialized or advanced load-balancing requirements.  For details about implementing the following variations, see:

WLS Clusters: EBS 12.2 uses WebLogic Server.  It is possible to deploy multiple WebLogic Server instances in a cluster to increase scalability and fault-tolerance. A cluster appears to clients to be a single WebLogic Server instance. The server instances that constitute a cluster can run on the same machine, or be located on different machines. You can increase a cluster’s capacity by adding additional server instances to the cluster on an existing machine, or you can add machines to the cluster to host the incremental server instances. Each server instance in a cluster must run the same version of WebLogic Server.

Multiple Web Entry Points:  It is possible to configure the same EBS environment to have multiple web entry points (URLs).  For example, users might be able to access your EBS environment via the following different URLs:

  • store.company.com
  • recruitment.company.com

Functional redirection:  It is possible to direct users to specific servers based upon the functional responsibilities that they chose.  For example, manufacturing end-users may be directed to a different pool of servers than, say, HRMS end-users.

Related Articles

Categories: APPS Blogs

Whitepapers now and in the future

Anthony Shorten - Tue, 2016-11-29 16:40

The whitepapers available for the product will be changing over the next few months to reflect the changes in the product documentation.

The following changes will happen over the next few months:

  • The online documentation provided with the product has been enhanced to encompass some of the content contained in the whitepapers. This means when you install the product you will get the information automatically in the online help and the PDF versions of the documentation.
  • If the online help fully encompasses the whitepaper contents, the whitepaper will be retired to avoid confusion. Always refer to the online documentation first as it is always the most up to date.
  • If some of the whitepaper information is not in the online help then the new version of the whitepapers will contain the information you need or other whitepaper such as the Best Practices series will be updated with the new information.

I will be making announcements on this blog as each whitepaper is updated to reflect this strategy. This will mean you will not have to download most of the whitepaper information separately and the information is available either online with the product, on Oracle's documentation site or available as a PDF download from Oracle's Delivery Cloud.

The first whitepaper to be retired is the Configuration Migration Assistant Overview which is now not available from My Oracle Support but is available from the documentation supplied with the product.

Remember the FIRST rule is to check the documentation supplied with the product FIRST before using the whitepapers. The documentation provided with the product is always up to date and the whitepapers are only updated on a semi-regular basis.

How to Style Built-In Sites Cloud Service Components

WebCenter Team - Tue, 2016-11-29 13:46
Authored by: Igor Polyakov, Senior Principal Product Manager, Oracle

In this blog post I will explain how you can style built-in components in Oracle Sites Cloud Service (SCS). Built-in SCS components get their visual styling from two places:
  • comp.css, a built-in CSS file that specifies the “base” look of each component
  • design.css, a CSS file that is part of the Theme that your site is using

In the design.css file, you can override and extend the built-in comp.css styles to create your own look and feel. In a Theme, the design.css file is in the designs/default directory.

I will start by describing classes in the comp.css file that are common to all built-in components, as well as a brief overview of defining a Theme. You can follow all of the steps in this tutorial here. Learn how to move image captions, change buttons and more!

Register for OTN Cloud Labs at UKOUG!

OTN TechBlog - Tue, 2016-11-29 13:12


Going to UKOUG? Register for the OTN Cloud Lab on Cloud Native Stack on Oracle Bare Metal Compute (BMCS)

Oracle has built Bare Metal Compute Service (BMCS) that can run both Oracle workloads and cloud native applications.  In this hands on lab, we will walk through getting a cloud native application stack on BMCS.  The purpose of this lab is to get familiar with Bare Metal Compute primitives.  At the end of this lab, you will be familiar with creating a network, launching an instance, and accessing the instance. For this lab, we will go through installing and running MEAN (MongoDB, Express, AngularJS, and Node.js) stack that is used widely by many applications.

Pre-Req’s
Participants should be familiar with the Linux Command Line interface and editors like visual editor.

Oracle Technology Network is giving you the opportunity to experience Oracle's Infrastructure Service live on Sunday 4th December.
Choose from one of two 2-hour sessions:
Sunday 13.00 - 15.00
Sunday 16.00 - 18.00

Register today!

Encryption in Oracle Public Cloud

Yann Neuhaus - Tue, 2016-11-29 12:04

Oracle Transparent Data Encryption is available without option on the Oracle Public Cloud: Standard Edition as well as Enterprise Edition (EE, EE-HP EE-EP, ECS). More than that, the DBaaS enforces TDE for any user tablespace even when not specifying in the CREATE TABLESPACE. It you are not familiar with TDE key management (wallets) then you have probably encountered ORA-28374: typed master key not found in wallet.
Rather than another tutorial on TDE I’ll try to explain it from the errors you may encounter when simply creating a tablespace.

I have created a new pluggable database PDB2 from the command line:

SQL> create pluggable database PDB2 admin user admin identified by "admin";
Pluggable database PDB2 created.
 
SQL> alter pluggable database PDB2 open read write;
Pluggable database PDB2 altered.
 
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------- --------- ----------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
7 PDB2 READ WRITE NO

I go to the PDB2 container and try to create a tablespace:

SQL> alter session set container=PDB2;
Session altered.
 
SQL> create tablespace mytablespace;
 
Error starting at line 1 in command -
create tablespace mytablespace
Error report -
ORA-28374: typed master key not found in wallet
28374. 0000 - "typed master key not found in wallet"
*Cause: You attempted to access encrypted tablespace or redo logs with
a typed master key not existing in the wallet.
*Action: Copy the correct Oracle Wallet from the instance where the tablespace
was created.

So, this message is related with TDE wallet.

encrypt_new_tablespaces

I didn’t specify any encryption clause in the CREATE TABLESPACE command but it is activated by default by the following parameter:

SQL> show parameter encrypt_new_tablespaces
 
NAME TYPE VALUE
----------------------- ------ ----------
encrypt_new_tablespaces string CLOUD_ONLY

The values can be DDL (the old behavior where encryption must be defined in the CREATE TABLESPACE statement), ALWAYS (AES128 encryption by default), or CLOUD_ONLY which is the same as ALWAYS when the instance is on the Cloud, or as DDL if the instance is on-premises. The default is CLOUD_ONLY.
This parameter has been introduced in 12.2 and has been backported to 11.2.0.4 and 12.1.0.2 with bug 21281607 that is applied on any Oracle Public Cloud DBaaS instance.

So, one solution to create our tablespace is to set encrypt_new_tablespaces to DDL but as it is recommended to encrypt all user tablespaces, let’s continue with it.

ORA-28374: typed master key not found in wallet

So the error message means that I don’t have a master key in the wallet for my newly created PDB because in multitenant each PDB has it’s own master key (but there’s only one wallet for the CDB).
The wallet is opened:

SQL> select * from v$encryption_wallet;
 
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------- --------------- ------------------- ------------ ------------- ---------------- ------
FILE OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 7

But empty (I’m still in the PDB2 container)

SQL> select * from v$encryption_keys order by creation_time;
no rows selected

SET KEY

So the idea is to set a key:

SQL> administer key management set key identified by "Ach1z0#d";

but:

Error starting at line 1 in command -
administer key management set key identified by "Ach1z0#d"
Error report -
ORA-28417: password-based keystore is not open
28417. 0000 - "password-based keystore is not open"
*Cause: Password-based keystore was not opened.
*Action: Close the auto login keystore, if required, and open a
password-based keystore.

Ok. An error because the wallet is not opened. Let’s try to open it:

SQL> administer key management set keystore open identified by "Ach1z0#d";
 
Error starting at line 1 in command -
administer key management set keystore open identified by "Ach1z0#d"
Error report -
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open
28354. 0000 - "Encryption wallet, auto login wallet, or HSM is already open"
*Cause: Encryption wallet, auto login wallet, or HSM was already opened.
*Action: None.

Actually, the wallet is opened. We have seen that the opened wallet is AUTOLOGIN:

SQL> select * from v$encryption_wallet;
 
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------- --------------- ------------------- ------------ ------------- ---------------- ------
FILE OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 7

On DBaaS an AUTOLOGIN wallet is used to be able to have the database automatically restarted without manual intervention. Without AUTOLOGIN wallet you have to provide the password.

But AUTOLOGIN wallet is limited to use it to access the tablespaces.
When administering the wallet, we need to provide the password manually:

We need to close the AUTOLOGIN one:

SQL> administer key management set keystore close;
Key MANAGEMENT succeeded.

Now that it is closed, we can try to open it and open it with the password:

SQL> administer key management set keystore open identified by "Ach1z0#d";
 
Error starting at line : 1 in command -
administer key management set keystore open identified by "Ach1z0#d"
Error report -
ORA-28417: password-based keystore is not open
28417. 0000 - "password-based keystore is not open"
*Cause: Password-based keystore was not opened.
*Action: Close the auto login keystore, if required, and open a
password-based keystore.

Oh… it is opened AUTOLOGIN once again:

SQL> select * from v$encryption_wallet;
 
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------- --------------- ------------------- ------------ ------------- ---------------- ------
FILE OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 7

CDB$ROOT

You need to open the wallet with password from CDB$ROOT:

SQL> alter session set container=CDB$ROOT;
Session altered.
 
SQL> administer key management set keystore close;
Key MANAGEMENT succeeded.
 
SQL> administer key management set keystore open identified by "Ach1z0#d";
Key MANAGEMENT succeeded.

So here is the right way to start: in CDB$ROOT close the AUTOLOGIN wallet and open it with the password.

PDB

Now ready to go further in the PDB2.


SQL> alter session set container=PDB2;
Session altered.

The wallet is now closed for the PDB:

SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
--------- -------------- ------- ------------ ------------- ---------------- ------
FILE CLOSED UNKNOWN SINGLE UNDEFINED 7

Let’s open it manually:

SQL> administer key management set keystore open identified by "Ach1z0#d";
Key MANAGEMENT succeeded.

We have no encryption key:

SQL> select * from v$encryption_keys order by creation_time;
no rows selected

Let’s do what we want to do from the get-go: create an encryption key for our PDB:

SQL> administer key management set key identified by "Ach1z0#d";
 
Error starting at line 1 in command -
administer key management set key identified by "Ach1z0#d"
Error report -
ORA-46631: keystore needs to be backed up
46631. 00000 - "keystore needs to be backed up"
*Cause: The keystore was not backed up. For this operation to proceed, the
keystore must be backed up.
*Action: Backup the keystore and try again.

Oh yes. Any change must be backed up. That’s easy:


SQL> administer key management set key identified by "Ach1z0#d" with backup;
Key MANAGEMENT succeeded.

Here we are. The key is there:


SQL> select * from v$encryption_keys order by creation_time;
 
KEY_ID TAG CREATION_TIME ACTIVATION_TIME CREATOR CREATOR_ID USER USER_ID KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_DBNAME CREATOR_DBID CREATOR_INSTANCE_NAME CREATOR_INSTANCE_NUMBER CREATOR_INSTANCE_SERIAL CREATOR_PDBNAME CREATOR_PDBID CREATOR_PDBUID CREATOR_PDBGUID ACTIVATING_DBNAME ACTIVATING_DBID ACTIVATING_INSTANCE_NAME ACTIVATING_INSTANCE_NUMBER ACTIVATING_INSTANCE_SERIAL ACTIVATING_PDBNAME ACTIVATING_PDBID ACTIVATING_PDBUID ACTIVATING_PDBGUID CON_ID
----------------------------------------------------- ---- --------------------------------------- --------------------------------------- -------- ----------- ----- -------- ----------- ------------------ ------- ---------- --------------- ------------- ---------------------- ------------------------ ------------------------ ---------------- -------------- --------------- --------------------------------- ------------------ ---------------- ------------------------- --------------------------- --------------------------- ------------------- ----------------- ------------------ --------------------------------- ------
AXP3BIrVW0+Evwfx7okZtcgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 28-NOV-16 08.41.20.629496000 PM +00:00 28-NOV-16 08.41.20.629498000 PM +00:00 SYS 0 SYS 0 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO CDB1 902797638 CDB1 1 4294967295 PDB2 7 96676154 42637D7C7F7A3315E053DA116A0A2666 CDB1 902797638 CDB1 1 4294967295 PDB2 7 96676154 42637D7C7F7A3315E053DA116A0A2666 7

All is perfect but the wallet is still opened with the password:

SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
-------- --------------- ------- ------------ ------------- --------------- -------
FILE OPEN PASSWORD SINGLE NO 7

In order to get back to the initial state, it is sufficient to close it (from the CDB$ROOT):


SQL> alter session set container=CDB$ROOT;
Session altered.
 
SQL> administer key management set keystore close;
 
Error starting at line 1 in command -
administer key management set keystore close
Error report -
ORA-28389: cannot close auto login wallet
28389. 00000 - "cannot close auto login wallet"
*Cause: Auto login wallet could not be closed because it was opened with
another wallet or HSM requiring a password.
*Action: Close the wallet or HSM with a password.

Ok. The ‘close’ command needs the password as it was not opened with AUTOLOGIN one.


SQL> administer key management set keystore close identified by "Ach1z0#d";
Key MANAGEMENT succeeded.

It is immediately automatically re-opened with the AUTOLOGIN one:

SQL> select * from v$encryption_wallet;
 
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
--------- --------------------------------------- ------- ------------ ------------- ---------------- ------
FILE /u01/app/oracle/admin/CDB1/tde_wallet/ OPEN AUTOLOGIN SINGLE NO 1

and from the CDB$ROOT I can see all of them:

SQL> select * from v$encryption_keys order by creation_time;
 
KEY_ID TAG CREATION_TIME ACTIVATION_TIME CREATOR CREATOR_ID USER USER_ID KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP CREATOR_DBNAME CREATOR_DBID CREATOR_INSTANCE_NAME CREATOR_INSTANCE_NUMBER CREATOR_INSTANCE_SERIAL CREATOR_PDBNAME CREATOR_PDBID CREATOR_PDBUID CREATOR_PDBGUID ACTIVATING_DBNAME ACTIVATING_DBID ACTIVATING_INSTANCE_NAME ACTIVATING_INSTANCE_NUMBER ACTIVATING_INSTANCE_SERIAL ACTIVATING_PDBNAME ACTIVATING_PDBID ACTIVATING_PDBUID ACTIVATING_PDBGUID CON_ID
----------------------------------------------------- ---- --------------------------------------- --------------------------------------- -------- ----------- ----- -------- ----------- ------------------ ------- ---------- --------------- ------------- ---------------------- ------------------------ ------------------------ ---------------- -------------- --------------- --------------------------------- ------------------ ---------------- ------------------------- --------------------------- --------------------------- ------------------- ----------------- ------------------ --------------------------------- ------
ATxUk1G7gU/0v3Ygk1MbZj8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 27-NOV-16 09.02.18.050676000 PM +00:00 27-NOV-16 09.02.18.130705000 PM +00:00 SYS 0 SYS 0 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES CDB1 902797638 CDB1 1 4294967295 CDB$ROOT 1 1 3D94C45E41CA19A9E05391E5E50AB8D8 CDB1 902797638 CDB1 1 4294967295 CDB$ROOT 1 1 3D94C45E41CA19A9E05391E5E50AB8D8 1
AWSs1Gr0WE86vyfWc123xccAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 27-NOV-16 09.02.18.089346000 PM +00:00 27-NOV-16 09.02.18.722365000 PM +00:00 SYS 0 SYS 0 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES CDB1 902797638 CDB1 1 4294967295 PDB1 3 2687567370 424FA3D9C61927FFE053DA116A0A85F7 CDB1 902797638 CDB1 1 4294967295 PDB1 3 2687567370 424FA3D9C61927FFE053DA116A0A85F7 3
AfwqzZP/Rk+5v5WqiNK5nl0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 28-NOV-16 08.36.43.980717000 PM +00:00 28-NOV-16 08.36.43.980720000 PM +00:00 SYS 0 SYS 0 TDE IN PDB SOFTWARE KEYSTORE LOCAL YES CDB1 902797638 CDB1 1 4294967295 PDB2 5 2602763579 42636D1380072BE7E053DA116A0A8E2D CDB1 902797638 CDB1 1 4294967295 PDB2 5 2602763579 42636D1380072BE7E053DA116A0A8E2D 5
AXP3BIrVW0+Evwfx7okZtcgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 28-NOV-16 08.41.20.629496000 PM +00:00 28-NOV-16 08.41.20.629498000 PM +00:00 SYS 0 SYS 0 TDE IN PDB SOFTWARE KEYSTORE LOCAL NO CDB1 902797638 CDB1 1 4294967295 PDB2 7 96676154 42637D7C7F7A3315E053DA116A0A2666 CDB1 902797638 CDB1 1 4294967295 PDB2 7 96676154 42637D7C7F7A3315E053DA116A0A2666 7

As you can see I did two attempts with the PDB2 to write this blog post. The previous keys are all in the wallet.

I check that the AUTOLOGIN is opened in PDB2:


SQL> alter session set container=PDB2;
Session altered.
 ;
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER FULLY_BACKED_UP CON_ID
--------- -------------- ------- ------------ ------------- ---------------- ------
FILE OPEN AUTOLOGIN SINGLE NO 7

And finally I can create my tablespace


SQL> create tablespace mytablespace;
Tablespace MYTABLESPACE created.

Easy, isn’t it?

If you create your PDB with the DBaaS monitor interface all is done automatically with the ‘create PDB’ button:

  • Close the AUTOLOGIN wallet (from CDB$ROOT)
  • Open the wallet with password
  • Create the pluggable database and open it
  • Open the wallet from the PDB, with password
  • Set the masterkey for the PDB
  • Close the wallet to get it opened with AUTOLOGIN
 

Cet article Encryption in Oracle Public Cloud est apparu en premier sur Blog dbi services.

Delete/Insert #2

Jonathan Lewis - Tue, 2016-11-29 10:33

In the previous post I threw out a couple of options for addressing the requirement to transfer data from one table to another (“cut and paste” rather than just “copy”) without running into odd inconsistency errors. This triggered of a wonderful comment trail of alternatives based on how large the volume might be, how relaxed the concurrency requirements might be, and so on.

A comment by SydOracle1 picked up on my failure to get Oracle working with the “as of SCN” syntax because it kept reporting ORA-08187 and suggested a straightforward use of the VERSIONS strategy. I thought it was quite surprising that this could work given that “as of SCN” didn’t, so I whipped up a quick test to check it – adding a couple of little refinements to the supplied sample – and it worked.

create table t1
as
select	object_id, object_name, owner
from	all_objects
;

create table t2
as
select	* from t1
where	rownum = 0
;

alter table t1 add constraint t1_pk primary key(object_id);

execute dbms_stats.gather_table_stats(user,'t1')

execute dbms_lock.sleep(5)

That dbms_lock.sleep() is very important for the purposes of this demonstration; it has to be just a few seconds otherwise the references back to earlier SCNs could report error: “ORA-01466: unable to read data – table definition has changed”. This is probably a side effect due to the 3 second interval in the capture that Oracle uses in the table smon_scn_time.

So now we do the following:

  • find the current SCN,
  • start a transaction,
  • get the transaction id,
  • delete the data from the source table,
  • find the current SCN again,
  • insert into the target table the data that was deleted by our transaction in the interval spanned by the two SCNs.

 


rem
rem     insert_delete_2.sql
rem     Jonathan Lewis
rem     Nov 2016
rem

column current_scn new_value m_start_scn 
select to_char(current_scn,'FM999999999999999999999') current_scn from v$database;

variable m_tx_id varchar2(20)
exec :m_tx_id := dbms_transaction.local_transaction_id(true)

column xid new_value m_xid
select xid from v$transaction where xidusn || '.' || xidslot || '.' || xidsqn = :m_tx_id;

delete from t1 where owner = 'SYSTEM';
commit;

column current_scn new_value m_end_scn format 999999999999999999
select to_char(current_scn,'FM999999999999999999999') current_scn from v$database;
 
insert	into t2
select	r.*
from	t1  versions between scn &m_start_scn and &m_end_scn r
where 
	versions_operation = 'D'
and	versions_xid = '&m_xid'
and	owner = 'SYSTEM'
;

commit;

There are a couple of variants on getting the transaction ID – I decided to use a function call to start a transaction without doing any work rather than doing the delete and then finding the transaction id that the delete initiated (I could have linked v$session for my SID to v$transaction after the delete). Because of the choice I made I have to do a little bit of messing around in the subsequent code – the function call returns the transaction ID in the form 31.16.19111 (that’s undo segment, slot number, sequence) but the VERSIONS mechanism wants a transaction ID in its HEX form which, for the example shown, would be ‘1F001000A74A0000’. I could have converted the three part form to the other using a messy bit of to_char(,’XXXXXXXX’) code, but I was feeling a little lazy.

To my surprise I didn’t see any ORA-08187 errors – which made me look back at the notes I had jotted down on the couple of tests I’d initially tried to find out what I had been doing wrong. My first attempt did the insert first then tried to do the delete “as of SCN” and failed, so my second attempt tried to do the delete first just in case the problem related to using “as of SCN” in the middle of a transaction:


column current_scn new_value m_scn

prompt  ============
prompt  Insert first
prompt  ============

select to_char(current_scn,'FM999999999999999999') current_scn from V$database;

insert into t2
select  *
from    t1 as of scn &m_scn r
where
        owner = 'SQLTXADMIN'
;

delete from t1 as of scn &m_scn r
where owner = 'SQLTXADMIN'
;

-- ORA-08171: snapshot expression not allowed here

rollback;

prompt  ============
prompt  Delete first
prompt  ============

select to_char(current_scn,'FM999999999999999999') current_scn from V$database;

delete from t1 as of scn &m_scn r
where owner = 'SQLTXADMIN'
;

-- ORA-08171: snapshot expression not allowed here

insert into t2
select  *
from    t1 as of scn &m_scn r
where
        owner = 'SQLTXADMIN'
;

rollback;

Clearly “delete as of scn” is illegal.

Of course, if I’d gone a little further with this idea I might have tried starting with a delete that didn’t use “as of SCN”, and then the code would have succeeded. In fact, though, this wouldn’t be a perfect solution because it would allow a window for error: some other session might delete a relevant row between my call for SCN and my delete, which means my insert would insert a row deleted by another user.

The code could be modified though in its choice of SCN. Provided I started my transaction with the delete I could then query v$transaction for the start SCN for the transaction, and use that as the “as of” SCN for the insert:


delete from t1
where owner = 'SQLTXADMIN'
;

select
        to_char(
                start_scnw * power(2,32) + start_scnb,
                'FM999999999999999999'
        )       current_scn
from
        v$transaction
where   ses_addr = (
                select  saddr
                from    v$session
                where   sid = (
                        select  sid
                        from    V$mystat
                        where   rownum = 1
                )
        )
;

insert into t2
select  *
from    t1 as of scn &m_scn r
where
        owner = 'SQLTXADMIN'
;

By using the delete to initiate the transaction and set the SCN I think we block any window of inconsistency and, apart from the messy little bit of code that finds the transaction entry, we have an even simpler piece of code than the example give by SydOracle.

Collaboration or, kicking ideas around, is a wonderful way to learn.

 


Insert All with sequence - feature or featurette?

Tom Kyte - Tue, 2016-11-29 08:26
I posted this problem elsewhere and we had a bit of a discussion. However I would like to know whether what we see is a feature (i.e. it can be relied upon to be somewhat consistent in future releases) or whether it is a "featurette" - i.e. unintende...
Categories: DBA Blogs

Generating excel report with multiple tabs from Oracle APEX

Tom Kyte - Tue, 2016-11-29 08:26
Hi, I'm new to Oracle Application Express and currently working on an APEX application. We have several reports which can be downloaded as csv files in various modules of the application. What I need is to have a master report(excel file with ...
Categories: DBA Blogs

Decoding from base64

Tom Kyte - Tue, 2016-11-29 08:26
Hi, I'm in need to encode and decode big xml, however when i'm decoding from base64 after some iterations it fails to decode and i get symbols like this, "S??FS???S??FS???S?". Here's the script to reproduce the problem i'm facing: <code>declare...
Categories: DBA Blogs

Drop a partition of reference partitioned tables with the UPDATE INDEXES clause

Tom Kyte - Tue, 2016-11-29 08:26
I have a set of 3 tables that are reference partitioned (a parent and two child tables). The parent table is range partitioned by date (TST_ADM_DTE) and contains 1,152 partitions, ranging in value from 3/1/14 to 12/31/16. The first seven (7) partitio...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator