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
Thursday, December 12, 2013
How to turn on trace for awr snapshot action
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
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.dbfSQL> 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 thePROCESSES 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_LIMITcannot 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
Subscribe to:
Posts (Atom)
Posts
