LAZARUS

LAZARUS co to jest z czym to się je ?
To zintegrowane środowisko programistyczne oparte na kompilatorze Free Pascal. Jest wzorowany na Delphi przez co jest prosty w użyciu. Środowisko IDE jest udostępniane na licencji GNU GPL.

Link do pobrania

Tutorial w języku angielskim link do wikipedia

Tutorial ze strony schoolfreeware

Poniżej screen'y z mojej autorskiej aplikacji dodatku do Płatnika ZUS, zestaw narzędzi administracyjnych.
Dane pochodzą z bazy testowej (dane wygenerowane)

Przykładowy ConnectionString dla bazy MSSQL
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=mpo1;Data Source=local\PLATNIK;Initial File Name="";Server SPN=""

PHP data grid

Data Grid - element GUI przedstawiający dane tabelaryczne.
W prosty sposób możemy zaprezentować dane - raporty.

Dla obiektu tabela wykorzystywać możemy

• sortowanie
• filtrowanie
• stronicowanie
• grupowanie
• strukturę hierarchiczną
• edycja wierszy i komórek tabeli
• akcje grupowe....

to wszystko umożliwi nam PHP Grid dostępny na stronie phpgrid.org w dwóch wersjach bezpłatna do pobrania (ograniczenia) i płatna z podziałem na premium 95 $, enterprise 200$ i developer 50 $.
Do testów możemy użyć środowiska XAMPP. Wrzucamy przygotowany projekt do folderu htdocs (domyślnie), aby uruchomić stronę wpisujemy adres localhost\nazwa_projektu

Dla bazy należy skonfigurować połączenie plik config.php
Dla baz MySQL np

define("PHPGRID_DBTYPE","mysql");
define("PHPGRID_DBHOST","localhost");
define("PHPGRID_DBUSER","root");
define("PHPGRID_DBPASS","");
define("PHPGRID_DBNAME","griddemo");

szczegółowy opis znajduje się na stronie

Poniżej zrzut  ekranowy wstępne GUI dla bazy PKWiU

  
Wersja Demo
Tabela porównująca wersje

Kolejnym tego typu produktem jest PHPGrid dostępny na stronie phpgrid.com również w dwóch wersjach. Ceny Basic 95$, Professional 245$, Enterprise 395$ i Super Enterprise 595$
Tabela porównująca wersje. Możesz zapisać się na lekcje przez 7 dni będziesz otrzymywał materiały w postaci kursu oraz przykłady zastosowania tego kodu.

I ostatnia propozycja to zestaw także bezpłatnych skryptów PHP dostępnych na stronie apphp.com
jak ApPhp DataGrid
Więcej informacji uzyskamy na blogu i forum
Bepłatnie skrypty
Wersja Demo
Tabela porównująca wersje

Procedura RMAN

Ustawienie ścieżki kopi
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
2> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\bck\backup\%F';
3> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\bck\backup\ORA_DF%t_s%s_s%p';
4> exit;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\bck\backup\%F';
new RMAN configuration parameters are successfully stored

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'c:\bck\backup\ORA_DF%t_s%s_s%p';
new RMAN configuration parameters are successfully stored

Recovery Manager complete.

--------------------------------------------Jeśli mamy wyłączony tryb noarchivelog

RMAN> backup database;
2> exit;
Starting backup at 15/06/08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/08/2015 13:50:26
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Recovery Manager complete.


----------------------------------Dla przypomnienia włączyć tryb archivelog można

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SQL "ALTER DATABASE ARCHIVELOG";
ALTER DATABASE OPEN;

-----------------------------------Wykonamy Backup typu FULL


RMAN> backup database;
2> exit;
Starting backup at 15/06/08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 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=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
channel ORA_DISK_1: starting piece 1 at 15/06/08
channel ORA_DISK_1: finished piece 1 at 15/06/08
piece handle=C:\BCK\BACKUP\ORA_DF881848488_S2_S1 tag=TAG20150608T135448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 15/06/08

Starting Control File and SPFILE Autobackup at 15/06/08
piece handle=C:\BCK\BACKUP\C-2762965997-20150608-00 comment=NONE
Finished Control File and SPFILE Autobackup at 15/06/08

Recovery Manager complete.

----------------------------Sprawdźmy czy backup został wykonany poleceniem List BACKUP

RMAN> list backup;
2> exit;
using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    755.95M    DISK        00:01:37     15/06/08      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20150608T135448
        Piece Name: C:\BCK\BACKUP\ORA_DF881848488_S2_S1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 379267     15/06/08 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
  2       Full 379267     15/06/08 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
  3       Full 379267     15/06/08 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
  4       Full 379267     15/06/08 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:03     15/06/08      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20150608T135634
        Piece Name: C:\BCK\BACKUP\C-2762965997-20150608-00
  SPFILE Included: Modification time: 15/06/08
  SPFILE db_unique_name: XE
  Control File Included: Ckp SCN: 379313       Ckp time: 15/06/08

Recovery Manager complete.
---------------------------------------------------------Konfiguracja RMAN


RMAN> show all;
2> exit;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name XE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\bck\backup\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'c:\bck\backup\ORA_DF%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SNCFXE.ORA'; # default

Recovery Manager complete.


-----------------------------------------Odtworzenie bazy Restore FULL + rozw. problemów

PROBLEM

RMAN> run {
2> shutdown immediate;startup mount;
3> restore database;recover database;}
4> exit;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 06/08/2015 14:06:21
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Recovery Manager complete.

ROZWIĄZANIE


RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount pfile="C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABA
SE\
2> SPFILE2INIT.ORA";
connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 06/08/2015 14:12:15
RMAN-04014: startup failed: ORA-01078: failure in processing system parameters
RMAN-04017: startup error description: LRM-00109: could not open parameter file
'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPFILE2INIT.ORA'

RMAN> startup nomount;

Oracle instance started

Total System Global Area     552402944 bytes

Fixed Size                     1384872 bytes
Variable Size                171970136 bytes
Database Buffers             373293056 bytes
Redo Buffers                   5754880 bytes

RMAN> startup mount force;

Oracle instance started
database mounted

Total System Global Area     552402944 bytes

Fixed Size                     1384872 bytes
Variable Size                171970136 bytes
Database Buffers             373293056 bytes
Redo Buffers                   5754880 bytes
RMAN> restore database;

Starting restore at 15/06/08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\SYSTEM.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\UNDOTBS1.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\SYSAUX.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\USERS.DBF
channel ORA_DISK_1: reading from backup piece C:\BCK\BACKUP\ORA_DF881848488_S2_S
1
channel ORA_DISK_1: piece handle=C:\BCK\BACKUP\ORA_DF881848488_S2_S1 tag=TAG2015
0608T135448
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 15/06/08

RMAN> recover database;

Starting recover at 15/06/08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 15/06/08

RMAN>

-----------------------------------------Usunięcie starych backupów

RMAN> delete noprompt obsolete;
2> exit;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
no obsolete backups found

Recovery Manager complete.


----------------------------------------Przywrócenie starych ustawień RESET


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
2> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
3> CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
4> exit;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\bck\backup\%F';
RMAN configuration parameters are successfully reset to default value

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'c:\bck\backup\ORA_DF%t_s%s_s%p';
old RMAN configuration parameters are successfully deleted

Recovery Manager complete.

Po przywróceniu starych ustawień backup będzie wykonywany w domyślnej lokalizacji
C:\oraclexe\app\oracle\fast_recovery_area\XE\BACKUPSET\2015_08_03
np pliki
O1_MF_NCSNF_TAG20150608T210236_BQCSSSN8_.BKP -plik kontrolny
O1_MF_NNNDF_TAG20150608T210236_BQCSPF63_.BKP -kopia bazy

Podstawowe komendy RMAN

BACKUP
   
    shutdown immediate;
    startup mount;
    configure controlfile autobackup format for device type disk clear;
    configure retention policy to redundancy 2;
    configure controlfile autobackup on;
    sql "create pfile=''%SPFILE2INIT%'' from spfile";
    backup as backupset device type disk database;
    configure controlfile autobackup off;
    alter database open;
    delete noprompt obsolete;

    //dla trybu archivelog
    configure controlfile autobackup format for device type disk clear;
    configure retention policy to redundancy 2;
    configure controlfile autobackup on;
    sql "create pfile=''%SPFILE2INIT%'' from spfile";
    backup as backupset device type disk database;
    configure controlfile autobackup off;
    sql 'alter system archive log current';
    delete noprompt obsolete;

RESTORE

    shutdown immediate;
    startup nomount pfile="%SPFILE2INIT%";
    restore (spfile from autobackup) (controlfile from autobackup);
    startup mount force;
    configure controlfile autobackup off;
    restore database;

    //dla trybu archivelog
    set  on;
    restore (spfile from autobackup db_recovery_file_dest='%rcvarea_loc%');
    startup nomount force;
    restore (controlfile from autobackup);
    alter database mount;
    configure controlfile autobackup off;
    restore database;

Prosty Restore odtworzenie bazy z kopi

Wykonujemy polecenia

startup mount force;
configure controlfile autobackup off;
restore database;

BACKUP LOGICZNY IMPORTY IMP

Po eksporcie 
exp.exe system/haslo@XE file="exp.dmp"
 
wykonamy import
imp.exe system/haslo@XE FromUser=HR ToUser=HR2 file="exp.dmp"

import wybranych tabel
imp FILE="C:\bck\exp.dmp"
LOG="C:\bck\exp_exp.log"
TABLES=('HR.COUNTRIES', 'HR.DEPARTMENTS', 'HR.EMPLOYEES', 'HR.JOB_HISTORY', 'HR.JOBS', 'HR.LOCATIONS', 'HR.REGIONS')
FEEDBACK=1000

import wszystkich tabel (sys)

imp FILE="C:\bck\exp.dmp"
LOG="C:\bck\imp_exp.log"
FROMUSER=SYS
TOUSER=SYS
IGNORE=Y
FEEDBACK=1000

import tabel bez indeksów, kluczy, uprawnień

imp FILE="C:\bck\exp.dmp"
LOG="C:\bck\imp_exp.log"
FROMUSER=SYS
TOUSER=SYS
GRANTS=N
INDEXES=N
CONSTRAINTS=N
IGNORE=Y
FEEDBACK=1000

import users

imp FILE="C:\bck\exp.dmp"
LOG="C:\bck\imp_exp.log"
FROMUSER=HR
TOUSER=HR
IGNORE=Y
FEEDBACK=1000

import bazy

imp FILE="C:\bck\exp.dmp"
LOG="C:\bck\imp_exp.log"
FULL=Y
TOUSER=HR
IGNORE=Y
FEEDBACK=1000

import tablespace

imp FILE="C:\bck\exp.dmp"
LOG="C:\bck\imp_exp.log"
TRANSPORT_TABLESPACE=Y
DATAFILES=('C:\oraclexe\app\oracle\oradata\XE\USERS.DBF')

Przykład importu
import danych z HR do HR2
wcześniej został utworzony użytkownik HR2

imp FILE="C:\bck\dumphr2.DMP"
LOG="C:\bck\imp_dumphr2.log"
FROMUSER=HR
TOUSER=HR2


Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Export file created by EXPORT:V11.02.00 via conventional pathWarning: the objects were exported by HR, not by you
import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR2
. . importing table                    "COUNTRIES"         25 rows imported
. . importing table                  "DEPARTMENTS"         27 rows imported
. . importing table                    "EMPLOYEES"        107 rows imported
. . importing table                  "JOB_HISTORY"         10 rows imported
. . importing table                         "JOBS"         19 rows imported
. . importing table                    "LOCATIONS"         23 rows imported
. . importing table                      "REGIONS"          4 rows imported
About to enable constraints...
Import terminated successfully without warnings.

Eksport EXP uprawnienia

Aby wykonać eksport bazy lub tablespace musimy posiadać odpowiednie uprawnienia
jeśli nie mamy uprawnień administratora DBA wyświetli się komunikat

EXP-00023: must be a DBA to do Full Database or Tablespace export

trzeba mieć uprawnienia do wykonywania exp typu FULL

musisz nadać

grant create session, create table, create procedure, unlimited tablespace to user_nazwa;
grant read, write on directory &folder_dump_exportu to user_nazwa;
grant exp_full_database, imp_full_database to user_nazwa;

Recovery przywracanie

Recover database; -odtwarza również dzienniki archivelog -jeśli tryb jest wyłączony  warto nie uwzględniać go w skrypcie komenda alter database open resetlogs; dopiero można wykonać.
W przypadku gdy korzystamy dzienników trzeba pamiętać o włączonym trybie archivelog
ponieważ będą problemy z odtwarzaniem bazy np do punktu czasu (restore point)


Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 14:48:28 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XE (DBID=2712378990)

RMAN> set echo on;
2> startup nomount pfile=C:\Users\admin\AppData\Local\Temp\rman_dummy.ora force;

3>
echo set on

Oracle instance started

Total System Global Area     284565504 bytes

Fixed Size                     1383076 bytes
Variable Size                100664668 bytes
Database Buffers             176160768 bytes
Redo Buffers                   6356992 bytes

Recovery Manager complete.

Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 14:49:04 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XE (not mounted)

RMAN> set echo on;
2> shutdown immediate;
3> startup nomount pfile="C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\
SPFILE2INIT.ORA";
4> restore (spfile from autobackup) (controlfile from autobackup);
5> startup mount force;
6> configure controlfile autobackup off;
7> restore database;
8>
echo set on

using target database control file instead of recovery catalog
Oracle instance shut down

connected to target database (not started)
Oracle instance started

Total System Global Area     376635392 bytes

Fixed Size                     1383692 bytes
Variable Size                289407732 bytes
Database Buffers              79691776 bytes
Redo Buffers                   6152192 bytes

Starting restore at 13/12/25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

recovery area destination: C:\oraclexe\app\oracle\fast_recovery_area
database name (or database unique name) used for search: XE
channel ORA_DISK_1: AUTOBACKUP C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTO
BACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring spfile from AUTOBACKUP C:\ORACLEXE\APP\ORACLE\FAST
_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
failover to previous backup

recovery area destination: C:\oraclexe\app\oracle\fast_recovery_area
database name (or database unique name) used for search: XE
channel ORA_DISK_1: AUTOBACKUP C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTO
BACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\ORACLEXE\APP\ORACL
E\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF
Finished restore at 13/12/25

Oracle instance started
database mounted

Total System Global Area     376635392 bytes

Fixed Size                     1383692 bytes
Variable Size                293602036 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6152192 bytes

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

Starting restore at 13/12/25
Starting implicit crosscheck backup at 13/12/25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 13/12/25

Starting implicit crosscheck copy at 13/12/25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13/12/25

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1
_MF_1_1_9COR8T1T_.ARC
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1
_MF_1_2_9COR9622_.ARC
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1
_MF_S_835106118_9COOLFW3_.BKP
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1
_MF_S_835109025_9COR8LSG_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\SYSTEM.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\UNDOTBS1.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\SYSAUX.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\USERS.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLEXE\APP\ORACLE\FAST_RECOVE
RY_AREA\XE\BACKUPSET\2013_12_25\O1_MF_NNNDF_TAG20131225T144217_9COR5V3Q_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\BA
CKUPSET\2013_12_25\O1_MF_NNNDF_TAG20131225T144217_9COR5V3Q_.BKP tag=TAG20131225T
144217
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 13/12/25

Recovery Manager complete.

Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 14:51:06 2013

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

connected to target database: XE (DBID=271755747, not open)

RMAN> set echo on;
2> recover database;
3> alter database open resetlogs;
4>
echo set on

Starting recover at 13/12/25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file C:\ORACLEXE
\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1_MF_1_1_9COR8T1T_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\ORACLEXE
\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1_MF_1_2_9COR9622_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\ORACLEXE
\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_981B260Y_.LOG
archived log file name=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2
013_12_25\O1_MF_1_1_9COR8T1T_.ARC thread=1 sequence=1
archived log file name=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2
013_12_25\O1_MF_1_2_9COR9622_.ARC thread=1 sequence=2
archived log file name=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1
_MF_1_981B260Y_.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:02
Finished recover at 13/12/25

database opened

Recovery Manager complete.
Restore of the database succeeded.
Log file is at C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\OXE_RESTORE
.LOG.

BACKUP LOGICZNY EKSPORTY EXP

Eksport wybranych tabel

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
TABLES=('HR.COUNTRIES', 'HR.DEPARTMENTS', 'HR.EMPLOYEES', 'HR.JOB_HISTORY', 'HR.JOBS', 'HR.LOCATIONS', 'HR.REGIONS')
FEEDBACK=1000

Eksport typu users np HR

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
OWNER=('HR')
FEEDBACK=1000

Eksport bazy

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
FULL=Y
FEEDBACK=1000


Eksport tablespace

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
TRANSPORT_TABLESPACE=Y
TABLESPACES=('USERS' )

Podstawowe skrypty backup i restore

------------- Skrypt BACKUP kopia bazy

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN {
ALLOCATE CHANNEL SQLDEV_DISK_BACKUP DEVICE TYPE DISK;
RECOVER COPY OF DATABASE WITH TAG 'ORA_SQLDEV_LEVEL_0';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE COPIES=1 FOR RECOVER OF COPY WITH TAG 'ORA_SQLDEV_LEVEL_0' DATABASE;
}
ALTER DATABASE OPEN;

--------------Skrypt RESTORE odtworzenie bazy

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN {
RESTORE DATABASE UNTIL TIME "TO_DATE('2014-06-01 18:18:57', 'YYYY-MM-DD HH24:MI:SS')";
}
ALTER DATABASE OPEN RESETLOGS;


---------------Skrypt RESTORE datafiles

RUN {
RESTORE DATAFILE  UNTIL TIME "TO_DATE('2014-06-01 18:21:02', 'YYYY-MM-DD HH24:MI:SS')";
}


---------------Skrypt RESTORE tablespace

RUN {
RESTORE TABLESPACE  UNTIL TIME "TO_DATE('2014-06-01 18:23:42', 'YYYY-MM-DD HH24:MI:SS')";
}


-------------Przyklad restore

Backup bez włączonego trybu archivelog

Jeśli mamy wyłączony tryb archivelog to wyświetli się komunikat

Warning: Log archiving (ARCHIVELOG mode) is currently disabled. If
you restore the database from this backup, any transactions that take
place between this backup and the next backup will be lost. It is
recommended that you enable ARCHIVELOG mode before proceeding so
that all transactions can be recovered upon restore. See the section
'Enabling ARCHIVELOG Mode...' in the online help for instructions.
Backup with log archiving disabled will shut down and restart the
database. Are you sure [Y/N]? y
Backup in progress...

Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 13:55:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XE (DBID=2790037099)

RMAN> set echo on;
2> shutdown immediate;
3> startup mount;
4> configure controlfile autobackup format for device type disk clear;
5> configure retention policy to redundancy 2;
6> configure controlfile autobackup on;
7> sql "create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPF
ILE2INIT.ORA'' from spfile";
8> backup as backupset device type disk database;
9> configure controlfile autobackup off;
10> alter database open;
11> delete noprompt obsolete;
12>
echo set on

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     376635392 bytes

Fixed Size                     1383692 bytes
Variable Size                289407732 bytes
Database Buffers              79691776 bytes
Redo Buffers                   6152192 bytes

RMAN configuration parameters are successfully reset to default value

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

sql statement: create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATAB
ASE\SPFILE2INIT.ORA'' from spfile

Starting backup at 13/12/25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1
.DBF
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.D
BF
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DB
F
input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.D
BF
channel ORA_DISK_1: starting piece 1 at 13/12/25
channel ORA_DISK_1: finished piece 1 at 13/12/25
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\BACKUPSET\2013_12_25\O
1_MF_NNNDF_TAG20131225T135543_9COOGJOP_.BKP tag=TAG20131225T135543 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:04
Finished backup at 13/12/25

Starting Control File and SPFILE Autobackup at 13/12/25
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\
O1_MF_S_835106118_9COOLFW3_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13/12/25

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

database opened

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
no obsolete backups found

Recovery Manager complete.
Backup of the database succeeded.
Log file is at C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\OXE_BACKUP_
CURRENT.LOG.

PEŁNA KULTURA :) BACKUP RMAN

Pełny backup zaczynamy:

rman target \
CONFIGURE CONTROLFILE AUTOBACKUP ON;# Dla wszystkich backupów zawiera plik kontrolny i spfile
CONFIGURE DEFAULT DEVICE TYPE TO DISK;# zmiana nośnika podać nazwę np DH1
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "C:\bck\%F.bck"; # Nazwa pliku i jego lokalizacja dot. pliku kontrolnego
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT "C:\bck\%d-%Y-%M-%D-%p-%s.bck"; # Nazwa pliku i lokalizacja kopii bazy -recovery catalog
CROSSCHECK ARCHIVELOG ALL;# sprawdza czy istnieją dzienniki
BACKUP DATABASE;# kopia bazy zawierająca pliki kontrolne spfile Uwaga baza musi być w trybie Archivelog

#BACKUP INCREMENTAL LEVEL 1 DATABASE;# W przypadku przyrostowego backupu
BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;          
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 3'; # usuwa starsze dzienniki niż 3 dni
                                                      
EXIT;

Problem z dostępem do bazy

Mamy problem z dostępem do bazy

C:\Users\admin>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on N Cze 15 20:23:10 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\11.2.0\serv
er\dbs/spfileXE.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) Nie mo?na odnale?? okre?lonego pliku.
SQL> startup pfile='C:\oraclexe\app\oracle\admin\XE\pfile\initXE.ora'; lub startup nomount pfile='...
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1382120 bytes
Variable Size              92277016 bytes
Database Buffers           50331648 bytes
Redo Buffers                2482176 bytes
Database mounted.
Database opened.
SQL>

nagle odłączenie instancji

shutdown abort;
startup nomount;

Utworzenie pliku spfile na podstawie pliku pfile

CREATE SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs\initXE.ora'FROM PFILE='C:\oraclexe\app\oracle\admin\XE\pfile\initXE.ora';


Pamiętajmy po zmianie parametru init.ora wykonujemy polecenie

create spfile from pfile;

Pomocne zapytania dla admina

Z jaką wersją Oracle mamy do czynienia

SELECT banner FROM v$version WHERE banner LIKE 'Oracle%'; 

Sprawdzenie czy istnieje restore point (odtwarzanie tylko w wersji Enterprise)

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT