Will interrupting a "alter table split partition"-command cause any harm to your database?
No, it will not. It is safe to kill the session. Oracle will be able to recover and the table would be left in its original state, prior to time when the "alter table split partition" command was executed.
I was recently in contact with Oracle support regarding such a case. My goal was to split a sub-partition, in order to accommodate rows for years 2017 to 2020, but there was way too much concurrent activities in the database to complete the "alter table split partition"-command.
The database was almost completely non-responsive and there was pressure to finish within a certain time frame.
The supporting engineer confirmed that there would be some rollback activities in the wake of this command, how much depended on the activity level in the database during the time when the DDL was executing.
He added the following explanation to how Oracle handles the split internally:
As for kill the split partition, the way it works internally is to create temporary segments for the 2 new partitions it is splitting into, and when the operation is complete, these new temporary segments are attached to the original table and become the new partitions. So the result of this is that it's perfectly safe to terminate the split partition, and it simply stops its work, drops the temporary segments, and you are left with the original table in-tact, so there will be no ill-effects of this.
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Tuesday, December 20, 2016
Sunday, December 18, 2016
How to create a common and a local user in a 12c multitenant database
In a multitenant container database, there are two types of users:
* Common users, who are known in both the root containers and in all the pluggable database containers
* Local users, who are only known in a single pluggable database container
Common users can, if granted the necessary privileges, perform administrative tasks across all the PDBs in multitenant database.
They can also perform tasks specific to the container database, also called the ROOT container.
To create a common user, make sure you are connected to the ROOT container
On my client, my tnsnames.ora entry looks as follows:
Since my client is a windows pc, I start sqlplus from the start menu, and connect as system@cdb and enter the password.
My default container will be CDB$ROOT, which is what you need to create a common user:
Create the common user:
Note the new rules for creating common users in a CDB database:
In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with C## or c##
Verify that the common user was created:
Give the common user the right to create a session, to switch between all containers in the CDB and the right to create and drop users in all the containers in the CDB:
Connect to ROOT container as the new common user:
Note that without the "container=all" privilege, the new common user C##DBAMASTER cannot connect directly to the vpdb01 pluggable database
Here is what happened:
This means that these two privileges are distinctly different:
* Common users, who are known in both the root containers and in all the pluggable database containers
* Local users, who are only known in a single pluggable database container
Common users can, if granted the necessary privileges, perform administrative tasks across all the PDBs in multitenant database.
They can also perform tasks specific to the container database, also called the ROOT container.
To create a common user, make sure you are connected to the ROOT container
On my client, my tnsnames.ora entry looks as follows:
# container database CDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.no)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = "container#db01") ) )
Since my client is a windows pc, I start sqlplus from the start menu, and connect as system@cdb and enter the password.
My default container will be CDB$ROOT, which is what you need to create a common user:
show con_name CON_NAME ------------------------------ CDB$ROOT
Create the common user:
create user C##DBAMASTER identified by **** default tablespace USERS temporary tablespace TEMP quota unlimited on USERS container=all; SQL> user C##DBAMASTER created.
Note the new rules for creating common users in a CDB database:
In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with C## or c##
Verify that the common user was created:
select USERNAME,CREATED,LAST_LOGIN,COMMON from dba_USERS where trunc(created) = trunc(sysdate) USERNAME CREATED LAST_LOGIN COM -------------------- --------- ---------------------------------------- --- C##DBAMASTER 18-DEC-16 18-DEC-16 03.16.16.000000000 PM +01:00 YES
Give the common user the right to create a session, to switch between all containers in the CDB and the right to create and drop users in all the containers in the CDB:
grant connect to C##DBAMASTER; grant set container to C##DBAMASTER container=all; grant create user to C##DBAMASTER container=all; grant drop user to C##DBAMASTER container=all; SQL> Grant succeeded.
Connect to ROOT container as the new common user:
connect C##DBAMASTER/***@cdb Connected. show user USER is "C##DBAMASTER"Switch container:
alter session set container=vpdb01; SQL> Session altered.Create a new local user in the current container:
create user "1Z0061" identified by **** default tablespace users quota unlimited on users temporary tablespace TEMP container=current; SQL> User created.
Note that without the "container=all" privilege, the new common user C##DBAMASTER cannot connect directly to the vpdb01 pluggable database
Here is what happened:
SQL> connect C##DBAMASTER/****@vpdb01 ERROR: ORA-01045: user C##DBAMASTER lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> connect system/*****@vpdb01 Connected. SQL> show con_name CON_NAME ------------------------------ VPDB01 SQL> grant create session to C##DBAMASTER container=current; Grant succeeded. SQL> connect C##DBAMASTER/****@vpdb01 Connected. SQL> show con_name CON_NAME ------------------------------ VPDB01
This means that these two privileges are distinctly different:
grant create session to C##DBAADMIN container=all;vs
grant set container to C#DBAADMIN container=all;
How to display the current container in an Oracle multitenant database
You can display the current container your session is currently connected to using three different methods.
First, connect to either the container database or one of the pluggable databases running out of your container database. In this example, I chose to connect to the CDB.
1. Display the current container using the "show con_name" or "show con_id" in sqlplus
Switch to a different container, and try again:
2. Display the current container using the SYS_CONTEXT function
In the example below I have thrown in a couple of other useful parameters, along with the
"con_name" and the "con_id" parameters:
From the output above, we see that we are connected to the container name VPDB01 with con_id 6.
Switch to the root container again, and verify that we are now connected to the ROOT container:
3. Display the current container using V$CONTAINERS view
This final method only makes sense if you are connected to a non-root container.
If you are connected to the root container, and when querying the v$container, the resulting output will list all containers in your cdb:
First, connect to either the container database or one of the pluggable databases running out of your container database. In this example, I chose to connect to the CDB.
1. Display the current container using the "show con_name" or "show con_id" in sqlplus
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT --> We are connectted to the ROOT container.
Switch to a different container, and try again:
SQL> alter session set container=vpdb01; Session altered. SQL> show con_name CON_NAME ------------------------------ VPDB01 --> We are now connected to the container named VPDB01. SQL> show con_id CON_ID ------------------------------ 6 --> The current container id is 6.
2. Display the current container using the SYS_CONTEXT function
In the example below I have thrown in a couple of other useful parameters, along with the
"con_name" and the "con_id" parameters:
col "container id" format a20 col "container name" format a20 col "container id" format a10 col "Current schema" format a20 col SID format a10 set lines 200 select SYS_CONTEXT('userenv','con_name') "container name", SYS_CONTEXT('userenv','con_id') "container id", SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema", SYS_CONTEXT('userenv','SID') "SID" FROM DUAL; container name container id Current schema SID -------------------- -------------------- -------------------- -------- VPDB01 6 SYSTEM 63
From the output above, we see that we are connected to the container name VPDB01 with con_id 6.
Switch to the root container again, and verify that we are now connected to the ROOT container:
SQL> alter session set container=CDB$ROOT; Session altered.Rerun the SYS_CONTEXT statement:
select SYS_CONTEXT('userenv','con_name') "container name", SYS_CONTEXT('userenv','con_id') "container id", SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema", SYS_CONTEXT('userenv','SID') "SID" FROM DUAL; container name container id Current schema SID -------------------- -------------------- -------------------- -------- CDB$ROOT 1 SYSTEM 63
3. Display the current container using V$CONTAINERS view
This final method only makes sense if you are connected to a non-root container.
SQL> alter session set container=vpdb01; Session altered.The result:
SQL> select CON_ID,DBID,name,OPEN_MODE 2 from v$containers order by con_id; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 6 2049231443 VPDB01 READ WRITE
If you are connected to the root container, and when querying the v$container, the resulting output will list all containers in your cdb:
SQL> select CON_ID,DBID,name,OPEN_MODE 2 from v$containers order by con_id; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 1 2294367729 CDB$ROOT READ WRITE 2 3254699093 PDB$SEED READ ONLY 6 2049231443 VPDB01 READ WRITE 3 rows selected.I have also written a short blog post about the same topic here, but with a slightly different angle.
Friday, December 16, 2016
How to change passwords for eBS schemas in an Oracle database
In an Oracle database supporting eBS, there are a number of schemas that supports the different modules in eBS. Everything is installed and must be maintained, regardless if the module is in use or not.
If you want to change the passwords for these accounts, you need to do that through the utility AFPASSWD or the deprecated FNDCPASS utility.
Requirements:
* The environment variable TWO_TASK must be set on the eBS application server
* The SYSTEM password for your eBS database
* The current APPS password
I strongly recommend using the same password for all eBS schemas.
Unfortunately, you cannot use special characters in the passwords, so you need to compensate this by making them at least 12-20 characters lang.
To change the passwords, use the following procedure:
1. logon to your eBS application server
2. change user to the owner of the software installation
3. shut down all eBS processes
4. take backup of FND_USER and FND_ORACLE_USERID tables (you can use CTAS for this)
5.
7. Change the passwords for type 2 schema password
8. Change the password for type 2 schema password APPLSYS
9. Change the password for type 2 schema password for APPLSYSPUB. Note that the APPLSYSPUB password must be uppercase, even if you have enabled case sensitive passwords
10. Update the s_gwyuid_pass variable in the AutoConfig context file to reflect the new password
11. verify that the users now have a new password:
12. start your eBS application processes
Here is a query that will extract the description of each schema, and when the password was last changed:
For a complete list, leave out the WHERE-clause.
If you want to change the passwords for these accounts, you need to do that through the utility AFPASSWD or the deprecated FNDCPASS utility.
Requirements:
* The environment variable TWO_TASK must be set on the eBS application server
* The SYSTEM password for your eBS database
* The current APPS password
I strongly recommend using the same password for all eBS schemas.
Unfortunately, you cannot use special characters in the passwords, so you need to compensate this by making them at least 12-20 characters lang.
To change the passwords, use the following procedure:
1. logon to your eBS application server
2. change user to the owner of the software installation
3. shut down all eBS processes
4. take backup of FND_USER and FND_ORACLE_USERID tables (you can use CTAS for this)
connect apps/oldpassword create table FND_USER_BUP AS SELECT * FROM FND_USER; create table FND_ORACLE_USERID_BUP AS SELECT * FROM FND_ORACLE_USERID;
5.
cd $FND_TOP/bin6. Change the password for all type 3 passwords (meaning all eBS schemas except APPS, APPLSYS,APPLSYSPUB)
./AFPASSWD -c APPS@$TWO_TASK -a
7. Change the passwords for type 2 schema password
./AFPASSWD -c APPS@$TWO_TASK -s APPS
8. Change the password for type 2 schema password APPLSYS
./AFPASSWD -c APPS@$TWO_TASK -s APPLSYS
9. Change the password for type 2 schema password for APPLSYSPUB. Note that the APPLSYSPUB password must be uppercase, even if you have enabled case sensitive passwords
./AFPASSWD -c APPS@$TWO_TASK -o APPLSYSPUB
10. Update the s_gwyuid_pass variable in the AutoConfig context file to reflect the new password
vi $CONTEXT_FILEChange from
<oa_user type="GWYUID"> <username oa_var="s_gwyuid_user">APPLSYSPUB</username> <password oa_var="s_gwyuid_pass">PUB</password> </oa_user>to
<oa_user type="GWYUID"> <username oa_var="s_gwyuid_user">APPLSYSPUB</username> <password oa_var="s_gwyuid_pass">YOURNEWSECRETCOMPLICATEDPASSWORD</password> </oa_user>
11. verify that the users now have a new password:
sqlplus /nolog SQL> connect APPS/YOURNEWSECRETCOMPLICATEDPASSWORD Connected. SQL> show user USER is "APPS"
12. start your eBS application processes
Here is a query that will extract the description of each schema, and when the password was last changed:
SELECT ORACLE_USERNAME,LAST_UPDATE_DATE, DESCRIPTION FROM FND_ORACLE_USERID WHERE ORACLE_USERNAME IN ('APPS','APPLSYSPUB','APPLSYS','AR','GMO','PFT') ORDER BY LAST_UPDATE_DATE;Output:
ORACLE_USERNAME | LAST_UPDATE_DATE | DESCRIPTION |
---|---|---|
AR | 15.12.2016 16:28:45 | Oracle Receivables Account |
GMO | 15.12.2016 16:28:47 | Oracle Manufacturing Execution System for Process Manufacturing Account |
PFT | 15.12.2016 16:28:47 | Oracle Profitability Manager Account |
APPLSYS | 15.12.2016 16:30:34 | Application Object Library Account |
APPS | 15.12.2016 16:30:34 | APPS #1 Account |
APPLSYSPUB | 15.12.2016 16:31:09 | Application Object Library Public Account |
For a complete list, leave out the WHERE-clause.
Thursday, December 15, 2016
Cloning a pluggable database in 12c
I have created a CDB using Oracles database configuration assistant (dbca).
While installing, I choosed to create a Pluggable database, too, called pdbvegdb01.
If you want to make a clone of a PDB, it's a relative simple process.
1. Check your current setup by connecting to the root container (CDB$ROOT), and issue the following query:
select C.PDB_ID,C.PDB_NAME,C.STATUS,C.CON_ID "container id", P.OPEN_MODE,P.restricted from CDB_PDBS C inner join V$PDBS P on C.CON_ID = P.CON_ID order by c.con_id;
PDB_ID | PDB_NAME | STATUS | container id | OPEN_MODE | RESTRICTED |
---|---|---|---|---|---|
2 | PDB$SEED | NORMAL | 2 | READ ONLY | NO |
3 | PDBVEGDB01 | NORMAL | 3 | READ WRITE | NO |
I would like to clone the PDBVEGDB01, to a new one, that I will name vpdb01.
2. Check if the root container uses OMF (Oracle Managed Files)
select name,value,DESCRIPTION from V$SYSTEM_PARAMETER WHERE NAME in( 'db_create_file_dest');
NAME | VALUE | DESCRIPTION |
---|---|---|
db_create_file_dest | /u02/oradata | default database location |
It does, and that means your new PDB will inherit this setting.
In this example, I would NOT like Oracle to manage the files in my PDB.
I want to place them in a folder called /u02/oradata/VPDB01.
3. Make directory on server:
mkdir -p /u02/oradata/VPDB01
4. Check the names and paths of the files being used by the source PDB:
select FILE_NAME from CDB_DATA_FILES where CON_ID=3 union select file_name from cdb_temp_files where con_id=3;The result of this union is:
FILE_NAME |
---|
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_system_d4tytlns_.dbf |
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_sysaux_d4tytlo2_.dbf |
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_temp_d4tytlo2_.dbf |
/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_users_d4tyw5y9_.dbf |
All the above files must be accounted for in your clone script, when deciding not to continue using OMF.
5. If not already done, switch to the root container:
alter session set container = CDB$ROOT;
6. Close the source pdb, and open it read only:
alter PLUGGABLE database pdbvegdb01 CLOSE; alter PLUGGABLE database pdbvegdb01 OPEN READ ONLY;
7. Clone the source pdb. Notice that I have matched the source pdb's datafiles, one by one, with a file name of my choosing:
create PLUGGABLE DATABASE VPDB01 from pdbvegdb01 FILE_NAME_CONVERT=( '/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_system_d4tytlns_.dbf', '/u02/oradata/VPDB01/system01.dbf', '/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_sysaux_d4tytlo2_.dbf', '/u02/oradata/VPDB01/sysaux01.dbf', '/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_temp_d4tytlo2_.dbf', '/u02/oradata/VPDB01/temp01.dbf' '/u02/oradata/CONTAINER#DB01/436546A3F70642C3E0537800A8C03C16/datafile/o1_mf_users_d4tyw5y9_.dbf, '/u02/oradata/VPDB01/users01.dbf' );
Your sqlplus session should return "pluggable DATABASE created."
8. Open the source pdb for read/write:
alter pluggable database pdbvegdb01 close; alter pluggable database pdbvegdb01 open read write;
You now see that your freshly cloned database is in status NEW and is MOUNTED, but not open:
PDB_ID | PDB_NAME | STATUS | container id | OPEN_MODE | RESTRICTED |
---|---|---|---|---|---|
2 | PDB$SEED | NORMAL | 2 | READ ONLY | NO |
3 | PDBVEGDB01 | NORMAL | 3 | READ WRITE | NO |
6 | VPDB01 | NEW | 6 | MOUNTED |
9. Open the pdb by switching your session container to the newly cloned pdb
alter session set container=vpdb01;Just to verify, check the open mode of the container.
select CON_ID,DBID,name,OPEN_MODE from v$containers order by con_id;You should have one row worth of output, since you switched from the root container to the pdb container:
CON_ID | DBID | NAME | OPEN_MODE |
---|---|---|---|
6 | 2049231443 | VPDB01 | MOUNTED |
10. Open the pluggable database:
alter pluggable database vpdb01 open;
For the last step to work, you strictly don't need to switch container, it can be done while you have your session set to the root container as well.
But it demonstrates the ability for the DBA to switch containers and work isolated with them, as though they were separate databases.
Finally, let's switch back to the root container and check the status of our PDB's:
alter session set container = CDB$ROOT; select C.PDB_ID,C.PDB_NAME,C.STATUS,C.CON_ID "container id", P.OPEN_MODE,P.restricted from CDB_PDBS C inner join V$PDBS P on C.CON_ID = P.CON_ID order by c.con_id;Result:
PDB_ID | PDB_NAME | STATUS | container id | OPEN_MODE | RESTRICTED |
---|---|---|---|---|---|
2 | PDB$SEED | NORMAL | 2 | READ ONLY | NO |
3 | PDBVEGDB01 | NORMAL | 3 | READ WRITE | NO |
4 | VPDB01 | NORMAL | 4 | READ WRITE | NO |
How to create a line-by-line comparison between the parameters used in two different databases
Here is a simple way to create a line-by-line comparison between two databases over a database link.
First make sure there is tns Connectivity between the database you execute the script in, and the remote database.
Then, create a datatabase link:
The following script will spool a text file to your current directory, which can be investigated for differences between the two databases' initialization parameters:
First make sure there is tns Connectivity between the database you execute the script in, and the remote database.
Then, create a datatabase link:
-- create db link from prod to test create public database link testdb connect to system identified by **** using 'testdb'; -- make sure that the link works before you proceed: select host_name from v$instance@testdb;
The following script will spool a text file to your current directory, which can be investigated for differences between the two databases' initialization parameters:
set trimspool on spool parameter_comparison.lst set lines 200 col name format a40 col "test value" format a50 col "prod value" format a50 select prod.name,prod.value "prod value",test.value "test value" from v$system_parameter prod full outer join v$system_parameter@testdb test on prod.name = test.name; exit
Monday, December 12, 2016
How to find the last password change a user made
Limit the search to those users who changed their password today:
select name,ctime "created", ptime "passw change time",ltime "last locked", lcount "# failed logins" from user$ where trunc(ptime) = trunc(sysdate) order by 3 desc;
Example output:
NAME | created | passw change time | last locked | # failed logins |
---|---|---|---|---|
JIM | 25.11.2016 02:25:38 | 12.12.2016 09:59:25 | 0 | |
JOHN | 25.11.2016 02:25:55 | 12.12.2016 09:53:19 | 0 | |
JAMES | 25.11.2016 02:25:54 | 12.12.2016 09:29:50 | 0 |
Subscribe to:
Posts (Atom)