Tag: database 12c

Point-In-Time Recovery for a Pluggable Database

Posted on by Osama Mustafa in Database
Check Pluggable database that you have :

SQL> select name from v$pdbs ;

NAME
——————————
PDB$SEED
TEST_1
TEST_2
TEST_3

Shutdown database , to configure database archivelog

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;

 ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size    2287864 bytes
Variable Size  838862600 bytes
Database Buffers  402653184 bytes
Redo Buffers    8859648 bytes
Database mounted.

SQL> alter database archivelog ;

Database altered.

SQL> alter database open 

Database altered.

SQL> alter pluggable database all open; 

Pluggable database altered.

SQL> alter system set db_recovery_file_dest_size = 2G scope=both; 

System altered.

SQL> alter pluggable database all open; 

Pluggable database altered.

Export ORACLE_SID for container database and enter rman to backup  like below

[oracle@test12c backup]$ export ORACLE_SID=db12c
[oracle@test12c backup]$ rman target ‘”/ as sysbackup”‘

Recovery Manager: Release 12.1.0.1.0 – Production on Sat Jul 13 17:13:42 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB12C (DBID=1274669151)

Auto Backup controlfile :

RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Backup Script that we need :

Run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u01/backup/db12c/%U’;
backup database plus archivelog;
}

 Where 
Format ‘/u01..’:  Location for backup

now move to our pluggable database

SQL>alter session set container=test_1 ;

SQL>create tablespace test_1  datafile ‘/u01/app/oracle/oradata/db12c/test_1/test_1.dbf’ size 10m;

SQL>create user test identified by test temporary tablespace temp default tablespace test_1;

SQL>grant create session, create table, unlimited tablespace to test ;

SQL>create table data (id varchar2(100)) tablespace test_1;

Enter Data using The Below code in above table :

begin
 for i in 1.. 10000 loop
    insert into data values (‘osama’);
 end loop;
 commit;
end;

After you insert data close pluggable database to start restore.

alter pluggable database test_1 CLOSE ;

Restore script :

run {
set until SCN = 1832026 ;
restore pluggable database test_1;
recover pluggable database Test_1 auxiliary destination=’/u01/backup/db12c’;
alter pluggable database TEST_1 open resetlogs;
}

Note :  To get SCN for database before insert use the query below :

SQL > select timestamp_to_scn(sysdate) from v$database;

After Restore :

sqlplus sys/sys@test12c:1521/test_1 ;
SQL> select count(*) from data ;
select count(*) from data
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Thank you
Osama Mustafa

Enterprise manager Performance Hub (Real Time) 12c

Posted on by Osama Mustafa in Database
Enterprise manager express is 

SQL> drop user osama cascade ;
User dropped.

SQL> create user osama identified by osama;
User created.

SQL> grant select any table to osama;
Grant succeeded.

SQL> grant select any dictionary to osama ;
Grant succeeded.

SQL> grant create session to osama;
Grant succeeded.

SQL> conn osama/osama ;
Connected.

SQL> EXEC  DBMS_SQL_MONITOR.BEGIN_OPERATION (‘My_First_JOB’, forced_tracking => ‘Y’);
BEGIN DBMS_SQL_MONITOR.BEGIN_OPERATION (‘My_First_JOB’, forced_tracking => ‘Y’); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: ‘BEGIN_OPERATION’ is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

to avoid this error :

SQL> VAR myjob_id NUMBER;

SQL> EXEC :myjob_id := DBMS_SQL_MONITOR.BEGIN_OPERATION (‘My_First_JOB’, forced_tracking => ‘Y’)

PL/SQL procedure successfully completed.

Where 
myjob_id : variable
My_first_Job : Job name.

Now Check Enterprise manager express Page : 

if you check the second picture you will see if you don’t stop the sql_monitor the Job will still running

SQL> EXEC DBMS_SQL_MONITOR.END_OPERATION(‘My_First_JOB’, :myjob_id) ;
PL/SQL procedure successfully completed.

Thank you 
Osama Mustafa

Another New Features 12c : generated as identity / Sequence Replacement

Posted on by Osama Mustafa in Database
in old version of oracle database if you want to create automatic generated number you have to create sequence and use attribute nextval.

But with oracle database 12c this concept is changed new features add when you create table called generated as identity.

Check the Below Demonstration which explain this new features :

SQL> create table test (test_id number generated as identity , test_name varchar2(20));

SQL> desc test ;
 Name   Null?      Type
 —————————————–   ——– ————–
 TEST_ID   NOT      NULL NUMBER
 TEST_NAME          VARCHAR2(20)

SQL> insert into test values (1,’osama’);
insert into test values (1,’osama’)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

The TEST_ID Column will be inserting automatically  no need to use in insert command.

SQL> insert into test (TEST_NAME) values ( ‘Jennifer’ );
1 row created.

SQL> select * from test ;
   TEST_ID TEST_NAME
———- ——————–
1       Jennifer 

Check another example :

SQL> create table test2 (TEST_ID NUMBER generated as identity (start with 1 increment by 1 cache 30 order), TEST_NAME varchar2(20));
Table created.

SQL> insert into test2 (test_name) values (‘JENNIFER’);
SQL> insert into test2 (test_name) values (‘STEVE’);
SQL> insert into test2 (test_name) values (‘USER’);

SQL> select * from test2 ;

   TEST_ID TEST_NAME
———- ——————–
1 JENNIFER
2 STEVE
3 USER

Thank you 
Osama mustafa