Shulou(Shulou.com)06/01 Report--

I. the client connects to the server

1. View the server listener configuration file (no changes for now)

[root@oracle/] # su-oracle

[oracle@oracle~] $cd $ORACLE_HOME/network/admin

[oracle@oracleadmin] $vim listener.ora

two。 View the instance name of the server

SQL > select instance_name from v$instance

INSTANCE_NAME

Orcl

3. Set up the client profile tnsnames.ora

ORCL = / / ORCL is the connection identifier

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = rhel1.benet.com) (PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = orcl)

)

4. Connect an instance

Please enter a user name: sys/123456@orcl as sysdba

5. Stop the listener

[oracle@rhel1 admin] $lsnrctl stop listener

6. Reconnect

Please enter a user name: sys/123456@orcl as sysdba

ERROR:

ORA-12541: TNS: no listener

7. Create a listener, register statically and connect at the same time

1. Create a new listener

LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522))

(ADDRESS = (PROTOCOL = TCP) (HOST = rhel1.benet.com) (PORT = 1522))

)

)

SID_LIST_LISTENER1 =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl)

(ORACLE_HOME = / opt/oracle/product/11.2/db_1)

(GLOBAL_DBNAME = orclabc)

) / / all () preceded by at least one space

)

two。 Reload the configuration file (can be omitted)

[oracle@rhel1 admin] $lsnrctl reload

3. Start the listener1 listener

The command completed successfully

[oracle@rhel1 admin] $lsnrctl start listener1

4. Modify the tnsnames.ora profile on the client

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = rhel1.benet.com) (PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = orcl)

)

)

ORCL1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = rhel1.benet.com) (PORT = 1522))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = orcl)

)

)

5. Connect the listener1 listener on the client

Please enter a user name: sys/123456@orcl1 as sysdba

Connect to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Management and control documents

1. Get control file information

SQL > select name from v$controlfile

NAME

/ u01/app/oracle/oradata/orcl/control01.ctl

/ u01/app/oracle/oradata/orcl/control02.ctl

two。 Get the contents contained in the control file

SQL > select type,record_size,records_total,records_used from v$controlfile_record_section

3. Create a multiplexing control file

① first creates a directory where the control files are stored, and changes the subordinate oracle.

[oracle@rhel1 ~] $su-root

[root@rhel1] # mkdir-p / backup1/control

[root@rhel1] # mkdir-p / backup2/control

[root@rhel1] # chown-R oracle / backup1

[root@rhel1] # chown-R oracle / backup2

② modifies the contro_files parameter in spfile while the database is still open

[root@rhel1 ~] # su-oracle

[oracle@rhel1 ~] $sqlplus sys/123456 as sysdba

SQL > alter system set

2 control_files=

3'/ opt/oracle/oradata/orcl/control01.ctl'

4'/ backup1/control/control02.ctl'

5'/ backup2/control/control03.ctl' scope=spfile

③ shuts down the database

SQL > shutdown immediate

④ uses operating system commands to copy files to a new location

SQL > quit

[oracle@rhel1 ~] $cp / opt/oracle/oradata/orcl/control01.ctl / backup1/control/control02.ctl

[oracle@rhel1 ~] $cp / opt/oracle/oradata/orcl/control01.ctl / backup2/control/control03.ctl

⑤ restart the database

[oracle@rhel1 ~] $sqlplus sys/123456 as sysdba

SQL > startup

4. Backup and recovery control files

Method 1: directly overwrite the damaged or missing control files with the existing intact control files

① simulates failure, stops database, deletes control files

Sql > shutdown immediat

Sql > quit

$rm-f / badkup1/control/control02.ctl

② starts the database

Sql > startup observation phenomenon

③ recovery control file

Sql > shutdown abort

Sql > quit

$cp / opt/oracle/oradata/orcl/control01.ctl / backup1/control/control02.ctl

④ starts the database again

Sql > startup

Method 2: use special backup database statements

① create backup

SQL > quit

[oracle@rhel1 ~] $su-root

[root@rhel1 ~] # mkdir / opt/oracle/oradata/orcl/backup

[root@rhel1] # chown-R oracle/ opt/oracle/oradata/orcl/backup/

SQL > alter database backup controlfile to'/ opt/oracle/oradata/orcl/backup/control.bkp'

② simulation fault

SQL > shutdown immediate

SQL > quit

[oracle@rhel1 ~] $rm backup1/control/control02.ctl

[oracle@rhel1 ~] $sqlplus sys/123456 as sysdba

SQL > startup

ORACLE instance started.

Total System Global Area 780824576 bytes

Fixed Size 2217424 bytes

Variable Size 490736176 bytes

Database Buffers 281018368 bytes

Redo Buffers 6852608 bytes

ORA-00205: error in identifying control file, check alert log for more info

③ recovery control file

 uses the os command to copy the backup file to the original path, and to maintain consistency, restore a copy of the control file that is not lost

[oracle@rhel1 ~] $cp/opt/oracle/oradata/orcl/backup/control.bkp / opt/oracle/oradata/orcl/control01.ctl

[oracle@rhel1 orcl] $cp / opt/oracle/oradata/orcl/backup/control.bkp / backup1/control/control02.ctl

[oracle@rhel1 orcl] $cp / opt/oracle/oradata/orcl/backup/control.bkp / backup2/control/control03.ctl

[oracle@rhel1 orcl] $sqlplus sys/123456 as sysdba

SQL > alter database mount

 to view the currently active log file

SQL > startup mount

SQL > select group#,sequence#,archived,status from v$log

GROUP# SEQUENCE# ARCHIV STATUS

1 4 NO INACTIVE 3 6 NO CURRENT 2 5 NO INACTIVE

 uses control files to restore the database

SQL > select group#,status,type,member from v$logfile

SQL > recover database using backup controlfile

ORA-00279: change 1039911 generated at 05/30/2017 12:55:30 needed for thread 1

ORA-00289: suggestion:

/ opt/oracle/flash_recovery_area/ORCL/archivelog/2017_05_30/o1_mf_16%u_.arc

ORA-00280: change 1039911 for thread 1 is in sequence # 6 / / enter the current log file below as prompted

/ opt/oracle/oradata/orcl/redo03.log

 opens the database

SQL > alter database open resetlogs

The resetlogs option means that when you want to open the data, reset the redo log, that is, set the sequence of the redo log to zero

three。 Manage redo log files

Use v$log to view redo log information

[oracle@oracle~] $sqlplus / as sysdba

SQL > select group#,sequence#,bytes,members,archived,status from v$log

GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED

STATUS

1 4 209715200 1 NO

CURRENT

2 2 209715200 1 NO

INACTIVE

3 3 209715200 1 NO

INACTIVE

two。 Use v$logfile to view redo log group information

SQL > select group#,status,type,member from v$logfile

GROUP# STATUS TYPEMEMBER 3 ONLINE

/ u01/app/oracle/oradata/orcl/redo03.log

2 ONLINE

/ u01/app/oracle/oradata/orcl/redo02.log

1 ONLINE

/ u01/app/oracle/oradata/orcl/redo01.log

3. Create a redo log group

SQL > quit [oracle@oracle~] $subr/ > [oracle@oracle~] $su

[root@oracleoracle] # mkdir-p / backup/orcl/log

[root@oracleoracle] # chown-R oracle / backup

[oracle@oracle~] $sqlplus / as sysdba

SQL > alter database add logfile group 4

('/ u01qqappqqoracleUnixoradataUniplicoorclUniple redo04a.log'

'/ backup/orcl/log/redo04b.log') size 10m

The database has changed.

4. Delete redo log group 4 (only the log group was deleted, the log files were not deleted)

Check ① before deleting it, and then delete it

SQL > select group#,sequence#,bytes,members,archived,status from v$log

GROUP# SEQUENCE# BYTES MEMBERS ARCHIVEDSTATUS 1 4 209715200 1 NO

CURRENT

2 2 209715200 1 NO

INACTIVE

3 3 209715200 1 NO

INACTIVE

GROUP# SEQUENCE# BYTES MEMBERS ARCHIVEDSTATUS 4 0 10485760 2 YES

UNUSED

SQL > alter database drop logfile group 4

The database has changed.

Description:

① 's current log group cannot be deleted. To delete the current log group, you need to switch the current log group first. Use the command alter system switch logfile

Log groups for ② activities cannot be deleted

Log groups that are not archived by ③ cannot be deleted (provided they are already running in archive mode)

5. Add / remove redo log files, adding one log file to log groups 1 and 2, respectively

① add redo log file

SQL > alter database add logfile member

2'/ backup/orcl/log/redo01a.log' to group 1

3'/ backup/orcl/log/redo02b.log' to group 2

The database has changed.

② deletes log files

SQL > alter database drop logfile member

'/ backup/orcl/log/redo02b.log'

The database has changed.

③ View Log File

SQL > select group#,status,type,member from v$logfile

GROUP# STATUS TYPEMEMBER 3 ONLINE

/ u01/app/oracle/oradata/orcl/redo03.log

2 ONLINE

/ u01/app/oracle/oradata/orcl/redo02.log

1 ONLINE

/ u01/app/oracle/oradata/orcl/redo01.log

GROUP# STATUS TYPEMEMBER 1 INVALID ONLINE

/ backup/orcl/log/redo01a.log

Description:

 cannot delete the members of the current group. If you want to delete it, execute the command to forcibly switch the redo log first.

Log members of  activities cannot be deleted

Log files that are not archived by  cannot be deleted (only if they are already running in archive mode)

The  log group has only one member

6. Log switching and checkpoint Tim

① forcibly switches log files

SQL > alter system swith logfile

② forces checkpoint events

SQL > alter system checkpoint

4. Manage archived log files

Configure database archive log

① looks at the database archive mode and determines that it is not currently in archive mode

SQL > archive log list

Database log mode non-archiving mode

Automatic archiving is disabled

End of Archive / u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

The earliest online log sequence 2

Current log sequence 4

② shuts down the database and starts the database to mount state

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 1593835520 bytes

Fixed Size 8793256 bytes

Variable Size 1023411032 bytes

Database Buffers 553648128 bytes

Redo Buffers 7983104 bytes

The database is loaded.

③ sets the database to archive mode and checks to see if the archive mode has changed

SQL > alter database archivelog

The database has changed.

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

End of Archive / u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

The earliest online log sequence 2

Next archive log sequence 4

Current log sequence 4

SQL > alter database open

The database has changed.

④ views the path of the archive log file

View the currently valid archive log file storage directory

SQL > selectdest_id,dest_name,status,destination from v$archive_dest

DEST_IDDEST_NAMESTATUSDESTINATION 1

LOG_ARCHIVE_DEST_1

VALID

/ u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

DEST_IDDEST_NAMESTATUSDESTINATION 2

LOG_ARCHIVE_DEST_2

INACTIVE// omits some information

SQL > select dest_id,name,archived from v$archived_log

DEST_ID NAME ARCHIV

1/opt/oracle/flash_recovery_area/ORCL/archivelog/2016_11_19/o1_mf_1_8d2yh7kfx.arc YES

⑤ changes the storage path of archived log files

[root@oracleserver~] # mkdir / aa

[root@oracleserver~] # chown-R oracle / aa

[root@oracleserver~] # su-oracle

[oracle@oracleserver~] $sqlplus / as sysdba

SQL > alter system set log_archive_dest='/aa' scope=spfile

Experiment 5: data dictionary management

1. Create a class tablespace with a space size of 100m and put the data files in the / data directory

[root@rhel1 ~] # mkdir / data

[root@rhel1] # chown-R oracle / data

[root@rhel1 ~] # su-oracle

[oracle@rhel1 ~] $sqlplus / as sysdba

SQL > create tablespace t374

Datafile'/ data/t374.dbf' size 100m

two。 Create my own user, default tablespace is class tablespace

SQL > create user name

Identified by 123456

Default tablespace t374

Give it permission to connect to the database and create a view chart.

SQL > grant connect,resource,create view to name; switch your account and create a table student that contains name and passwd fields

SQL > SQL > conn name

SQL > create table student

(name varchar (10)

Passwd varchar (20))

5. Create a view student_view

SQL > create view student_view

2 as

3 select * from student;6. Query which tables the current user has

SQL > desc user_tables

SQL > select * from user_tables

SQL > select table_name fromuser_tables

TABLE_NAMESTUDENT

7. View what views the current user has

SQL > desc user_views

SQL > select view_name fromuser_views

VIEW_NAMESTUDENT_VIEW

8. Query what database objects the current user has

SQL > select object_name from user_objects

OBJECT_NAME

STUDENT_VIEW

STUDENT

9. Query the current user's information, including user id, user status, and default tablespace

① view table structure

SQL > desc user_users

Name Null? Type

USERNAME NOT NULL VARCHAR2 (30)

USER_ID NOT NULL NUMBER

ACCOUNT_STATUS NOT NULL VARCHAR2 (32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2 (30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2 (30)

CREATED NOT NULL DATE

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 (30)

EXTERNAL_NAME

② formatted output

SQL > col user_id for 999

SQL > col account_status for A10

SQL > col default_tablespace for A30

③ query

SQL > select user_id,account_status,default_tablespace from user_users

USER_ID ACCOUNT_ST DEFAULT_TABLESPACE

91 OPEN T374

10. Query all objects that the current user can access

SQL > select owner,object_name,object_type from all_objects

11. Query all data dictionaries and query all tables that start with user

SQL > desc dictionary

Name Null? Type

TABLE_NAME VARCHAR2 (30)

COMMENTS VARCHAR2 (4000)

SQL > select table_name from dictionary where table_name like 'USER%'

twelve。 View scott users' tables and tablespaces

SQL > col owner for A10

SQL > col table_namefor A20

SQL > col tablespace_name for A30

SQL > select owner,table_name,tablespace_name from dba_tables where owner='SCOTT'

OWNER TABLE_NAME TABLESPACE_NAME

SCOTT DEPT USERS

SCOTT EMP USERS

SCOTT BONUS USERS

SCOTT SALGRADE USERS

Experiment 6: dynamic data Dictionary

1. View information related to log files (note uppercase)

SQL > select * from v$fixed_table where name like 'vault log%'

two。 View log group status information

SQL > select group#,members,archived,status from v$log

GROUP# MEMBERS ARCHIV STATUS

1 1 NO INACTIVE 2 1 NO INACTIVE 3 1 NO CURRENT

3. View log file information

SQL > col type for A10

SQL > col group# for 99

SQL > select * fromv$logfile

GROUP# STATUS TYPE MEMBER IS_REC

3 ONLINE / opt/oracle/oradata/orcl/redo03.log NO 2 ONLINE / opt/oracle/oradata/orcl/redo02.log NO 1 ONLINE / opt/oracle/oradata/orcl/redo01.log NO

4. View information about redo log files that are currently in use

SQL > select l.groupwriting from v$log l.archived from v$log l.statusdiary lf.typeredlf.member from v$log l, v$logfilelf where l.group#=lf.group#

GROUP# ARCHIV STATUS TYPE

MEMBER 3 NO INACTIVE ONLINE

/ opt/oracle/oradata/orcl/redo03.log

2 NO INACTIVE ONLINE

/ opt/oracle/oradata/orcl/redo02.log

1 NO CURRENT ONLINE

/ opt/oracle/oradata/orcl/redo01.log

5. View instance information

SQL > col instance_name for A20

SQL > col host_name for A10

SQL > select instance_name,host_name,version,startup_time,logins from v$instance

INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME

LOGINS

Orcl rhel1.bene 11.2.0.1.0 30-MAY-17

T.com

ALLOWED

6. View database information

SQL > col name for A10

SQL > select name,created,log_mode from v$database

NAME CREATED LOG_MODE

ORCL 30-MAY-17 NOARCHIVELOG

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.