Welcome to the Oracle community forum and interact with the 2 million technical staff to enter the workshop (u01oradatadb01log_01_db01.rdo) SIZE15M, GROUP2 (u01oradatadb01log_02_db01.rdo) SIZE
Welcome to the Oracle community forum and interact with 2 million technical staff> go to create database db01 maxlogfiles 10 maxdatafiles 1024 maxinstances 2 logfile GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo ') SIZE 15 M, GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE
Welcome to the Oracle community forum and interact with 2 million technical staff> enter
Create database db01
Maxlogfiles 10
Maxdatafiles 1024
Maxinstances 2
Logfile
GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15 M,
GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15 M,
GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15 M,
Datafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100 M,
Undo tablespace UNDO
Datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40 M
Default temporary tablespace TEMP
Tempfile '/u01/oradata/db01/temp_01_db01.dbf' SIZE 20 M
Extent management local uniform size 128 k
Character set AL32UTE8
National character set AL16UTF16
Set time_zone = 'America/New_York ';
############## Data dictionary ##########
Set wrap off
Select * from v $ dba_users;
Grant select on table_name to user/rule;
Select * from user_tables;
Select * from all_tables;
Select * from dba_tables;
Revoke dba from user_name;
Shutdown immediate
Startup nomount
Select * from v $ instance;
Select * from v $ sga;
Select * from v $ tablespace;
Alter session set nls_language = american;
Alter database mount;
Select * from v $ database;
Alter database open;
Desc dictionary
Select * from dict;
Desc v $ fixed_table;
Select * from v $ fixed_table;
Set oracle_sid = foxconn
Select * from dba_objects;
Set serveroutput on
Execute dbms_output.put_line ('sfasd ');
############ Control file ###########
Select * from v $ database;
Select * from v $ tablespace;
Select * from v $ logfile;
Select * from v $ log;
Select * from v $ backup;
/* Backup user tablespace */
Alter tablespace users begin backup;
Select * from v $ archived_log;
Select * from v $ controlfile;
Alter system set control_files = '$ ORACLE_HOME/oradata/u01/ctrl01.ctl ',
'$ ORACLE_HOME/oradata/u01/ctrl02.ctl' scope = spfile;
Cp $ ORACLE_HOME/oradata/u01/ctrl01.ctl $ ORACLE_HOME/oradata/u01/ctrl02.ctl
Startup pfile = '../initSID. ora'
Select * from v $ parameter where name like 'control % ';
Show parameter control;
Select * from v $ controlfile_record_p;
Select * from v $ tempfile;
/* Backup control file */
Alter database backup controlfile to '../filepath/control. Bak ';
/* Back up the control file and change the binary control file to an asc text file */
Alter database backup controlfile to trace;
############## Redo log ##############
Archive log list;
Alter system archive log start; -- start automatic archiving
Alter system switch logfile; -- force a log switch
Alter system checkpoint; -- force a checkpoint
Alter tablspace users begin backup;
Alter tablespace offline;
/* The checkpoint synchronization frequency parameter FAST_START_MTTR_TARGET. The higher the synchronization frequency, the shorter the system recovery time */
Show parameter fast;
Show parameter log_checkpoint;
/* Join a log Group */
Alter database add logfile group 3 ('/$ ORACLE_HOME/oracle/ora_log_file6.rdo' size 10 M );
/* Add a member to the log Group */
Alter database add logfile member '/$ ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;
/* Delete a log group: The current log group cannot be deleted; the active log group cannot be deleted; non-archive log groups cannot be deleted */
Alter database drop logfile group 3;
/* Delete a member in the log group, but the last member in each group cannot be deleted */
Alter databse drop logfile member '$ ORACLE_HOME/oracle/ora_log_file6.rdo ';
/* Clear online logs */
Alter database clear logfile '$ ORACLE_HOME/oracle/ora_log_file6.rdo ';
Alter database clear logfile group 3;
/* Clear non-archive logs */
Alter database clear unarchived logfile group 3;
/* Rename the log file */
Alter database rename file '$ ORACLE_HOME/oracle/ora_log_file6.rdo' to '$ ORACLE_HOME/oracle/ora_log_file6a.rdo ';
Show parameter db_create;
Alter system set db_create_online_log_dest_1 = 'path _ name ';
Select * from v $ log;
Select * from v $ logfile;
/* When the archive mode of a database is switched to the non-archive mode, it must be started to the mount State before it can be changed; startup mount;
Then open the database .*/
Alter database noarchivelog/archivelog;
Achive log start; --- start automatic archiving
Alter system archive all; -- manually archive all log files
Select * from v $ archived_log;
Show parameter log_archive;
###### Analyze the log file logmnr ##############
1) set utl_file_dir parameter in init. ora
2) Restart oracle
3) create directory file
Desc dbms_logmnr_d;
Dbms_logmnr_d.build;
4) add/remove log file
Dhms_logmnr.add_logfile
Dbms_logmnr.removefile
5) start logmnr
Dbms_logmnr.start_logmnr
6) query the analyzed content v $ logmnr_content -- sqlredo/sqlundo
Practice:
Desc dbms_logmnr_d;
/* Perform operations on the data table to prepare for the Restoration Operation */
Update table set qty = 10 where stor_id = 6380;
Delete table where stor_id = 7066;
/***********************************/
Utl_file_dir path
Execute dbms_logmnr_d.build ('foxdict. ora ',' $ ORACLE_HOME/oracle/admin/fox/cdump ');
Execute dbms_logmnr.add_logfile ('$ ORACLE_HOME/oracle/ora_log_file6.log', dbms_logmnr.newfile );
Execute dbms_logmnr.start_logmnr (dictfilename =>; '$ ORACLE_HOME/oracle/admin/fox/cdump/foxdict. ora ');
######### Tablespace ##############
Select * form v $ tablespace;
Select * from v $ datafile;
/* Ing between tablespaces and data files */
Select t1.name, t2.name from v $ tablespace t1, v $ datafile t2 where t1.ts # = t2.ts #;
Alter tablespace users add datafile 'path' size 10 M;
Select * from dba_rollback_segs;
/* Restrict the usage of a tablespace */
Alter user user_name quota 10 m on tablespace_name;
Create tablespace xxx [datafile 'path _ name/datafile_name '] [size xxx] [extent management local/dictionary] [default storage (xxx)];
Exmple: create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 m autoextend on next 5 m maxsize 200 M;
Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management dictionary default storage (initial 100 k next 100 k pctincrease 10) offline;
/* After 9i, oracle recommends using local management instead of dictionary management. because local uses bitmap to manage tablespaces, no voluntary competition for system tablespaces will be generated ;*/
Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management local uniform size 1 m;
Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management local autoallocate;
/* When creating a tablespace, set the segment space management mode in the tablespace. Automatic Management is used here */
Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management local uniform size 1 m segment space management auto;
Alter tablespace userdata mininum extent 10;
Alter tablespace userdata default storage (initial 1 m next 1 m pctincrease 20 );
/* Undo tablespace (cannot be used in dictionary management mode )*/
Create undo tablespace undo1 datafile '$ ORACLE_HOME/oradata/undo101.dbf' size 40 M extent management local;
Show parameter undo;
/* Temporary tablespace */
Create temporary tablespace userdata tempfile '$ ORACLE_HOME/oradata/undo101.dbf' size 10 m extent management local;
[1] [2] [3] [4] [5]