12c, backup/recovery, CDB, Multitenant, PDB, RMAN

Recovery of PDB in Oracle 12c Multitenant Database

November 30, 2015 Larry Catt Leave a comment

Oracle 12c multitenant database can use the Recovery Manager utility or EM Cloud control to recover a database from a previously taken backup set. This procedure outlines the general steps to recover a PDB database using the RMAN utility.

Logon to your database server as the oracle software owner.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:45:07 EST 2015 on pts/1

[oracle@linux2 ~]$

Logon to RMAN utility with sysbackup privileges.

[oracle@linux2 ~]$ rman target ‘”/ as sysbackup”‘

Recovery Manager: Release 12.1.0.2.0 – Production on Thu Dec 1 09:26:07 2015

connected to target database: CDB1 (DBID=898155058)

RMAN>

Place the database in mount mode.

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

RMAN>

Issue the command; RESTORE PLUGGABLE DATABASE <PDB>;

RMAN> RESTORE PLUGGABLE DATABASE pdb1;

Starting restore at 01-DEC-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/cdb1/pdb1/system01.dbf

channel ORA_DISK_1: restoring datafile 00009 to /u01/oradata/cdb1/pdb1/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00010 to /u01/oradata/cdb1/pdb1/pdb1_users01.dbf

channel ORA_DISK_1: restoring datafile 00015 to /opt/app/oradata/pdb1_users.dbf

channel ORA_DISK_1: reading from backup piece /opt/app/oracle/fast_recovery_area/CDB1/3F4D938FE2151097E0534B0F1E0A4221/backupset/2015_12_01/o1_mf_nnndf_TAG20151201T092735_d40dy1qr_.bkp

channel ORA_DISK_1: piece handle=/opt/app/oracle/fast_recovery_area/CDB1/3F4D938FE2151097E0534B0F1E0A4221/backupset/2015_12_01/o1_mf_nnndf_TAG20151201T092735_d40dy1qr_.bkp tag=TAG20151201T092735

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 01-DEC-15

RMAN>

Issue the command RESTORE PLUGGABLE DATABASE <PDB>;

RMAN> RESTORE PLUGGABLE DATABASE pdb1;

Starting restore at 01-DEC-15

using channel ORA_DISK_1

skipping datafile 8; already restored to file /u01/oradata/cdb1/pdb1/system01.dbf

skipping datafile 9; already restored to file /u01/oradata/cdb1/pdb1/sysaux01.dbf

skipping datafile 10; already restored to file /u01/oradata/cdb1/pdb1/pdb1_users01.dbf

skipping datafile 15; already restored to file /opt/app/oradata/pdb1_users.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 01-DEC-15

RMAN>

You can remove any database archive redo logs after they are no longer needed using command: RECOVER DATABASE DELETE ARCHIVELOG;

RMAN> RECOVER DATABASE DELETE ARCHIVELOG;

Starting recover at 01-DEC-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 01-DEC-15

RMAN>

Examine RMAN logs for errors and if no errors open root and PDBs.

ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

RMAN> ALTER DATABASE OPEN;

Statement processed

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Statement processed

RMAN>

This completes recovery of PDB database.

Larry Catt

OCP

12c, Administration, alert / logs, listener, tnsnames

Oracle 12c – Troubleshoot network issues

November 15, 2015 Larry Catt Leave a comment

Troubleshooting of Oracle networking issues can be a bit more complicated than a general database issue because it involves the client configuration, network, and server configuration. On the client side we have determine the method used for connection and that all the parameter used to resolve the server side connection are correct. Ensuring the network connection from the client machine to the server is also critical and will normally include network administrative support. Finally, on the server side, the listener process must be properly configured to accept client connection requests and pass them to the appropriate server process. The entire process of determining the solution to an Oracle networking issue can be very convoluted. It helps to eliminate first what is not the problem. This article outlines steps to address networking issues in a systematic process. It addresses the three areas of network issues separately and if all work should make for a fluid network connection between oracle client and oracle database server. It is assumed that the network connection has worked in the past and all appropriate files have been configured.

Server side network troubleshooting:

Logon to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Thu Jan 5 07:25:33 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Verify that the listener process is running with the command lsnrctl status.

[oracle@linux2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:46:48

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 111: Connection refused

[oracle@linux2 ~]$

The listener process is not running start the process with lsnrctl start.

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:47:01

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 05-JAN-2015 10:47:01

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

Once listener has been started attempt to use the listener with tnsping utility.

[oracle@linux2 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

[oracle@linux2 ~]$

This shows that the listener is accepting connection, but there may be issues in the database such as dispatcher or server processes that are beyond this instruction set.

Network troubleshooting between client and server:

From the client machine open a command prompt, ensure that you can successfully ping the client machine.

C:\Users\MYWINDOWS>ping 192.168.0.102

Pinging 192.168.0.102 with 32 bytes of data:

Reply from 192.168.0.102: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.0.102:

Packets: Sent = 3, Received = 3, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

Minimum = 0ms, Maximum = 0ms, Average = 0ms

Control-C

C:\Users\MYWINDOWS>

Success full connection to server machine. If this connection fails, contact your network administrator.

Client side:

Client side network troubleshooting:

Logon to your oracle client machine with normal user account.
Open a command prompt and attempt a tnsping to your alias machine

C:\Users\MYWINDOWS> tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

C:\Users\MYWINDOWS>

If this fails verify that the values for POTOCAL, HOST, PORT, SERVICE_NAME are correct for the server and database service you are seeking to connect to.

NOTE: You can trace oracle net communications at the server level and client level this gives very detail logs of the communications occurring and very helpful in troubleshooting network connectivity issues.

Larry Catt

OCP

12c, data pump

Oracle 12c – Load and Unload Data with Data Pump Overview

November 14, 2015 Larry Catt Leave a comment

Oracle Data Pump utility is high speed movement of data and metadata between DB. It replaces export/import utility in previous versions. Three parts of Oracle Data Pump are:

EXPDP and IMPDP – command line utility that uses DBMS_DATAPUMP package to execute export and import operations.
DBMS_DATAPUMP – package used to all or part of DB from one DB to another.
DBMS_METADATA – facility to extract, manipulation, and recreation of dictionary metadata.

Data Pump utility runs as a job called the MASTER PROCESS for each data pump execution. This master process can be stopped, restarted, and monitored throughout its execution. A MASTER TABLE is used by data pump to track job while in progress and this master table is stored in the database data pump utility is operating against. The users executing the data pump command must have CREATE TABLE privilege. The Master Table will have the same name as the job that created it and cannot have the same name as an existing table or view. Data in Master table can be used to restart the job. WORKER PROCESSES executed by Master Processes to perform actual processing of the data and metadata for the data pump utility. At any given time a master process can have multiple worker processes running in parallel.

Larry Catt

OCP

12c, CDB, PDB

Instance Parameter for CDB and PDB

November 10, 2015 Larry Catt Leave a comment

In Oracle 12c multitenant databases CDB and PDBs use initialization parameters to control their behavior. Initialization Parameters for CDB and PDB have the following characteristics:

CDB will have one SPFILE.
PDB does not have an SPFILE.
PDB inherit initialization parameters from their CDB.
Most initialization parameters can be changed at the PDB level, these parameters that can have a value of TRUE in the column ISPDB_MODIFIABLE column of V$SYSTEM_PARAMETER view.
Changing initialization parameters use the ALTER SYSTEM command with scope of SPFILE or BOTH and they survive system reboot.

Larry Catt

OCP

12c, security

Oracle 12c – Develop and implement a security policy

November 8, 2015 Larry Catt Leave a comment

Oracle provides excellent protection against data loss from equipment failure, accident, or malicious damage. Administrator should focus on stopping someone from gaining access via stealing passwords, hacking accounts, or accessing restricted data elements.

Security Policy should:

Reduce possibility of unauthorized access to database.
Reduce damage that can be caused by user-level access.
Detect illicit access to the database and restrict data access/damage

Three primary areas to address in Security are:

User Accounts
Privileges and roles given to user
Auditing

Users:

Users must authenticate to the database before access is granted. Most common method is username/password combination. Since 11g, passwords have been case sensitive. 12c provides two new password complexity functions:

ORA12C_VERIFY_FUNCTION (default for 12c using DBCA)
ORA12C_STRONG_VERIFY_FUNCTION

Either function can be modified to increase or customize password complexity. If you create database with CREATE DATABASE command, no password function is used unless you execute the script.

Privileges and Roles:

Best practice is to provide least privilege and roles required for a user account to do their job.

Privileges can be granted:

Explicit – Privilege granted directly to user
Role-based – Privilege granted to a role then the role is granted to one or more users.

Role-based privileges is easy to administer because you classify users into groups. Create a role based on the group and assign members of that group that particular role.

Auditing:

Allows you to monitor and record the actions of user and administrators. In 12c you can use unified audit policies, DBMS_FA package for fine-grained auditing policies. Auditing allows you to determine if malicious activity is in the database.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Automatic Startup all PDB in a CDB

November 6, 2015 Larry Catt Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB. Startup of the PDB must be done after the CDB it is contained in has been started. This procedures covers the implementation of a trigger to startup all PDBs in a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Oct 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

Logon to your CDB where the PDB you wish to start is stored.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 09:41:06 2016

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

View the available PDBs within the CDB with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

—————————— ———-

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

NOTE: MOUNTED is the closed state for a PDB while it CDB is open.

Create a trigger which executes after the CDB starts with the following create trigger statement.

create trigger PDB_OPEN

after startup on database

begin

execute immediate ‘alter pluggable database all open’;

end;

/SQL> 2 3 4 5 6

Trigger created.

SQL>

Now shutdown the CDB with the command: shutdown immediate.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Restart the CDB with command: startup open.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

Check the status of PDBs with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

Note: All PDBs are now open after restart of CDB without the need to manually issue a command from SQL prompt.
This completes startup of all PDB within its CDB automatically.

Larry Catt

OCP

12c, dbca, Multitenant, PDB

Oracle 12c Create PDB

November 2, 2015 Larry Catt Leave a comment

In Oracle 12c, Container Databases (CDB) were introduced which allowed for multiple Pluggable Database (PDB) to be placed in a single oracle instance to reduce the amount of resource consumed. This procedure covers the creation of a PDB in an already existing CDB using Oracle Database Configuration Assistant in a Windows environment.

Logon your windows server as the administrator.

Open Oracle Database Configuration Assistant.

At the Database Operation Page select “Create Database”

At the Manage Pluggable Database Page select “Create a Pluggable Database”

At the Database List Page, select the Container Database CDB you would like your PDB to be created in.

At the Create Pluggable Database page, select “Create a new Pluggable Database” and press Next.

At the Pluggable Database Options page, Enter the name of the Pluggable Database, Storage options, and Password Credentials. NOTE: This is an administrator account inside of the new PDB, not administrator account of OS or CDB. Press Next button.

At the Summary Page, Press the Finish button.

The PDB will now be created in Windows.

A confirmation screen will appear once the install is completed and press the OK button.

Back at the Progress Page, press the Close button to terminate Database Configuration Assistant.

This completes creation of Oracle 12c PDB in an existing CDB with Database Configuration Assistant.

Larry Catt

OCP

Catt Solutions

Monthly Archives: November 2015

Recovery of PDB in Oracle 12c Multitenant Database

Oracle 12c – Troubleshoot network issues

Oracle 12c – Load and Unload Data with Data Pump Overview

Instance Parameter for CDB and PDB

Oracle 12c – Develop and implement a security policy

Automatic Startup all PDB in a CDB

Oracle 12c Create PDB

Oracle tips and tricks.