Tag: Oracle dba blog

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