Oracle Database 12c Multitenant

Thursday, September 10, 2015

cama

1.Disconnect internet and if also if u can, block Camtasia’s internet access/online validation with firewall
or

2. Block this URL in host file by enter the following below;

C:\Windows\System32\drivers\etc - file 'hosts' - add this URL

127.0.0.1 activation.cloud.techsmith.com

Activation keys -

GCABC-CPCCE-BPMMB-XAJXP-S8F6R

EA5AC-CLMAM-A8W6W-EZLYM-LM58F

HLCZF-HDCPL-KGB6W-ZSCAZ-VF6A2

Please Visit http://www.esoftzone.com/ and Give a Support To Download Best Free Apps.

For more software visit my Profile - click at my nickname.I share many new interesting apps every day

Feel Free to PM me if You Need to Distribute My Torrents on Your Site.

TEAM MESMERiZE

3MHCA-5DMCV-H89T8-V8GML-W6FB8
AMHCC-5DCCP-H89Y8-V84MM-76F73
9CZCA-5KGCQ-H89V8-V8GEY-7B59E

Wednesday, September 9, 2015

UPGRADE 11.2.0.2 to 11.2.0.4

UPGRADE 11.2.0.2 to 11.2.0.4

1) Put DB in no archive log mode

2) Backup database using RMAN (cold backup would preferable) or DATAPUMP depending on

size of DB

3) Run this from 11.2.0.2 home

Run Pre-upgrade Information Tool

SQL>spool /tmp/utlu112i.log

SQL>@ORACLE_HOME/rdbms/admin/utlu112i.sql

SQL>spool off

Note: view log for any errors that need to be fixed.

4) On 11.2.0.2 home run EXECUTE dbms_stats.gather_dictionary_stats;

5) Changes will need to be made in the init.ora or spfile.

6) Run dbupgdiag.sql.Check any invalid objects in dba_registry.

Valid all Dba_registry object by running utlrp.sql

sqlplus / as sysdba

SQL> @?/rdbms/admin/utlrp.sql

7) Shut down database normal:

8) Set New Oracle_HOME,PATH,LIBRARY_PATH.Copy tnsnames.ora,listener.ora,spfile,pfile &

password to new oracle_home location.Update /etc/oratab.

a) Update oratab.

vi /etc/oratab

#adstest:/opt/oracle/product/11.2:N

adstest: /u02/app/oracle/product/11.2.0.4/db_1:N

+ASM:/u01/app/11.2.0/grid:N

b) export ORACLE_HOME=/u02/app/oracle/product/11.2.0.4/db_1

export ORACLE_SID=adstest

c) Copy pfile,spfile & password files

cp *ora /u02/app/oracle/product/11.2.0.4/db_1

$vi initadstest.ora

/u02/app/oracle/product/11.2.0.4/db_1/dbs

Note: make sure you edit right pfile the one from new home is the one you need to modify

d)Copy tnsnames.ora,listener.ora & sqlnet.ora

9) Upgrade database manually

10) Start upgraded db

11) Run the post upgrade script utlu112s.sql to display the results of the upgrade and

run catuppst.sql to finish the upgrade. Finally run utlrp.sql to recompile any remaining stored

PL/SQL and Java code and verify that all expected packages and classes are valid:

Post things:

1) Edit pfile and uncomment hidden parameter

2) Create spfile from pfile and start db with spfile

Backup up new database after upgrade

Enable archive log mode

HAPPY UPGRADE!!!!!!!!!

VIMAL:Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c

Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c
-------------------------------------------------------------------

Prerequisites

The user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the root container.
The remote database (PDB or non-CDB) must be open in read-only mode.
The local database must have a database link to the remote database. If the remote database is a PDB, the database
link can point to the remote CDB using a common user, or the PDB using a local or common user.
The user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASE privilege.
The local and remote databases must have the same endianness, options installed and character sets.
When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.

Cloning a Remote PDB

cdb1 : The local database that will eventually house the clones.
db12c : The remote non-CDB.
cdb3 : The remote CDB, used for cloning a remote PDB (pdb5).
pdb5 : Remote pluggable database

1) Create a user in the remote database for use with the database link. In this case, we will use a local user in the remote PDB.

ALTER SESSION SET CONTAINER=pdb5;

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

2) ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;
EXIT;

3) Open the remote PDB in read-only mode.

ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;
EXIT;

4) Connect to the local database to initiate the clone.

5) Create a database link in the local database, pointing to the remote database.

DROP DATABASE LINK clone_link;

CREATE DATABASE LINK clone_link
CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'pdb5';

-- Test link.
DESC user_tables@clone_link

6) Create a new PDB in the local database by cloning the remote PDB

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

7) We can see the new PDB has been created, but it is in the MOUNTED state.

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

8) The PDB is opened in read-write mode to complete the process.

ALTER PLUGGABLE DATABASE pdb5new OPEN;

Cloning a Remote Non-CDB

1) Connect to the remote database to prepare it for cloning.

2) Create a user in the remote database for use with the database link.

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

3) Open the remote database in read-only mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
EXIT;

4) Create a database link in the local database, pointing to the remote database.

DROP DATABASE LINK clone_link;

CREATE DATABASE LINK clone_link
CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'db12c';

-- Test link.
DESC user_tables@clone_link

5) Create a new PDB in the local database by cloning the remote non-CDB.

CREATE PLUGGABLE DATABASE db12cpdb FROM NON$CDB@clone_link;
SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

6) Since this PDB was created as a clone of a non-CDB, before it can be opened we need to run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean it up.

ALTER SESSION SET CONTAINER=db12cpdb;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

The PDB can now be opened in read-write mode.

ALTER PLUGGABLE DATABASE db12cpdb OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

VIMAL:CREATION OF CONTAINER DATABASE

Creation of a CDB

The CREATE DATABASE ... ENABLE PLUGGABLE DATABASE SQL statement creates a new CDB. If you do not specify the ENABLE PLUGGABLE DATABASE clause, then the newly created database is a non-CDB and can never contain PDBs.

Along with the root (CDB$ROOT), Oracle Database automatically creates a seed PDB (PDB$SEED). The following graphic shows a newly created CDB:

Determining Whether a Database Is a CDB

SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

Creating a pluggable Database

SQL> CREATE PLUGGABLE DATABASE hrpdb ADMIN USER dba1 IDENTIFIED BY password

If you clone from a remote CDB, then you must use a database link.

The following SQL statement clones a PDB named salespdb from the plugged-in PDB named hrpdb:

SQL > CREATE PLUGGABLE DATABASE salespdb FROM hrpdb

VIMAL :CREATION OF CONTAINER DATABASE

VIMAL :Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c

Tuesday, September 8, 2015

SYED MULTIENENT BACKUP AND RECOVERY SAFARI

RMAN>backup pluggable database 'CDB$ROOT';

Performing a Whole CDB Backup

connect to root as common user with either sysdba privs or sysbackup privs

RMAN>connect target /

RMAN>configure controlfile autobackup on;

RMAN>backup database plus archivelog;

Backing Up the Root Container

You can back up just the root container by itself. Because of its critical importance, Oracle recommends that you back up the root (or back up the whole CDB, which includes the root container) at regular intervals.

Here are the steps to back up the root container with RMAN:

1. Start RMAN and connect to the root as a common user with the SYSBACKUP or SYSDBA privilege.

2. Enter the following command:

RMAN>backup database root;

Making Partial CDB Backups

You can back up just the root using the following backup command:

image

This command backs up the system, sysaux, and the undo data files.

You can add one or more PDBs to the backup of the root container:

image

This command backs up the system, sysaux, and the undo data files belonging to the root container, in addition to the data files that belong to SYSTEM, SYSAUX, and any user-created tablespaces in the PDB named pdb1

Backing Up a PDB

You can back up a PDB in two different ways through RMAN. You can back up multiple PDBs simultaneously by connecting to the root and issuing the BACKUP PLUGGABLE DATABASE command. Here’s how you do this:

1. Start RMAN and connect to the root as a common user with the SYSBACKUP or SYSDBA privilege.

rman target /

RMAN>backup pluggable database plus archivelogs

RMAN>backup pluggable database sales,hr;

1. Start RMAN and connect to the root as a local user with the SYSBACKUP or SYSDBA privilege.

==================================================================================================

Recovering the Root Container

Although you can recover the root container by itself, Oracle strongly recommends that you also recover all the PDBs in a CDB, to prevent metadata inconsistencies between the root and the PDBs. Thus, you end up doing a complete recovery of the whole CDB.

Here are the procedures to recover the root container:

1. Start RMAN and connect to the root as a common user with the SYSDBA or SYSBACKUP privilege.

$ rman target /

2. Place the CDB in the mount state.

RMAN>shutdown immediate;

RMAN>startup mount

3. Restore and recover the root container:

RMAN>restore database root;

4. Recover all PDBs, including the seed PDB.

RMAN>restore pluggable database ' PDB$SEED' sales,hr;

RMAN>recover pluggable database 'PDB$SEED' sales,hr;

The restore and recovery commands shown here will recover the seed PDB, along with two PDBs named SALES and HR.

5. Open the CDB.

RMAN>alter database open;

6. Open all the PDBs.

RMAN>alter pluggable database all open;

===================================================================================================================

Performing Complete Recovery of PDBs

You can perform complete recovery of one or more PDBs without affecting operations of other open PDBs.

You can restore and recover an entire PDB in one of two different ways—you can do so by connecting to root or by connecting to the affected PDB.

By Connecting to the Root You can connect to the root and issue the RESTORE PLUGGABLE DATABASE and RECOVER PLUGGABLE DATABASE commands to recover one or more pluggable databases.

The following example shows how you can restore two pluggable databases (and the seed database) with a single command:

1. First, close the PDBs you wish to recover.

RMAN>alter pluggable database sales,hr close

RMAN>restore pluggable database 'pdb$seed',sales,hr;

RMAN>restore pluggable database 'pdb$seed' sales,hr;

2. Open the PDBs.

RMAN>alter pluggable database 'pdb$seed' sales,hr open;

===================================================================================================

By Connecting to the PDB If you wish to recover one PDB, you can connect as a local user to that PDB and recover the PDB. Here are the steps:

1. Close the PDB.

RMAN>alter pluggable database close;

2. Issue the RESTORE DATABASE and RECOVER DATABASE commands to recover the PDB, as shown here:

RMAN>restore database;

RMAN>recover database;

3. Open the PDB.

RMAN>alter pluggable database open;

============================================================================================

Following is another example showing how to recover a PDB from the root container:

RMAN>startup mount;

RMAN>restore pluggable database pdb2;

RMAN>recover pluggable database pdb2;

RMAN>alter database open;

============================================================================================

CDB or PDB Temporary File Recovery A CDB instance can start in spite of a missing or corrupted temporary file. If one or more of the temporary files don’t exist when the instance starts, the CDB instance starts up with a message such as the following being recorded in the alert log:

You can also re-create the temp file while connected to root:

SQL>alter tablespace temp add tempfile '/u01/app/oracle/oradata/cdb3/temp01.dbf' size 100m;

Remember that a PDB can have its own temporary tablespace (otherwise, it uses the container’s temporary tablespace). If a PDB’s temporary tablespace temporary files are missing, the database doesn’t create them automatically when you open the PDB. You must manually re-create them while connected to the PDB:

SQL>alter tablespace temp add tempfile '/u01/app/oracle/oradata/cdb3/PDB3/temp01.dbf' size 100m;

===========================================================================================================================

Media Failure

By Connecting to the PDB If you wish to recover one PDB, you can connect as a local user to that PDB and recover the PDB. Here are the steps:

1. Close the PDB.

image

2. Issue the RESTORE DATABASE and RECOVER DATABASE commands to recover the PDB, as shown here:

image

3. Open the PDB.

image

Following is another example showing how to recover a PDB from the root container:

image

Media

==========================================================================================================

Missing Control Files Pluggable databases don’t have any control files—control files belong to the CDB. If a control file is missing or corrupted, the instance will eventually crash, and you need to perform a whole CDB media recovery by following these steps:

1. Start the CDB instance.

RMAN>startup nomount;

2. Restore the control file from backup.

RMAN>restore controlfile from autobackup

3. Recover the database.

RMAN>alter database mount;

RMAN>recover database;

4. Open the CDB with the RESETLOGS option. You can execute either of the following statement

RMAN>alter database open resetlogs;

RMAN>alter pluggable database all open;

sql> alter database open resetlogs;

======================================================================================================================

Losing One or More Redo Log Files Only the CDB has redo log files. There’s only a single redo stream for the CDB instance, and a redo stream for each instance of a RAC CDB. You need to perform a whole CDB media recovery, depending on whether the status of the lost redo log file is active, inactive, or current. The procedures are the same as those for non-CDBs.

====================================================================================================================

Losing the Root System or Undo Data File When a database loses data files belonging to its system or undo tablespace, the CDB instance will shut down and you need to perform media recovery. All PDBs, of course, will be closed as well.

Here are the steps to recover from the loss of an undo tablespace data file. The procedures are similar when you lose the root system tablespace’s data files.

1. Start up the instance in the mount mode.

RMAN>start up mount

2. Restore the undo tablespace.

RMAN>restore tablespace undotbsp1;

3. Recover the undo tablespace.

RMAN>recover tablespace undotbspq

4. Open the CDB.

RMAN>alter database open;

5. Open all the PDBs.

RMAN>alter pluggable database all open;

The procedure for recovering a SYSTEM tablespace data file is similar to what’s described here.

==========================================================================================================Loss of the Root SYSAUX Data File The procedure to recover from the loss of the root container’s SYSAUX data file(s) is similar to that of a non-CDB, and you have to perform a tablespace media recovery after taking the SYSAUX tablespace offline. Here are the steps:

1. Take the SYSAUX tablespace offline.

RMAN>alter tablespace sysaux offline immediate;

2. Restore the SYSAUX tablespace.

RMAN>restore tablespace sysaux

3. Recover the SYSAUX tablespace.

RMAN>recover tablespace sysaux

4. Bring the SYSAUX tablespace online.

RMAN>alter tablespace sysaux online;

Both the CDB and all the PDBs continue to be open during the restore and recovery of the root SYSAUX tablespace because the SYSAUX tablespace isn’t a critical tablespace in the same sense as the SYSTEM tablespace.

===================================================

Loss of a PDB System Tablespace Data File It’s a little tricky to recover from the loss of a PDB SYSTEM data file. If the PDB was closed, users can continue to work in the other PDBs while you are recovering the SYSTEM tablespace for this PDB. If the PDB was open when you lost its SYSTEM tablespace, however, you’ll need to shut down the CDB first and restart it in the mount state, which means that users in other PDBs can’t work in those PDBs.

Here are the steps if the PDB was closed:

RMAN>restore tablespace pdb1:system

RMAN>recover tablespace pdb1:system

Because the PDB was closed when it lost its SYSTEM tablespace, the CDB isn’t affected and you don’t need to close it while you restore and recover the PDB’s SYSTEM tablespace.

And here’s how to recover from a PDB’s loss of its SYSTEM tablespace when the PDB was opened when it lost the SYSTEM tablespace:

1. Shut down the CDB and restart it in the MOUNT state.

RMAN>shutdown immediate;

RMAN>startup mount;

2. Restore the pluggable database’s SYSTEM tablespace.

RMAN>restore tablespace pdb1:system

3. Recover the PDB’s SYSTEM tablespace.

RMAN>recover tablespace pdb1:system

RMAN>alter database open;

RMAN>alter pluggable database pdb1 opeb

4. Open the CDB.

RMAN>alter database open;

5. Once you open the CDB, you can then open the affected PDB.

RMAN>alter pluggable database pdb1 open;

====================================================

Loss of a PDB Non-System Data File You must first connect to the PDB and take the affected tablespace offline. You can then restore and recover the tablespace while users in other PDBs continue to work in those PDBs. The affected PDB itself can be open as well because you don’t need to close a database during tablespace recovery unless you’re recovering the SYSTEM tablespace. Here are the steps for performing the recovery within the PDB:

1. Connect to the PDB and take the affected tablespaces (the USERS tablespace in this example) offline.
SQL>connect system@pdb1
SQL>alter tablespace users offline immediate;
2. Connect to RMAN (from root).
SQL>connect target/
3. Restore the tablespace USERS in the PDB named pdb1.
RMAN>restore tablespace pdb1:users

4. Recover the USERS tablespace in PDB pdb1.
RMAN>recover tablespace pdb1:users;
5. Bring the recovered tablespace online.
SQL>alter tablespace users online;

Performing a PITR for a PDB