Active Data Guard

In the database has been started in SQL*Plus using STARTUP MOUNT and the database is subsequently opened read only, then invoking managed recovery will enable Active Data Guard. For example:

[oracle@server14]$ sqlplus / as sysdba
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 6497189888 bytes
Fixed Size 2238672 bytes
Variable Size 3372222256 bytes
Database Buffers 3103784960 bytes
Redo Buffers 18944000 bytes
Database mounted
Database opened

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE
WITH SESSION SHUTDOWN;

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

Curiosidades de PLSQL - exec keyword

this is the first one:

var a number
exec :a := 1
exec for a in 1..10 loop null; end loop;

this is another one:

exec open :cur for select * from dual;

this is to generate lots of gets:

desc sydney_demo1
desc sydney_demo2
desc sydney_demo3

alter session set plsql_optimize_level=0;

set timing on
exec for i in 1..&1 loop insert into sydney_demo1 values (0); end loop;

set timing off termout off
rollback;
alter system checkpoint;
set timing on  termout on

exec for i in 1..&1 loop insert into sydney_demo2 values (i); end loop;

set timing off termout off
rollback;
alter system checkpoint;
set timing on  termout on

exec for i in 1..&1 loop insert into sydney_demo3 values (i); end loop;

set timing off termout off
rollback;
alter system checkpoint;
set timing on  termout on

set timing off

Generating load in parallel

create or replace procedure do_insert
as
    l_stop  date default sysdate+5/24/60;
    l_date  date default sysdate;
begin
    while (l_date < l_stop)
    loop
        insert into test (x)
        values (sysdate)
        returning x into l_date;
        commit;
    end loop;
end;
/

exec perfstat.statspack.snap;

declare
    l_job number;
begin
    for i in 1 .. 5
    loop
        dbms_job.submit( l_job, 'do_insert;' );
    end loop;
    commit;
end;
/

Wait until all jobs has been finished using the following statement. If you get no more rows as a result, the jobs has been completed.

select substr(job,1,4) "job",
       substr(schema_user,1,10) "user",
       substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
       substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
       substr(broken,1,2) "b",
       substr(failures,1,6) "failed",
       substr(what,1,32) "command"
  from dba_jobs;

PLSQL: declaring variables

There are a several ways of declaring variables in SQL*Plus scripts.

The first is to use VAR. The mechanism for assigning values to a VAR is with an EXEC call:

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.

Alternativley we can use subsitution variables. These are good for interactive mode:

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

When we're writing a script which calls other scripts it can be useful to DEFine the variables upfront:
SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

Finally there's the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:
SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

RMAN backup script to FRA

$rman_script="backup incremental level 0 cumulative device type disk tag '%TAG' database;
backup device type disk tag '%TAG' archivelog all not backed up;
allocate channel for maintenance type disk;
crosscheck backup;
crosscheck archivelog all;
delete force noprompt expired archivelog all;
delete force noprompt obsolete device type disk;
release channel;";
&br_save_agent_env();
&br_prebackup($l_db_connect_string, $l_is_cold_backup, $l_use_rcvcat, $l_db_10_or_higher, $l_backup_strategy, "TRUE");
my $result = &br_backup();
exit($result);