Thursday, December 12, 2013

How to turn on trace for awr snapshot action

 
oradebug setmypid
oradebug unlimit
 
alter session set "_swrf_test_action" = 10;
begin
dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND', 
module_name=>'MMON_SLAVE', action_name=>'Auto-Flush Slave Action');
dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND', 
module_name=>'MMON_SLAVE', action_name=>'Remote-Flush Slave Action');
end;
/
 
 
begin
 dbms_workload_repository.create_snapshot();
end;
/
 
 
begin
dbms_monitor.serv_mod_act_trace_disable(service_name=>'SYS$BACKGROUND', 
module_name=>'MMON_SLAVE', action_name=>'Auto-Flush Slave Action');
dbms_monitor.serv_mod_act_trace_disable(service_name=>'SYS$BACKGROUND', 
module_name=>'MMON_SLAVE', action_name=>'Remote-Flush Slave Action');
end;
/
 
alter session set "_swrf_test_action" = 11;
oradebug tracefile_name

Tuesday, December 10, 2013

Listener Registration Process (LREG)

The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net listener (see "The Oracle Net Listener"). When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes it relevant parameters. If it is not running, then LREG periodically attempts to contact it.

Note:

In releases before Oracle Database 12c, PMON performed the listener registration.”

Excerpt From: Corporation, Oracle. “Oracle® Database Concepts, 12c Release 1 (12.1).”

Monday, December 9, 2013

Oracle Database 12c–Move Datafile Clause

Move_datafile_clause

Use the MOVE DATAFILE clause to move an online data file to a new location. The database can be open and accessing the data file when you perform this operation. The database creates a copy of the data file when it is performing this operation. Ensure that there is adequate disk space for the original data file and the copy before using this clause.

You can specify the original data file using the file_name, ASM_filename, or file_number. Refer to ASM_filename for information on ASM file names. If you identify the file by number, then file_number is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view.

Use the TO clause to specify the new file_name or ASM_filename. If you are using Oracle Managed Files, then you can omit the TO clause. In this case, Oracle Database creates a unique name for the data file and saves it in the directory specified by the DB_CREATE_FILE_DEST initialization parameter.

If you specify REUSE, then the new data file is created even if it already exists.

If you specify KEEP, then the original data file will be kept after the MOVE DATAFILE operation. You cannot specify KEEP if the original data file is an Oracle Managed File. You can specify KEEP if the new data file is an Oracle Managed File.

Syntax:

MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
 [ TO ( 'filename' | 'ASM_filename' ) ]
 [ REUSE ] [ KEEP ]

Examples:

SQL> select f.con_id,p.pdb_name,f.file_id,f.file_name
  2  from cdb_data_files f left join cdb_pdbs p
  3  on f.con_id=p.pdb_id
  4  order by f.con_id,f.file_id;

CON_ID PDB_NAME   FILE_ID FILE_NAME
------ ---------- ------- -----------------------------------------------------------------
     1                  1 /u01/app/oracle/oradata/cdborcl/system01.dbf
     1                  3 /u01/app/oracle/oradata/cdborcl/sysaux01.dbf
     1                  4 /u01/app/oracle/oradata/cdborcl/undotbs01.dbf
     1                  6 /u01/app/oracle/oradata/cdborcl/users01.dbf
     2 PDB$SEED         5 /u01/app/oracle/oradata/cdborcl/pdbseed/system01.dbf
     2 PDB$SEED         7 /u01/app/oracle/oradata/cdborcl/pdbseed/sysaux01.dbf
     3 DEMO             8 /u01/app/oracle/oradata/cdborcl/demo/system01.dbf
     3 DEMO             9 /u01/app/oracle/oradata/cdborcl/demo/sysaux01.dbf
     3 DEMO            10 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01.dbf
     3 DEMO            11 /u01/app/oracle/oradata/cdborcl/demo/example01.dbf
     4 PDB1            12 /u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf
     4 PDB1            13 /u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf

12 rows selected.

SQL> show con_id

CON_ID
------------------------------
1
SQL> alter database move datafile 6 to '/u01/app/oracle/oradata/cdborcl/users01_new.dbf';

Database altered.

SQL> ! ls -l /u01/app/oracle/oradata/cdborcl/users01.dbf
ls: cannot access /u01/app/oracle/oradata/cdborcl/users01.dbf: No such file or directory

SQL> ! ls -l /u01/app/oracle/oradata/cdborcl/users01_new.dbf
-rw-r-----. 1 oracle oinstall 5251072 Dec  9 21:06 /u01/app/oracle/oradata/cdborcl/users01_new.dbf

SQL> alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf';
alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "10"

SQL> alter session set container=demo;

Session altered.

SQL> alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf';

Database altered.

SQL> ! ls -l /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
-rw-r-----. 1 oracle oinstall 5251072 Dec  9 21:07 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf

SQL> alter session set container=cdb$root;

Session altered.

SQL> select f.con_id,p.pdb_name,f.file_id,f.file_name
  2  from cdb_data_files f left join cdb_pdbs p
  3  on f.con_id=p.pdb_id
  4  order by f.con_id,f.file_id;

CON_ID PDB_NAME   FILE_ID FILE_NAME
------ ---------- ------- ------------------------------------------------------------------
     1                  1 /u01/app/oracle/oradata/cdborcl/system01.dbf
     1                  3 /u01/app/oracle/oradata/cdborcl/sysaux01.dbf
     1                  4 /u01/app/oracle/oradata/cdborcl/undotbs01.dbf
     1                  6 /u01/app/oracle/oradata/cdborcl/users01_new.dbf
     2 PDB$SEED         5 /u01/app/oracle/oradata/cdborcl/pdbseed/system01.dbf
     2 PDB$SEED         7 /u01/app/oracle/oradata/cdborcl/pdbseed/sysaux01.dbf
     3 DEMO             8 /u01/app/oracle/oradata/cdborcl/demo/system01.dbf
     3 DEMO             9 /u01/app/oracle/oradata/cdborcl/demo/sysaux01.dbf
     3 DEMO            10 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
     3 DEMO            11 /u01/app/oracle/oradata/cdborcl/demo/example01.dbf
     4 PDB1            12 /u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf
     4 PDB1            13 /u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf

12 rows selected.

Alert Log Output:

2013-12-09 21:06:38.668000 +08:00
alter database move datafile 6 to '/u01/app/oracle/oradata/cdborcl/users01_new.dbf'
Moving datafile /u01/app/oracle/oradata/cdborcl/users01.dbf (6) to /u01/app/oracle/oradata/cdborcl/users01_new.dbf
Move operation committed for file /u01/app/oracle/oradata/cdborcl/users01_new.dbf
Completed: alter database move datafile 6 to '/u01/app/oracle/oradata/cdborcl/users01_new.dbf'

2013-12-09 21:07:55.245000 +08:00
alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf'
Moving datafile /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01.dbf (10) to /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
Move operation committed for file /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
Completed: alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf'

Sunday, December 8, 2013

Oracle Database 12c–Create PDB from PDBSEED example

[oracle@gc12c ~]$ sqlplus sys/password@cdborcl as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 8 22:04:50 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ! mkdir /u01/app/oracle/oradata/cdborcl/pdb1

SQL> create pluggable database pdb1
2 admin user pdb1_admin identified by password
3 roles = (DBA)
4 file_name_convert=('/u01/app/oracle/oradata/cdborcl/pdbseed','/u01/app/oracle/oradata/cdborcl/pdb1');

Pluggable database created.

SQL> col pdb_name for a20
SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME STATUS
-------------------- -------------
DEMO NORMAL
PDB$SEED NORMAL
PDB1 NEW

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
DEMO READ WRITE
PDB1 MOUNTED

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
cdborcl 1
cdborclXDB 1
demo 3
pdb1 4

6 rows selected.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME STATUS
-------------------- -------------
DEMO NORMAL
PDB$SEED NORMAL
PDB1 NORMAL

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
DEMO READ WRITE
PDB1 READ WRITE

SQL> select name from v$datafile where con_id=4;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf
/u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf

SQL> alter session set container=pdb1;

Session altered.

SQL> show con_id

CON_ID
------------------------------
4
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdborcl/undotbs01.dbf
/u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf
/u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf

SQL> select con_id,file_name from cdb_data_files order by 1,2;

CON_ID FILE_NAME
------ ----------------------------------------------------------------------
     1 /u01/app/oracle/oradata/cdborcl/sysaux01.dbf
     1 /u01/app/oracle/oradata/cdborcl/system01.dbf
     1 /u01/app/oracle/oradata/cdborcl/undotbs01.dbf
     1 /u01/app/oracle/oradata/cdborcl/users01.dbf
     2 /u01/app/oracle/oradata/cdborcl/pdbseed/sysaux01.dbf
     2 /u01/app/oracle/oradata/cdborcl/pdbseed/system01.dbf
     3 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01.dbf
     3 /u01/app/oracle/oradata/cdborcl/demo/example01.dbf
     3 /u01/app/oracle/oradata/cdborcl/demo/sysaux01.dbf
     3 /u01/app/oracle/oradata/cdborcl/demo/system01.dbf
     4 /u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf
     4 /u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf

12 rows selected.

SQL> alter session set container=cdb$root;

Session altered.

SQL> conn pdb1_admin/password@gc12c:1521/pdb1
Connected.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB1 READ WRITE

SQL> select granted_role from dba_role_privs where grantee='PDB1_ADMIN';

GRANTED_ROLE
--------------------------------------------------------------------------------
PDB_DBA

SQL> conn sys/password@cdborcl as sysdba
Connected.

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> conn pdb1_admin/password@gc12c:1521/pdb1
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Oracle Database 12c–Duplicate database using RMAN (multi-tenant setup)

1. Preparation work

[oracle@gc12c ~]$ sqlplus sys/password@cdborcl as sysdba
SQL> create pfile='?/dbs/initcdbdup.ora' from spfile;
File created.
SQL> exit


[oracle@gc12c ~]$ mkdir /u01/app/oracle/oradata/cdbdup
[oracle@gc12c ~]$ mkdir -p /u01/app/oracle/admin/cdbdup/adump
[oracle@gc12c ~]$ mkdir /u01/app/oracle/oradata/cdbdup/demo
[oracle@gc12c ~]$ mkdir /u01/app/oracle/oradata/cdbdup/pdbseed
[oracle@gc12c ~]$ mkdir /u01/app/oracle/fast_recovery_area/cdbdup

[oracle@gc12c ~]$ sed  's/cdborcl/cdbdup/g' $ORACLE_HOME/dbs/initcdbdup.ora > $ORACLE_HOME/dbs/initcdbdup.ora.new
[oracle@gc12c ~]$ mv $ORACLE_HOME/dbs/initcdbdup.ora.new $ORACLE_HOME/dbs/initcdbdup.ora
[oracle@gc12c ~]$ orapwd  file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwcdbdup password=password

[oracle@gc12c admin]$ echo "
> SID_LIST_LISTENER =
>   (SID_LIST =
>     (SID_DESC =
>       (GLOBAL_DBNAME = cdbdup)
>       (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
>       (SID_NAME = cdbdup)
>     )
>   )
> " >> $ORACLE_HOME/network/admin/listener.ora

[oracle@gc12c ~]$ echo "
> CDBDUP =
>   (DESCRIPTION =
>     (ADDRESS = (PROTOCOL = TCP)(HOST = gc12c)(PORT = 1521))
>     (CONNECT_DATA =
>       (SERVER = DEDICATED)
>       (SERVICE_NAME = cdbdup)
>     )
>   )" >> $ORACLE_HOME/network/admin/tnsnames.ora

2. Startup auxiliary instance and reload listener

[oracle@gc12c ~]$ sqlplus sys/password@cdbdup as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 8 21:25:41 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size             989856440 bytes
Database Buffers          637534208 bytes
Redo Buffers                7135232 bytes
SQL> exit


[oracle@gc12c ~]$ lsnrctl reload
[oracle@gc12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 08-DEC-2013 20:43:16

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gc12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-DEC-2013 20:21:17
Uptime                    0 days 0 hr. 21 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/gc12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gc12c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=gc12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdborcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "cdbdup" has 2 instance(s).
  Instance "cdbdup", status UNKNOWN, has 1 handler(s) for this service...
  Instance "cdbdup", status BLOCKED, has 1 handler(s) for this service...
Service "cdborcl" has 1 instance(s).
  Instance "cdborcl", status READY, has 1 handler(s) for this service...
Service "cdborclXDB" has 1 instance(s).
  Instance "cdborcl", status READY, has 1 handler(s) for this service...
Service "demo" has 1 instance(s).
  Instance "cdborcl", status READY, has 1 handler(s) for this service...
The command completed successfully

3. Startup RMAN duplication command

[oracle@gc12c ~]$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
[oracle@gc12c ~]$ rman target sys/password@cdborcl auxiliary sys/password@cdbdup

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Dec 8 21:26:58 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBORCL (DBID=2818129616)
connected to auxiliary database: CDBDUP (not mounted)

RMAN> run {
2> duplicate target database to cdbdup from active database
3>   db_file_name_convert('cdborcl','cdbdup');
4> }

Starting Duplicate Db at 08-DEC-2013 21:28:11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
current log archived

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1636814848 bytes

Fixed Size                     2288968 bytes
Variable Size               1006633656 bytes
Database Buffers             620756992 bytes
Redo Buffers                   7135232 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CDBORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CDBDUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'cdborcl' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''CDBORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CDBDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1636814848 bytes

Fixed Size                     2288968 bytes
Variable Size               1006633656 bytes
Database Buffers             620756992 bytes
Redo Buffers                   7135232 bytes

Starting restore at 08-DEC-2013 21:28:22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/cdbdup/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/cdbdup/control02.ctl
Finished restore at 08-DEC-2013 21:28:26

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/cdbdup/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/cdbdup/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/cdbdup/undotbs01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/cdbdup/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/cdbdup/users01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/cdbdup/demo/system01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/cdbdup/demo/example01.dbf";
   restore
   from service  'cdborcl'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-DEC-2013 21:28:30
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cdbdup/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cdbdup/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cdbdup/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cdbdup/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cdbdup/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cdbdup/demo/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/cdbdup/demo/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 08-DEC-2013 21:30:39

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'cdborcl'
           archivelog from scn  1899959;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 08-DEC-2013 21:30:39
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdborcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-DEC-2013 21:30:41

datafile 1 switched to datafile copy
input datafile copy RECID=17 STAMP=833664641 file name=/u01/app/oracle/oradata/cdbdup/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=833664641 file name=/u01/app/oracle/oradata/cdbdup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=19 STAMP=833664641 file name=/u01/app/oracle/oradata/cdbdup/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=20 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=21 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=22 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=23 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/demo/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=24 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=25 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=26 STAMP=833664642 file name=/u01/app/oracle/oradata/cdbdup/demo/example01.dbf

contents of Memory Script:
{
   set until scn  1900238;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-DEC-2013 21:30:42
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/CDBDUP/archivelog/2013_12_08/o1_mf_1_26_9b8x4002_.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/CDBDUP/archivelog/2013_12_08/o1_mf_1_27_9b8x40vw_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDBDUP/archivelog/2013_12_08/o1_mf_1_26_9b8x4002_.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/fast_recovery_area/CDBDUP/archivelog/2013_12_08/o1_mf_1_27_9b8x40vw_.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:02
Finished recover at 08-DEC-2013 21:30:46
Oracle instance started

Total System Global Area    1636814848 bytes

Fixed Size                     2288968 bytes
Variable Size               1006633656 bytes
Database Buffers             620756992 bytes
Redo Buffers                   7135232 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CDBDUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''CDBDUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    1636814848 bytes

Fixed Size                     2288968 bytes
Variable Size               1006633656 bytes
Database Buffers             620756992 bytes
Redo Buffers                   7135232 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CDBDUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/cdbdup/system01.dbf',
  '/u01/app/oracle/oradata/cdbdup/pdbseed/system01.dbf',
  '/u01/app/oracle/oradata/cdbdup/demo/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/cdbdup/temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/cdbdup/pdbseed/pdbseed_temp01.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/cdbdup/demo/demo_temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/cdbdup/sysaux01.dbf",
 "/u01/app/oracle/oradata/cdbdup/undotbs01.dbf",
 "/u01/app/oracle/oradata/cdbdup/users01.dbf",
 "/u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf",
 "/u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf",
 "/u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf",
 "/u01/app/oracle/oradata/cdbdup/demo/example01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/cdbdup/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/cdbdup/pdbseed/pdbseed_temp01.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/cdbdup/demo/demo_temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/sysaux01.dbf RECID=1 STAMP=833664663
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/undotbs01.dbf RECID=2 STAMP=833664663
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/users01.dbf RECID=3 STAMP=833664663
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf RECID=4 STAMP=833664663
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf RECID=5 STAMP=833664663
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf RECID=6 STAMP=833664663
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/cdbdup/demo/example01.dbf RECID=7 STAMP=833664663

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=5 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=6 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=7 STAMP=833664663 file name=/u01/app/oracle/oradata/cdbdup/demo/example01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Cannot remove created server parameter file
Finished Duplicate Db at 08-DEC-2013 21:31:39

RMAN>

RMAN> exit


Recovery Manager complete.

4. Possible errors during the duplicate process

RMAN-05609: Must specify a username for target connection when using active duplicate
RMAN-05610: Must specify a username for auxiliary connection when using active duplicate
ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/cdbdup/control02.ctl"
ORA-27040: file create error, unable to create file
RMAN-05520: database name mismatch, auxiliary instance has CDBORCL, command specified CDBDUP

5. What will be the full create controlfile steps?

SQL> alter database  backup controlfile to trace noresetlogs;

Database altered.

SQL> select value from v$diag_info where name ='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdbdup/cdbdup/trace/cdbdup_ora_15099.trc

*** 2013-12-08 21:49:43.031
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="CDBDUP"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CDBDUP" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/fast_recovery_area/CDBDUP/onlinelog/o1_mf_1_9b8x4n3t_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/fast_recovery_area/CDBDUP/onlinelog/o1_mf_2_9b8x4nv6_.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/fast_recovery_area/CDBDUP/onlinelog/o1_mf_3_9b8x4oqq_.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/cdbdup/system01.dbf',
  '/u01/app/oracle/oradata/cdbdup/sysaux01.dbf',
  '/u01/app/oracle/oradata/cdbdup/undotbs01.dbf',
  '/u01/app/oracle/oradata/cdbdup/pdbseed/system01.dbf',
  '/u01/app/oracle/oradata/cdbdup/users01.dbf',
  '/u01/app/oracle/oradata/cdbdup/pdbseed/sysaux01.dbf',
  '/u01/app/oracle/oradata/cdbdup/demo/system01.dbf',
  '/u01/app/oracle/oradata/cdbdup/demo/sysaux01.dbf',
  '/u01/app/oracle/oradata/cdbdup/demo/SAMPLE_SCHEMA_users01.dbf',
  '/u01/app/oracle/oradata/cdbdup/demo/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/CDBDUP/archivelog/2013_12_08/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/CDBDUP/archivelog/2013_12_08/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/cdbdup/temp01.dbf'
     SIZE 62914560  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/cdbdup/pdbseed/pdbseed_temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = DEMO;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/cdbdup/demo/demo_temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- End of tempfile additions.

Create a startup trigger to start all PDBs after DB startup

SQL> create or replace trigger open_all_pdbs
  2    after startup on database
  3  begin
  4    execute immediate 'alter pluggable database all open';
  5  end;
  6  /

Trigger created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size             989856440 bytes
Database Buffers          637534208 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
DEMO                           READ WRITE

Difference between PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT

PGA_AGGREGATE_TARGET

Property	Description
Parameter type	Big integer
Syntax	`PGA_AGGREGATE_TARGET =` `integer` `[K \| M \| G]`
Default value	10 MB or 20% of the size of the SGA, whichever is greater
Modifiable	`ALTER SYSTEM`
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

- Only controls “tunable” memory allocations

- “Tunable” means the oprations can opt to use PGA or temp space, like hash join, sort, etc

- Actual PGA usage is often much higher (3x) since operations for “untunable” memory do not heed this parameter

- Particularly problematic with parallel queries with high DOPs, badly behaved PL/SQL

PGA_AGGREGATE_LIMIT ^{(New in 12c)}

Property	Description
Parameter type	Big integer
Syntax	`PGA_AGGREGATE_LIMIT =` `integer` `[K \| M \| G]`
Default value	By default, `PGA_AGGREGATE_LIMIT` is set to the greater of 2 GB, 200% of `PGA_AGGREGATE_TARGET`, and 3 MB times the`PROCESSES` parameter. It will not exceed 120% of the physical memory size minus the total SGA size.
Modifiable	`ALTER SYSTEM`
Range of values	Oracle recommends that you do not set `PGA_AGGREGATE_LIMIT` below its default value. `PGA_AGGREGATE_LIMIT`cannot be set below its default value except in a text initialization parameter file (pfile) or binary server parameter file (spfile). If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
Basic	No

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.

There is no difference in behavior between PGA_AGGREGATE_LIMIT being explicitly set or being set to the default.

Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.

SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

What is the value for PGA_AGGREGATE_LIMIT if value is 0? Either not set or under AMM control.

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 0

SQL> select name,value,unit from v$pgastat
2 where name in ('aggregate PGA target parameter','aggregate PGA auto target');

NAME                                     VALUE UNIT
----------------------------------- ---------- ------------
aggregate PGA target parameter       587202560 bytes
aggregate PGA auto target            417484800 bytes

Friday, December 6, 2013

How to rebuild Master database in SQL Server 2014

Step 1: shutdown SQL Service (In this case PROD2014)

Step 2: Prepare necessary parameters. If you are not sure, get the list of parameters from:

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\yyyymmdd-hhmiss\ConfigurationFile.ini

Step 3: Run setup command to rebuild the master master after providing these non-default parameters

PS C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14CTP2> .\setup.exe /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=PROD2014 /SQLSYSADMINACCOUNTS=WIN-PNHQAK6JRRE\Administrator /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Appendix: Sample ConfigurationFile.ini

;SQL Server 2014 CTP2 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="Install"

; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system. 

ENU="True"

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block. 

UIMODE="Normal"

; Setup will not display any user interface. 

QUIET="False"

; Setup will display progress only, without any user interaction. 

QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found. 

UpdateEnabled="True"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. 

ERRORREPORTING="False"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components. 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS,DQC,CONN,IS,BOL,SSMS,ADV_SSMS

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services. 

UpdateSource="MU"

; Displays the command line parameters usage 

HELP="False"

; Specifies that the detailed Setup log should be piped to the console. 

INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. 

X86="False"

; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed. 

INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed. 

INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). 

INSTANCENAME="PROD2014"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. 

SQMREPORTING="False"

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. 

INSTANCEID="PROD2014"

; RSInputSettings_RSInstallMode_Description 

RSINSTALLMODE="FilesOnlyMode"

; Specify the installation directory. 

INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Agent account name 

AGTSVCACCOUNT="NT Service\SQLAgent$PROD2014"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Automatic"

; Startup type for Integration Services. 

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account. 

ISSVCACCOUNT="NT Service\MsDtsServer120"

; The name of the account that the Analysis Services service runs under. 

ASSVCACCOUNT="NT Service\MSOLAP$PROD2014"

; Controls the service startup type setting after the service has been created. 

ASSVCSTARTUPTYPE="Automatic"

; The collation to be used by Analysis Services. 

ASCOLLATION="Latin1_General_CI_AS"

; The location for the Analysis Services data files. 

ASDATADIR="C:\Program Files\Microsoft SQL Server\MSAS12.PROD2014\OLAP\Data"

; The location for the Analysis Services log files. 

ASLOGDIR="C:\Program Files\Microsoft SQL Server\MSAS12.PROD2014\OLAP\Log"

; The location for the Analysis Services backup files. 

ASBACKUPDIR="C:\Program Files\Microsoft SQL Server\MSAS12.PROD2014\OLAP\Backup"

; The location for the Analysis Services temporary files. 

ASTEMPDIR="C:\Program Files\Microsoft SQL Server\MSAS12.PROD2014\OLAP\Temp"

; The location for the Analysis Services configuration files. 

ASCONFIGDIR="C:\Program Files\Microsoft SQL Server\MSAS12.PROD2014\OLAP\Config"

; Specifies whether or not the MSOLAP provider is allowed to run in process. 

ASPROVIDERMSOLAP="1"

; Specifies the list of administrator accounts that need to be provisioned. 

ASSYSADMINACCOUNTS="WIN-PNHQAK6JRRE\Administrator"

; Specifies the server mode of the Analysis Services instance. Valid values are MULTIDIMENSIONAL and TABULAR. The default value is MULTIDIMENSIONAL. 

ASSERVERMODE="MULTIDIMENSIONAL"

; CM brick TCP communication port 

COMMFABRICPORT="0"

; How matrix will use private networks 

COMMFABRICNETWORKLEVEL="0"

; How inter brick communication will be protected 

COMMFABRICENCRYPTION="0"

; TCP port used by the CM brick 

MATRIXCMBRICKCOMMPORT="0"

; Startup type for the SQL Server service. 

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express. 

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine. 

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account. 

SQLSVCACCOUNT="NT Service\MSSQL$PROD2014"

; Windows account(s) to provision as SQL Server system administrators. 

SQLSYSADMINACCOUNTS="WIN-PNHQAK6JRRE\Administrator"

; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express. 

ADDCURRENTUSERASSQLADMIN="False"

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol. 

NPENABLED="0"

; Startup type for Browser Service. 

BROWSERSVCSTARTUPTYPE="Automatic"

; Specifies which account the report server NT service should execute under.  When omitted or when the value is empty string, the default built-in account for the current operating system.
; The username part of RSSVCACCOUNT is a maximum of 20 characters long and
; The domain part of RSSVCACCOUNT is a maximum of 254 characters long. 

RSSVCACCOUNT="NT Service\ReportServer$PROD2014"

; Specifies how the startup mode of the report server NT service.  When 
; Manual - Service startup is manual mode (default).
; Automatic - Service startup is automatic mode.
; Disabled - Service is disabled 

RSSVCSTARTUPTYPE="Automatic"

; Add description of input argument FTSVCACCOUNT 

FTSVCACCOUNT="NT Service\MSSQLFDLauncher$PROD2014"

Thursday, December 5, 2013

How to tuning Oracle performance in RAC

Learning points from Oracle Expert
1. Focus on the INSERT, UPDATE and DELETE intensive indexes and tablespace (READS will always scale well)
2. Focus on "write hot spots". Use "non-ordered" with ultra large cache size for sequence, as well as hash partition index/reverse index
3. Random access to database is not an issue,frequently modified small tables are interesting
4. A small subset of statements and objects causes 90% of performance issues
5. Standard SQL and schema tuning solves > 80% of performance problems
6. Standard common sense system tuning practices should be applied
7. Almost everything can be scaled out quickly with load-direction and load balancing via services

Wednesday, December 4, 2013

Oracle Database 12c–Recover Table using RMAN

Please take note that demo database is a pluggable database, although the table recovery shall be the same regardless of PDB or not, with minor syntax difference.

1. Perform the backup

[oracle@gc12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 4 20:41:25 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBORCL (DBID=2818129616)

RMAN> backup database demo;

Starting backup at 04-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/cdborcl/demo/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/cdborcl/demo/example01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/cdborcl/demo/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-DEC-13
channel ORA_DISK_1: finished piece 1 at 04-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/EC8587CDE0150D19E043C906A8C0B994/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T204133_99y8qy4k_.bkp tag=TAG20131204T204133 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 04-DEC-13

Starting Control File and SPFILE Autobackup at 04-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/autobackup/2013_12_04/o1_mf_s_833316139_99y8scgp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-DEC-13

RMAN> exit

2. Simulate disaster

[oracle@gc12c ~]$ sqlplus hr/hr@localhost:1521/demo

SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 4 20:43:07 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Sat May 25 2013 04:22:56 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> truncate table employee2;

Table truncated.

3. Perform Table Recovery

[oracle@gc12c ~]$ export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
[oracle@gc12c ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 4 21:17:27 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBORCL (DBID=2818129616)

RMAN> recover table hr.employee2 of pluggable database demo until time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')"
2> auxiliary destination '/tmp' remap table hr.employee2:employee2_restored;

Starting recover at 04-DEC-2013 21:19:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='CEee'

initialization parameters used for automatic instance:
db_name=CDBORCL
db_unique_name=CEee_pitr_demo_CDBORCL
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/tmp
log_archive_dest_1='location=/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDBORCL

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                281019648 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5480448 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 04-DEC-2013 21:19:17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDBORCL/autobackup/2013_12_04/o1_mf_s_833316139_99y8scgp_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/autobackup/2013_12_04/o1_mf_s_833316139_99y8scgp_.bkp tag=TAG20131204T204219
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/CDBORCL/controlfile/o1_mf_99ybyp57_.ctl
Finished restore at 04-DEC-2013 21:19:19

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  8 to new;
set newname for clone datafile  9 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 8, 9;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/CDBORCL/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /tmp/CDBORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 04-DEC-2013 21:19:23
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/CDBORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/CDBORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/CDBORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDBORCL/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T203105_99y849k3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T203105_99y849k3_.bkp tag=TAG20131204T203105
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:57
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /tmp/CDBORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /tmp/CDBORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDBORCL/EC8587CDE0150D19E043C906A8C0B994/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T204133_99y8qy4k_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/EC8587CDE0150D19E043C906A8C0B994/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T204133_99y8qy4k_.bkp tag=TAG20131204T204133
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 04-DEC-2013 21:20:45

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=833318445 file name=/tmp/CDBORCL/datafile/o1_mf_system_99ybyvx8_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=833318445 file name=/tmp/CDBORCL/datafile/o1_mf_undotbs1_99ybyvy0_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=833318445 file name=/tmp/CDBORCL/datafile/o1_mf_sysaux_99ybyvxw_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=15 STAMP=833318445 file name=/tmp/CDBORCL/datafile/o1_mf_system_99yc0nj2_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=833318445 file name=/tmp/CDBORCL/datafile/o1_mf_sysaux_99yc0nhg_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone 'DEMO' "alter database datafile
 8 online";
sql clone 'DEMO' "alter database datafile
 9 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "DEMO":"SYSTEM", "DEMO":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  9 online

Starting recover at 04-DEC-2013 21:20:45
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/CDBORCL/archivelog/2013_12_04/o1_mf_1_15_99y90sr7_.arc
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDBORCL/backupset/2013_12_04/o1_mf_annnn_TAG20131204T203240_99y87926_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/backupset/2013_12_04/o1_mf_annnn_TAG20131204T203240_99y87926_.bkp tag=TAG20131204T203240
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/tmp/1_14_833108818.dbf thread=1 sequence=14
archived log file name=/u01/app/oracle/fast_recovery_area/CDBORCL/archivelog/2013_12_04/o1_mf_1_15_99y90sr7_.arc thread=1 sequence=15
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-DEC-2013 21:20:48

sql statement: alter database open read only

contents of Memory Script:
{
sql clone 'alter pluggable database  DEMO open read only';
}
executing Memory Script

sql statement: alter pluggable database  DEMO open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/tmp/CDBORCL/controlfile/o1_mf_99ybyp57_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                285213952 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/tmp/CDBORCL/controlfile/o1_mf_99ybyp57_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                285213952 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  11 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  11;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 04-DEC-2013 21:21:13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /tmp/CEEE_PITR_DEMO_CDBORCL/datafile/o1_mf_example_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDBORCL/EC8587CDE0150D19E043C906A8C0B994/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T204133_99y8qy4k_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDBORCL/EC8587CDE0150D19E043C906A8C0B994/backupset/2013_12_04/o1_mf_nnndf_TAG20131204T204133_99y8qy4k_.bkp tag=TAG20131204T204133
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 04-DEC-2013 21:21:28

datafile 11 switched to datafile copy
input datafile copy RECID=18 STAMP=833318488 file name=/tmp/CEEE_PITR_DEMO_CDBORCL/datafile/o1_mf_example_99yc29jc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'DEMO' "alter database datafile
 11 online";
# recover and open resetlogs
recover clone database tablespace  "DEMO":"EXAMPLE", "SYSTEM", "UNDOTBS1", "SYSAUX", "DEMO":"SYSTEM", "DEMO":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  11 online

Starting recover at 04-DEC-2013 21:21:28
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/CDBORCL/archivelog/2013_12_04/o1_mf_1_15_99y90sr7_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDBORCL/archivelog/2013_12_04/o1_mf_1_15_99y90sr7_.arc thread=1 sequence=15
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-DEC-2013 21:21:29

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  DEMO open';
}
executing Memory Script

sql statement: alter pluggable database  DEMO open

contents of Memory Script:
{
# create directory for datapump import
sql 'DEMO' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp''";
# create directory for datapump export
sql clone 'DEMO' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_CEee_uCuC":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "HR"."EMPLOYEE2"                            17.07 KB     107 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_CEee_uCuC" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_CEee_uCuC is:
   EXPDP>   /tmp/tspitr_CEee_70300.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_CEee_uCuC" successfully completed at Wed Dec 4 21:22:14 2013 elapsed 0 00:00:19
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_CEee_yigA" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_CEee_yigA":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "HR"."EMPLOYEE2_RESTORED"                   17.07 KB     107 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_CEee_yigA" successfully completed at Wed Dec 4 21:22:33 2013 elapsed 0 00:00:13
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_temp_99yc1py4_.tmp deleted
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_temp_99yc1kdl_.tmp deleted
auxiliary instance file /tmp/CEEE_PITR_DEMO_CDBORCL/onlinelog/o1_mf_3_99yc2tmo_.log deleted
auxiliary instance file /tmp/CEEE_PITR_DEMO_CDBORCL/onlinelog/o1_mf_2_99yc2sr7_.log deleted
auxiliary instance file /tmp/CEEE_PITR_DEMO_CDBORCL/onlinelog/o1_mf_1_99yc2sc8_.log deleted
auxiliary instance file /tmp/CEEE_PITR_DEMO_CDBORCL/datafile/o1_mf_example_99yc29jc_.dbf deleted
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_sysaux_99yc0nhg_.dbf deleted
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_system_99yc0nj2_.dbf deleted
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_sysaux_99ybyvxw_.dbf deleted
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_undotbs1_99ybyvy0_.dbf deleted
auxiliary instance file /tmp/CDBORCL/datafile/o1_mf_system_99ybyvx8_.dbf deleted
auxiliary instance file /tmp/CDBORCL/controlfile/o1_mf_99ybyp57_.ctl deleted
auxiliary instance file tspitr_CEee_70300.dmp deleted
Finished recover at 04-DEC-2013 21:22:35

RMAN> quit

4. Post verification (RMAN New Features)

[oracle@gc12c ~]$ rman target sys/password@localhost:1521/demo

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 4 21:23:17 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDBORCL (DBID=2818129616)

RMAN> select count(*) from hr.employee2_restored;

using target database control file instead of recovery catalog
  COUNT(*)
----------
       107

RMAN> exit


Recovery Manager complete.

5. Possible errors

Error 1:

RMAN> recover table hr.employee2 of pluggable database demo until time "to_date('04-dec 20:43:07','dd-mon hh24:mi:ss')" preview;

Starting recover at 04-DEC-13
using target database control file instead of recovery catalog
current log archived
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/04/2013 20:46:17
RMAN-05063: Cannot recover specified tables
RMAN-05112: table "HR"."EMPLOYEE2" already exists

Error 2:

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_dCcB_Enrl" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_dCcB_Enrl":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> ORA-39083: Object type TABLE:"HR"."EMPLOYEES_RESTORED" failed to create with error:
ORA-02264: name already used by an existing constraint
Failing sql is:
CREATE TABLE "HR"."EMPLOYEES_RESTORED" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20 BYTE), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, "JOB_ID" VARCH
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
   IMPDP> ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"HR" skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed
   IMPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
   IMPDP> ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"HR"."EMPLOYEES" creation failed

Donghua's Blog - DBAGlobe

Thursday, December 12, 2013

How to turn on trace for awr snapshot action

Tuesday, December 10, 2013

Listener Registration Process (LREG)

Monday, December 9, 2013

Oracle Database 12c–Move Datafile Clause

Syntax:

Examples:

Alert Log Output:

Sunday, December 8, 2013

Oracle Database 12c–Create PDB from PDBSEED example

Oracle Database 12c–Duplicate database using RMAN (multi-tenant setup)

1. Preparation work

2. Startup auxiliary instance and reload listener

3. Startup RMAN duplication command

4. Possible errors during the duplicate process

5. What will be the full create controlfile steps?

Create a startup trigger to start all PDBs after DB startup

Difference between PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT

PGA_AGGREGATE_TARGET

PGA_AGGREGATE_LIMIT ^{(New in 12c)}

What is the value for PGA_AGGREGATE_LIMIT if value is 0? Either not set or under AMM control.

Friday, December 6, 2013

How to rebuild Master database in SQL Server 2014

Thursday, December 5, 2013

How to tuning Oracle performance in RAC

Wednesday, December 4, 2013

Oracle Database 12c–Recover Table using RMAN

1. Perform the backup

2. Simulate disaster

3. Perform Table Recovery

4. Post verification (RMAN New Features)

5. Possible errors

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

Thursday, December 12, 2013

Tuesday, December 10, 2013

Monday, December 9, 2013

Syntax:

Examples:

Alert Log Output:

Sunday, December 8, 2013

1. Preparation work

2. Startup auxiliary instance and reload listener

3. Startup RMAN duplication command

4. Possible errors during the duplicate process

5. What will be the full create controlfile steps?

PGA_AGGREGATE_TARGET

PGA_AGGREGATE_LIMIT (New in 12c)

What is the value for PGA_AGGREGATE_LIMIT if value is 0? Either not set or under AMM control.

Friday, December 6, 2013

Thursday, December 5, 2013

Wednesday, December 4, 2013

1. Perform the backup

2. Simulate disaster

3. Perform Table Recovery

4. Post verification (RMAN New Features)

5. Possible errors

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

PGA_AGGREGATE_LIMIT ^{(New in 12c)}