Oracle

  1. Copying schema with data pump
  2. Archivelog mode
  3. INCREMENTAL BACKUPS
  4. BACKUP STATUS
  5. Gathering Statistics
  6. Explain plan
  7. Auditing SQL
  8. DBMS_SCHEDULER
  9. Usage of the fast recovery area
  10. Setting fast explain in SQLPLUS
  11. Setting up debugger in SQL Developer
  12. Setting up Oracle 12c em express in Linux 7.2 for a pluggable database
  13. Setting up Oracle 12c release 2 pluggable database flashback
Copying schema with data pump
Archive log mode
Makes hot backups possible.
  1. SQL>shutdown
  2. SQL>strartup mount
  3. SQL>alter database archivelog
  4. SQL>startup open
INCREMENTAL BACKUPS
RMAN Backup Status

select
INPUT_TYPE
,STATUS
,TO_CHAR(START_TIME,'MON DD, HH24:MI:SS') start_time
,TO_CHAR(END_TIME,'MON DD, HH24:MI:SS') end_time
from SYS.v$rman_backup_job_details
Gathering table statistics
exec SYS.dbms_stats.gather_table_stats ('SCOTT','EMP');
table for explain plan
sqlplus you/pwd @${ORACLE_HOME}/rdbms/admin/utlxplan.sql
explain plan for select * from SCOTT.EMP;
Auditing SQL
1 MOVE THE AUDIT TABLE OUT OF SYS TABLESPACE:
select tablespace_name from dba_tables where table_name='AUD$'
THIS WILL MOVE IT TO SYSAUX
execute dbms_audit_mgmt.set_audit_trail_location( AUDIT_TRAIL_TYPE =>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,AUDIT_TRAIL_LOCATION_VALUE =>'SYSAUX');
VERIFY
select tablespace_name from dba_tables where table_name='AUD$'
connect as sysdba and change the parameter in the server file
alter system set audit_trail=db,extended scope=spfile;
Restart the instance
issue an audit statement
AUDIT SELECT ON SCOTT.EMP BY ACCESS WHENEVER SUCCESSFUL
NOW the audit table CLOB field sql_text will be populated with the sql statement
select os_username,userhost,action_name,owner,obj_name,sql_text from dba_audit_trail where username like 'SCOTT%'
TO IMPLEMENT UNIFIED audit policy(Oracle 12C) Oracle needs to be relinked
Minimum Unified policy is enabled by default, so it is possible to query
select os_username,os_process,userhost,authentication_type
,dbusername, sql_text, object_schema,object_name,event_timestamp
from SYS.unified_audit_trail
where sql_text is not null
order by event_timestamp
DBMS_SCHEDULER
as DBA:

grant execute on dbms_scheduler to scott;
grant scheduler_admin to SCOTT;
As SCOTT:
create table SCOTT.MYTAB(f1 number(38);
create or replace procedure insmytab
IS
BEGIN
INSERT INTO SCOTT.MYTAB VALUES(2);
COMMIT;
END;
/
Create a scheduler job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSJOB01'
, job_type => 'STORED_PROCEDURE'
, job_action => 'INSMYTAB'
, start_date => systimestamp
, repeat_interval =>'freq=minutely;interval=12'
, end_date => NULL
, enabled => true
);
END;
/
select * from SYS.dba_scheduler_jobs where owner = 'SCOTT'
Dropping the job:
exec dbms_scheduler.drop_job
(job_name =>'INSJOB01');
Fast recovery area information
Usage in percent for various file types

select
file_type
,percent_space_used
,number_of_files
from SYS.v$recovery_area_usage
Recovery area space limit,location and current used space in GB

select
name
,to_char(space_limit /(1024*1024*1024),'999.99') TOTAL_SIZE_GB
,to_char(space_used /(1024*1024*1024),'999.99') USED_SPACE_GB
,number_of_files
from v$recovery_file_dest
Setting fast explain in SQLPLUS

SQL>set autotrace traceonly explain
Setting up Debugger in SQL Developer

To avoid the ORA-24247 error you will need to grant permissions to user boris_alexandrov in SQLPLUS as DBA:
SQL> grant debug connect session to boris_alexandrov;
SQL> grant debug any procedure to boris_alexandrov;
SQL> grant execute on DBMS_DEBUG_JDWP to boris_alexandrov;
SQL> begin
2 dbms_network_acl_admin.append_host_ace
3 (host=>'192.168.1.194',
4 ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
5 principal_name=>'BORIS_ALEXANDROV',
6 principal_type=>sys.XS_ACL.PTYPE_DB) );
7 end;
8 /
Setting up 12c em express in Linux 7.2 for a pluggable database
Currently Linux does not officially support flash plug-in which is used in EM Express, so it should be set up to be used from a Windows based box.
  1. sqlplus / as sysdba
  2. SQL> alter session set container=pdbcba1;
  3. Check the XDB port in sqlplus:
    SQL> select dbms_xdb.gethttpport() from dual;
  4. If the port is 0 add a new one:
    SQL> exec dbms_xdb_config.sethttpport(8080);
  5. grant a pdb admintrator all permissions on EM(DBA role already includes those):
    SQL> grant EM_EXPRESS_ALL to pdbcba1adm;
  6. Grant a pdb user read permission on EM:
    SQL> grant EM_EXPRESS_BASIC to dev1;
    SQL> grant EM_EXPRESS_BASIC to qa1;
  7. Open the port in firewall as root:
    # firewall-cmd --list-ports
    # firewall-cmd --add-port=8080/tcp --permanent
    # firewall-cmd --reload
    # firewall-cmd --list-ports
  8. In a windows based computer webbrowser, supporting adobe flash, browse to your linux host:port/em
    http://192.168.1.220:8080/em
    login as oracle user
Setting up flashback database for pluggable database in Oracle 12c release 2
  1. Make sure the database is at 12.2.0.0.0 or above
    Connect as sysdba and change the 'compatible' parameter if needed.
    SQL> show parameter compatible
    SQL>alter session set container=cdb$root;
    SQL> alter system set compatible='12.2.0.0.0' scope=spfile;
    SQL>shutdown immediate
    SQL>startup open
    SQL> show parameter compatible
  2. Set up a local undo.
    SQL>conn / as sysdba
    SQL>shutdown immediate
    SQL>startup upgrade
    SQL>alter database local undo on
    SQL>shutdown immediate
    SQL>startup open
    SQL>select name,con_id from v$tablespace
    You should see undo tablespaces for root container and all the pluggable databases, including seed
    verify UNOD is enabled:
    SQL>select property_name,property_value from
    database_properties
    where property_name like '%LOCAL_UNDO%';
  3. Create a restore point in the pluggable database:
    SQL>alter session set container=PDBCBA1
    SQL>create restore point pdb_before_new_tabs;
  4. Create a table or two in the pluggable database
    SQL> create table mynewtable(id number,name varchar2(23));
    SQL> insert into mynewtable values(1,'will loose it');
    SQL> insert into mynewtable values(2,'will loose it too')
    SQL> COMMIT;
  5. Flashback the pluggable database to the state before the tables were created
    SQL>CONN / AS SYSDBA
    SQL> ALTER PLUGGABLE DATABASE pdbcba1 CLOSE;
    SQL> FLASHBACK PLUGGABLE DATABASE pdbcba1 TO RESTORE POINT pdb_before_new_tabs;
    SQL>ALTER PLUGGABLE DATABASE pdbcba1 OPEN RESETLOGS;
    SQL>SELECT * from mynewtable; --- should generate error, as the table should not be present

top of the page